作者
digoal
日期
2021-03-27
标签
PostgreSQL , owner , pg_database_owner
背景
为了权限管理的便捷性(一个role代表一类权限的集合. 当然我们也可以自定义role, 并赋予某些特定权限给这个自定义role, 然后将这个role再赋予给其他role来进行管理), PostgreSQL 内部ROLE已达9种, PG 14新增pg_database_owner role, 是数据库owner, 这个role不能显示赋予, 也不能被赋予.
https://www.postgresql.org/docs/devel/default-roles.html
Role | Allowed Access
---|---
pg_read_all_settings |Read all configuration variables, even those normally visible only to superusers.
pg_read_all_stats |Read all pg_stat_* views and use various statistics related extensions, even those normally visible only to superusers.
pg_stat_scan_tables |Execute monitoring functions that may take ACCESS SHARE locks on tables, potentially for a long time.
pg_monitor |Read/execute various monitoring views and functions. This role is a member of pg_read_all_settings, pg_read_all_stats and pg_stat_scan_tables.
pg_database_owner |None. Membership consists, implicitly, of the current database owner.
pg_signal_backend |Signal another backend to cancel a query or terminate its session.
pg_read_server_files |Allow reading files from any location the database can access on the server with COPY and other file-access functions.
pg_write_server_files |Allow writing to files in any location the database can access on the server with COPY and other file-access functions.
pg_execute_server_program |Allow executing programs on the database server as the user the database runs as with COPY and other functions which allow executing a server-side program.
+ <para>
+ The <literal>pg_database_owner</literal> role has one implicit,
+ situation-dependent member, namely the owner of the current database. The
+ role conveys no rights at first. Like any role, it can own objects or
+ receive grants of access privileges. Consequently, once
+ <literal>pg_database_owner</literal> has rights within a template database,
+ each owner of a database instantiated from that template will exercise those
+ rights. <literal>pg_database_owner</literal> cannot be a member of any
+ role, and it cannot have non-implicit members.
+ </para>
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=a14a0118a1fecf4066e53af52ed0f188607d0c4b
```
Add "pg_database_owner" default role.
author Noah Misch noah@leadboat.com
Fri, 26 Mar 2021 17:42:17 +0000 (10:42 -0700)
committer Noah Misch noah@leadboat.com
Fri, 26 Mar 2021 17:42:17 +0000 (10:42 -0700)
commit a14a0118a1fecf4066e53af52ed0f188607d0c4b
tree d24b18e8c0fe2d3af805efe11e54d5718c249732 tree
parent f687bf61ed4dc75ec074c387f848147da2097e13 commit | diff
Add "pg_database_owner" default role.
Membership consists, implicitly, of the current database owner. Expect
use in template databases. Once pg_database_owner has rights within a
template, each owner of a database instantiated from that template will
exercise those rights.
Reviewed by John Naylor.
Discussion: https://postgr.es/m/20201228043148.GA1053024@rfd.leadboat.com
```
+-- test pg_database_owner
+RESET SESSION AUTHORIZATION;
+GRANT pg_database_owner TO regress_priv_user1;
+ERROR: role "pg_database_owner" cannot have explicit members
+GRANT regress_priv_user1 TO pg_database_owner;
+ERROR: role "pg_database_owner" cannot be a member of any role
+CREATE TABLE datdba_only ();
+ALTER TABLE datdba_only OWNER TO pg_database_owner;
+REVOKE DELETE ON datdba_only FROM pg_database_owner;
+SELECT
+ pg_has_role('regress_priv_user1', 'pg_database_owner', 'USAGE') as priv,
+ pg_has_role('regress_priv_user1', 'pg_database_owner', 'MEMBER') as mem,
+ pg_has_role('regress_priv_user1', 'pg_database_owner',
+ 'MEMBER WITH ADMIN OPTION') as admin;
+ priv | mem | admin
+------+-----+-------
+ f | f | f
+(1 row)
+
+BEGIN;
+DO $$BEGIN EXECUTE format(
+ 'ALTER DATABASE %I OWNER TO regress_priv_group2', current_catalog); END$$;
+SELECT
+ pg_has_role('regress_priv_user1', 'pg_database_owner', 'USAGE') as priv,
+ pg_has_role('regress_priv_user1', 'pg_database_owner', 'MEMBER') as mem,
+ pg_has_role('regress_priv_user1', 'pg_database_owner',
+ 'MEMBER WITH ADMIN OPTION') as admin;
+ priv | mem | admin
+------+-----+-------
+ t | t | f
+(1 row)
+
+SET SESSION AUTHORIZATION regress_priv_user1;
+TABLE information_schema.enabled_roles ORDER BY role_name COLLATE "C";
+ role_name
+---------------------
+ pg_database_owner
+ regress_priv_group2
+ regress_priv_user1
+(3 rows)
+
+TABLE information_schema.applicable_roles ORDER BY role_name COLLATE "C";
+ grantee | role_name | is_grantable
+---------------------+---------------------+--------------
+ regress_priv_group2 | pg_database_owner | NO
+ regress_priv_user1 | regress_priv_group2 | NO
+(2 rows)
+
+INSERT INTO datdba_only DEFAULT VALUES;
+SAVEPOINT q; DELETE FROM datdba_only; ROLLBACK TO q;
+ERROR: permission denied for table datdba_only
+SET SESSION AUTHORIZATION regress_priv_user2;
+TABLE information_schema.enabled_roles;
+ role_name
+--------------------
+ regress_priv_user2
+(1 row)
+
+INSERT INTO datdba_only DEFAULT VALUES;
+ERROR: permission denied for table datdba_only
+ROLLBACK;
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
