英文:
How to GRANT permission to a user to read all the VIEWs in PostgreSQL database
问题
我有一个名为 Testuser 的用户在一个 PostgreSQL 14 数据库中，我需要让这个 Testuser 连接到名为 Testdatabase 的数据库，并且只读取该数据库中的所有 VIEW。
有人可以帮助我了解如何授予 Testuser 仅仅读取 Testdatabase 中的所有 VIEW 的权限吗？
谢谢！
英文:
I have a user named Testuser in a PostgreSQL 14 database, and I need this Testuser to connect to a database named Testdatabase and read all the VIEWs in that database.
Can anyone help me understand how to GRANT permission to Testuser to only read all the VIEWs in Testdatabase?
Thank you!
答案1
得分: 2
您可以在以下查询的组合中使用 psql 的 \gexec：
SELECT format(
'GRANT SELECT ON %s TO testuser',
oid::regclass
)
FROM pg_class /* 所有关系的元数据表 */
WHERE relkind = 'v' /* 是一个视图 */
AND relnamespace NOT IN /* 不在系统模式中的 */
('pg_catalog'::regnamespace,
'information_schema'::regnamespace)
\gexec
\gexec 将执行每个结果行作为对数据库的 SQL 语句。
不要忘记在模式上授予 USAGE 权限。
要处理将来创建的视图，您需要采用不同的方法。您可以更改默认特权，但这将不加区分地应用于表和视图，这可能不是您想要的。在这种情况下，一种解决方法是将视图保存在单独的模式中，然后仅为该模式中的对象更改默认特权：
CREATE SCHEMA myviews;
GRANT USAGE ON SCHEMA myviews TO testuser;
ALTER DEFAULT PRIVILEGES FOR ROLE view_creator
GRANT SELECT ON TABLES IN SCHEMA myviews TO testuser;
英文:
You can use psql's \gexec in combination with the following query:
SELECT format(
'GRANT SELECT ON %s TO testuser',
oid::regclass
)
FROM pg_class /* the metadata table of all relations */
WHERE relkind = 'v' /* is a view */
AND relnamespace NOT IN /* that is not in a system schema */
('pg_catalog'::regnamespace,
'information_schema'::regnamespace)
\gexec
\gexec will execute each result row as an SQL statement against the database.
Don't forget to grant USAGE on the schemas as well.
To deal with views created in the future, you need a different approach. You could change default privileges, but that will apply to tables and views indiscriminately, which is probably not what you want. A way out here could be to keep the views in a separate schema and change the default privileges only for objects in that schema:
CREATE SCHEMA myviews;
GRANT USAGE ON SCHEMA myviews TO testuser;
ALTER DEFAULT PRIVILEGES FOR ROLE view_creator
GRANT SELECT ON TABLES IN SCHEMA myviews TO testuser;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论