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

Activity

1. ThoSap

   self-assigned this

   on Sep 11, 2026
2. ThoSap

   added the

   Bug

   issue type on Sep 11, 2026
3. ThoSap

   added

   bugSomething isn't workingSomething isn't working

   on Sep 11, 2026
4. ThoSap commented on Sep 11, 2026

   

   ThoSap

   on Sep 11, 2026

   Member

   More actions

   Thanks for opening the issue, I'm having a look at this.
5. ThoSap commented on Sep 11, 2026

   

   ThoSap

   on Sep 11, 2026

   Last edited by ThoSap

   Member

   More actions

   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.
6. wesselOC commented on Sep 11, 2026

   

   wesselOC

   on Sep 11, 2026

   Author

   More actions

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

   #72
7. ThoSap

   closed this as completedin #71on Oct 5, 2026
8. ThoSap commented on Oct 6, 2026

   

   ThoSap

   on Oct 6, 2026

   Member

   More actions

   @wesselOC @rudolfduplessis new version https://github.com/aboutbits/postgresql-operator/releases/tag/v0.7.0 released just now 🎉

   See section Upgrade notes in the release.

   Sorry for the long wait 🙏🏼
9. rudolfduplessis commented on Oct 7, 2026

   

   rudolfduplessis

   on Oct 7, 2026

   More actions

   No problem thank you very much!

   👍React with 👍1Reacted by Thomas Sapelza