Google Cloud SQL for PostgreSQL

Looker supports connections to Cloud SQL for PostgreSQL , a fully managed database service that helps you set up, maintain, manage, and administer your PostgreSQL relational databases on Google Cloud.

This page provides the instructions for connecting to Cloud SQL for PostgreSQL from Looker.

Encrypting network traffic

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

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

Connect using Application Default Credentials (ADC)

For Looker (Google Cloud core) instances, Application Default Credentials (ADC) is supported as a method of authentication for Google Cloud SQL for PostgreSQL. When you set up ADC for Google Cloud SQL for PostgreSQL, you must do the following procedures:

• Add the impersonated service account to your Cloud SQL database
• Set up service account impersonation on your Cloud SQL database
• Run additional configuration commands for Cloud SQL for PostgreSQL

See the Looker (Google Cloud core) documentation for the full procedure.

Users and security

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

If you're on a Looker (Google Cloud core) instance and you want to use ADC, use the impersonated service account username that you added to your Cloud SQL database . The service account username will have the format service-<project number>@gcp-sa-looker.iam.gserviceaccount.com . If your service account username ends with .gserviceaccount.com , remove the .gserviceaccount.com portion of the username. After it's truncated, the username would look like service-<project number>@gcp-sa-looker.iam .

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 
     
    ' PASSWORD 
   ' 
    ; 
    
   

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

Create a scratch schema:

  CREATE 
  
 SCHEMA 
  
  SCHEMA_NAME 
 
 ; 
 

Grant the admin user to be a member of the user role:

  GRANT 
  
  USERNAME 
 
  
 TO 
  
  ADMIN_USER 
 
 ; 
 

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 
  
 '$user' 
 , 
  SCHEMA_NAME 
 
 , 
  SCHEMA_NAME_2 
 
 , 
  SCHEMA_NAME_3 
 
  
 ^^^^^^^^^^^^^^^^^^ 
  
 ^^^^^^^^^^^^^^^^^^ 
  
 include 
  
 a 
  
 comma 
 - 
 separated 
  
 list 
  
 of 
  
 all 
  
 schemas 
  
 that 
  
 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. Open the Connect your database to Lookerpage by doing one of the following:

   • Click the menu Main menuicon and select Admin, and then select Connectionsfrom the Databasesection in the Adminpanel. On the Connectionspage, click the Add Connectionbutton.
   • Click the Createbutton in the main navigation menu , and then select the Connectionmenu item.

2. From the Dialectdrop-down menu, select Google Cloud PostgreSQL.

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 more information.

4. To verify that the connection was 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.

Google Cloud PostgreSQL supports the following features as of Looker 26.2: