Skip to content

Database not owned by user #4085

Open
Open
@idc77

Description

@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;

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions