CNPG – Installation – Roles and Databases

Installing Your CNPG Cluster: Roles, Databases, and Access Control

In the previous post, we walked through installing the CloudNativePG operator and bootstrapping a basic cluster. Now it is time to go deeper and configure roles, databases, and proper access controls—especially when running multiple databases within the same cluster.

If you are only hosting a single database, the basic bootstrap configuration from the last post is often all you need. It automatically creates the database, its owner, and a Kubernetes Secret for credentials:

bootstrap:
  initdb:
    database: app          # Name of your database
    owner: app             # Database owner role
    secret:
      name: app-secret     # Kubernetes Secret for credentials

Choose clear, descriptive names that match your application. For many environments, however, you will need multiple databases with isolated owners and stricter security rules.

Enabling Superuser Access

Especially with multiple databases, it is highly recommended to enable superuser access on the cluster. This account is extremely useful for cross-database maintenance tasks, running custom backup scripts, performing cleanups, or executing administrative procedures.

Add this to your Cluster spec:

spec:
  instances: 3
  imagePullPolicy: IfNotPresent
  enableSuperuserAccess: true   # Recommended,  this enables the superuser account

The operator automatically creates a superuser and stores its credentials in a Secret named -superuser in the same namespace. You can retrieve the username and password from there whenever needed.

Adding Multiple Databases: Secrets, Roles, and pg_hba Rules

For each additional database, you typically need three components:

• A Kubernetes Secret containing the username and password.
• A managed Role defined in the Cluster.
• A Database custom resource to create the actual database.
• Optional but recommended: pg_hba rules for fine-grained connection control.

Here is a complete example for a database called my-cool-app-db:

# 1. Kubernetes Secret (create this first)
apiVersion: v1
kind: Secret
metadata:
  name: my-cool-app-db-cluster-secret
  namespace: postgres
  labels:
    cnpg.io/reload: "true"
type: kubernetes.io/basic-auth
data:
  username: YWRtaW4=                      # base64 encoded "admin"
  password: c3VwZXJzdHJvbmdwYXNzd29yZA==  # base64 encoded "password"

# 2. Cluster definition with role and pg_hba
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: cluster-example-initdb
  namespace: postgres
spec:
  instances: 3
  imagePullPolicy: IfNotPresent
  enableSuperuserAccess: true

  managed:
    roles:
      - name: my-cool-app-role
        ensure: present
        comment: "Role for Cool-App database"
        passwordSecret:
          name: my-cool-app-db-cluster-secret
        login: true                    # Important: allows login

  postgresql:
    pg_hba:
      - hostssl my-cool-app-db my-cool-app-role 10.244.0.0/16 md5

  # ... existing bootstrap and storage sections ...

# 3. Create the actual database
apiVersion: postgresql.cnpg.io/v1
kind: Database
metadata:
  name: my-cool-app-db
  namespace: postgres
spec:
  name: my-cool-app-db
  owner: my-cool-app-role
  cluster:
    name: cluster-example-initdb
  ```

Apply everything with:

```bash
kubectl apply -f your-cluster.yaml

or your preferred CI/CD tol, i.e. argo-cd.

Tip: For demonstration reasons, the 3 sections above were all listed in the cluster-config.yaml For better long-term maintainability, split Secrets, the main Cluster definition, and individual Database resources into separate YAML files. This makes it easier to manage and review changes over time.

How it all acts together

• Kubernetes Secrets store credentials securely.
• The CNPG Operator reads these Secrets and creates or updates PostgreSQL Roles (users) inside the database.
• You define pg_hba rules in the Cluster manifest to control who can connect, from where, and how (SSL, authentication method).
• Applications connect using the credentials from the Secrets, and only connections matching the pg_hba rules are allowed.

This setup gives you strong isolation and security while keeping everything declarative and GitOps-friendly.

Best Practices

• Use strong, unique passwords for each database role.
• Leverage pg_hba rules to restrict connections by database, user, and network source.
• Keep Secrets in Git (encrypted if necessary) or manage them through your secret management solution.
• Test access thoroughly after applying changes.

With roles and databases properly configured, your CNPG cluster is now ready for real data.

In the next post, we will cover importing existing database content into your new CNPG cluster—using logical replication, pg_dump/restore, and other proven strategies.
Have you started building your CNPG clusters? What challenges have you run into with roles and multi-database setups? Share your experiences in the comments.
Stay tuned for the next installment in the CNPG series.

Did you find this post helpful? You can support me.

Related posts

• Databases – Postgresql – Pilot
• Databases – Postgresql – Gitea and PGBouncer
• Databases – Postgresql – PGbouncer
• CNPG – Cloud Native Postgresql – Pilot
• CNPG – considerations for migrating databases
• CNPG – installation
• CNPG – Installation – Database Import

Author Profile

12ww1160DevOps engineer & architect

Latest entries

• blog27.06.2026CNPG – Installation – Database Import
• blog27.06.2026CNPG – Installation – Roles and Databases
• blog25.06.2026CNPG – installation
• blog24.06.2026CNPG – considerations for migrating databases