postgresql – 错误:在字符46处拒绝模式user1_gmail_com的权限

前端之家收集整理的这篇文章主要介绍了postgresql – 错误:在字符46处拒绝模式user1_gmail_com的权限前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
我需要限制一个用户,只访问一个特定的schema表。所以我尝试以下的查询登录为user1_gmail_com。但是当我尝试浏览任何模式表时,我收到以下错误

我的查询

SELECT clone_schema('my_application_template_schema','user1_gmail_com');
CREATE USER user1_gmail_com WITH PASSWORD 'myloginpassword';
REVOKE  ALL ON ALL TABLES IN SCHEMA user1_gmail_com FROM PUBLIC;
GRANT SELECT ON ALL TABLES IN SCHEMA user1_gmail_com TO user1_gmail_com;

sql错误

ERROR:  permission denied for schema user1_gmail_com at character 46
In statement:
SELECT COUNT(*) AS total FROM (SELECT * FROM "user1_gmail_com"."organisations_table") AS sub

更新工作查询

SELECT clone_schema('my_application_template_schema','user1_gmail_com');
CREATE USER user1_gmail_com WITH PASSWORD 'myloginpassword';
REVOKE  ALL ON ALL TABLES IN SCHEMA user1_gmail_com FROM PUBLIC;
GRANT USAGE ON SCHEMA user1_gmail_com TO user1_gmail_com;
GRANT SELECT ON ALL TABLES IN SCHEMA user1_gmail_com TO user1_gmail_com;
您不仅需要授予对架构中的表的访问权限,还可以授予架构本身的访问权限。

manual

By default,users cannot access any objects in schemas they do not own. To allow that,the owner of the schema must grant the USAGE privilege on the schema.

因此,要么将创建的用户设置为模式的所有者,要么将该模式的USAGE授予该用户

猜你在找的Postgre SQL相关文章