ERROR: permission denied for relation tablename on Postgres while trying a SELECT as a readonly user如何解决?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (101)
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

readonly用户可以连接,查看表格,但是当它试图做一个简单的选择时,它会得到:

ERROR: permission denied for relation mytable
SQL state: 42501

这发生在PostgreSQL 9.1上

我做错了什么?

提问于
用户回答回答于

这里是最近更新的PostgreSQL 9+的完整解决方案。

CREATE USER readonly  WITH ENCRYPTED PASSWORD 'readonly';
GRANT USAGE ON SCHEMA public to readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

-- repeat code below for each database:

GRANT CONNECT ON DATABASE foo to readonly;
\c foo
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO readonly; --- this grants privileges on new tables generated in new database "foo"
GRANT USAGE ON SCHEMA public to readonly; 
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

感谢http://jamie.curle.io/creating-a-read-only-user-in-postgres/对于几个重要方面

如果任何人发现较短的代码,并且最好能够对所有现有数据库执行此操作,那么额外的荣誉。

用户回答回答于

尝试添加

GRANT USAGE ON SCHEMA public to readonly;

可能错过了,必须具有架构的权限才能使用此架构中的对象。

扫码关注云+社区

领取腾讯云代金券