Users / Roles

Define business users & roles in PostgreSQL

In this context, the User refers to objects created by SQL CREATE USER/ROLE.

Define User

There are two parameters related to users:

  • pg_users : Define business users & roles at cluster level
  • pg_default_roles : Define system-wide roles & global users at global level

They are both arrays of user/role definition. You can define multiple users/roles in one cluster.

  1. pg-meta:
  2.   hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
  3.   vars:
  4.     pg_cluster: pg-meta
  5.     pg_databases:
  6.       - {name: dbuser_meta     ,password: DBUser.Meta     ,pgbouncer: true ,roles: [dbrole_admin]    ,comment: pigsty admin user }
  7.       - {name: dbuser_view     ,password: DBUser.Viewer   ,pgbouncer: true ,roles: [dbrole_readonly] ,comment: read-only viewer for meta database }
  8.       - {name: dbuser_grafana  ,password: DBUser.Grafana  ,pgbouncer: true ,roles: [dbrole_admin]    ,comment: admin user for grafana database    }
  9.       - {name: dbuser_bytebase ,password: DBUser.Bytebase ,pgbouncer: true ,roles: [dbrole_admin]    ,comment: admin user for bytebase database   }
  10.       - {name: dbuser_kong     ,password: DBUser.Kong     ,pgbouncer: true ,roles: [dbrole_admin]    ,comment: admin user for kong api gateway    }
  11.       - {name: dbuser_gitea    ,password: DBUser.Gitea    ,pgbouncer: true ,roles: [dbrole_admin]    ,comment: admin user for gitea service       }
  12.       - {name: dbuser_wiki     ,password: DBUser.Wiki     ,pgbouncer: true ,roles: [dbrole_admin]    ,comment: admin user for wiki.js service     }

And each user definition may look like:

  1. - name: dbuser_meta               # REQUIRED, `name` is the only mandatory field of a user definition
  2.   password: DBUser.Meta           # optional, password, can be a scram-sha-256 hash string or plain text
  3.   login: true                     # optional, can log in, true by default  (new biz ROLE should be false)
  4.   superuser: false                # optional, is superuser? false by default
  5.   createdb: false                 # optional, can create database? false by default
  6.   createrole: false               # optional, can create role? false by default
  7.   inherit: true                   # optional, can this role use inherited privileges? true by default
  8.   replication: false              # optional, can this role do replication? false by default
  9.   bypassrls: false                # optional, can this role bypass row level security? false by default
  10.   pgbouncer: true                 # optional, add this user to pgbouncer user-list? false by default (production user should be true explicitly)
  11.   connlimit: -1                   # optional, user connection limit, default -1 disable limit
  12.   expire_in: 3650                 # optional, now + n days when this role is expired (OVERWRITE expire_at)
  13.   expire_at: '2030-12-31'         # optional, YYYY-MM-DD 'timestamp' when this role is expired  (OVERWRITTEN by expire_in)
  14.   comment: pigsty admin user      # optional, comment string for this user/role
  15.   roles: [dbrole_admin]           # optional, belonged roles. default roles are: dbrole_{admin,readonly,readwrite,offline}
  16.   parameters: {}                  # optional, role level parameters with `ALTER ROLE SET`
  17.   pool_mode: transaction          # optional, pgbouncer pool mode at user level, transaction by default
  18.   pool_connlimit: -1              # optional, max database connections at user level, default -1 disable limit
  19.   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 are created one by one in array order. So make sure role/group definition is ahead of its members
  • login, superuser, createdb, createrole, inherit, replication, bypassrls are boolean flags
  • pgbouncer is disabled by default. To add a business user to the pgbouncer user-list, you should set it to true explicitly.

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 & templates.

Create User

Users & Roles defined in pg_default_roles and pg_users will be automatically created one by one during cluster bootstrap.

If you wish to create user on an existing cluster, the bin/pgsql-user util can be used.

Add new user definition to all.children.<cls>.pg_users, and create that database with:

  1. bin/pgsql-user <cls> <username>    # pgsql-user.yml -l <cls> -e username=<username>

The playbook is idempotent, so it’s ok to run this multiple times on the existing cluster.

If you are using the default pgbouncer as proxy middleware, YOU MUST create a new user with pgsql-user util, or pgsql-user.yml playbook, Otherwise, the new user will not be added to the pgbouncer userlist.

Pgbouncer User

Pgbouncer is enabled by default and serves as a connection pool middleware, and its user is managed by default.

Pigsty will add all users in pg_users with pgbouncer: true flag to the pgbouncer userlist by default.

The user is listed in /etc/pgbouncer/userlist.txt:

  1. "postgres" ""
  2. "dbuser_wiki" "SCRAM-SHA-256$4096:+77dyhrPeFDT/TptHs7/7Q==$KeatuohpKIYzHPCt/tqBu85vI11o9mar/by0hHYM2W8=:X9gig4JtjoS8Y/o1vQsIX/gY1Fns8ynTXkbWOjUfbRQ="
  3. "dbuser_view" "SCRAM-SHA-256$4096:DFoZHU/DXsHL8MJ8regdEw==$gx9sUGgpVpdSM4o6A2R9PKAUkAsRPLhLoBDLBUYtKS0=:MujSgKe6rxcIUMv4GnyXJmV0YNbf39uFRZv724+X1FE="
  4. "dbuser_monitor" "SCRAM-SHA-256$4096:fwU97ZMO/KR0ScHO5+UuBg==$CrNsmGrx1DkIGrtrD1Wjexb/aygzqQdirTO1oBZROPY=:L8+dJ+fqlMQh7y4PmVR/gbAOvYWOr+KINjeMZ8LlFww="
  5. "dbuser_meta" "SCRAM-SHA-256$4096:leB2RQPcw1OIiRnPnOMUEg==$eyC+NIMKeoTxshJu314+BmbMFpCcspzI3UFZ1RYfNyU=:fJgXcykVPvOfro2MWNkl5q38oz21nSl1dTtM65uYR1Q="
  6. "dbuser_kong" "SCRAM-SHA-256$4096:bK8sLXIieMwFDz67/0dqXQ==$P/tCRgyKx9MC9LH3ErnKsnlOqgNd/nn2RyvThyiK6e4=:CDM8QZNHBdPf97ztusgnE7olaKDNHBN0WeAbP/nzu5A="
  7. "dbuser_grafana" "SCRAM-SHA-256$4096:HjLdGaGmeIAGdWyn2gDt/Q==$jgoyOB8ugoce+Wqjr0EwFf8NaIEMtiTuQTg1iEJs9BM=:ed4HUFqLyB4YpRr+y25FBT7KnlFDnan6JPVT9imxzA4="
  8. "dbuser_gitea" "SCRAM-SHA-256$4096:l1DBGCc4dtircZ8O8Fbzkw==$tpmGwgLuWPDog8IEKdsaDGtiPAxD16z09slvu+rHE74=:pYuFOSDuWSofpD9OZhG7oWvyAR0PQjJBffgHZLpLHds="
  9. "dbuser_dba" "SCRAM-SHA-256$4096:zH8niABU7xmtblVUo2QFew==$Zj7/pq+ICZx7fDcXikiN7GLqkKFA+X5NsvAX6CMshF0=:pqevR2WpizjRecPIQjMZOm+Ap+x0kgPL2Iv5zHZs0+g="
  10. "dbuser_bytebase" "SCRAM-SHA-256$4096:OMoTM9Zf8QcCCMD0svK5gg==$kMchqbf4iLK1U67pVOfGrERa/fY818AwqfBPhsTShNQ=:6HqWteN+AadrUnrgC0byr5A72noqnPugItQjOLFw0Wk="

And user level parameters are listed in /etc/pgbouncer/useropts.txt:

  1. dbuser_dba                  = pool_mode=session max_user_connections=16
  2. dbuser_monitor              = pool_mode=session max_user_connections=8

The userlist & useropts file will be updated automatically when you add a new user with pgsql-user util, or pgsql-user.yml playbook.

You can use pgbouncer_auth_query to simplify pgbouncer user management (with the cost of reliability & security).

Last modified 2023-02-27: refresh en docs to v2.0 (e82b371)