This article describes how to connect a Postgres database to Peliqan. This is referred to as an “external DB connection” in Peliqan, a direct DB connection is made to query data, without duplicating the data to the Peliqan data warehouse.

Connect Postgres

In Peliqan, go to Connections > Add Connection > Select Postgres in the list > Enter the details of your Postgres instance:

SSL

Peliqan will connect with SSL enabled first. If that fails, Peliqan will try to connect without SSL.

IP whitelisting

Make sure to add Peliqan’s IP addresses if IP whitelisting is active on your database. More info:

Permissions in Postgres

Make sure to configure following permissions in Postgres, so that Peliqan can discover schemas and tables in your DB.

Create user

CREATE USER peliqan WITH PASSWORD 'xxxxxxxxxxxxx';

Grant all permissions

Grant all permissions:

GRANT ALL PRIVILEGES ON DATABASE "mydb" to peliqan;

Grant granular read-only permissions

Grant USAGE on one schema:

GRANT USAGE ON SCHEMA myschema TO peliqan;

Grant USAGE on all schemas:

DO $$
BEGIN
  EXECUTE (
    SELECT string_agg(format('GRANT USAGE ON SCHEMA %I TO peliqan;', schema_name), ' ')
    FROM information_schema.schemata
    WHERE schema_name NOT IN ('pg_catalog', 'information_schema')
  );
END;
$$;

Grant SELECT on all tables in a schema:

GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO peliqan;

Add permissions on new tables:

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO peliqan;