Dialects that use these instructions
PostgreSQL, Google Cloud PostgreSQL, and Microsoft Azure PostgreSQL share database setup requirements as described on this page. However, for PostgreSQL on Heroku, see the Heroku documentation.
Encrypting network traffic
Looker strongly recommends encrypting 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:
If you’re using a schema other than public
, run this command to grant usage permissions to Looker:
To make sure that future tables you add to the public schema are also available to the looker
user, run these commands:
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:
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:
See ALTER DEFAULT PRIVILEGES
on PostgresSQL’s website for more information.
Temp schema setup
Self-hosted Postgres
Create a schema owned by the looker
user:
Postgres on Amazon RDS
Create a scratch schema:
Change the ownership of the scratch schema to the looker
user:
For PostgreSQL persistent derived tables to work, you must not be using Looker on top of a “hot swap” streaming replication database. You must be able to write to the database from outside the application.
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
, the command is:
Adding the connection
After completing the database configuration, you can connect to the database from Looker. In the Admin section of Looker, select Connections, and then select New Connection.
Fill out the connection details. The majority of these settings are common to most database dialects. They are described on the Connecting Looker to your database documentation page.
After configuring the connection options, click on Test These Settings to verify that the connection to PostgreSQL is successful. See the Testing database connectivity documentation page for troubleshooting information.
Click Add Connection to save the connection.
Feature support
For Looker to support some features, your database dialect must also support them.
In Looker 22.6, PostgreSQL supports the following Looker features:
In Looker 22.6, Google Cloud PostgreSQL supports the following Looker features:
In Looker 22.6, Microsoft Azure PostgreSQL supports the following Looker features: