# 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 parameters, they are both arrays consisting of user objects: - [`pg_users`](/docs/pgsql/param#pg_users) : Define business users & roles at cluster level (**Cluster Definition**) - [`pg_default_roles`](/docs/pgsql/param#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: ```yaml 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: ```yaml - 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`](/docs/pgsql/param#pg_default_roles) first, [`pg_users`](/docs/pgsql/param#pg_users) later, in sequence order. - Make sure role / group definition is ahead of its members. - [Role Attributes](https://www.postgresql.org/docs/current/role-attributes.html): `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](/docs/pgsql/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](/docs/pgsql/config/#offline) instance) If you wish to re-design your [ACL](/docs/pgsql/acl) system, check the following parameters and SQL templates. - [`pg_default_roles`](/docs/pgsql/param#pg_default_roles) : System-wide roles & global users - [`pg_default_privileges`](/docs/pgsql/param#pg_default_privileges) : Default privileges for newly created objects - [`roles/pgsql/templates/pg-init-roles.sql`](https://github.com/pgsty/pigsty/blob/v3.7.0/roles/pgsql/templates/pg-init-roles.sql): Role creation SQL template - [`roles/pgsql/templates/pg-init-template.sql`](https://github.com/pgsty/pigsty/blob/v3.7.0/roles/pgsql/templates/pg-init-template.sql): Privilege SQL template ------ ## Create User Users and roles defined in [`pg_default_roles`](/docs/pgsql/param#pg_default_roles) and [`pg_users`](/docs/pgsql/param#pg_users) will be automatically created one by one during module installation. It only runs on cluster leader, the primary instance. To [**create users**](/docs/pgsql/admin#create-user) on an existing cluster, add new user/role definitions to `all.children..pg_users`, and create the database with the `bin/pgsql-user` util or [`pgsql-user.yml`](/docs/pgsql/playbook#pgsql-useryml) playbook: ```bash tab="utils" bin/pgsql-user # the bin util script bin/pgsql-user pg-meta dbuser_meta # example: create dbuser_meta user in pg-meta cluster ``` ```bash tab="playbook" ./pgsql-user.yml -l -e username= # 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](#pgbouncer-user), so please create business databases with the Pigsty playbook/utils. Check [create user](/docs/pgsql/admin#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 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**](/docs/pgsql/user#create-user). Adjust your user definition by modifying the [config inventory](/docs/config/inventory), then re-run [`create user`](#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: ```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`](https://www.postgresql.org/docs/current/sql-alteruser.html). ------ ## 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: ```sql DROP USER ""; ``` 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: ```sql REVOKE "" FROM ""; ``` 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: ```sql REASSIGN OWNED BY "" TO ""; ``` Check PostgreSQL Docs for more details on [`DROP USER`](https://www.postgresql.org/docs/current/sql-dropuser.html), [`REASSIGN OWNED`](https://www.postgresql.org/docs/current/sql-reassign-owned.html), and [`REVOKE`](https://www.postgresql.org/docs/current/sql-revoke.html). ------ ## 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`](/docs/pgsql/param#pg_admin_username)) and monitoring user ([`pg_monitor_username`](/docs/pgsql/param#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: ```ini {title="/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: ```ini {title="/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](/docs/pgsql/user#create-user) 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 ```bash tab="playbook" title="on admin node" ./pgsql.yml -t pgbouncer_reload ``` ```bash tab="systemctl" title="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. ```bash {title="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: ```bash ./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: ```bash systemctl reload pgbouncer ``` ### Dynamic User Authentication Note that the [`pgbouncer_auth_query`](/docs/pgsql/param#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.