Skip to content

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 directly to the database cluster rather than a specific database. Therefore, when creating business databases and users, follow the principle of “users first, databases later”.

Pigsty defines roles and users through two config parameters:

The former defines roles/users shared across the entire environment; the latter defines business roles/users specific to a single cluster. Both have the same format as arrays of user definition objects. Users/roles are created sequentially in array order, so later users can belong to roles defined earlier.

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/role definition is a complex 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 - a valid, unique username within the cluster. All other params have sensible 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

String, required. Username - must be unique within the cluster.

Must be a valid PostgreSQL identifier matching ^[a-z_][a-z0-9_]{0,62}$: starts with lowercase letter or underscore, contains only lowercase letters, digits, underscores, max 63 chars.

- 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

These system users cannot be deleted via state: absent (to prevent cluster failure):

password

String, mutable. User password - users without password can’t login via password auth.

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 setting password, Pigsty temporarily disables logging to prevent leakage:

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}.

Set via COMMENT ON ROLE, supports special chars (quotes auto-escaped).

- name: dbuser_app
  comment: 'Main business application account'
COMMENT ON ROLE "dbuser_app" IS 'Main business application account';

login

Boolean, mutable. Can login, default true.

Setting false creates a Role rather than User - typically for permission grouping.

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

Boolean, mutable. Is superuser, default false.

Superusers have full database privileges, bypassing all permission checks.

- name: dbuser_admin
  superuser: true            # Dangerous: full privileges
ALTER USER "dbuser_admin" SUPERUSER;

Pigsty provides default superuser via pg_admin_username (dbuser_dba). Don’t create additional superusers unless necessary.

createdb

Boolean, mutable. Can create databases, default false.

- name: dbuser_dev
  createdb: true             # Allow create database
ALTER USER "dbuser_dev" CREATEDB;

Some applications (Gitea, Odoo, etc.) may require CREATEDB privilege for their admin users.

createrole

Boolean, mutable. Can create other roles, default false.

Users with CREATEROLE can create, modify, delete other non-superuser roles.

- name: dbuser_admin
  createrole: true           # Allow manage other roles
ALTER USER "dbuser_admin" CREATEROLE;

inherit

Boolean, mutable. Auto-inherit privileges from member roles, default true.

Setting false requires explicit SET ROLE to use member 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

Boolean, mutable. Can initiate streaming replication, default false.

Usually only replication users (replicator) need this. Normal users shouldn’t have it unless for logical decoding subscriptions.

- name: replicator
  replication: true          # Allow streaming replication
  roles: [pg_monitor, dbrole_readonly]
ALTER USER "replicator" REPLICATION;

bypassrls

Boolean, mutable. Bypass row-level security (RLS) policies, default false.

When enabled, user can access all rows even with RLS policies. Usually only for admins.

- name: dbuser_myappadmin
  bypassrls: true            # Bypass RLS policies
ALTER USER "dbuser_myappadmin" BYPASSRLS;

connlimit

Integer, mutable. Max concurrent connections, default -1 (unlimited).

Positive integer limits max simultaneous sessions for this user. Doesn’t affect 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

Integer, mutable. Expire N days from current date.

This param has higher priority than expire_at. Expiration recalculated on each playbook run - good for temp users needing 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.

Lower priority than expire_in. Use infinity for never-expiring users.

- 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

Array, additive. Roles this user belongs to. 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";

Full format - objects for fine-grained 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 }

set and inherit options only work in PG16+. On earlier versions they’re ignored with warning comments.

parameters

Object, mutable. Role-level config params via ALTER ROLE ... SET. Applies 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 special value DEFAULT (case-insensitive) to reset to PostgreSQL default:

- 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 params via pg_db_role_setting system view.

pgbouncer

Boolean, mutable. Add user to Pgbouncer user list, default false.

For prod users needing connection pool access, must explicitly set pgbouncer: true. Default false prevents accidentally exposing internal users to the pool.

# 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

Integer, mutable. User-level maximum pool connections. If omitted, no user-level override is generated and Pigsty’s global pgbouncer.ini default of 100 applies. PgBouncer uses 0 to mean unlimited.

- 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 itself restrict a user to offline instances. To establish that boundary, set role: offline on the corresponding HBA rule; see Offline Role and Instance Isolation.

To redesign your own ACL system, customize:

Pgbouncer Users

Pgbouncer is enabled by default as connection pool middleware. Pigsty adds all users in pg_users with explicit pgbouncer: true flag to the pgbouncer user list.

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 creating users, Pgbouncer user list is refreshed via online reload - doesn’t affect existing connections.

Pgbouncer runs as same dbsu as PostgreSQL (default postgres OS user). Use pgb alias to access pgbouncer admin functions.

pgbouncer_auth_query param allows dynamic query for pool user auth - convenient when you prefer not to manually manage pool users.

For user management operations, see User Management.

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