PostgreSQL

Dialects that use these instructions

The following dialects share database setup requirements as described on this page:

• PostgreSQL
• Microsoft Azure PostgreSQL
• AlloyDB for PostgreSQL
• Amazon Aurora PostgreSQL
• Amazon RDS for PostgreSQL

For AlloyDB for PostgreSQL, Amazon RDS for PostgreSQL, and Amazon Aurora PostgreSQL, Looker has integration support .

For PostgreSQL on Heroku, see the Heroku documentation .

For Google Cloud SQL for PostgreSQL, see our dedicated Google Cloud SQL for PostgreSQL page.

Encrypting network traffic

It is a best practice to encrypt network traffic between the Looker application and your database. Consider one of the options described on the Enabling secure database access documentation page.

If you're interested in using SSL encryption, see the PostgreSQL documentation .

Users and security

To perform actions on your database, Looker needs to have a user account on your database.

To configure a database user for Looker to use, perform the following steps on your database:

1. Create a database user and password.

     CREATE 
     
    USER 
     
     USERNAME 
    
     
    WITH 
     
    ENCRYPTED 
     
    PASSWORD 
     
    & #39;PASS 
   WOR 
    
   D' ; 
   

2. Grant permissions to the database user so that Looker can perform actions on your database:

     GRANT 
     
    CONNECT 
     
    ON 
     
    DATABASE 
     
     DATABASE_NAME 
    
     
    to 
     
     USERNAME 
    
    ; 
    \ 
    c 
     
     DATABASE_NAME 
    
    GRANT 
     
    SELECT 
     
    ON 
     
    ALL 
     
    SEQUENCES 
     
    IN 
     
    SCHEMA 
     
    public 
     
    TO 
     
     USERNAME 
    
    ; 
    GRANT 
     
    SELECT 
     
    ON 
     
    ALL 
     
    TABLES 
     
    IN 
     
    SCHEMA 
     
    public 
     
    TO 
     
     USERNAME 
    
    ; 
    
   

3. If you're using a schema other than public , run this command to grant usage permissions to Looker:

     GRANT 
     
    USAGE 
     
    ON 
     
    SCHEMA 
     
     SCHEMA_NAME 
    
     
    TO 
     
     USERNAME 
    
    ; 
    
   

4. To make sure that future tables that you add to the public schema are also available to the Looker user, run these commands:

     ALTER 
     
    DEFAULT 
     
    PRIVILEGES 
     
    IN 
     
    SCHEMA 
     
    public 
     
    GRANT 
     
    SELECT 
     
    ON 
     
    tables 
     
    TO 
     
     USERNAME 
    
    ; 
    ALTER 
     
    DEFAULT 
     
    PRIVILEGES 
     
    IN 
     
    SCHEMA 
     
    public 
     
    GRANT 
     
    SELECT 
     
    ON 
     
    sequences 
     
    TO 
     
     USERNAME 
    
    ; 
    
   

Depending on your setup, the preceding commands may need to be altered. If another user or role is creating tables that the Looker user needs future permissions for, you must specify a target role or user to apply the Looker user's permission grants to:

  ALTER 
  
 DEFAULT 
  
 PRIVILEGES 
  
 FOR 
  
 USER 
  
  ANOTHER_USERNAME 
 
  
 IN 
  
 SCHEMA 
  
  SCHEMA_NAME 
 
  
 GRANT 
  
 SELECT 
  
 ON 
  
 tables 
  
 TO 
  
  USERNAME 
 
 ; 
 ALTER 
  
 DEFAULT 
  
 PRIVILEGES 
  
 FOR 
  
 ROLE 
  
  TARGET_ROLE 
 
  
 IN 
  
 SCHEMA 
  
  SCHEMA_NAME 
 
  
 GRANT 
  
 SELECT 
  
 ON 
  
 sequences 
  
 TO 
  
  USERNAME 
 
 ; 
 

For example, if a web_app user creates tables and you want the looker user to be able to use those tables, you must run a GRANT statement to give the looker user permissions on tables that are created by the web_app user. The target role or user in this case is the web_app user, meaning you want to alter privileges on tables that are created by web_app so that the looker user can have permissions to read the tables. Here is an example:

  ALTER 
  
 DEFAULT 
  
 PRIVILEGES 
  
 FOR 
  
 USER 
  
 web_app 
  
 IN 
  
 SCHEMA 
  
 public 
  
 GRANT 
  
 SELECT 
  
 ON 
  
 tables 
  
 TO 
  
 looker 
 ; 
 

See ALTER DEFAULT PRIVILEGES on PostgreSQL's website for more information.

Temp schema setup

Self-hosted Postgres

Create a schema owned by the Looker user:

  CREATE 
  
 SCHEMA 
  
  SCHEMA_NAME 
 
  
 AUTHORIZATION 
  
  USERNAME 
 
 ; 
 

Postgres on Amazon RDS

Create a scratch schema:

  CREATE 
  
 SCHEMA 
  
  SCHEMA_NAME 
 
 ; 
 

Change the ownership of the scratch schema to the Looker user:

  ALTER 
  
 SCHEMA 
  
  SCHEMA_NAME 
 
  
 OWNER 
  
 TO 
  
  USERNAME 
 
 ; 
 

Setting the search_path

Before connecting Looker to your database, you should set an appropriate search_path , which the Looker SQL Runner can use to retrieve certain metadata from your database:

  ALTER 
  
 USER 
  
  USERNAME 
 
  
 SET 
  
 search_path 
  
 TO 
  
 '$us er' 
 
 , 
 SCHE MA_NAME 
 
 , 
 SCHEMA _NAME_2 
 
 , 
 SCHEMA_NAME_3 
  
 ^^^^^^^^^^^^^^^^^^ 
  
 ^^^^^^^^^^^^^^^^^^ 
  
 include 
  
 a 
  
 comma 
 - 
 separated 
  
 list 
  
 of 
  
 all 
  
 schemas 
  
 you 
 ' 
 ll 
  
 use 
  
 
 with 
  
 Looker 

Creating the Looker connection to your database

Follow these steps to create the connection from Looker to your database:

1. In the Adminsection of Looker, select Connections, and then click Add Connection.

2. From the Dialectdrop-down menu, select your database dialect name:

   • For Microsoft Azure PostgreSQL, select Microsoft Azure PostgreSQL.
   • For PostgreSQL, select PostgreSQL 9.5+or PostgreSQL pre-9.5, depending on your version of PostgreSQL.
   • For AlloyDB for PostgreSQL, Amazon RDS for PostgreSQL, and Amazon Aurora PostgreSQL, select PostgreSQL 9.5+.

3. Fill out the connection details. The majority of the settings are common to most database dialects. See the Connecting Looker to your database documentation page for information.

4. To verify that the connection is successful, click Test. See the Testing database connectivity documentation page for troubleshooting information.

5. To save these settings, click Connect.

Feature support

For Looker to support some features, your database dialect must also support them.

PostgreSQL 9.5+ supports the following features as of Looker 26.0:

Microsoft Azure PostgreSQL supports the following features as of Looker 26.0: