在所有上下文中都禁止AWS RDS PostgreSQL访问pg_catalog.pg_authid吗? [英] AWS RDS PostgreSQL access to pg_catalog.pg_authid forbidden in all contexts?

问题描述

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吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！