错误：模式46的user1_gmail_com权限被拒绝 [英] ERROR: permission denied for schema user1_gmail_com at character 46

问题描述

我需要限制用户，只能访问特定的模式表。因此，我尝试按照user1_gmail_com的身份进行查询和登录。但是，当我尝试浏览任何架构表时出现以下错误。

I need to restrict a user, access only on a particualr schema tables only.So I tried following query and login as user1_gmail_com. But I got following error when I try to browse any schema table.

我的查询

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;

推荐答案

您不仅需要授予对表的访问权限

You need to grant access not only to the tables in the schema, but also to the schema itself.

来自手动：

默认情况下，用户无法访问他们不拥有的模式中的任何对象。为此，架构的所有者必须授予该架构的USAGE特权。

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授予该用户。

So either make your created user the owner of the schema, or grant USAGE on the schema to this user.

这篇关于错误：模式46的user1_gmail_com权限被拒绝的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！