User Role
In this context, User refers to logical objects created by SQL `CREATE USER / ROLE`
You can manage PostgreSQL users and roles with Pigsty, in an IaC manner.
Define User
You can define roles/users with the following param, they all both arrays consist of user objects:
pg_users
: Define business users & roles at cluster level (Cluster Definition)pg_default_roles
: Define system-wide roles & global users (Global Defaults)
The former defines global roles and users shared across the entire environment, while the latter defines business roles and users specific to a single cluster. Here are some examples of user definitions:
pg-meta:
hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
vars:
pg_cluster: pg-meta
pg_databases:
- {name: dbuser_meta ,password: DBUser.Meta ,pgbouncer: true ,roles: [dbrole_admin] ,comment: pigsty admin user }
- {name: dbuser_view ,password: DBUser.Viewer ,pgbouncer: true ,roles: [dbrole_readonly] ,comment: read-only viewer for meta database }
- {name: dbuser_grafana ,password: DBUser.Grafana ,pgbouncer: true ,roles: [dbrole_admin] ,comment: admin user for grafana database }
- {name: dbuser_bytebase ,password: DBUser.Bytebase ,pgbouncer: true ,roles: [dbrole_admin] ,comment: admin user for bytebase database }
- {name: dbuser_kong ,password: DBUser.Kong ,pgbouncer: true ,roles: [dbrole_admin] ,comment: admin user for kong api gateway }
- {name: dbuser_gitea ,password: DBUser.Gitea ,pgbouncer: true ,roles: [dbrole_admin] ,comment: admin user for gitea service }
- {name: dbuser_wiki ,password: DBUser.Wiki ,pgbouncer: true ,roles: [dbrole_admin] ,comment: admin user for wiki.js service }
- {name: dbuser_noco ,password: DBUser.Noco ,pgbouncer: true ,roles: [dbrole_admin] ,comment: admin user for nocodb service }
User Attributes
You can customize users with more attributes, the full example is as follows:
- name: dbuser_meta # REQUIRED, `name` is the only mandatory field of a user definition
password: DBUser.Meta # optional, password, can be a scram-sha-256 hash string or plain text
login: true # optional, can log in, true by default (new biz ROLE should be false)
superuser: false # optional, is superuser? false by default
createdb: false # optional, can create database? false by default
createrole: false # optional, can create role? false by default
inherit: true # optional, can this role use inherited privileges? true by default
replication: false # optional, can this role do replication? false by default
bypassrls: false # optional, can this role bypass row level security? false by default
pgbouncer: true # optional, add this user to pgbouncer user-list? false by default (production user should be true explicitly)
connlimit: -1 # optional, user connection limit, default -1 disable limit
expire_in: 3650 # optional, now + n days when this role is expired (OVERWRITE expire_at)
expire_at: '2030-12-31' # optional, YYYY-MM-DD 'timestamp' when this role is expired (OVERWRITTEN by expire_in)
comment: pigsty admin user # optional, comment string for this user/role
roles: [dbrole_admin] # optional, belonged roles. default roles are: dbrole_{admin,readonly,readwrite,offline}
parameters: {} # optional, role level parameters with `ALTER ROLE SET`
pool_mode: transaction # optional, pgbouncer pool mode at user level, transaction by default
pool_connlimit: -1 # optional, max database connections at user level, default -1 disable limit
search_path: public # key value config parameters, according to postgresql documentation (e.g: use pigsty as default search_path)
- The only required field is
name
, which should be a valid & unique username in PostgreSQL. - Roles don’t need a
password
, while it could be necessary for a login-able user. - The
password
can be plain text or a scram-sha-256 / md5 hash string. - User / Role definition order matters,
pg_default_roles
first,pg_users
later, in sequence order. - Make sure role / group definition is ahead of its members.
- Role Attributes:
login
,superuser
,createdb
,createrole
,inherit
,replication
,bypassrls
pgbouncer
is disabled by default. Set it totrue
explicitly to enable it in pgbouncer.
ACL System
Pigsty has a battery-included ACL system, which can be easily used by assigning roles to users:
dbrole_readonly
: The role for global read-only accessdbrole_readwrite
: The role for global read-write accessdbrole_admin
: The role for object creationdbrole_offline
: The role for restricted read-only access (offline instance)
If you wish to re-design your ACL system, check the following parameters and SQL templates.
pg_default_roles
: System-wide roles & global userspg_default_privileges
: Default privileges for newly created objectsroles/pgsql/templates/pg-init-role.sql
: Role creation SQL templateroles/pgsql/templates/pg-init-template.sql
: Privilege SQL template
Create User
Users and roles defined in pg_default_roles
and pg_users
will be automatically created one by one during module installation.
It only runs on cluster leader, the primary instance.
To create users on an existing cluster,
add new user/role definitions to all.children.<cls>.pg_users
, and create the database with the bin/pgsql-user
util or pgsql-user.yml
playbook:
bin/pgsql-user <cls> <dbname> # the bin util script
bin/pgsql-user pg-meta dbuser_meta # example: create dbuser_meta user in pg-meta cluster
./pgsql-user.yml -l <cls> -e username=<dbname> # the actual playbook
./pgsql-user.yml -l pg-meta -e username=meta # example: create dbuser_meta user in pg-meta cluster
Create user is an idempotent operation, meaning it can be run multiple times safely.
Create user / role with Pigsty
Pigsty will manage the pgbouncer userlist, so please create business databases with the Pigsty playbook/utils. Check create user SOP for details. If you are not using pgbouncer or able to maintain it by yourself, you can create users in any way you like.
Create owner user before create database
In PostgreSQL, users belongs to the database cluster, not a specific database.
If your user is an owner of any databases, make sure the user is created before creating the database.
Modify User
Modifying PostgreSQL user attributes is the same as creating users.
Adjust your user definition by modifying the config inventory, then re-run create user
.
There are two exceptions: name
and roles
, which require manual intervention:
Rename user is not supported directly in Pigsty
The username is used as the identity of the user, so if you really want to do that, use the standard SQL:
ALTER USER "old_name" RENAME TO "new_name";
Membership will NOT be revoked by Pigsty
Note that modifying a user does not delete the user, but modifies user attributes using the ALTER USER
command.
It also DOES NOT revoke user permissions and group memberships, and uses the GRANT
command to grant new roles.
Check PostgreSQL Docs for more details on ALTER USER
.
Delete User
For security reasons, Pigsty does not automatically delete users, even if you remove user definitions from the configuration, Pigsty will not delete existing users.
You need to use the SQL command DROP USER
to manually delete users:
DROP USER "<username>";
If the role you want to delete is a group (has other users belonging to it), you need to first remove other users from the group before deleting the group:
REVOKE "<rolename>" FROM "<other_user>";
If the user you want to delete owns database objects, you need to first change the ownership of these objects to another user before deleting the user:
REASSIGN OWNED BY "<username>" TO "<another_user>";
Check PostgreSQL Docs for more details on DROP USER
, REASSIGN OWNED
, and REVOKE
.
Pgbouncer User
Pigsty helps manage users in pgbouncer userlist, and keep it in sync with the postgres.
It requires explicitly setting the pgbouncer: true
flag in the user definition to be enrolled in the pgbouncer user list.
The system admin user (pg_admin_username
) and monitoring user (pg_monitor_username
)
will always be added to the pgbouncer user list for administration & monitoring.
Configuration Files
Users in the Pgbouncer connection pool are listed in /etc/pgbouncer/userlist.txt
, examples:
"postgres" ""
"dbuser_wiki" "SCRAM-SHA-256$4096:+77dyhrPeFDT/TptHs7/7Q==$KeatuohpKIYzHPCt/tqBu85vI11o9mar/by0hHYM2W8=:X9gig4JtjoS8Y/o1vQsIX/gY1Fns8ynTXkbWOjUfbRQ="
"dbuser_view" "SCRAM-SHA-256$4096:DFoZHU/DXsHL8MJ8regdEw==$gx9sUGgpVpdSM4o6A2R9PKAUkAsRPLhLoBDLBUYtKS0=:MujSgKe6rxcIUMv4GnyXJmV0YNbf39uFRZv724+X1FE="
"dbuser_monitor" "SCRAM-SHA-256$4096:fwU97ZMO/KR0ScHO5+UuBg==$CrNsmGrx1DkIGrtrD1Wjexb/aygzqQdirTO1oBZROPY=:L8+dJ+fqlMQh7y4PmVR/gbAOvYWOr+KINjeMZ8LlFww="
"dbuser_meta" "SCRAM-SHA-256$4096:leB2RQPcw1OIiRnPnOMUEg==$eyC+NIMKeoTxshJu314+BmbMFpCcspzI3UFZ1RYfNyU=:fJgXcykVPvOfro2MWNkl5q38oz21nSl1dTtM65uYR1Q="
"dbuser_kong" "SCRAM-SHA-256$4096:bK8sLXIieMwFDz67/0dqXQ==$P/tCRgyKx9MC9LH3ErnKsnlOqgNd/nn2RyvThyiK6e4=:CDM8QZNHBdPf97ztusgnE7olaKDNHBN0WeAbP/nzu5A="
"dbuser_grafana" "SCRAM-SHA-256$4096:HjLdGaGmeIAGdWyn2gDt/Q==$jgoyOB8ugoce+Wqjr0EwFf8NaIEMtiTuQTg1iEJs9BM=:ed4HUFqLyB4YpRr+y25FBT7KnlFDnan6JPVT9imxzA4="
"dbuser_gitea" "SCRAM-SHA-256$4096:l1DBGCc4dtircZ8O8Fbzkw==$tpmGwgLuWPDog8IEKdsaDGtiPAxD16z09slvu+rHE74=:pYuFOSDuWSofpD9OZhG7oWvyAR0PQjJBffgHZLpLHds="
"dbuser_dba" "SCRAM-SHA-256$4096:zH8niABU7xmtblVUo2QFew==$Zj7/pq+ICZx7fDcXikiN7GLqkKFA+X5NsvAX6CMshF0=:pqevR2WpizjRecPIQjMZOm+Ap+x0kgPL2Iv5zHZs0+g="
"dbuser_bytebase" "SCRAM-SHA-256$4096:OMoTM9Zf8QcCCMD0svK5gg==$kMchqbf4iLK1U67pVOfGrERa/fY818AwqfBPhsTShNQ=:6HqWteN+AadrUnrgC0byr5A72noqnPugItQjOLFw0Wk="
User-level parameters are maintained in a separate file: /etc/pgbouncer/useropts.txt
, examples:
dbuser_dba = pool_mode=session max_user_connections=16
dbuser_monitor = pool_mode=session max_user_connections=8
The userlist.txt
and useropts.txt
will be automatically refreshed when you create users
and take effect with systemctl reload pgbouncer
, normally without affecting existing connections.
Reload
To reload pgbouncer configuration, you can use the ansible playbook, or systemctl command
./pgsql.yml -t pgbouncer_reload
systemctl reload pgbouncer
Admin
Pgbouncer runs with the same dbsu
as PostgreSQL, defaulting to the postgres
os user.
You can use the pgb
alias to access pgbouncer management functions using dbsu.
sudo su - postgres
pgb # login to pgbouncer command line interface using admin user
Delete Pgbouncer User
If all database users are managed by Pigsty, you can just regenerate pgbouncer userlist (without the removed user in the list in the config inventory) and reload it:
./pgsql.yml -t pgbouncer_user,pgbouncer_reload -e pg_reload=true
To manually remove a user from the pgbouncer pool, simply delete the corresponding line from /etc/pgbouncer/userlist.txt
and reload pgbouncer:
systemctl reload pgbouncer
Dynamic User Authentication
Note that the pgbouncer_auth_query
parameter allows you to use dynamic queries to complete connection pool user authentication, which is a compromise when you don’t want to manage users in the connection pool.