PostgreSQL Privilege
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'orpg_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;
| Owner | Schema | Type | Access privileges |
|---|---|---|---|
| username | schema | postgres=UC/postgres | |
| dbrole_readonly=U/postgres | |||
| dbrole_offline=U/postgres | |||
| dbrole_admin=C/postgres | |||
| username | sequence | postgres=rwU/postgres | |
| dbrole_readonly=r/postgres | |||
| dbrole_readwrite=wU/postgres | |||
| dbrole_offline=r/postgres | |||
| username | table | postgres=arwdDxt/postgres | |
| dbrole_readonly=r/postgres | |||
| dbrole_readwrite=awd/postgres | |||
| dbrole_offline=r/postgres | |||
| dbrole_admin=Dxt/postgres | |||
| username | function | =X/postgres | |
| postgres=X/postgres | |||
| dbrole_readonly=X/postgres | |||
| dbrole_offline=X/postgres |
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
Feedback
Was this page helpful?
Glad to hear it! Please tell us how we can improve.
Sorry to hear that. Please tell us how we can improve.