Rermission denied for table pg_authid (SQLSTATE 42501) on AWS RDS

[wesselOC]

Summary

Reconciling a Role against an AWS RDS PostgreSQL instance fails with:

SQL Error [42501]: ERROR: permission denied for table pg_authid

Cause

The operator reads role state directly from the pg_authid catalog. On AWS RDS,
SELECT on pg_authid is revoked from every role — including the master
user and rds_superuser. The very first read (roleExists) therefore fails
before any role can be created or updated, so the operator is unusable on RDS.

The same restriction applies to any non-superuser role on vanilla PostgreSQL,
since pg_authid is superuser-only by default.

Impact

• Role resources never reconcile on RDS; they error out immediately.
• Affects all role operations (existence, login/flags/membership checks, and
  password verification).

Expected behaviour

The operator should reconcile Role resources on RDS (and as any non-superuser)
without requiring SELECT on pg_authid.

Environment

• PostgreSQL: AWS RDS
• Operator: v0.6.0

[ThoSap]

Thanks for opening the issue, I'm having a look at this.

[ThoSap]

I reproduced the problem and analyzed the fix. Here is what I found and what is planned.

Verified

I ran the operator's SQL against PostgreSQL 15 and 18 as a role with LOGIN NOSUPERUSER CREATEDB CREATEROLE. This mirrors the RDS master user (rds_superuser).

• SELECT on pg_authid and pg_shadow is denied (SQLSTATE 42501). The Role controller reads both role state and the password hash from pg_authid, so every reconcile of an existing role fails.
• pg_roles, pg_auth_members, and shobj_description(oid, 'pg_authid') work. They provide every column the operator needs, except the password hash.
• There is a second blocker. The operator's ALTER ROLE statement always names NOSUPERUSER, NOREPLICATION, and NOBYPASSRLS. PostgreSQL rejects these options from a non-superuser even when the value does not change. So after the catalog fix, updates of existing roles would still fail on RDS. CREATE ROLE is not affected.
• The password hash cannot be read on RDS in any way. AWS confirms this. The same applies to Cloud SQL, Azure Flexible Server, Neon, Supabase, and most other managed offerings.

Planned fix

One PR with three changes:

1. Read role state from pg_roles instead of pg_authid. Membership still comes from pg_auth_members.
2. Emit SUPERUSER, REPLICATION, and BYPASSRLS only when the value changes. A Role that asks for one of these flags on RDS then fails with a clear PostgreSQL error in its status.
3. Replace the password hash comparison. The operator will store a keyed fingerprint of the Secret password in the Role status and compare against it on each reconcile. The fingerprint is an HMAC-SHA256 with a random key that only the operator holds, kept in a Secret in the operator namespace. A reader of the Role status learns nothing about the password without that key. When the fingerprint differs, the operator runs ALTER ROLE ... PASSWORD. The plan is also to send a SCRAM-SHA-256 verifier instead of the cleartext password, as CloudNativePG does since v1.29.2. The cleartext then never reaches the server or its logs.

A new test profile will run the Role tests with a non-superuser admin, so this does not regress.

Consequences that will be documented

• Minimum privileges of the admin role: LOGIN, CREATEROLE, and CREATEDB for the Database resource.
• A non-superuser admin cannot set superuser, replication, or bypassrls on a Role.
• On PostgreSQL 16 and later, the admin can only alter roles on which it has ADMIN OPTION. Roles created by another user are outside the operator's reach.
• A password change made directly in PostgreSQL is no longer detected. The Secret is the source of truth.
• Existing Roles get one password update after the upgrade, because their status has no fingerprint yet. The same happens once if the operator's fingerprint key is lost.
• A pre-hashed password bypasses server-side password policies (for example credcheck or the Cloud SQL password policy) and requires SCRAM-capable clients. An opt-out for MD5-only setups is planned.

A later step could read pg_authid when permitted and fall back to the fingerprint on SQLSTATE 42501. This would restore drift detection for superuser admins. That decision comes after the first fix ships.

[wesselOC]

I made the following PR if you are interested in another possible fix

#72