3
PostgreSQL said: permission denied for relation pg_authid

Is pg_authid just unavailable on AWS RDS in all contexts because of RDS locking down the super role? My role created the table, so pg_catalog should come by default (and not need to be added to search path) if I'm reading psql docs right. Just need SELECT, not create ability.

Haven't been able to find a definitive AWS RDS documentation page where it says that pg_catalog.pg_authid is not allowed in any context, but I've inherited a documentation project that is relying on being able to form queries and joins on the pg_authid table in the DB I just created. I always get the above permission denied.

Tried adding a postgres role and giving it to myself, and also explicitly adding the db to my search path, to no avail.

6

The catalog pg_authid contains information about database authorization identifiers (roles). As you might be aware, that due to managed nature off RDS as a service, unfortunately it is not possible to have the full superuser role in RDS.

Unfortunately as the above mentioned is a limitation on RDS, if the access to 'pg_authid' is utmost necessary for performing your business, I would suggest you to look for EC2 hosted Postgres (community Postgres) as an option. The workaround to view the contents of 'pg_authid' is to use 'pg_roles', but the passwords are masked and would not tell you if it is encrypted or not.

Kindly note, not all catalogs are restricted from being read on RDS, below is the SQL Query which shows the privileges rds_superuser/master user has on each catalog.

SELECT relname, has_table_privilege('rds_superuser',relname,'SELECT') as SELECT,has_table_privilege('rds_superuser',relname,'UPDATE') as UPDATE,has_table_privilege('rds_superuser',relname,'INSERT') as INSERT,has_table_privilege('rds_superuser',relname,'TRUNCATE') as TRUNCATE FROM pg_class c , pg_namespace n where n.oid = c.relnamespace  and n.nspname in ('pg_catalog')  and relkind='r';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.