PostgreSQL Privilege

There’s a battery-included ACL models

Pigsty’s default privilege model is related to the default role. When using the Pigsty access control, all newly created business users should belong to one of the four default roles, which have the privileges shown below:

• All users have access to all schemas.
• Read-only users can read all tables.
• Read-write users can perform DML operations (INSERT, UPDATE, DELETE).
• Admin users can perform DDL change operations (CREATE, USAGE, TRUNCATE, REFERENCES, TRIGGER).
• Offline and read-only users are only allowed to access instances of pg_role == 'offline' or pg_offline_query = true.

GRANT USAGE                         ON SCHEMAS   TO dbrole_readonly;
GRANT SELECT                        ON TABLES    TO dbrole_readonly;
GRANT SELECT                        ON SEQUENCES TO dbrole_readonly;
GRANT EXECUTE                       ON FUNCTIONS TO dbrole_readonly;
GRANT USAGE                         ON SCHEMAS   TO dbrole_offline;
GRANT SELECT                        ON TABLES    TO dbrole_offline;
GRANT SELECT                        ON SEQUENCES TO dbrole_offline;
GRANT EXECUTE                       ON FUNCTIONS TO dbrole_offline;
GRANT INSERT, UPDATE, DELETE        ON TABLES    TO dbrole_readwrite;
GRANT USAGE,  UPDATE                ON SEQUENCES TO dbrole_readwrite;
GRANT TRUNCATE, REFERENCES, TRIGGER ON TABLES    TO dbrole_admin;
GRANT CREATE                        ON SCHEMAS   TO dbrole_admin;
GRANT USAGE                         ON TYPES     TO dbrole_admin;

Privilege Maintenance

PostgreSQL’s ALTER DEFAULT PRIVILEGES ensures default access to database objects.

All objects created by {{ dbsu }}, {{ pg_admin_username }}, {{ dbrole_admin }} will have the default privileges.

PostgreSQL’s ALTER DEFAULT PRIVILEGE only takes effect for “objects created by specific users” objects created by superuser postgres, and dbuser_dba have default privileges. Suppose you want to give business users privileges to execute DDL besides giving the dbrole_admin role to business users. You should also remember that you should first run the following command when executing DDL changes.

SET ROLE dbrole_admin; -- dbrole_admin creates objects with the correct default permissions

Database Privileges

The database has three privileges: CONNECT, CREATE, TEMP, and a special genus OWNERSHIP. The parameter pg_database controls the definition of the database. A complete database definition is shown below:

pg_databases:                       # define business databases on this cluster, array of database definition
  # define the default `meta` database
  - name: meta                      # required, `name` is the only mandatory field of a database definition
    baseline: cmdb.sql              # optional, database sql baseline path, (relative path among ansible search path, e.g files/)
    owner: postgres                 # optional, database owner, postgres by default
    template: template1             # optional, which template to use, template1 by default
    encoding: UTF8                  # optional, database encoding, UTF8 by default. (MUST same as template database)
    locale: C                       # optional, database locale, C by default.  (MUST same as template database)
    lc_collate: C                   # optional, database collate, C by default. (MUST same as template database)
    lc_ctype: C                     # optional, database ctype, C by default.   (MUST same as template database)
    tablespace: pg_default          # optional, default tablespace, 'pg_default' by default.
    allowconn: true                 # optional, allow connection, true by default. false will disable connect at all
    revokeconn: false               # optional, revoke public connection privilege. false by default. (leave connect with grant option to owner)
    pgbouncer: true                 # optional, add this database to pgbouncer database list? true by default
    comment: pigsty meta database   # optional, comment string for this database
    connlimit: -1                   # optional, database connection limit, default -1 disable limit
    schemas: [pigsty]               # optional, additional schemas to be created, array of schema names
    extensions:                     # optional, additional extensions to be installed: array of schema definition `{name,schema}`
      - {name: adminpack, schema: pg_catalog}    # install adminpack to pg_catalog and install postgis to public
      - {name: postgis, schema: public}          # if schema is omitted, extension will be installed according to search_path.

If the database is not configured with an owner, dbsu will be the default OWNER of the database. Otherwise, it will be the specified user.

All users have the CONNECT privilege to the newly created database; set revokeconn == true if you wish to reclaim this privilege. Only the default user (dbsu|admin|monitor|replicator) with the database’s owner is explicitly given the CONNECT privilege. Also, admin|owner will have GRANT OPTION for the CONNECT privilege and can transfer the CONNECT privilege to others.

If you implement access isolation between different databases, you can create a business user as the owner for each database and set the revokeconn option for all of them.

A sample database for privilege isolation

#--------------------------------------------------------------#
# pg-infra (example database for cluster loading)
#--------------------------------------------------------------#
pg-infra:
  hosts:
    10.10.10.40: { pg_seq: 1, pg_role: primary }
    10.10.10.41: { pg_seq: 2, pg_role: replica , pg_offline_query: true }
  vars:
    pg_cluster: pg-infrastructure
    pg_version: 14
    vip_address: 10.10.10.4
    pgbouncer_poolmode: session
    pg_hba_rules_extra:
      - title: allow confluence jira gitlab eazybi direct access
        role: common
        rules:
          - host    confluence dbuser_confluence   10.0.0.0/8        md5
          - host    jira       dbuser_jira         10.0.0.0/8        md5
          - host    gitlab     dbuser_gitlab       10.0.0.0/8        md5
    pg_users:
      # infra prod user
      - { name: dbuser_hybridcloud, password: ssag-2xd, pgbouncer: true, roles: [ dbrole_readwrite ] }
      - { name: dbuser_confluence, password: mc2iohos , pgbouncer: true, roles: [ dbrole_admin ] }
      - { name: dbuser_gitlab, password: sdf23g22sfdd , pgbouncer: true, roles: [ dbrole_readwrite ] }
      - { name: dbuser_jira, password: sdpijfsfdsfdfs , pgbouncer: true, roles: [ dbrole_admin ] }
    pg_databases:
      # infra database
      - { name: hybridcloud , revokeconn: true, owner: dbuser_hybridcloud , parameters: { search_path: yay,public } , connlimit: 100 }
      - { name: confluence , revokeconn: true, owner: dbuser_confluence , connlimit: 100 }
      - { name: gitlab , revokeconn: true, owner: dbuser_gitlab, connlimit: 100 }
      - { name: jira , revokeconn: true, owner: dbuser_jira , connlimit: 100 }

Create Privilege

Pigsty revokes the PUBLIC user’s privilege to CREATE a new schema under the database for security reasons. It also revokes the PUBLIC user’s privilege to create new relationships in the PUBLIC schema. The database superuser and admin user are not subject to this restriction.

Privileges to create objects in the database are independent of whether the user is the database owner or not. It only depends on whether the user was given admin privileges when it was created.

pg_users:
  - {name: test1, password: xxx , groups: [dbrole_readwrite]}  # Schema with objects cannot be created 
  - {name: test2, password: xxx , groups: [dbrole_admin]}      # Schema and objects can be created

Last modified 2022-07-05: fix https://github.com/Vonng/pigsty/issues/147 (e588a1e)