Database User: Permission denied for schema public

[aco-fwinzek]

Description

Provisioning a database results with a user that is not privileged to create tables on the public schema when using postgres version >= 15.

Expected Behavior

Though postgres revoked the usage of the public schema, the operator should grant the user of the database all privileges on schema public regarding the code: cursor.execute("GRANT ALL PRIVILEGES ON SCHEMA public TO %s", (AsIs(name),))

Setup / Steps to reproduce

• Azure Kubernetes Service (Kubernetes version: 1.28.9)
• Hybrid Cloud Postgresql Operator Version 0.5.4
• Backend: Azure Flexible Server

Operator config:

handler_on_resume: false
backend: azurepostgresflexible
allowed_backends: 
- azurepostgresflexible
backends:
  azure:
    subscription_id: {{ .Values.common.azureSubscriptionId }}
    location: {{ .Values.common.location }}
    name_pattern: "pg-{{ .Values.common.namePrefix }}-{name}"
    resource_group: {{ .Values.common.resourceGroup }}
    virtual_network: {{ .Values.common.vnet }}
    subnet:  {{ .Values.postgresql.subnet }}
    cpu_limit: 64
    storage_limit_gb: 1024
    server_delete_fake: false
    database_delete_fake: false
    lock_from_deletion: false
    admin_username: postgres
    tags: {}
    network:
      public_access: false
      allow_azure_services: true
    parameters:
      geo_redundant_backup: false
  azurepostgresflexible:
    classes:
      dev:
        name: Standard_B1ms
        tier: Burstable
      small:
        name: Standard_D2ds_v4
        tier: GeneralPurpose
    default_class: dev
    availability_zone: "3"
    #standby_availability_zone: "2"
    dns_zone: 
      name: {{ .Values.postgresql.dnsZone }}

Example Server/Database config:

# A simple example, can be used with the azure and helm backends
apiVersion: hybridcloud.maibornwolff.de/v1alpha1
kind: PostgreSQLServer
metadata:
  name: demoteam
  namespace: default
spec:
  version: "16"
  credentialsSecret: demoteam-postgres-credentials
---
apiVersion: hybridcloud.maibornwolff.de/v1alpha1
kind: PostgreSQLDatabase
metadata:
  name: test
  namespace: default
spec:
  serverRef:
    name: demoteam
  credentialsSecret: demoservice-postgres-credentials

On the schema public only the azure_pg_admin got UC privileges:

On the database test the user "test" got CTc privileges, which probably comes from cursor.execute("GRANT ALL PRIVILEGES ON DATABASE %s TO %s", (AsIs(database), AsIs(name))):

And, as expected, the user "test" has no permission to create tables:

But, when you look at the default postgres database, we see UC privileges for the user "test":

Suggestion

I think the problem is that with executing cursor.execute("GRANT ALL PRIVILEGES ON SCHEMA public TO %s", (AsIs(name),)) it will be executed on the database that is currently active.

    def __init__(self, credentials, dbname=None):
        if not dbname:
            dbname = credentials["dbname"]
        self._con = psycopg2.connect(host=credentials["host"], port=credentials["port"], dbname=dbname, user=credentials["username"], password=credentials["password"], sslmode=credentials["sslmode"])
        self._con.set_session(autocommit=True)

The database connection is established with the admin-credentials which is probably the postgres database?

    def _pgclient(self, admin_credentials, dbname=None) -> PostgresSQLClient:
        return PostgresSQLClient(admin_credentials, dbname)

[swoehrl-mw]

Hi @aco-fwinzek. Thanks for reporting this. I was able to reproduce this. I'll push a fix in the next few days.

[swoehrl-mw]

Hi @aco-fwinzek. I've created a fix and released it as version 0.5.5. Could you verify it works for you?

[aco-fwinzek]

@swoehrl-mw Works like a charm. Thank you.