Manage PostgreSQL users and pg_hba rules with PGO

When you create a PostgresCluster, PGO automatically provisions PostgreSQL users and databases and stores the resulting connection credentials in Kubernetes Secrets. You can declare additional users in the spec, control which databases each user can access, choose a password format, and customize authentication rules by writing directly to pg_hba.conf through the spec.authentication field.

Default user and database

PGO creates one PostgreSQL user and one database by default, both named after the PostgresCluster resource. For a cluster named hippo, this means:

PostgreSQL user: hippo
PostgreSQL database: hippo
Credentials Secret: hippo-pguser-hippo

The default user has full privileges on the default database. You can disable this behavior by setting spec.users to an empty list ([]), which instructs PGO to create no users at all.

Reading connection credentials

PGO stores connection details for each user in a Secret named <cluster>-pguser-<username>. The Secret contains the following keys:

Key	Content
`user`	PostgreSQL user name
`password`	Generated password (SCRAM-SHA-256 compatible)
`dbname`	Name of the first database listed in `spec.users[].databases`
`host`	DNS name of the primary Service (e.g. `hippo-primary.postgres-operator.svc`)
`port`	PostgreSQL listen port (default `5432`)
`uri`	`postgresql://` connection URI
`jdbc-uri`	`jdbc:postgresql://` connection URI for JDBC drivers

The dbname, uri, and jdbc-uri keys are only populated when at least one database is listed in spec.users[].databases. If no database is specified, those keys are absent.

If PGBouncer is enabled on the cluster, additional keys are added automatically:

Key	Content
`pgbouncer-host`	DNS name of the PGBouncer Service
`pgbouncer-port`	PGBouncer listen port
`pgbouncer-uri`	`postgresql://` URI pointing at PGBouncer
`pgbouncer-jdbc-uri`	JDBC URI pointing at PGBouncer (with `prepareThreshold=0`)

To retrieve credentials for the default user:

kubectl get secret hippo-pguser-hippo \
  --namespace postgres-operator \
  -o jsonpath='{.data.uri}' | base64 -d

Creating additional users

Declare extra users in spec.users. Each entry requires a name and optionally lists databases, role attributes, and a password format:

apiVersion: postgres-operator.crunchydata.com/v1beta1
kind: PostgresCluster
metadata:
  name: hippo
  namespace: postgres-operator
spec:
  postgresVersion: 16
  users:
    - name: hippo          # default user
      databases:
        - hippo
    - name: app-readonly
      databases:
        - hippo
      options: "NOSUPERUSER NOCREATEROLE NOCREATEDB"
    - name: app-writer
      databases:
        - hippo
        - reporting
      password:
        type: AlphaNumeric
  instances:
    - name: instance1
      replicas: 1
      dataVolumeClaimSpec:
        accessModes:
          - ReadWriteOnce
        resources:
          requests:
            storage: 1Gi

Removing a user from spec.users does not drop the PostgreSQL role or revoke database access. You must run DROP ROLE manually if you want to remove the user from PostgreSQL.

User field reference

Field	Type	Description
`name`	string (required)	PostgreSQL role name. Must be lowercase alphanumeric or hyphens; becomes part of the Secret name.
`databases`	list of strings	Databases this user can connect to and create objects in. Removing a database from this list does not revoke access.
`options`	string	`ALTER ROLE` options excluding `PASSWORD`. Example: `"NOSUPERUSER NOCREATEROLE"`. Cannot contain comments or the word `PASSWORD`.
`password.type`	string	`ASCII` (default) or `AlphaNumeric`. `ASCII` passwords include symbols; `AlphaNumeric` restricts to letters and digits.

The options and databases fields are ignored for the built-in postgres superuser.

Customizing authentication rules (pg_hba.conf)

PGO writes pg_hba.conf based on mandatory rules it controls (for monitoring, PgBouncer, and replication) plus any rules you define in spec.authentication.rules. Rules are evaluated in the order they appear; the first match determines whether and how the connection authenticates. When spec.authentication.rules is omitted or empty, PGO applies a default that accepts encrypted connections from any user with a password. To customize rules, set spec.authentication:

spec:
  authentication:
    rules:
      - connection: hostssl
        databases:
          - hippo
        users:
          - app-readonly
        method: scram-sha-256
      - connection: hostssl
        databases:
          - hippo
        users:
          - app-writer
        method: scram-sha-256
      - connection: host
        method: reject

Each rule supports these fields drawn from PostgresHBARuleSpec:

Field	Description
`connection`	Transport type: `host`, `hostssl`, `hostgssenc`, or others accepted by PostgreSQL.
`databases`	List of database names this rule matches. Omit or leave empty to match all databases.
`users`	List of user names this rule matches. Omit or leave empty to match all users.
`method`	Authentication method: `scram-sha-256`, `md5`, `cert`, `ldap`, `radius`, `reject`, etc. The value `trust` is explicitly blocked. The value `password` is automatically upgraded to `scram-sha-256`.
`options`	Key/value map of method-specific options (e.g. LDAP bind parameters).
`hba`	A single raw `pg_hba.conf` line. Cannot be combined with the other fields.

The trust method is blocked by PGO validation and will cause the PostgresCluster to be rejected. Use scram-sha-256 for password-based authentication.

Example: require certificate authentication

spec:
  authentication:
    rules:
      - connection: hostssl
        databases:
          - hippo
        users:
          - app-writer
        method: cert
      - connection: host
        method: reject

Example: raw HBA line

For advanced cases where you need a line not expressible via structured fields, use the hba key:

spec:
  authentication:
    rules:
      - hba: "hostssl hippo app-writer 10.0.0.0/8 scram-sha-256"

Rules written using hba are inserted verbatim into pg_hba.conf without validation. Include files (include, include_if_exists, include_dir) are not permitted.

TLS encryption — configuring server and replication certificates
Create a cluster — full cluster creation walkthrough
Connection pooling — PgBouncer setup and the extra Secret keys it populates
PostgresCluster reference — complete spec field documentation

Get Started

Installation

Core Operations

Day-Two Operations

Security

Manage PostgreSQL users and pg_hba rules with PGO

Default user and database

Reading connection credentials

Creating additional users

User field reference

Customizing authentication rules (pg_hba.conf)

Example: require certificate authentication

Example: raw HBA line

​Default user and database

​Reading connection credentials

​Creating additional users

​User field reference

​Customizing authentication rules (pg_hba.conf)

​Example: require certificate authentication

​Example: raw HBA line

​Related pages

Default user and database

Reading connection credentials

Creating additional users

User field reference

Customizing authentication rules (pg_hba.conf)

Example: require certificate authentication

Example: raw HBA line

Related pages