postgresql 错误：架构 user1_gmail_com 在字符 46 处的权限被拒绝
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19045149/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ERROR: permission denied for schema user1_gmail_com at character 46
提问by Ramprasad
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.
我需要限制用户，只能访问特定的模式表。所以我尝试按照查询并以 user1_gmail_com 登录。但是当我尝试浏览任何架构表时出现以下错误。
My Query:
我的查询：
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:
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
Updated Working Query:
更新的工作查询：
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;
回答by Petter Engstr?m
You need to grant access not only to the tables in the schema, but also to the schema itself.
您不仅需要授予对架构中表的访问权限，还需要授予对架构本身的访问权限。
From the 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 权限。
So either make your created user the owner of the schema, or grant USAGE on the schema to this user.
因此，要么让您创建的用户成为架构的所有者，要么将架构上的 USAGE 授予该用户。
回答by Stéphane
This confused me. Still not sure I'm handling it correctly. Run \h grantfor the syntax within psql. Here is how I managed to get my other users and groups to work as I needed:
这让我很困惑。仍然不确定我是否正确处理它。运行\h grantpsql 中的语法。以下是我设法让我的其他用户和组根据需要工作的方法：
GRANT ALL PRIVILEGES ON SCHEMA foo TO GROUP bar;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA foo TO GROUP bar;
回答by ps2goat
I kept getting this error when using flywayto deploy database changes. I do some manual setup first, such as creating the database, so flyway wouldn't need those super-admin permissions.
flyway用于部署数据库更改时，我不断收到此错误。我先做一些手动设置，比如创建数据库，所以 flyway 不需要那些超级管理员权限。
My Fix
我的修复
I had to ensure that the database user that flyway job used had ownership rights to the public schema, so that the flyway user could then assign the right to use the schema to other roles.
我必须确保 flyway 作业使用的数据库用户拥有公共架构的所有权，以便 flyway 用户可以将使用该架构的权限分配给其他角色。
Additional setup Details
其他设置详情
I am using AWS RDS (both regular and Aurora), and they don't allow super users in the databases. RDS reserves super users for use by AWS, only, so that consumers are unable to break the replication stuff that is built in. However, there's a catch-22 that you must be an owner in postgres to be able to modify it.
我正在使用 AWS RDS（常规和 Aurora），并且它们不允许数据库中的超级用户。RDS 保留超级用户仅供 AWS 使用，因此消费者无法破坏内置的复制内容。但是，有一个第 22 条规定，您必须是 postgres 的所有者才能对其进行修改。
My solution was to create a role that acts as the owner ('owner role'), and then assign both my admin user and the flyway user to the owner role, and use ALTERscripts for each object to assign the object's owner to the owner role.
我的解决方案是创建一个充当所有者的角色（“所有者角色”），然后将我的 admin 用户和 flyway 用户都分配给所有者角色，并ALTER为每个对象使用脚本将对象的所有者分配给所有者角色.
I missed the public schema, since that was auto-created when I created the database script manually. The public schema defaulted to my admin role rather than the shared owner role. So when the flyway user tried to assign public schema permissions to other roles, it didn't have the authority to do that. An error was not thrown during flyway execution, however.
我错过了公共模式，因为这是我手动创建数据库脚本时自动创建的。公共架构默认为我的管理员角色，而不是共享所有者角色。因此，当 flyway 用户尝试将公共架构权限分配给其他角色时，它没有这样做的权限。但是，在 Flyway 执行期间没有抛出错误。
