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

发布时间：2020-12-13 16:38:17 所属栏目：百科来源：网络整理

导读：我需要限制一个用户，只访问一个特定的schema表。所以我尝试以下的查询和登录为user1_gmail_com。但是当我尝试浏览任何模式表时，我收到以下错误。我的查询： SELECT clone_schema('my_application_template_schema','user1_gmail_com');CREATE USER user1_

我需要限制一个用户，只访问一个特定的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授予该用户。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!