Dialects that use these instructions
The following dialects share database setup requirements as described on this page:
- PostgreSQL
- Google Cloud SQL for PostgreSQL
- Microsoft Azure PostgreSQL
- AlloyDB for PostgreSQL
- Amazon Aurora PostgreSQL
- Amazon RDS for PostgreSQL
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
Change some_password_here to a unique, secure password:
CREATE USER looker WITH ENCRYPTED PASSWORD 'some_password_here';
GRANT CONNECT ON DATABASE database_name to looker;
\c database_name
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO looker;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO looker;
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 looker;
To make sure that future tables 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 looker;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON sequences TO looker;
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 for:
ALTER DEFAULT PRIVILEGES FOR USER <USER_WHO_CREATES_TABLES> IN SCHEMA public GRANT SELECT ON tables TO looker;
ALTER DEFAULT PRIVILEGES FOR ROLE <ROLE_THAT_CREATES_TABLES> IN SCHEMA public GRANT SELECT ON sequences TO looker;
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/user in this case is the web_app user, meaning we are targeting 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 looker_scratch AUTHORIZATION looker;
Postgres on Amazon RDS
Create a scratch schema:
CREATE SCHEMA looker_scratch;
Change the ownership of the scratch schema to the looker user:
ALTER SCHEMA looker_scratch OWNER TO looker;
Setting the search_path
Finally, you should set an appropriate search_path, which Looker's SQL Runner uses to retrieve certain metadata from your database. Assuming you have created a user called looker, and a temp schema called looker_scratch, run the following command:
ALTER USER looker SET search_path TO '$user',looker_scratch,schema_of_interest,public
^^^^^^^^^^^^^^^^^^
^^^^^^^^^^^^^^^^^^
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:
- In the Admin section of Looker, select Connections, and then click Add Connection.
From the Dialect drop-down menu, select your database dialect name. For the AlloyDB for PostgreSQL dialect, select PostgreSQL 9.5+.
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.
To verify that the connection is successful, click Test. See the Testing database connectivity documentation page for troubleshooting information.
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 24.6:
| Feature | Supported? |
|---|---|
| Support Level | 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 |
| Timezones | 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 Credentials | No |
| Context Comments | Yes |
| Connection Pooling | Yes |
| HLL Sketches | No |
| Aggregate Awareness | Yes |
| Incremental PDTs | Yes |
| Milliseconds | Yes |
| Microseconds | Yes |
| Materialized Views | No |
| Approximate Count Distinct | No |
Google Cloud PostgreSQL supports the following features as of Looker 24.6:
| Feature | Supported? |
|---|---|
| Support Level | 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 |
| Timezones | 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 Credentials | No |
| Context Comments | Yes |
| Connection Pooling | Yes |
| HLL Sketches | No |
| Aggregate Awareness | Yes |
| Incremental PDTs | Yes |
| Milliseconds | Yes |
| Microseconds | Yes |
| Materialized Views | No |
| Approximate Count Distinct | No |
Microsoft Azure PostgreSQL supports the following features as of Looker 24.6:
| Feature | Supported? |
|---|---|
| Support Level | 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 |
| Timezones | 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 Credentials | No |
| Context Comments | Yes |
| Connection Pooling | Yes |
| HLL Sketches | No |
| Aggregate Awareness | Yes |
| Incremental PDTs | Yes |
| Milliseconds | Yes |
| Microseconds | Yes |
| Materialized Views | No |
| Approximate Count Distinct | No |