How to create a new DB user?

Octolis needs to access your databases for two distinct reasons:

Using a DB as Datawarehouse (we will create three schemas for our own usage and tables for Sources, Datasets, and Syncs inside of them, see ).

If your want to plug your DB as Connection from which Octolis needs to retrieve data.

In the first case, Octolis will need read & write access to your DB: create schemas, tables, functions, sequences...

In the second case, Octolis only needs to read some tables inside of your database.

We promise we will never write/modify anything outside of the three schemas we manage, but you might still want to create specific users for us to make sure we can’t. Here below is an example of how you can achieve this with PostgreSQL.

Create a readwrite role & user (for the Datawarehouse or for a Connection used as a Source & Destination).

Create role, user & grant rights

Don’t forget to replace {{adminuser}}, {{dbname}} and {{user_password}}.

javascript
-- Create role client_readwrite
CREATE ROLE client_readwrite;

-- Grant rights to role to access the DB and create schemas
GRANT CONNECT ON DATABASE "{{dbname}}" TO client_readwrite;
GRANT CREATE ON DATABASE "{{dbname}}" TO client_readwrite;

-- Grant rights to role to do anything on existing tables, sequences, functions in the public schema
GRANT ALL ON ALL TABLES IN SCHEMA public TO client_readwrite;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO client_readwrite;
GRANT ALL ON ALL FUNCTIONS IN SCHEMA public TO client_readwrite;

-- Grant rights to role to do anything on tables, sequences, functions in that will be created later in the public schema
ALTER DEFAULT PRIVILEGES FOR USER {{adminuser}} IN SCHEMA public GRANT ALL ON TABLES TO client_readwrite;
ALTER DEFAULT PRIVILEGES FOR USER {{adminuser}} IN SCHEMA public GRANT ALL ON SEQUENCES TO client_readwrite;
ALTER DEFAULT PRIVILEGES FOR USER {{adminuser}} IN SCHEMA public GRANT ALL ON FUNCTIONS TO client_readwrite;

-- Create user in role client_readwrite
CREATE USER octolis_readwrite IN ROLE client_readwrite PASSWORD '{{user_password}}';

Revoke rights & delete role & user

javascript
-- Revoke everything
REVOKE ALL ON DATABASE "{{dbname}}" FROM client_readwrite;

REVOKE ALL ON ALL TABLES IN SCHEMA public FROM client_readwrite;
REVOKE ALL ON ALL SEQUENCES IN SCHEMA public FROM client_readwrite;
REVOKE ALL ON ALL FUNCTIONS IN SCHEMA public FROM client_readwrite;

ALTER DEFAULT PRIVILEGES FOR USER {{adminuser}} IN SCHEMA public REVOKE ALL ON TABLES FROM client_readwrite;
ALTER DEFAULT PRIVILEGES FOR USER {{adminuser}} IN SCHEMA public REVOKE ALL ON SEQUENCES FROM client_readwrite;
ALTER DEFAULT PRIVILEGES FOR USER {{adminuser}} IN SCHEMA public REVOKE ALL ON FUNCTIONS FROM client_readwrite;

-- Reassign user's ownership, delete user & role (reassign does not always work, especially on AWS RDS) 
-- REASSIGN OWNED BY octolis_readwrite TO {{adminuser}}
-- REASSIGN OWNED BY client_readwrite TO {{adminuser}}
DROP USER octolis_readwrite;
DROP ROLE client_readwrite;

Create a readonly role & user (for a Connection used as a Source only).

Create role, user & grant rights

Don’t forget to replace {{adminuser}}, {{dbname}} , {{schema_name}} and {{user_password}}.

javascript
-- Create role
CREATE ROLE client_readonly;

-- Grant rights to role to access the DB and create schemas
GRANT CONNECT ON DATABASE "{{dbname}}" TO client_readonly;

-- GRANT role client_readonly to read a schema
GRANT USAGE ON SCHEMA {{schema_name}} TO client_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA {{schema_name}} TO client_readonly;
ALTER DEFAULT PRIVILEGES FOR USER {{adminuser}} IN SCHEMA {{schema_name}} GRANT SELECT ON TABLES TO client_readonly;

-- Create user
CREATE USER octolis_readonlyIN ROLE client_readonly PASSWORD '{{user_password}}';

Revoke rights & delete role & user

javascript
-- Revokes and drops everything
REVOKE ALL ON SCHEMA {{schema_name}} FROM client_readonly;
REVOKE ALL ON ALL TABLES IN SCHEMA {{schema_name}} FROM client_readonly;
ALTER DEFAULT PRIVILEGES FOR USER {{adminuser}} IN SCHEMA {{schema_name}} REVOKE ALL ON TABLES FROM client_readonly;

-- Delete user & role 
DROP USER octolis_readonly;
DROP ROLE client_readonly;

By the way, if your DB is protected by an IP address whitelist, don’t forget to add our IP! (see ).