User/Role

How to define and customize PostgreSQL users and roles through configuration?

In this document, “user” refers to a logical object within a database cluster created with CREATE USER/ROLE.

In PostgreSQL, users belong to the database cluster, rather than a specific database. Create business users before you create business databases.

Pigsty defines roles and users through two config parameters:

pg_default_roles: Define globally shared roles and users
pg_users: Define business users and roles at cluster level

The first parameter defines roles and users shared across the environment. The second defines business roles and users for one cluster. Both parameters use arrays of user definition objects. Pigsty creates users and roles in array order. Thus, a later user can belong to an earlier role.

By default, all users marked with pgbouncer: true are added to the Pgbouncer connection pool user list.

Define Users

Example from Pigsty demo pg-meta cluster:

pg-meta:
  hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
  vars:
    pg_cluster: pg-meta
    pg_users:
      - {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      }
      - {name: dbuser_remove   ,state: absent }  # use state: absent to delete user

Each user or role definition is an object. Only name is required:

- name: dbuser_meta               # REQUIRED, `name` is the only mandatory field
  state: create                   # Optional, user state: create (default), absent
  password: DBUser.Meta           # Optional, password, can be scram-sha-256 hash or plaintext
  login: true                     # Optional, can login, default true
  superuser: false                # Optional, is superuser, default false
  createdb: false                 # Optional, can create databases, default false
  createrole: false               # Optional, can create roles, default false
  inherit: true                   # Optional, inherit role privileges, default true
  replication: false              # Optional, can replicate, default false
  bypassrls: false                # Optional, bypass row-level security, default false
  connlimit: -1                   # Optional, connection limit, default -1 (unlimited)
  expire_in: 3650                 # Optional, expire N days from creation (priority over expire_at)
  expire_at: '2030-12-31'         # Optional, expiration date in YYYY-MM-DD format
  comment: pigsty admin user      # Optional, user comment
  roles: [dbrole_admin]           # Optional, roles array
  parameters:                     # Optional, role-level config params
    search_path: public
  pgbouncer: true                 # Optional, add to connection pool user list, default false
  pool_mode: transaction          # Optional, pgbouncer pool mode, default transaction
  pool_connlimit: 100             # Optional, user-level max pool connections; omitted inherits global default 100

User-level pool quota is consistently defined by pool_connlimit (mapped to Pgbouncer max_user_connections).

Parameter Overview

The only required field is name. It must be a valid username that is unique within the cluster. All other parameters have defaults.

Field	Category	Type	Attr	Description
`name`	Basic	`string`	Required	Username, must be valid and unique
`state`	Basic	`enum`	Optional	State: `create` (default), `absent`
`password`	Basic	`string`	Mutable	User password, plaintext or hash
`comment`	Basic	`string`	Mutable	User comment
`login`	Privilege	`bool`	Mutable	Can login, default `true`
`superuser`	Privilege	`bool`	Mutable	Is superuser, default `false`
`createdb`	Privilege	`bool`	Mutable	Can create databases, default `false`
`createrole`	Privilege	`bool`	Mutable	Can create roles, default `false`
`inherit`	Privilege	`bool`	Mutable	Inherit role privileges, default `true`
`replication`	Privilege	`bool`	Mutable	Can replicate, default `false`
`bypassrls`	Privilege	`bool`	Mutable	Bypass RLS, default `false`
`connlimit`	Privilege	`int`	Mutable	Connection limit, `-1` unlimited
`expire_in`	Validity	`int`	Mutable	Expire N days from now (priority)
`expire_at`	Validity	`string`	Mutable	Expiration date, `YYYY-MM-DD` format
`roles`	Role	`array`	Additive	Roles array, string or object format
`parameters`	Params	`object`	Mutable	Role-level parameters
`pgbouncer`	Pool	`bool`	Mutable	Add to connection pool, default `false`
`pool_mode`	Pool	`enum`	Mutable	Pool mode: `transaction` (default)
`pool_connlimit`	Pool	`int`	Mutable	Pool user max connections

Parameter Details

`name`

This required string specifies the username. The username must be unique within the cluster.

Use a valid PostgreSQL identifier that matches ^[a-z_][a-z0-9_]{0,62}$. Start with a lowercase letter or underscore. Use only lowercase letters, digits, and underscores. The maximum length is 63 characters.

- name: dbuser_app         # Standard naming
- name: app_readonly       # Underscore separated
- name: _internal          # Underscore prefix (for internal roles)

`state`

Enum for user operation: create or absent. Default create.

State	Description
`create`	Default, create user, update if exists
`absent`	Delete user with `DROP ROLE`

- name: dbuser_app             # state defaults to create
- name: dbuser_old
  state: absent                # Delete user

Pigsty prevents deletion of these system users with state: absent to prevent cluster failure:

postgres: Database superuser
replicator: Replication user (or pg_replication_username)
dbuser_dba: Admin user (or pg_admin_username)
dbuser_monitor: Monitor user (or pg_monitor_username)

`password`

This mutable string specifies the user password. Users without a password cannot log in with password authentication.

Password can be:

Format	Example	Description
Plaintext	`DBUser.Meta`	Not recommended, logged to config
SCRAM-SHA-256	`SCRAM-SHA-256$4096:xxx$yyy:zzz`	Recommended, PG10+ default
MD5 hash	`md5...`	Legacy compatibility

# Plaintext (not recommended, logged to config)
- name: dbuser_app
  password: MySecretPassword

# SCRAM-SHA-256 hash (recommended)
- name: dbuser_app
  password: 'SCRAM-SHA-256$4096:xxx$yyy:zzz'

When Pigsty sets a password, it temporarily disables logging to prevent password disclosure:

SET log_statement TO 'none';
ALTER USER "dbuser_app" PASSWORD 'xxx';
SET log_statement TO DEFAULT;

To generate SCRAM-SHA-256 hash:

# Using PostgreSQL (requires pgcrypto extension)
psql -c "SELECT encode(digest('password' || 'username', 'sha256'), 'hex')"

`comment`

String, mutable. User comment, defaults to business user {name}.

Pigsty sets the comment with COMMENT ON ROLE. Special characters are supported, and Pigsty automatically escapes quotes.

- name: dbuser_app
  comment: 'Main business application account'

COMMENT ON ROLE "dbuser_app" IS 'Main business application account';

`login`

This mutable Boolean controls login access. The default is true.

Set false to create a Role rather than a user. Roles usually group permissions.

In PostgreSQL, CREATE USER equals CREATE ROLE ... LOGIN.

# Create login-able user
- name: dbuser_app
  login: true

# Create role (no login, for permission grouping)
- name: dbrole_custom
  login: false
  comment: custom permission role

CREATE USER "dbuser_app" LOGIN;
CREATE USER "dbrole_custom" NOLOGIN;

`superuser`

This mutable Boolean controls superuser privileges. The default is false.

Superusers have full database privileges. They bypass all permission checks.

- name: dbuser_admin
  superuser: true            # Dangerous: full privileges

ALTER USER "dbuser_admin" SUPERUSER;

Pigsty provides a default superuser through pg_admin_username (dbuser_dba). Do not create additional superusers unless necessary.

`createdb`

This mutable Boolean controls permission to create databases. The default is false.

- name: dbuser_dev
  createdb: true             # Allow create database

ALTER USER "dbuser_dev" CREATEDB;

Some applications, such as Gitea and Odoo, may require the CREATEDB privilege for their administration users.

`createrole`

This mutable Boolean controls permission to create other roles. The default is false.

Users with CREATEROLE can create, modify, and delete other roles that are not superusers.

- name: dbuser_admin
  createrole: true           # Allow manage other roles

ALTER USER "dbuser_admin" CREATEROLE;

`inherit`

This mutable Boolean controls automatic inheritance of privileges from roles to which the user belongs. The default is true.

If you set false, use an explicit SET ROLE command to use the role privileges.

# Auto-inherit role privileges (default)
- name: dbuser_app
  inherit: true
  roles: [dbrole_readwrite]

# Requires explicit SET ROLE
- name: dbuser_special
  inherit: false
  roles: [dbrole_admin]

ALTER USER "dbuser_special" NOINHERIT;
-- User must execute SET ROLE dbrole_admin to get privileges

`replication`

This mutable Boolean controls permission to start streaming replication. The default is false.

Usually, only replication users (replicator) need this permission. Regular users should not have it unless they need logical decoding subscriptions.

- name: replicator
  replication: true          # Allow streaming replication
  roles: [pg_monitor, dbrole_readonly]

ALTER USER "replicator" REPLICATION;

`bypassrls`

This mutable Boolean controls permission to bypass row-level security (RLS) policies. The default is false.

When enabled, the user can access all rows, even when RLS policies apply. Usually, only administrators need this permission.

- name: dbuser_myappadmin
  bypassrls: true            # Bypass RLS policies

ALTER USER "dbuser_myappadmin" BYPASSRLS;

`connlimit`

This mutable integer sets the maximum number of concurrent connections. The default is -1 (unlimited).

A positive integer limits the number of simultaneous sessions for this user. The limit does not apply to superusers.

- name: dbuser_app
  connlimit: 100             # Max 100 concurrent connections

- name: dbuser_batch
  connlimit: 10              # Limit batch user connections

ALTER USER "dbuser_app" CONNECTION LIMIT 100;

`expire_in`

This mutable integer sets expiration to N days from the current date.

This parameter takes priority over expire_at. Each playbook run recalculates the expiration date. Use this parameter for temporary users that need periodic renewal.

- name: temp_user
  expire_in: 30              # Expire in 30 days

- name: contractor_user
  expire_in: 90              # Expire in 90 days

Generates SQL:

-- expire_in: 30, assuming current date is 2025-01-01
ALTER USER "temp_user" VALID UNTIL '2025-01-31';

`expire_at`

String, mutable. Expiration date in YYYY-MM-DD format, or special value infinity.

This parameter has lower priority than expire_in. Use infinity for users that must not expire.

- name: contractor_user
  expire_at: '2024-12-31'    # Expire on specific date

- name: permanent_user
  expire_at: 'infinity'      # Never expires

ALTER USER "contractor_user" VALID UNTIL '2024-12-31';
ALTER USER "permanent_user" VALID UNTIL 'infinity';

`roles`

This additive array lists the roles to which the user belongs. Array elements can be strings or objects.

Simple format - strings for role names:

- name: dbuser_app
  roles:
    - dbrole_readwrite
    - pg_read_all_data

GRANT "dbrole_readwrite" TO "dbuser_app";
GRANT "pg_read_all_data" TO "dbuser_app";

Use objects for more detailed control:

- name: dbuser_app
  roles:
    - dbrole_readwrite                            # Simple string: GRANT role
    - { name: dbrole_admin, admin: true }         # WITH ADMIN OPTION
    - { name: pg_monitor, set: false }            # PG16+: disallow SET ROLE
    - { name: pg_signal_backend, inherit: false } # PG16+: don't auto-inherit
    - { name: old_role, state: absent }           # Revoke role membership

Object Format Parameters:

Param	Type	Description
`name`	string	Role name (required)
`state`	enum	`grant` (default) or `absent`/`revoke`: control membership
`admin`	bool	`true`: WITH ADMIN OPTION, `false`: REVOKE ADMIN
`set`	bool	PG16+: `true`: WITH SET TRUE, `false`: REVOKE SET
`inherit`	bool	PG16+: `true`: WITH INHERIT TRUE, `false`: REVOKE INHERIT

PostgreSQL 16+ New Features:

PostgreSQL 16 introduced finer-grained role membership control:

ADMIN OPTION: Allow granting role to other users
SET OPTION: Allow using SET ROLE to switch to this role
INHERIT OPTION: Auto-inherit this role’s privileges

# PostgreSQL 16+ complete example
- name: dbuser_app
  roles:
    # Normal membership
    - dbrole_readwrite

    # Can grant dbrole_admin to other users
    - { name: dbrole_admin, admin: true }

    # Cannot SET ROLE to pg_monitor (only inherit privileges)
    - { name: pg_monitor, set: false }

    # Don't auto-inherit pg_execute_server_program (need explicit SET ROLE)
    - { name: pg_execute_server_program, inherit: false }

    # Revoke old_role membership
    - { name: old_role, state: absent }

The set and inherit options work only in PG16+. Earlier versions ignore them and add warning comments.

`parameters`

This mutable object sets role-level configuration parameters with ALTER ROLE ... SET. The parameters apply to all sessions for this user.

- name: dbuser_analyst
  parameters:
    work_mem: '256MB'
    statement_timeout: '5min'
    search_path: 'analytics,public'
    log_statement: 'all'

ALTER USER "dbuser_analyst" SET "work_mem" = '256MB';
ALTER USER "dbuser_analyst" SET "statement_timeout" = '5min';
ALTER USER "dbuser_analyst" SET "search_path" = 'analytics,public';
ALTER USER "dbuser_analyst" SET "log_statement" = 'all';

Use the special value DEFAULT to reset a parameter to its PostgreSQL default. This value is case-insensitive:

- name: dbuser_app
  parameters:
    work_mem: DEFAULT          # Reset to default
    statement_timeout: '30s'   # Set new value

ALTER USER "dbuser_app" SET "work_mem" = DEFAULT;
ALTER USER "dbuser_app" SET "statement_timeout" = '30s';

Common role-level params:

Parameter	Description	Example
`work_mem`	Query work memory	`'64MB'`
`statement_timeout`	Statement timeout	`'30s'`
`lock_timeout`	Lock wait timeout	`'10s'`
`idle_in_transaction_session_timeout`	Idle transaction timeout	`'10min'`
`search_path`	Schema search path	`'app,public'`
`log_statement`	Log level	`'ddl'`
`temp_file_limit`	Temp file size limit	`'10GB'`

Query user-level parameters through the pg_db_role_setting system view.

`pgbouncer`

This mutable Boolean adds the user to the Pgbouncer user list. The default is false.

For production users that need connection pool access, explicitly set pgbouncer: true. The default, false, prevents accidental pool access for internal users.

# Prod user: needs connection pool
- name: dbuser_app
  password: DBUser.App
  pgbouncer: true

# Internal user: no connection pool needed
- name: dbuser_internal
  password: DBUser.Internal
  pgbouncer: false           # Default, can be omitted

Users with pgbouncer: true are added to /etc/pgbouncer/userlist.txt.

`pool_mode`

Enum, mutable. User-level pool mode: transaction, session, or statement. Default transaction.

Mode	Description	Use Case
`transaction`	Return connection after txn	Most OLTP apps, default
`session`	Return connection after session	Apps needing session state
`statement`	Return after each statement	Simple stateless queries

# DBA user: session mode (may need SET commands etc.)
- name: dbuser_dba
  pgbouncer: true
  pool_mode: session

# Normal business user: transaction mode
- name: dbuser_app
  pgbouncer: true
  pool_mode: transaction

User-level pool params are configured via /etc/pgbouncer/useropts.txt:

dbuser_dba      = pool_mode=session max_user_connections=16
dbuser_monitor  = pool_mode=session max_user_connections=8

`pool_connlimit`

This mutable integer sets the maximum pool connections for the user. If you omit it, Pigsty does not generate a user-level override. The global pgbouncer.ini default of 100 then applies. PgBouncer uses 0 for unlimited connections.

- name: dbuser_app
  pgbouncer: true
  pool_connlimit: 50         # Max 50 pool connections for this user

ACL System

Pigsty provides a built-in access control / ACL model. Assign these default business roles to users as required:

Role	Privileges	Typical Use Case
`dbrole_readwrite`	Global read-write	Primary application accounts
`dbrole_readonly`	Global read-only	Read-only application access
`dbrole_admin`	DDL privileges	Application administrators and table creation
`dbrole_offline`	Independent read-only; instance scope controlled by HBA	Ad hoc users, ETL, and analytics

# Typical business user configuration
pg_users:
  - name: dbuser_app
    password: DBUser.App
    pgbouncer: true
    roles: [dbrole_readwrite]    # Prod account, read-write

  - name: dbuser_readonly
    password: DBUser.Readonly
    pgbouncer: true
    roles: [dbrole_readonly]     # Read-only account

  - name: dbuser_admin
    password: DBUser.Admin
    pgbouncer: true
    roles: [dbrole_admin]        # Admin, can execute DDL

  - name: dbuser_etl
    password: DBUser.ETL
    roles: [dbrole_offline]      # Analytics account; restrict instance scope through HBA

dbrole_offline does not restrict a user to offline instances by itself. To apply this restriction, set role: offline on the corresponding HBA rule. See Offline Role and Instance Isolation.

To define your own ACL system, customize these parameters and templates:

pg_default_roles: System-wide roles and global users
pg_default_privileges: Default privileges for new objects
pg-init-roles.sql: Role creation SQL template
pg-init-template.sql: Privilege SQL template

Pgbouncer Users

Pigsty enables Pgbouncer as connection pool middleware by default. Pigsty adds users from pg_users to the Pgbouncer user list only when they have pgbouncer: true.

Users in connection pool are listed in /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="

User-level pool params are maintained in /etc/pgbouncer/useropts.txt:

dbuser_dba      = pool_mode=session max_user_connections=16
dbuser_monitor  = pool_mode=session max_user_connections=8

When you create users, Pigsty reloads the Pgbouncer user list online. The reload does not affect existing connections.

Pgbouncer runs as the same dbsu as PostgreSQL. The default is the postgres operating system user. Use the pgb alias to access Pgbouncer administration functions.

Use pgbouncer_auth_query to query authentication data dynamically for pool users. This parameter removes the need to manage pool users manually.

For user management operations, see User Management.

For user access privileges, see Access Control: Role System.

User/Role

Define Users

Parameter Overview

Parameter Details

name

state

password

comment

login

superuser

createdb

createrole

inherit

replication

bypassrls

connlimit

expire_in

expire_at

roles

parameters

pgbouncer

pool_mode

pool_connlimit