在PostgreSQL中创建真正的只读用户。

huangapple go评论55阅读模式
英文:

Create true read only user in PostgreSQL

问题

我已经在Digital Ocean上创建了一个PostgreSQL数据库。现在我需要为其创建一组用户和数据库,我考虑创建多个数据库(productionstaging等),并为每个数据库创建两个相关的角色,一个具有只读权限,一个具有读写权限(production_roproduction_rwstaging_rostaging_rw等)。我的想法是,通过拥有这些角色,我现在可以创建个别用户并分配给他们其中一个角色,以便在发生违规情况时能够快速更改或删除他们。

我已经在这方面进行了研究,我可以找到的所有页面都有类似于此处的一组类似的指令(https://aws.amazon.com/blogs/database/managing-postgresql-users-and-roles/):

-- 从 'public' 角色中撤销权限
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE mydatabase FROM PUBLIC;

-- 只读角色
CREATE ROLE readonly;
GRANT CONNECT ON DATABASE mydatabase TO readonly;
GRANT USAGE ON SCHEMA myschema TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO readonly;

-- 读写角色
CREATE ROLE readwrite;
GRANT CONNECT ON DATABASE mydatabase TO readwrite;
GRANT USAGE, CREATE ON SCHEMA myschema TO readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA myschema TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA myschema TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT USAGE ON SEQUENCES TO readwrite;

-- 创建用户
CREATE USER reporting_user1 WITH PASSWORD 'some_secret_passwd';
CREATE USER reporting_user2 WITH PASSWORD 'some_secret_passwd';
CREATE USER app_user1 WITH PASSWORD 'some_secret_passwd';
CREATE USER app_user2 WITH PASSWORD 'some_secret_passwd';

-- 授予权限给用户
GRANT readonly TO reporting_user1;
GRANT readonly TO reporting_user2;
GRANT readwrite TO app_user1;
GRANT readwrite TO app_user2;

我已经仔细按照这些指令进行操作,并监控确保没有失败,但在成功运行它们之后,我发现实际上有了看似只读的用户,他们实际上可以创建表,无法看到其他用户创建的表,并可以切换数据库。

我做错了什么?

--- 编辑 ---

这是 \dn+ 命令的结果:

          模式列表
  名称  |  拥有者   |  访问权限   |      描述
--------+----------+----------------------+------------------------
 public | postgres | postgres=UC/postgres+| 标准公共模式
        |          | =UC/postgres         |

--- 编辑 2 ---

由于安全原因,这是我所做的操作(我会将用户名称编辑为<USER_A><USER_B>等,这些编辑后的用户名称将与实际用户名称一一对应):

$ psql "postgresql://USER_A:<PASSWORD>@<DOMAIN>:<PORT>/<DEFAULT_DB>?sslmode=require"
psql (15.1, 服务器 14.6)
SSL 连接(协议:TLSv1.3,密码:TLS_AES_256_GCM_SHA384,压缩:关闭)
键入 "help" 获取帮助。

<DEFAULT_DB>=> \connect production
psql (15.1, 服务器 14.6)
SSL 连接(协议:TLSv1.3,密码:TLS_AES_256_GCM_SHA384,压缩:关闭)
您现在以用户 "USER_A" 连接到数据库 "production"production=> \du
          角色列表
    角色名称    |                         属性                         |                                 成员
-----------------+-----------------------------------------------------+---------------------------------------------------------------------------
 USER_B          | 超级用户,复制                                      | {}
 USER_A          | 创建角色,创建数据库,复制,绕过RLS            | {pg_read_all_stats,pg_stat_scan_tables,pg_signal_backend,r_production_ro}
 postgres        | 超级用户,创建角色,创建数据库,复制,绕过RLS | {}
 production_ro   | 无法登录                                           | {}

production=> REVOKE CREATE ON SCHEMA public FROM PUBLIC;
警告: 无法撤销 "public" 的权限
REVOKE
production=>

--- 编辑 3 ---

已联系DigitalOcean,以下是他们的回应:

只是想让您知道我们正在调查此问题,目前我能够重现此行为。似乎为了从用户中删除创建表的权限,我们需要“REVOKE CREATE ON SCHEMA public FROM PUBLIC;”,但由于doadmin用户不是超级用户,因此不允许撤销此权限,否则会影响其他角色。

英文:

I've spanned a PostgreSQL database in Digital Ocean. I now need to come with a set of users and databases for which I've thought on creating several databases (production, staging, etc) and having 2 associated roles for each database with read-only and read-write permissions (production_ro, production_rw, staging_ro, staging_rw, etc). My idea is that, by having those roles, I can now create individual users and assign them one of the roles so that I can quickly change/remove them in case of a breach.

I've been researching on this and all pages I can find have a set of instructions similar to the ones in here:

-- Revoke privileges from &#39;public&#39; role
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE mydatabase FROM PUBLIC;

-- Read-only role
CREATE ROLE readonly;
GRANT CONNECT ON DATABASE mydatabase TO readonly;
GRANT USAGE ON SCHEMA myschema TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO readonly;

-- Read/write role
CREATE ROLE readwrite;
GRANT CONNECT ON DATABASE mydatabase TO readwrite;
GRANT USAGE, CREATE ON SCHEMA myschema TO readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA myschema TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA myschema TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT USAGE ON SEQUENCES TO readwrite;

-- Users creation
CREATE USER reporting_user1 WITH PASSWORD &#39;some_secret_passwd&#39;;
CREATE USER reporting_user2 WITH PASSWORD &#39;some_secret_passwd&#39;;
CREATE USER app_user1 WITH PASSWORD &#39;some_secret_passwd&#39;;
CREATE USER app_user2 WITH PASSWORD &#39;some_secret_passwd&#39;;

-- Grant privileges to users
GRANT readonly TO reporting_user1;
GRANT readonly TO reporting_user2;
GRANT readwrite TO app_user1;
GRANT readwrite TO app_user2;

I've carefully followed those instructions and monitored that none of them failed but, after successfully running them all, I'm left with supposedly read-only users that can, in fact, create tables, not see the tables created by other users, and switch databases.

What am I doing wrong?

--- Edit ---

This is the result of the \dn+ command:

                          List of schemas
  Name  |  Owner   |  Access privileges   |      Description
--------+----------+----------------------+------------------------
 public | postgres | postgres=UC/postgres+| standard public schema
        |          | =UC/postgres         |

--- Edit 2 ---

Here is what I do (for security reasons, I'll redact the users as &lt;USER_A&gt;, &lt;USER_B&gt;, etc. those redacted users will match 1 to 1 to the real ones):

$ psql &quot;postgresql://USER_A:&lt;PASSWORD&gt;@&lt;DOMAIN&gt;:&lt;PORT&gt;/&lt;DEFAULT_DB&gt;?sslmode=require&quot;
psql (15.1, server 14.6)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type &quot;help&quot; for help.

&lt;DEFAULT_DB&gt;=&gt; \connect production
psql (15.1, server 14.6)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
You are now connected to database &quot;production&quot; as user &quot;USER_A&quot;
production=&gt; \du
                                                                      List of roles
    Role name    |                         Attributes                         |                                 Member of
-----------------+------------------------------------------------------------+---------------------------------------------------------------------------
 USER_B          | Superuser, Replication                                     | {}
 USER_A          | Create role, Create DB, Replication, Bypass RLS            | {pg_read_all_stats,pg_stat_scan_tables,pg_signal_backend,r_production_ro}
 postgres        | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 production_ro   | Cannot login                                               | {}

production=&gt; REVOKE CREATE ON SCHEMA public FROM PUBLIC;
WARNING:  no privileges could be revoked for &quot;public&quot;
REVOKE
production=&gt;

--- Edit 3 ---

Got in contact with DigitalOcean. This is their response:
> Just to let you know that we are investigating this issue, so far I
> was able to reproduce the behavior. It seems that in order to remove
> the create table from the public schema from a user we would need to
> "REVOKE CREATE ON SCHEMA public FROM PUBLIC;" which is not allowed as
> the doadmin use is not a superuser and revoking this privilege would
> impact other roles.

答案1

得分: 1

如果用户可以创建表格,那么它在相关模式上具有CREATE权限。在psql中使用\dn+ public查看这些权限。识别相关权限并REVOKE它们。

或者,如果您使用的是 PostgreSQL v15 或更高版本,可能您的数据库用户直接或间接是预定义角色pg_write_all_data的成员。撤销该成员关系。

在您的具体情况下,正确的做法是从PUBLIC,即所有人,撤销模式public上的默认CREATE权限:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

您说您已经这样做了,但它引发了警告并没有产生效果。这不是 PostgreSQL 的正常行为。您需要咨询修改了 PostgreSQL 的人,也就是在您的情况下是 DigitalOcean。

英文:

If the user can create tables it has CREATE permissions on the schema in question. Look at those permissions with \dn+ public in psql. Identify the permissions in question and REVOKE them.

Alternatively, if you use PostgreSQL v15 or above, it micht be that your database user is directly or indirectly a member of the predefiled role pg_write_all_data. Revoke that membership.

In your specific case, the correct thing to do is to revoke the default CREATE privilege on schema public from PUBLIC, that is, everyone:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

You say you already did that, and it caused a warning and had no effect. That is not what PostgreSQL would do. You'll have to ask the people who modified PostgreSQL, in your case DigitalOcean.

huangapple
  • 本文由 发表于 2023年2月6日 17:56:20
  • 转载请务必保留本文链接:https://go.coder-hub.com/75359771.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定