(postgresql) Grant privileges on database - external resource existence never confirmed

[aawoznia26]

What happened?

When granting database privileges to a role that already has some existing privileges for the same database and grant option, the managed resource never reaches a ready state. Most probably issue is in grant validation query expecting exactly the same array of privileges to be granted as specified in manages resource. For example, the actual privileges value assigned to the role is {CONNECT,CREATE,TEMPORARY}, while the validation query only expects {CONNECT}

How can we reproduce it?

Create database and role

CREATE ROLE "my-service_user" PASSWORD 'mKMTrwhl79' INHERIT NOCREATEDB NOCREATEROLE LOGIN;
GRANT "my-service_user" TO "postgres";
ALTER ROLE "my-service_user" CONNECTION LIMIT -1;
CREATE DATABASE "my-service" OWNER "my-service_user" ALLOW_CONNECTIONS true CONNECTION LIMIT -1;

Then add grant

apiVersion: postgresql.sql.crossplane.io/v1alpha1
kind: Grant
metadata:
  name: example-grant
spec:
  deletionPolicy: Delete
  forProvider:
    database: my-service
    privileges:
      - CONNECT
    revokePublicOnDb: true
    role: my-service_user
  managementPolicies:
    - '*'
  providerConfigRef:
    name: postgresql-config

Check status
kubectl get grants.postgresql.sql.crossplane.io example-grant -o jsonpath='{.status.conditions}' | jq '.'

Ready is false, while expected is true

[
  {
    "lastTransitionTime": "2025-05-30T17:30:29Z",
    "reason": "Creating",
    "status": "False",
    "type": "Ready"
  },
  {
    "lastTransitionTime": "2025-05-30T17:30:29Z",
    "reason": "ReconcileSuccess",
    "status": "True",
    "type": "Synced"
  }
]

provider-sql logs

2025-05-30T17:34:35Z	DEBUG	provider-sql	Waiting for external resource existence to be confirmed	{"controller": "managed/grant.postgresql.sql.crossplane.io", "request": {"name":"example-grant"}, "uid": "3df4922e-a5ca-4d07-bb77-3bad096dfacc", "version": "568185", "external-name": "example-grant"}
2025-05-30T17:34:35Z	DEBUG	events	Waiting for external resource existence to be confirmed	{"type": "Normal", "object": {"kind":"Grant","name":"example-grant","uid":"3df4922e-a5ca-4d07-bb77-3bad096dfacc","apiVersion":"postgresql.sql.crossplane.io/v1alpha1","resourceVersion":"568185"}, "reason": "PendingExternalResource"}

What environment did it happen in?

Crossplane version:

• Client Version: v1.17.0
• Server Version: v1.17.0

Kubernetes version:

• Client Version: v1.29.6
• Server Version: v1.29.6+k3s2

provider-sql version: 0.11.0