Database not owned by user

[idc77]

Overview

When applying the modified example kustomize/postgres yaml the database created is not owned by the user, but postgres.

Environment

Please provide the following details:

• Platform: microk8s
• Platform Version: 1.32.1
• PGO Image Tag: I don't know
• Postgres Version 17
• Storage: rook ceph

Steps to Reproduce

REPRO

Provide steps to get to the error condition:

1. follow the docs
2. try to connect as the user providing the password or using the uri from the secret from some other container in the same namespace

EXPECTED

1. The database created should be owned by the user it's associated with in the yaml file

ACTUAL

1. It's owned by postgres

Logs

apiVersion: postgres-operator.crunchydata.com/v1beta1
kind: PostgresCluster
metadata:
  name: blogs
  namespace: blogs
spec:
  postgresVersion: 17
  users:
    - name: blogs
      databases:
        - blogs
  instances:
    - name: instance1
      dataVolumeClaimSpec:
        accessModes:
          - "ReadWriteOnce"
        resources:
          requests:
            storage: 1Gi
  backups:
    pgbackrest:
      repos:
        - name: repo1
          volume:
            volumeClaimSpec:
              accessModes:
                - "ReadWriteOnce"
              resources:
                requests:
                  storage: 1Gi

Inside the database instance

bash-4.4$ psql
psql (17.2)
Type "help" for help.

postgres=# \du
                               List of roles
  Role name   |                         Attributes                         
--------------+------------------------------------------------------------
 _crunchyrepl | Replication
 blogs        | 
 postgres     | Superuser, Create role, Create DB, Replication, Bypass RLS

postgres=# \l
                                                     List of databases
   Name    |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | Locale | ICU Rules |   Access privileges   
-----------+----------+----------+-----------------+-------------+-------------+--------+-----------+-----------------------
 blogs     | postgres | UTF8     | libc            | en_US.utf-8 | en_US.utf-8 |        |           | =Tc/postgres         +
           |          |          |                 |             |             |        |           | postgres=CTc/postgres+
           |          |          |                 |             |             |        |           | blogs=CTc/postgres
 postgres  | postgres | UTF8     | libc            | en_US.utf-8 | en_US.utf-8 |        |           | 
 template0 | postgres | UTF8     | libc            | en_US.utf-8 | en_US.utf-8 |        |           | =c/postgres          +
           |          |          |                 |             |             |        |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | en_US.utf-8 | en_US.utf-8 |        |           | =c/postgres          +
           |          |          |                 |             |             |        |           | postgres=CTc/postgres

Additional Information

When I tried to connect from my deployment to the database as the user with the secret credentials provided, I received

ERROR: permission denied for schema public (SQLSTATE 42501)

I had to manually

ALTER DATABASE blogs OWNER TO blogs;

[andrewlecuyer]

@idc77 to avoid this error, you'll want to enable the automatic creation of per-user schemas, as described here:

https://access.crunchydata.com/documentation/postgres-operator/latest/tutorials/basic-setup/user-management#automatically-creating-per-user-schemas

[idc77]

That is not what I want. It just happened that the user and database name is the same. Every created database should be owned by the user under which it's under.

This cost me the better part of the day from going to debugging encoding to rewriting yaml files to finally actually checking what should be obvious, that a database that is under the user should be owned by that user. That's a no brainer

[Kariton]

i ran into the same issue.

my db and my user match by chance. but the db is not owned by the user assigned through config:

spec:
  users:
    # Superuser
    - name: postgres
      databases: ["postgres"]
      options: SUPERUSER
      password:
        type: AlphaNumeric
    # Application
    - name: ${USERNAME:=${APP}}
      databases: ${DATABASES:=["${APP}"]}
      password:
        type: AlphaNumeric

i template those through flux.

manually altering db owner resolves the issue.