Connecting BI to PostgreSQL ¶

A read-only database user is available for connecting Business Intelligence (BI) tools to import or directly query the database. The database port is currently available via direct TCP/IP over the public internet.

Connection settings ¶

Host: db.bi.indevets.com
Port: 5432
Database name: prod_indevets
Database username: obtain from credential vault
Database password: obtain from credential vault

Example shell connection ¶

docker pull postgres:11-alpine

docker run --rm -it postgres:11-alpine \
    psql \
        -h db.bi.indevets.com \
        -p 5432 \
        -U dumper \
        prod_indevets

Creating read-only users ¶

To create a new read-only login for a BI user with access to all schema, run the following script (replacing NEWUSER and NEWPASSWORD before running):

CREATE ROLE NEWUSER LOGIN PASSWORD 'NEWPASSWORD';
GRANT CONNECT ON DATABASE prod_indevets TO NEWUSER;

GRANT USAGE ON SCHEMA public TO NEWUSER;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO NEWUSER;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO NEWUSER;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO NEWUSER;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO NEWUSER;

GRANT USAGE ON SCHEMA data TO NEWUSER;
GRANT SELECT ON ALL TABLES IN SCHEMA data TO NEWUSER;
ALTER DEFAULT PRIVILEGES IN SCHEMA data GRANT SELECT ON TABLES TO NEWUSER;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA data TO NEWUSER;
ALTER DEFAULT PRIVILEGES IN SCHEMA data GRANT USAGE, SELECT ON SEQUENCES TO NEWUSER;

GRANT USAGE ON SCHEMA goals TO NEWUSER;
GRANT SELECT ON ALL TABLES IN SCHEMA goals TO NEWUSER;
ALTER DEFAULT PRIVILEGES IN SCHEMA goals GRANT SELECT ON TABLES TO NEWUSER;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA goals TO NEWUSER;
ALTER DEFAULT PRIVILEGES IN SCHEMA goals GRANT USAGE, SELECT ON SEQUENCES TO NEWUSER;

GRANT USAGE ON SCHEMA greenhouse TO NEWUSER;
GRANT SELECT ON ALL TABLES IN SCHEMA greenhouse TO NEWUSER;
ALTER DEFAULT PRIVILEGES IN SCHEMA greenhouse GRANT SELECT ON TABLES TO NEWUSER;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA greenhouse TO NEWUSER;
ALTER DEFAULT PRIVILEGES IN SCHEMA greenhouse GRANT USAGE, SELECT ON SEQUENCES TO NEWUSER;

GRANT USAGE ON SCHEMA helpers TO NEWUSER;
GRANT SELECT ON ALL TABLES IN SCHEMA helpers TO NEWUSER;
ALTER DEFAULT PRIVILEGES IN SCHEMA helpers GRANT SELECT ON TABLES TO NEWUSER;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA helpers TO NEWUSER;
ALTER DEFAULT PRIVILEGES IN SCHEMA helpers GRANT USAGE, SELECT ON SEQUENCES TO NEWUSER;

GRANT USAGE ON SCHEMA paycor TO NEWUSER;
GRANT SELECT ON ALL TABLES IN SCHEMA paycor TO NEWUSER;
ALTER DEFAULT PRIVILEGES IN SCHEMA paycor GRANT SELECT ON TABLES TO NEWUSER;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA paycor TO NEWUSER;
ALTER DEFAULT PRIVILEGES IN SCHEMA paycor GRANT USAGE, SELECT ON SEQUENCES TO NEWUSER;

GRANT USAGE ON SCHEMA reports TO NEWUSER;
GRANT SELECT ON ALL TABLES IN SCHEMA reports TO NEWUSER;
ALTER DEFAULT PRIVILEGES IN SCHEMA reports GRANT SELECT ON TABLES TO NEWUSER;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA reports TO NEWUSER;
ALTER DEFAULT PRIVILEGES IN SCHEMA reports GRANT USAGE, SELECT ON SEQUENCES TO NEWUSER;

GRANT USAGE ON SCHEMA staging TO NEWUSER;
GRANT SELECT ON ALL TABLES IN SCHEMA staging TO NEWUSER;
ALTER DEFAULT PRIVILEGES IN SCHEMA staging GRANT SELECT ON TABLES TO NEWUSER;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA staging TO NEWUSER;
ALTER DEFAULT PRIVILEGES IN SCHEMA staging GRANT USAGE, SELECT ON SEQUENCES TO NEWUSER;

GRANT USAGE ON SCHEMA zoho TO NEWUSER;
GRANT SELECT ON ALL TABLES IN SCHEMA zoho TO NEWUSER;
ALTER DEFAULT PRIVILEGES IN SCHEMA zoho GRANT SELECT ON TABLES TO NEWUSER;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA zoho TO NEWUSER;
ALTER DEFAULT PRIVILEGES IN SCHEMA zoho GRANT USAGE, SELECT ON SEQUENCES TO NEWUSER;