Version v3.7.0 of the documentation is no longer actively maintained. The site that you are currently viewing is an archived snapshot. For up-to-date documentation, see the latest version.
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 parameters, they are both arrays consisting 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:
User Attributes
You can customize users with more attributes, the full example is as follows:
- 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
passwordcan be plain text or a scram-sha-256 / md5 hash string. - User / Role definition order matters,
pg_default_rolesfirst,pg_userslater, in sequence order. - Make sure role / group definition is ahead of its members.
- Role Attributes:
login,superuser,createdb,createrole,inherit,replication,bypassrls pgbounceris disabled by default. Set it totrueexplicitly 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-roles.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:
Create user is an idempotent operation, meaning it can be run multiple times safely.
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.
In PostgreSQL, users belong 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:
The username is used as the identity of the user, so if you really want to do that, use the standard SQL:
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:
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:
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:
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:
User-level parameters are maintained in a separate file: /etc/pgbouncer/useropts.txt, examples:
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
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.
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:
To manually remove a user from the pgbouncer pool, simply delete the corresponding line from /etc/pgbouncer/userlist.txt and 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.