Create a Database and give the ownership to a new role (ALL PRIVILEGES)

[Smana]

What happened?

I'm currently building a composition that allows to create an RDS instance, databases and their credentials. The RDS instance is created and the SQL provider is configured properly.

My databases are created

kubectl get databases.postgresql.sql.crossplane.io 
NAME            READY   SYNCED   AGE
harbor          True    True     23m
notary-server   True    True     23m
notary-signer   True    True     23m

example of a database manifest:

apiVersion: postgresql.sql.crossplane.io/v1alpha1
kind: Database
metadata:
  annotations:
    crossplane.io/composition-resource-name: db-harbor
    crossplane.io/external-create-pending: "2023-11-29T19:52:28Z"
    crossplane.io/external-create-succeeded: "2023-11-29T19:52:28Z"
    crossplane.io/external-name: harbor
  labels:
    crossplane.io/claim-name: xplane-harbor
    crossplane.io/claim-namespace: harbor
    crossplane.io/composite: xplane-harbor-6nkw9
  name: harbor
spec:
  deletionPolicy: Orphan
  forProvider:
    allowConnections: true
    connectionLimit: -1
    encoding: UTF8
    isTemplate: false
    lcCType: en_US.UTF-8
    lcCollate: en_US.UTF-8
    owner: master
    tablespace: pg_default
  providerConfigRef:
    name: xplane-harbor

A role is created properly

kubectl get roles.postgresql.sql.crossplane.io 
NAME     READY   SYNCED   CONN LIMIT   PRIVILEGES
harbor   True    True     -1           ["NOSUPERUSER","INHERIT","NOCREATEDB","NOCREATEROLE","LOGIN","NOREPLICATION","NOBYPASSRLS"]

apiVersion: postgresql.sql.crossplane.io/v1alpha1
kind: Role
metadata:
  annotations:
    crossplane.io/composition-resource-name: owner-harbor
    crossplane.io/external-create-pending: "2023-11-29T19:54:32Z"
    crossplane.io/external-create-succeeded: "2023-11-29T19:54:32Z"
    crossplane.io/external-name: harbor
  labels:
    crossplane.io/claim-name: xplane-harbor
    crossplane.io/claim-namespace: harbor
    crossplane.io/composite: xplane-harbor-6nkw9
  name: harbor
spec:
  deletionPolicy: Delete
  forProvider:
    connectionLimit: -1
    privileges:
      bypassRls: false
      createDb: false
      createRole: false
      inherit: true
      login: true
      replication: false
      superUser: false
  providerConfigRef:
    name: xplane-harbor
  writeConnectionSecretToRef:
    name: sql-role-harbor
    namespace: crossplane-system

However I'm not able to grant all privileges to the databases for this role. The grants statuses stay False

kubectl get grants.postgresql.sql.crossplane.io 
NAME                         READY   SYNCED   AGE   ROLE     MEMBER OF   DATABASE        PRIVILEGES
grant-harbor-harbor          False   True     12m   harbor               harbor          ["ALL"]
grant-notary-server-harbor   False   True     12m   harbor               notary-server   ["ALL"]
grant-notary-signer-harbor   False   True     12m   harbor               notary-signer   ["ALL"]

apiVersion: postgresql.sql.crossplane.io/v1alpha1
kind: Grant
metadata:
  annotations:
    crossplane.io/composition-resource-name: grant-harbor-harbor
    crossplane.io/external-create-pending: "2023-11-29T20:10:41Z"
    crossplane.io/external-create-succeeded: "2023-11-29T20:10:41Z"
    crossplane.io/external-name: grant-harbor-harbor
  labels:
    crossplane.io/claim-name: xplane-harbor
    crossplane.io/claim-namespace: harbor
    crossplane.io/composite: xplane-harbor-6nkw9
  name: grant-harbor-harbor
spec:
  deletionPolicy: Delete
  forProvider:
    database: harbor
    databaseRef:
      name: harbor
    privileges:
    - ALL
    role: harbor
    roleRef:
      name: harbor
    withOption: GRANT
  providerConfigRef:
    name: xplane-harbor

I'm probably missing something but I didn't find anything. I checked the logs on RDS but nothing related to GRANT commands... Could you please give me a hand?

What environment did it happen in?

Crossplane version: 1.14.4 provider version 0.7.0 Running on EKS 1.28

[Smana]

The only method I find so far requires an manual operation.

1. Create the database and the role, and set the owner field

   apiVersion: postgresql.sql.crossplane.io/v1alpha1
   kind: Role
   metadata:
   annotations:
   crossplane.io/composition-resource-name: owner-harbor
   crossplane.io/external-create-pending: "2023-12-01T08:25:39Z"
   crossplane.io/external-create-succeeded: "2023-12-01T08:25:39Z"
   crossplane.io/external-name: harbor
   labels:
   crossplane.io/claim-name: xplane-harbor
   crossplane.io/claim-namespace: harbor
   crossplane.io/composite: xplane-harbor-8c62n
   name: harbor
   spec:
   deletionPolicy: Delete
   forProvider:
   connectionLimit: -1
   privileges:
     bypassRls: false
     createDb: false
     createRole: false
     inherit: true
     login: true
     replication: false
     superUser: false
   providerConfigRef:
   name: xplane-harbor
   writeConnectionSecretToRef:
   name: sql-role-harbor
   namespace: crossplane-system
   ---
   apiVersion: postgresql.sql.crossplane.io/v1alpha1
   kind: Database
   metadata:
   annotations:
   crossplane.io/composition-resource-name: db-harbor
   crossplane.io/external-create-pending: "2023-12-01T08:25:39Z"
   crossplane.io/external-create-succeeded: "2023-12-01T08:25:39Z"
   crossplane.io/external-name: harbor
   labels:
   crossplane.io/claim-name: xplane-harbor
   crossplane.io/claim-namespace: harbor
   crossplane.io/composite: xplane-harbor-8c62n
   name: harbor
   spec:
   deletionPolicy: Orphan
   forProvider:
   allowConnections: true
   connectionLimit: -1
   encoding: UTF8
   isTemplate: false
   lcCType: en_US.UTF-8
   lcCollate: en_US.UTF-8
   owner: harbor
   tablespace: pg_default
   providerConfigRef:
   name: xplane-harbor

2. Then a manual grant using the master user so that the master user is able to change the ownership

   
   psql -h xplane-harbor-8c62n-72k8n.cymnaynfchjt.eu-west-3.rds.amazonaws.com -U master -W postgres

postgres=> GRANT harbor to master; GRANT ROLE

Othewise I get an error
```console
ERROR:  must be member of role "harbor"