Access Control

Pigsty has a default role system consist of four and four default users：

Default Roles

There are four default roles in pigsty:

• Read Only (dbrole_readonly): Role for global read-only access
• Read Write (dbrole_readwrite): Role for global read-write access, inherits dbrole_readonly.
• Admin (dbrole_admin): Role for DDL commands, inherits dbrole_readwrite.
• Offline (dbrole_offline): Role for restricted read-only access (offline instance)

Default roles are defined in , change default roles is not recommended.

- { name: dbrole_readonly  , login: false , comment: role for global read-only access  }                            # production read-only role
- { name: dbrole_offline ,   login: false , comment: role for restricted read-only access (offline instance) }      # restricted-read-only role
- { name: dbrole_readwrite , login: false , roles: [dbrole_readonly], comment: role for global read-write access }  # production read-write role
- { name: dbrole_admin , login: false , roles: [pg_monitor, dbrole_readwrite] , comment: role for object creation } # production DDL change role

There are four default users in pigsty, too.

• Superuser (postgres), the owner and creator of the cluster, same as the OS dbsu.
• Monitor user (dbuser_monitor), a user used to monitor database and connection pool metrics.
• Admin user (dbuser_dba), the admin user who performs daily operations and database changes.

Default users’ username/password are defined with dedicate parameters (except for dbsu password):

• : os dbsu name, postgres by default, better not change it
• pg_replication_username : postgres replication username, replicator by default
• : postgres replication password, DBUser.Replicator by default
• pg_admin_username : postgres admin username, dbuser_dba by default
• : postgres admin password in plain text, DBUser.DBA by default
• pg_monitor_username : postgres monitor username, dbuser_monitor by default
• : postgres monitor password, DBUser.Monitor by default

!> Remember to change these password in production deployment !

- { name: postgres     ,superuser: true                                          ,comment: system superuser }
- { name: replicator ,replication: true  ,roles: [pg_monitor, dbrole_readonly]   ,comment: system replicator }
- { name: dbuser_dba   ,superuser: true  ,roles: [dbrole_admin]  ,pgbouncer: true ,pool_mode: session, pool_connlimit: 16 , comment: pgsql admin user }
- { name: dbuser_monitor   ,roles: [pg_monitor, dbrole_readonly] ,pgbouncer: true ,parameters: {log_min_duration_statement: 1000 } ,pool_mode: session ,pool_connlimit: 8 ,comment: pgsql monitor user }

Privileges

Pigsty has a battery-included privilege model that works with .

• All users have access to all schemas.
• Read-Only user can read from all tables. (SELECT, EXECUTE)
• Read-Write user can write to all tables run DML. (INSERT, UPDATE, DELETE).
• Admin user can create object and run DDL (CREATE, USAGE, TRUNCATE, REFERENCES, TRIGGER).
• Object created by admin users will have correct privilege.
• Default privileges are installed on all databases, including template database.
• Database connect privilege is covered by database definition
• CREATE privileges of database & public schema are revoked from PUBLIC by default

Default object privileges are defined in pg_default_privileges.

Which will be rendered in alone with ALTER DEFAULT PRIVILEGES statement for admin users. The \ddp+ may looks like:

Newly created objects will have corresponding privileges when it is created by admin users

Default Privilege

allows you to set the privileges that will be applied to objects created in the future. It does not affect privileges assigned to already-existing objects, and objects created by non-admin users.

That is to say, to maintain the correct object privilege, you have to run DDL with admin users, which could be:

1. {{ pg_dbsu }}, postgres by default
2. , dbuser_dba by default
3. Business admin user granted with dbrole_admin

It’s wise to use postgres as global object owner. If you wish to create objects with business admin user, YOU MUST USE SET ROLE dbrole_admin before running that DDL to maintain the correct privileges.

There are 3 database level privileges: CONNECT, CREATE, , and a special ‘privilege’: OWNERSHIP.

- name: meta         # required, `name` is the only mandatory field of a database definition
  owner: postgres    # optional, specify a database owner, {{ pg_dbsu }} 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)

• If owner exists, it will be used as database owner instead of default
• If revokeconn is false, all users have the CONNECT privilege of the database, this is the default behavior.
• If revokeconn is set to true explicitly:
  • CONNECT privilege of the database will be revoked from PUBLIC
  • CONNECT privilege will be granted to {{ pg_replication_username }}, {{ pg_monitor_username }} and {{ pg_admin_username }}
  • CONNECT privilege will be granted to database owner with GRANT OPTION

revokeconn flag can be used for database access isolation, you can create different business users as the owners for each database and set the revokeconn option for all of them.

Example: Database Isolation

Create Privilege

Pigsty revokes the CREATE privilege on database from PUBLIC by default, for security consideration.

Pigsty revokes the CREATE privilege on public schema from by default. Which is the default behavior since PostgreSQL 15.

The database owner have the full capability to adjust these privileges as they see fit.

Last modified 2023-02-27: