postgresql - 错误 : permission denied for schema user1_gmail_com at character 46

Question

我需要限制一个用户，只能访问特定的模式表。所以我尝试了以下查询并以 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;

Answer 1

您不仅需要授予对架构中表的访问权限，还需要授予对架构本身的访问权限。

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