User Guide Getting Started Help Center Documentation Community Training
  
English
日本語
PostgreSQL

Dialects Using these Instructions

Postgres, including Google Cloud PostgreSQL, share database setup requirements as described on this page. However, for PostgresSQL on Heroku please see this page.

Encrypting Network Traffic

Looker strongly recommends encrypting network traffic between the Looker application and your database. Consider one of the options described here.

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;

Postgres Versions pre 9.2

For Postgres versions prior to 9.2, non-superusers do not have the necessary permissions to kill connections. To work around this, a custom function created with a security definer can be used instead, as shown below (see this article for further details):

CREATE OR REPLACE FUNCTION pg_kill_connection(integer) RETURNS boolean AS 'select pg_terminate_backend($1);' LANGUAGE SQL SECURITY DEFINER;

Postgres Versions pre 9.0

Additionally, for Postgres versions prior to 9.0, you cannot grant SELECT to multiple tables at the same time. You must execute a command for each table like this:

GRANT SELECT ON TABLE public.table1 TO looker; GRANT SELECT ON TABLE public.table2 TO looker; … GRANT SELECT ON TABLE public.tableN TO looker;

If you wish to GRANT SELECT on all of your tables to Looker you can generate the statements by executing this query:

SELECT 'GRANT SELECT ON ' || relname || ' TO looker;' FROM pg_class JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace WHERE nspname = 'public' AND relkind IN ('r', 'v', 'S');

Just copy and paste the output from this query into your psql session or database GUI.

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;

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:

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

Feature Support

Looker’s ability to provide some features depends on whether the database dialect can support them.

In the current Looker release, PostgreSQL supports the following Looker features:

Next Steps

After completing the database configuration, you can connect to the database from Looker using these directions.

Top