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 to true 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 access
dbrole_readwrite : The role for global read-write access
dbrole_admin : The role for object creation
dbrole_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 users
pg_default_privileges : Default privileges for newly created objects
roles/pgsql/templates/pg-init-role.sql: Role creation SQL template
roles/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:

/etc/pgbouncer/userlist.txt

"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:

/etc/pgbouncer/useropts.txt

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

on admin node

./pgsql.yml -t pgbouncer_reload

on database node

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.

postgres

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.

User Role

On this page