Looker supports connections to [Cloud SQL for PostgreSQL](https://cloud.google.com/sql/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](https://docs.cloud.google.com/looker/docs/2622/enabling-secure-db-access.md.txt) documentation page.

If you're interested in using SSL encryption, see the [PostgreSQL documentation](https://www.postgresql.org/docs/).

## Connect using Application Default Credentials (ADC)

For [Looker (Google Cloud core)](https://docs.cloud.google.com/looker/docs/looker-core-overview.md.txt) instances, [Application Default Credentials (ADC)](https://docs.cloud.google.com/docs/authentication/application-default-credentials.md.txt) 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](https://docs.cloud.google.com/looker/docs/looker-core-dialects.md.txt#add-the-impersonated-service-account)
- [Set up service account impersonation on your Cloud SQL database](https://docs.cloud.google.com/looker/docs/looker-core-dialects.md.txt#set-up-service-account-impersonation)
- [Run additional configuration commands for Cloud SQL for PostgreSQL](https://docs.cloud.google.com/looker/docs/looker-core-dialects.md.txt#additional-commands-postgres)

See the [Looker (Google Cloud core) documentation](https://docs.cloud.google.com/looker/docs/looker-core-dialects.md.txt#using-application-default-credentials-to-connect-to-a-database) 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)](https://docs.cloud.google.com/looker/docs/looker-core-overview.md.txt) instance and you want to use ADC, use the impersonated service account username that you [added to your Cloud SQL database](https://docs.cloud.google.com/looker/docs/looker-core-dialects.md.txt#add-the-impersonated-service-account). 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.

   > [!NOTE]
   > **Note:** Skip this step if you're on a Looker (Google Cloud core) instance and you want to use [Application Default Credentials](https://docs.cloud.google.com/looker/docs/looker-core-dialects.md.txt#using-application-default-credentials-to-connect-to-a-database). You don't need to create a user account: instead, you will use an existing service account.

   ```sql
   CREATE USER USERNAME WITH ENCRYPTED PASSWORD 'PASSWORD';
   ```
2. Grant permissions to the database user so that Looker can perform actions on your database:

   ```sql
   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:

   ```sql
   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:

   ```sql
   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:

```sql
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:

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

See [`ALTER DEFAULT PRIVILEGES`](https://www.postgresql.org/docs/9.4/sql-alterdefaultprivileges.html) on PostgreSQL's website for more information.

## Temp schema setup

Create a scratch schema:

```sql
CREATE SCHEMA SCHEMA_NAME;
```

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

```sql
GRANT USERNAME TO ADMIN_USER;
```

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

```sql
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:

```sql
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
```

> [!NOTE]
> **Note:** 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](https://docs.cloud.google.com/looker/docs/looker-core-dialects.md.txt#add-the-impersonated-service-account). The service account username will have the format `service-<project number>@gcp-sa-looker.iam.gserviceaccount.com`. If the 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`.

## 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 Looker** page by doing one of the following:

   - Click the **Main menu** icon and select **Admin** , and then select **Connections** from the **Database** section in the **Admin** panel. On the **Connections** page, click the **Add Connection** button.
   - Click the **Create** button in the [main navigation menu](https://docs.cloud.google.com/looker/docs/2622/finding-content.md.txt#using_the_main_navigation_menu), and then select the **Connection** menu item.
2. From the **Dialect** drop-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](https://docs.cloud.google.com/looker/docs/2622/connecting-to-your-db.md.txt) documentation page for more information.

4. To verify that the connection is successful, click the **Test Connection** button in the **Status details** section at the bottom of the **Connections Settings** page.

5. If Looker displays **Can Connect** , press **Connect** to create the connection.

Your database connection is then added to the list on the Looker [**Connections**](https://docs.cloud.google.com/looker/docs/2622/admin-panel-database-connections.md.txt) **Admin** page.

## Supported JDBC parameters

For Google Cloud SQL for PostgreSQL, Looker supports the following JDBC parameters in the [**Additional JDBC parameters**](https://docs.cloud.google.com/looker/docs/2622/connecting-to-your-db.md.txt#additional_jdbc_parameters) field for the connection. See the documentation for your database for information on these parameters.

- `allowEncodingChanges`
- `ApplicationName`
- `assumeMinServerVersion`
- `binaryTransfer`
- `binaryTransferDisable`
- `binaryTransferEnable`
- `cancelSignalTimeout`
- `connectTimeout`
- `currentSchema`
- `databaseMetadataCacheFields`
- `defaultRowFetchSize`
- `escapeSyntaxCallMode`
- `gssEncMode`
- `hostRecheckSeconds`
- `jaasApplicationName`
- `jaasLogin`
- `kerberosServerName`
- `loadBalanceHosts`
- `loginTimeout`
- `logServerErrorDetail`
- `options`
- `password`
- `preferQueryMode`
- `preparedStatementCacheQueries`
- `prepareThreshold`
- `queryTimeout`
- `readOnly`
- `reWriteBatchedInserts`
- `socketTimeout`
- `ssl`
- `sslfactory`
- `sslhostnameverifier`
- `sslmode`
- `sslNegotiation`
- `sslpassword`
- `sslpasswordcallback`
- `stringtype`
- `targetServerType`
- `tcpKeepAlive`
- `unknownLength`
- `user`

## 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.22:

| Feature | Supported? |
|---|---|
| Looker (Google Cloud core) | Yes |
| Symmetric aggregates | Yes |
| Derived tables | Yes |
| Persistent SQL derived tables | Yes |
| Persistent native derived tables | Yes |
| Stable views | Yes |
| Query killing | Yes |
| SQL-based pivots | Yes |
| Time zones | Yes |
| SSL | Yes |
| Subtotals | Yes |
| JDBC additional params | Yes |
| Case-sensitive | Yes |
| Location type | Yes |
| List type | Yes |
| Percentile | Yes |
| Distinct percentile | Yes |
| SQL Runner Show Processes | Yes |
| SQL Runner Describe Table | Yes |
| SQL Runner Show Indexes | Yes |
| SQL Runner Select 10 | Yes |
| SQL Runner Count | Yes |
| SQL Explain | Yes |
| OAuth 2.0 credentials | No |
| Context comments | Yes |
| Connection pooling | Yes |
| HLL sketches | No |
| Aggregate awareness | Yes |
| Incremental PDTs | Yes |
| Milliseconds | Yes |
| Microseconds | Yes |
| Materialized views | No |
| Period-over-period measures | No |
| Approximate count distinct | No |
| In-database analytic models | No |
| Custom calendars | No |