PostgreSQL 只读角色与用户

Question

我找不到这个问题的答案：为什么在授予权限后从表中选择失败?

-- create new role
CREATE ROLE readonly;

-- grant access to all existing tables
GRANT CONNECT ON DATABASE shop TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO readonly;

-- grant access to all table which will be created in the future
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON SEQUENCES TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT EXECUTE ON FUNCTIONS TO readonly;

-- create user and grant role to this user
CREATE USER b_readonly WITH PASSWORD 'reAdOnLy123';
GRANT readonly TO b_readonly;

我的来自数据库的错误信息如下：

错误：关系 customer_search_query 的权限被拒绝 SQL状态：42501

在Postgresql 9.6.5中有什么新技巧吗？

- Mike Winkelmann

---

排除显而易见的情况：表customer_search_query是否在模式public中，或者它可能是在搜索路径上的其他模式中创建的？ - user330315

Answer 1

如果pg版本小于14，则尝试如下：

postgres=# CREATE ROLE readaccess;
postgres=# CREATE USER read_user WITH PASSWORD 'read_password';
postgres=# GRANT readaccess TO read_user;
    
--- INPORTANT (select needed db)---
postgres=# \с your_db;
your_db=# GRANT CONNECT ON DATABASE your_db TO readaccess;
your_db=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO readaccess;

如果pg版本>=14

 GRANT pg_read_all_data TO readaccess;

- Slvk

---

pg_read_all_data 应该也能在 Postgresql 14 中使用，但我还没有尝试过。相关文档。 - yildirim

Answer 2

很可能你正在查询的表，customer_search_query 不在公共模式中。尝试运行此命令。

GRANT SELECT ON customer_search_query TO readonly;

- sindhu_sp