Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commita14a011

Browse files
committed
Add "pg_database_owner" default role.
Membership consists, implicitly, of the current database owner. Expectuse in template databases. Once pg_database_owner has rights within atemplate, each owner of a database instantiated from that template willexercise those rights.Reviewed by John Naylor.Discussion:https://postgr.es/m/20201228043148.GA1053024@rfd.leadboat.com
1 parentf687bf6 commita14a011

File tree

11 files changed

+207
-6
lines changed

11 files changed

+207
-6
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -10138,6 +10138,9 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
1013810138
<primary>pg_group</primary>
1013910139
</indexterm>
1014010140

10141+
<!-- Unlike information_schema.applicable_roles, this shows no members for
10142+
pg_database_owner. The v8.1 catalog would have shown no members if
10143+
that role had existed at the time. -->
1014110144
<para>
1014210145
The view <structname>pg_group</structname> exists for backwards
1014310146
compatibility: it emulates a catalog that existed in

‎doc/src/sgml/user-manag.sgml

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -540,6 +540,10 @@ DROP ROLE doomed_role;
540540
<literal>pg_read_all_stats</literal> and
541541
<literal>pg_stat_scan_tables</literal>.</entry>
542542
</row>
543+
<row>
544+
<entry>pg_database_owner</entry>
545+
<entry>None. Membership consists, implicitly, of the current database owner.</entry>
546+
</row>
543547
<row>
544548
<entry>pg_signal_backend</entry>
545549
<entry>Signal another backend to cancel a query or terminate its session.</entry>
@@ -572,6 +576,17 @@ DROP ROLE doomed_role;
572576
other system information normally restricted to superusers.
573577
</para>
574578

579+
<para>
580+
The <literal>pg_database_owner</literal> role has one implicit,
581+
situation-dependent member, namely the owner of the current database. The
582+
role conveys no rights at first. Like any role, it can own objects or
583+
receive grants of access privileges. Consequently, once
584+
<literal>pg_database_owner</literal> has rights within a template database,
585+
each owner of a database instantiated from that template will exercise those
586+
rights. <literal>pg_database_owner</literal> cannot be a member of any
587+
role, and it cannot have non-implicit members.
588+
</para>
589+
575590
<para>
576591
The <literal>pg_signal_backend</literal> role is intended to allow
577592
administrators to enable trusted, but non-superuser, roles to send signals

‎src/backend/catalog/information_schema.sql

Lines changed: 8 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -255,7 +255,14 @@ CREATE VIEW applicable_roles AS
255255
SELECT CAST(a.rolnameAS sql_identifier)AS grantee,
256256
CAST(b.rolnameAS sql_identifier)AS role_name,
257257
CAST(CASE WHENm.admin_option THEN'YES' ELSE'NO' ENDAS yes_or_no)AS is_grantable
258-
FROM pg_auth_members m
258+
FROM (SELECT member, roleid, admin_optionFROM pg_auth_members
259+
-- This UNION could be UNION ALL, but UNION works even if we start
260+
-- to allow explicit pg_database_owner membership.
261+
UNION
262+
SELECT datdba,pg_authid.oid, false
263+
FROM pg_database, pg_authid
264+
WHERE datname= current_database()AND rolname='pg_database_owner'
265+
) m
259266
JOIN pg_authid aON (m.member=a.oid)
260267
JOIN pg_authid bON (m.roleid=b.oid)
261268
WHERE pg_has_role(a.oid,'USAGE');

‎src/backend/commands/user.c

Lines changed: 36 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1496,6 +1496,18 @@ AddRoleMems(const char *rolename, Oid roleid,
14961496
rolename)));
14971497
}
14981498

1499+
/*
1500+
* The charter of pg_database_owner is to have exactly one, implicit,
1501+
* situation-dependent member. There's no technical need for this
1502+
* restriction. (One could lift it and take the further step of making
1503+
* pg_database_ownercheck() equivalent to has_privs_of_role(roleid,
1504+
* DEFAULT_ROLE_DATABASE_OWNER), in which case explicit,
1505+
* situation-independent members could act as the owner of any database.)
1506+
*/
1507+
if (roleid==DEFAULT_ROLE_DATABASE_OWNER)
1508+
ereport(ERROR,
1509+
errmsg("role \"%s\" cannot have explicit members",rolename));
1510+
14991511
/*
15001512
* The role membership grantor of record has little significance at
15011513
* present. Nonetheless, inasmuch as users might look to it for a crude
@@ -1524,6 +1536,30 @@ AddRoleMems(const char *rolename, Oid roleid,
15241536
boolnew_record_nulls[Natts_pg_auth_members];
15251537
boolnew_record_repl[Natts_pg_auth_members];
15261538

1539+
/*
1540+
* pg_database_owner is never a role member. Lifting this restriction
1541+
* would require a policy decision about membership loops. One could
1542+
* prevent loops, which would include making "ALTER DATABASE x OWNER
1543+
* TO proposed_datdba" fail if is_member_of_role(pg_database_owner,
1544+
* proposed_datdba). Hence, gaining a membership could reduce what a
1545+
* role could do. Alternately, one could allow these memberships to
1546+
* complete loops. A role could then have actual WITH ADMIN OPTION on
1547+
* itself, prompting a decision about is_admin_of_role() treatment of
1548+
* the case.
1549+
*
1550+
* Lifting this restriction also has policy implications for ownership
1551+
* of shared objects (databases and tablespaces). We allow such
1552+
* ownership, but we might find cause to ban it in the future.
1553+
* Designing such a ban would more troublesome if the design had to
1554+
* address pg_database_owner being a member of role FOO that owns a
1555+
* shared object. (The effect of such ownership is that any owner of
1556+
* another database can act as the owner of affected shared objects.)
1557+
*/
1558+
if (memberid==DEFAULT_ROLE_DATABASE_OWNER)
1559+
ereport(ERROR,
1560+
errmsg("role \"%s\" cannot be a member of any role",
1561+
get_rolespec_name(memberRole)));
1562+
15271563
/*
15281564
* Refuse creation of membership loops, including the trivial case
15291565
* where a role is made a member of itself. We do this by checking to

‎src/backend/utils/adt/acl.c

Lines changed: 42 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -22,6 +22,7 @@
2222
#include"catalog/pg_auth_members.h"
2323
#include"catalog/pg_authid.h"
2424
#include"catalog/pg_class.h"
25+
#include"catalog/pg_database.h"
2526
#include"catalog/pg_type.h"
2627
#include"commands/dbcommands.h"
2728
#include"commands/proclang.h"
@@ -68,6 +69,7 @@ enum RoleRecurseType
6869
};
6970
staticOidcached_role[]= {InvalidOid,InvalidOid};
7071
staticList*cached_roles[]= {NIL,NIL};
72+
staticuint32cached_db_hash;
7173

7274

7375
staticconstchar*getid(constchar*s,char*n);
@@ -4665,17 +4667,24 @@ initialize_acl(void)
46654667
{
46664668
if (!IsBootstrapProcessingMode())
46674669
{
4670+
cached_db_hash=
4671+
GetSysCacheHashValue1(DATABASEOID,
4672+
ObjectIdGetDatum(MyDatabaseId));
4673+
46684674
/*
46694675
* In normal mode, set a callback on any syscache invalidation of rows
4670-
* of pg_auth_members (for roles_is_member_of()) or pg_authid (for
4671-
* has_rolinherit())
4676+
* of pg_auth_members (for roles_is_member_of()), pg_authid (for
4677+
* has_rolinherit()), or pg_database (for roles_is_member_of())
46724678
*/
46734679
CacheRegisterSyscacheCallback(AUTHMEMROLEMEM,
46744680
RoleMembershipCacheCallback,
46754681
(Datum)0);
46764682
CacheRegisterSyscacheCallback(AUTHOID,
46774683
RoleMembershipCacheCallback,
46784684
(Datum)0);
4685+
CacheRegisterSyscacheCallback(DATABASEOID,
4686+
RoleMembershipCacheCallback,
4687+
(Datum)0);
46794688
}
46804689
}
46814690

@@ -4686,6 +4695,13 @@ initialize_acl(void)
46864695
staticvoid
46874696
RoleMembershipCacheCallback(Datumarg,intcacheid,uint32hashvalue)
46884697
{
4698+
if (cacheid==DATABASEOID&&
4699+
hashvalue!=cached_db_hash&&
4700+
hashvalue!=0)
4701+
{
4702+
return;/* ignore pg_database changes for other DBs */
4703+
}
4704+
46894705
/* Force membership caches to be recomputed on next use */
46904706
cached_role[ROLERECURSE_PRIVS]=InvalidOid;
46914707
cached_role[ROLERECURSE_MEMBERS]=InvalidOid;
@@ -4728,6 +4744,7 @@ static List *
47284744
roles_is_member_of(Oidroleid,enumRoleRecurseTypetype,
47294745
Oidadmin_of,bool*is_admin)
47304746
{
4747+
Oiddba;
47314748
List*roles_list;
47324749
ListCell*l;
47334750
List*new_cached_roles;
@@ -4740,6 +4757,24 @@ roles_is_member_of(Oid roleid, enum RoleRecurseType type,
47404757
OidIsValid(cached_role[type]))
47414758
returncached_roles[type];
47424759

4760+
/*
4761+
* Role expansion happens in a non-database backend when guc.c checks
4762+
* DEFAULT_ROLE_READ_ALL_SETTINGS for a physical walsender SHOW command.
4763+
* In that case, no role gets pg_database_owner.
4764+
*/
4765+
if (!OidIsValid(MyDatabaseId))
4766+
dba=InvalidOid;
4767+
else
4768+
{
4769+
HeapTupledbtup;
4770+
4771+
dbtup=SearchSysCache1(DATABASEOID,ObjectIdGetDatum(MyDatabaseId));
4772+
if (!HeapTupleIsValid(dbtup))
4773+
elog(ERROR,"cache lookup failed for database %u",MyDatabaseId);
4774+
dba= ((Form_pg_database)GETSTRUCT(dbtup))->datdba;
4775+
ReleaseSysCache(dbtup);
4776+
}
4777+
47434778
/*
47444779
* Find all the roles that roleid is a member of, including multi-level
47454780
* recursion. The role itself will always be the first element of the
@@ -4787,6 +4822,11 @@ roles_is_member_of(Oid roleid, enum RoleRecurseType type,
47874822
roles_list=list_append_unique_oid(roles_list,otherid);
47884823
}
47894824
ReleaseSysCacheList(memlist);
4825+
4826+
/* implement pg_database_owner implicit membership */
4827+
if (memberid==dba&&OidIsValid(dba))
4828+
roles_list=list_append_unique_oid(roles_list,
4829+
DEFAULT_ROLE_DATABASE_OWNER);
47904830
}
47914831

47924832
/*

‎src/backend/utils/cache/catcache.c

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1076,8 +1076,9 @@ InitCatCachePhase2(CatCache *cache, bool touch_index)
10761076
*criticalRelcachesBuilt), we don't have to worry anymore.
10771077
*
10781078
*Similarly, during backend startup we have to be able to use the
1079-
*pg_authid and pg_auth_members syscaches for authentication even if
1080-
*we don't yet have relcache entries for those catalogs' indexes.
1079+
*pg_authid, pg_auth_members and pg_database syscaches for
1080+
*authentication even if we don't yet have relcache entries for those
1081+
*catalogs' indexes.
10811082
*/
10821083
staticbool
10831084
IndexScanOK(CatCache*cache,ScanKeycur_skey)
@@ -1110,6 +1111,7 @@ IndexScanOK(CatCache *cache, ScanKey cur_skey)
11101111
caseAUTHNAME:
11111112
caseAUTHOID:
11121113
caseAUTHMEMMEMROLE:
1114+
caseDATABASEOID:
11131115

11141116
/*
11151117
* Protect authentication lookups occurring before relcache has

‎src/bin/psql/describe.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3557,6 +3557,7 @@ describeRoles(const char *pattern, bool verbose, bool showSystem)
35573557

35583558
printTableAddHeader(&cont,gettext_noop("Role name"), true,align);
35593559
printTableAddHeader(&cont,gettext_noop("Attributes"), true,align);
3560+
/* ignores implicit memberships from superuser & pg_database_owner */
35603561
printTableAddHeader(&cont,gettext_noop("Member of"), true,align);
35613562

35623563
if (verbose&&pset.sversion >=80200)

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO202103264
56+
#defineCATALOG_VERSION_NO202103265
5757

5858
#endif

‎src/include/catalog/pg_authid.dat

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -24,6 +24,11 @@
2424
rolcreaterole => 't', rolcreatedb => 't', rolcanlogin => 't',
2525
rolreplication => 't', rolbypassrls => 't', rolconnlimit => '-1',
2626
rolpassword => '_null_', rolvaliduntil => '_null_' },
27+
{ oid => '8778', oid_symbol => 'DEFAULT_ROLE_DATABASE_OWNER',
28+
rolname => 'pg_database_owner', rolsuper => 'f', rolinherit => 't',
29+
rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
30+
rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
31+
rolpassword => '_null_', rolvaliduntil => '_null_' },
2732
{ oid => '3373', oid_symbol => 'DEFAULT_ROLE_MONITOR',
2833
rolname => 'pg_monitor', rolsuper => 'f', rolinherit => 't',
2934
rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',

‎src/test/regress/expected/privileges.out

Lines changed: 61 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1719,6 +1719,67 @@ SELECT * FROM pg_largeobject LIMIT 0;
17191719
SET SESSION AUTHORIZATION regress_priv_user1;
17201720
SELECT * FROM pg_largeobject LIMIT 0;-- to be denied
17211721
ERROR: permission denied for table pg_largeobject
1722+
-- test pg_database_owner
1723+
RESET SESSION AUTHORIZATION;
1724+
GRANT pg_database_owner TO regress_priv_user1;
1725+
ERROR: role "pg_database_owner" cannot have explicit members
1726+
GRANT regress_priv_user1 TO pg_database_owner;
1727+
ERROR: role "pg_database_owner" cannot be a member of any role
1728+
CREATE TABLE datdba_only ();
1729+
ALTER TABLE datdba_only OWNER TO pg_database_owner;
1730+
REVOKE DELETE ON datdba_only FROM pg_database_owner;
1731+
SELECT
1732+
pg_has_role('regress_priv_user1', 'pg_database_owner', 'USAGE') as priv,
1733+
pg_has_role('regress_priv_user1', 'pg_database_owner', 'MEMBER') as mem,
1734+
pg_has_role('regress_priv_user1', 'pg_database_owner',
1735+
'MEMBER WITH ADMIN OPTION') as admin;
1736+
priv | mem | admin
1737+
------+-----+-------
1738+
f | f | f
1739+
(1 row)
1740+
1741+
BEGIN;
1742+
DO $$BEGIN EXECUTE format(
1743+
'ALTER DATABASE %I OWNER TO regress_priv_group2', current_catalog); END$$;
1744+
SELECT
1745+
pg_has_role('regress_priv_user1', 'pg_database_owner', 'USAGE') as priv,
1746+
pg_has_role('regress_priv_user1', 'pg_database_owner', 'MEMBER') as mem,
1747+
pg_has_role('regress_priv_user1', 'pg_database_owner',
1748+
'MEMBER WITH ADMIN OPTION') as admin;
1749+
priv | mem | admin
1750+
------+-----+-------
1751+
t | t | f
1752+
(1 row)
1753+
1754+
SET SESSION AUTHORIZATION regress_priv_user1;
1755+
TABLE information_schema.enabled_roles ORDER BY role_name COLLATE "C";
1756+
role_name
1757+
---------------------
1758+
pg_database_owner
1759+
regress_priv_group2
1760+
regress_priv_user1
1761+
(3 rows)
1762+
1763+
TABLE information_schema.applicable_roles ORDER BY role_name COLLATE "C";
1764+
grantee | role_name | is_grantable
1765+
---------------------+---------------------+--------------
1766+
regress_priv_group2 | pg_database_owner | NO
1767+
regress_priv_user1 | regress_priv_group2 | NO
1768+
(2 rows)
1769+
1770+
INSERT INTO datdba_only DEFAULT VALUES;
1771+
SAVEPOINT q; DELETE FROM datdba_only; ROLLBACK TO q;
1772+
ERROR: permission denied for table datdba_only
1773+
SET SESSION AUTHORIZATION regress_priv_user2;
1774+
TABLE information_schema.enabled_roles;
1775+
role_name
1776+
--------------------
1777+
regress_priv_user2
1778+
(1 row)
1779+
1780+
INSERT INTO datdba_only DEFAULT VALUES;
1781+
ERROR: permission denied for table datdba_only
1782+
ROLLBACK;
17221783
-- test default ACLs
17231784
\c -
17241785
CREATE SCHEMA testns;

‎src/test/regress/sql/privileges.sql

Lines changed: 31 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1034,6 +1034,37 @@ SELECT * FROM pg_largeobject LIMIT 0;
10341034
SET SESSION AUTHORIZATION regress_priv_user1;
10351035
SELECT*FROM pg_largeobjectLIMIT0;-- to be denied
10361036

1037+
-- test pg_database_owner
1038+
RESET SESSION AUTHORIZATION;
1039+
GRANT pg_database_owner TO regress_priv_user1;
1040+
GRANT regress_priv_user1 TO pg_database_owner;
1041+
CREATETABLEdatdba_only ();
1042+
ALTERTABLE datdba_only OWNER TO pg_database_owner;
1043+
REVOKEDELETEON datdba_onlyFROM pg_database_owner;
1044+
SELECT
1045+
pg_has_role('regress_priv_user1','pg_database_owner','USAGE')as priv,
1046+
pg_has_role('regress_priv_user1','pg_database_owner','MEMBER')as mem,
1047+
pg_has_role('regress_priv_user1','pg_database_owner',
1048+
'MEMBER WITH ADMIN OPTION')as admin;
1049+
1050+
BEGIN;
1051+
DO $$BEGIN EXECUTE format(
1052+
'ALTER DATABASE %I OWNER TO regress_priv_group2', current_catalog); END$$;
1053+
SELECT
1054+
pg_has_role('regress_priv_user1','pg_database_owner','USAGE')as priv,
1055+
pg_has_role('regress_priv_user1','pg_database_owner','MEMBER')as mem,
1056+
pg_has_role('regress_priv_user1','pg_database_owner',
1057+
'MEMBER WITH ADMIN OPTION')as admin;
1058+
SET SESSION AUTHORIZATION regress_priv_user1;
1059+
TABLEinformation_schema.enabled_rolesORDER BY role_name COLLATE"C";
1060+
TABLEinformation_schema.applicable_rolesORDER BY role_name COLLATE"C";
1061+
INSERT INTO datdba_only DEFAULTVALUES;
1062+
SAVEPOINT q;DELETEFROM datdba_only;ROLLBACK TO q;
1063+
SET SESSION AUTHORIZATION regress_priv_user2;
1064+
TABLEinformation_schema.enabled_roles;
1065+
INSERT INTO datdba_only DEFAULTVALUES;
1066+
ROLLBACK;
1067+
10371068
-- test default ACLs
10381069
\c-
10391070

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp