Manage PostgreSQL roles and permissions with Mitto SQL job

Runner
#1

Mitto’s PostgreSQL database is created with a single admin user, however you can manage the PostgreSQL database however you’d like (e.g. create new roles, assign permissions, create new databases, etc).

Relevant PostgreSQL documentation:

Here are some example SQL queries that can be used for simple user management. This SQL can be added to a Mitto SQL job and automated.

SQL

/* Create a Role - this should only be run once */
CREATE ROLE viewer WITH LOGIN PASSWORD 'this_is_the_password';

/* Grant permissions to the role */
GRANT ALL PRIVILEGES ON SCHEMA model TO viewer;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA model TO viewer;
ALTER DEFAULT PRIVILEGES IN SCHEMA model GRANT ALL PRIVILEGES ON TABLES TO viewer;

Mitto SQL Job

Title: [SQL] User Permissions
Type: sql
Job Conf:

{
    "dbo": "postgresql://localhost/analytics",
    "kwargs": {},
    "parameters": {},
    "split": false,
    "sql": [
        "/* Create a Role - this should only be run once */",
        "",
        "CREATE ROLE viewer WITH LOGIN PASSWORD 'this_is_the_password';",
        "",
        "/* Grant permissions to the role */",
        "GRANT ALL PRIVILEGES ON SCHEMA model TO viewer;",
        "GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA model TO viewer;",
        "ALTER DEFAULT PRIVILEGES IN SCHEMA model GRANT ALL PRIVILEGES ON TABLES TO viewer;"
    ],
    "transaction": true
}