问题描述
PostgreSQL said: permission denied for relation pg_authid
由于RDS锁定了super角色，pg_authid在所有上下文中是否仅在AWS RDS上不可用?我的角色创建了表格，因此pg_catalog应该默认出现(并且不需要添加到搜索路径中)，如果我没看错psql文档.只需要SELECT，而不是创造能力.
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.
未能找到确定的AWS RDS文档页面，该页面上说在任何情况下都不允许使用pg_catalog.pg_authid，但是我继承了一个文档项目，该项目依赖于能够形成查询和加入我刚创建的数据库中的pg_authid表.我总是会拒绝上述许可.
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.
试图添加postgres角色并将其分配给自己，并且还明确地将数据库添加到我的搜索路径中，但无济于事.
Tried adding a postgres role and giving it to myself, and also explicitly adding the db to my search path, to no avail.
推荐答案
目录pg_authid包含有关数据库授权标识符(角色)的信息.您可能已经知道，由于RDS即服务的托管性质，因此很可能无法在RDS中拥有完整的超级用户角色.
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.
不幸的是，由于上面提到的是对RDS的限制，如果对执行业务非常需要访问"pg_authid"，我建议您选择使用EC2托管的Postgres(社区Postgres).查看"pg_authid"内容的解决方法是使用"pg_roles"，但是密码被屏蔽，并且不会告诉您是否已加密.
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.
请注意，并非所有目录都被限制不能在RDS上读取，下面是SQL查询，该查询显示rds_superuser/master用户对每个目录具有的特权.
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';
这篇关于在所有上下文中都禁止AWS RDS PostgreSQL访问pg_catalog.pg_authid吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！