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

Commitab89e46

Browse files
committed
Altering default privileges on schemas
Extend ALTER DEFAULT PRIVILEGES command to schemas.Author: Matheus OliveiraReviewed-by: Petr Jelínek, Ashutosh Sharmahttps://commitfest.postgresql.org/13/887/
1 parent8516364 commitab89e46

File tree

13 files changed

+176
-12
lines changed

13 files changed

+176
-12
lines changed

‎doc/src/sgml/ref/alter_default_privileges.sgml

Lines changed: 15 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -46,6 +46,10 @@ GRANT { USAGE | ALL [ PRIVILEGES ] }
4646
ON TYPES
4747
TO { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
4848

49+
GRANT { USAGE | CREATE | ALL [ PRIVILEGES ] }
50+
ON SCHEMAS
51+
TO { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
52+
4953
REVOKE [ GRANT OPTION FOR ]
5054
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
5155
[, ...] | ALL [ PRIVILEGES ] }
@@ -71,6 +75,12 @@ REVOKE [ GRANT OPTION FOR ]
7175
ON TYPES
7276
FROM { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...]
7377
[ CASCADE | RESTRICT ]
78+
79+
REVOKE [ GRANT OPTION FOR ]
80+
{ USAGE | CREATE | ALL [ PRIVILEGES ] }
81+
ON SCHEMAS
82+
FROM { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...]
83+
[ CASCADE | RESTRICT ]
7484
</synopsis>
7585
</refsynopsisdiv>
7686

@@ -81,8 +91,9 @@ REVOKE [ GRANT OPTION FOR ]
8191
<command>ALTER DEFAULT PRIVILEGES</> allows you to set the privileges
8292
that will be applied to objects created in the future. (It does not
8393
affect privileges assigned to already-existing objects.) Currently,
84-
only the privileges for tables (including views and foreign tables),
85-
sequences, functions, and types (including domains) can be altered.
94+
only the privileges for schemas, tables (including views and foreign
95+
tables), sequences, functions, and types (including domains) can be
96+
altered.
8697
</para>
8798

8899
<para>
@@ -125,6 +136,8 @@ REVOKE [ GRANT OPTION FOR ]
125136
are altered for objects later created in that schema.
126137
If <literal>IN SCHEMA</> is omitted, the global default privileges
127138
are altered.
139+
<literal>IN SCHEMA</> is not allowed when using <literal>ON SCHEMAS</>
140+
as schemas can't be nested.
128141
</para>
129142
</listitem>
130143
</varlistentry>

‎src/backend/catalog/aclchk.c

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -959,6 +959,10 @@ ExecAlterDefaultPrivilegesStmt(ParseState *pstate, AlterDefaultPrivilegesStmt *s
959959
all_privileges=ACL_ALL_RIGHTS_TYPE;
960960
errormsg=gettext_noop("invalid privilege type %s for type");
961961
break;
962+
caseACL_OBJECT_NAMESPACE:
963+
all_privileges=ACL_ALL_RIGHTS_NAMESPACE;
964+
errormsg=gettext_noop("invalid privilege type %s for schema");
965+
break;
962966
default:
963967
elog(ERROR,"unrecognized GrantStmt.objtype: %d",
964968
(int)action->objtype);
@@ -1146,6 +1150,16 @@ SetDefaultACL(InternalDefaultACL *iacls)
11461150
this_privileges=ACL_ALL_RIGHTS_TYPE;
11471151
break;
11481152

1153+
caseACL_OBJECT_NAMESPACE:
1154+
if (OidIsValid(iacls->nspid))
1155+
ereport(ERROR,
1156+
(errcode(ERRCODE_INVALID_GRANT_OPERATION),
1157+
errmsg("cannot use IN SCHEMA clause when using GRANT/REVOKE ON SCHEMAS")));
1158+
objtype=DEFACLOBJ_NAMESPACE;
1159+
if (iacls->all_privs&&this_privileges==ACL_NO_RIGHTS)
1160+
this_privileges=ACL_ALL_RIGHTS_NAMESPACE;
1161+
break;
1162+
11491163
default:
11501164
elog(ERROR,"unrecognized objtype: %d",
11511165
(int)iacls->objtype);
@@ -1369,6 +1383,9 @@ RemoveRoleFromObjectACL(Oid roleid, Oid classid, Oid objid)
13691383
caseDEFACLOBJ_TYPE:
13701384
iacls.objtype=ACL_OBJECT_TYPE;
13711385
break;
1386+
caseDEFACLOBJ_NAMESPACE:
1387+
iacls.objtype=ACL_OBJECT_NAMESPACE;
1388+
break;
13721389
default:
13731390
/* Shouldn't get here */
13741391
elog(ERROR,"unexpected default ACL type: %d",
@@ -5259,6 +5276,10 @@ get_user_default_acl(GrantObjectType objtype, Oid ownerId, Oid nsp_oid)
52595276
defaclobjtype=DEFACLOBJ_TYPE;
52605277
break;
52615278

5279+
caseACL_OBJECT_NAMESPACE:
5280+
defaclobjtype=DEFACLOBJ_NAMESPACE;
5281+
break;
5282+
52625283
default:
52635284
returnNULL;
52645285
}

‎src/backend/catalog/objectaddress.c

Lines changed: 13 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1843,11 +1843,14 @@ get_object_address_defacl(List *object, bool missing_ok)
18431843
caseDEFACLOBJ_TYPE:
18441844
objtype_str="types";
18451845
break;
1846+
caseDEFACLOBJ_NAMESPACE:
1847+
objtype_str="schemas";
1848+
break;
18461849
default:
18471850
ereport(ERROR,
18481851
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
18491852
errmsg("unrecognized default ACL object type %c",objtype),
1850-
errhint("Valid object types are \"r\", \"S\", \"f\", and \"T\".")));
1853+
errhint("Valid object types are \"r\", \"S\", \"f\",\"T\"and \"s\".")));
18511854
}
18521855

18531856
/*
@@ -3255,6 +3258,11 @@ getObjectDescription(const ObjectAddress *object)
32553258
_("default privileges on new types belonging to role %s"),
32563259
GetUserNameFromId(defacl->defaclrole, false));
32573260
break;
3261+
caseDEFACLOBJ_NAMESPACE:
3262+
appendStringInfo(&buffer,
3263+
_("default privileges on new schemas belonging to role %s"),
3264+
GetUserNameFromId(defacl->defaclrole, false));
3265+
break;
32583266
default:
32593267
/* shouldn't get here */
32603268
appendStringInfo(&buffer,
@@ -4762,6 +4770,10 @@ getObjectIdentityParts(const ObjectAddress *object,
47624770
appendStringInfoString(&buffer,
47634771
" on types");
47644772
break;
4773+
caseDEFACLOBJ_NAMESPACE:
4774+
appendStringInfoString(&buffer,
4775+
" on schemas");
4776+
break;
47654777
}
47664778

47674779
if (objname)

‎src/backend/catalog/pg_namespace.c

Lines changed: 16 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -31,10 +31,11 @@
3131
* Create a namespace (schema) with the given name and owner OID.
3232
*
3333
* If isTemp is true, this schema is a per-backend schema for holding
34-
* temporary tables. Currently, the only effect of that is to prevent it
35-
* from being linked as a member of any active extension. (If someone
36-
* does CREATE TEMP TABLE in an extension script, we don't want the temp
37-
* schema to become part of the extension.)
34+
* temporary tables. Currently, it is used to prevent it from being
35+
* linked as a member of any active extension. (If someone does CREATE
36+
* TEMP TABLE in an extension script, we don't want the temp schema to
37+
* become part of the extension). And to avoid checking for default ACL
38+
* for temp namespace (as it is not necessary).
3839
* ---------------
3940
*/
4041
Oid
@@ -49,6 +50,7 @@ NamespaceCreate(const char *nspName, Oid ownerId, bool isTemp)
4950
TupleDesctupDesc;
5051
ObjectAddressmyself;
5152
inti;
53+
Acl*nspacl;
5254

5355
/* sanity checks */
5456
if (!nspName)
@@ -60,6 +62,12 @@ NamespaceCreate(const char *nspName, Oid ownerId, bool isTemp)
6062
(errcode(ERRCODE_DUPLICATE_SCHEMA),
6163
errmsg("schema \"%s\" already exists",nspName)));
6264

65+
if (!isTemp)
66+
nspacl=get_user_default_acl(ACL_OBJECT_NAMESPACE,ownerId,
67+
InvalidOid);
68+
else
69+
nspacl=NULL;
70+
6371
/* initialize nulls and values */
6472
for (i=0;i<Natts_pg_namespace;i++)
6573
{
@@ -69,7 +77,10 @@ NamespaceCreate(const char *nspName, Oid ownerId, bool isTemp)
6977
namestrcpy(&nname,nspName);
7078
values[Anum_pg_namespace_nspname-1]=NameGetDatum(&nname);
7179
values[Anum_pg_namespace_nspowner-1]=ObjectIdGetDatum(ownerId);
72-
nulls[Anum_pg_namespace_nspacl-1]= true;
80+
if (nspacl!=NULL)
81+
values[Anum_pg_namespace_nspacl-1]=PointerGetDatum(nspacl);
82+
else
83+
nulls[Anum_pg_namespace_nspacl-1]= true;
7384

7485
nspdesc=heap_open(NamespaceRelationId,RowExclusiveLock);
7586
tupDesc=nspdesc->rd_att;

‎src/backend/parser/gram.y

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -668,7 +668,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
668668
RESET RESTART RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROLLUP
669669
ROW ROWS RULE
670670

671-
SAVEPOINT SCHEMA SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE SEQUENCES
671+
SAVEPOINT SCHEMASCHEMASSCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE SEQUENCES
672672
SERIALIZABLE SERVER SESSION SESSION_USER SET SETS SETOF SHARE SHOW
673673
SIMILAR SIMPLE SKIP SLOT SMALLINT SNAPSHOT SOME SQL_P STABLE STANDALONE_P
674674
START STATEMENT STATISTICS STDIN STDOUT STORAGE STRICT_P STRIP_P
@@ -7035,6 +7035,7 @@ defacl_privilege_target:
70357035
|FUNCTIONS{$$ = ACL_OBJECT_FUNCTION; }
70367036
|SEQUENCES{$$ = ACL_OBJECT_SEQUENCE; }
70377037
|TYPES_P{$$ = ACL_OBJECT_TYPE; }
7038+
|SCHEMAS{$$ = ACL_OBJECT_NAMESPACE; }
70387039
;
70397040

70407041

@@ -14713,6 +14714,7 @@ unreserved_keyword:
1471314714
| RULE
1471414715
| SAVEPOINT
1471514716
| SCHEMA
14717+
| SCHEMAS
1471614718
| SCROLL
1471714719
| SEARCH
1471814720
| SECOND_P

‎src/bin/pg_dump/dumputils.c

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -520,7 +520,9 @@ do { \
520520
CONVERT_PRIV('X',"EXECUTE");
521521
elseif (strcmp(type,"LANGUAGE")==0)
522522
CONVERT_PRIV('U',"USAGE");
523-
elseif (strcmp(type,"SCHEMA")==0)
523+
elseif (strcmp(type,"SCHEMA")==0||
524+
strcmp(type,"SCHEMAS")==0
525+
)
524526
{
525527
CONVERT_PRIV('C',"CREATE");
526528
CONVERT_PRIV('U',"USAGE");

‎src/bin/pg_dump/pg_dump.c

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -14295,6 +14295,9 @@ dumpDefaultACL(Archive *fout, DefaultACLInfo *daclinfo)
1429514295
case DEFACLOBJ_TYPE:
1429614296
type = "TYPES";
1429714297
break;
14298+
case DEFACLOBJ_NAMESPACE:
14299+
type = "SCHEMAS";
14300+
break;
1429814301
default:
1429914302
/* shouldn't get here */
1430014303
exit_horribly(NULL,

‎src/bin/psql/describe.c

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1028,7 +1028,7 @@ listDefaultACLs(const char *pattern)
10281028
printfPQExpBuffer(&buf,
10291029
"SELECT pg_catalog.pg_get_userbyid(d.defaclrole) AS \"%s\",\n"
10301030
" n.nspname AS \"%s\",\n"
1031-
" CASE d.defaclobjtype WHEN '%c' THEN '%s' WHEN '%c' THEN '%s' WHEN '%c' THEN '%s' WHEN '%c' THEN '%s' END AS \"%s\",\n"
1031+
" CASE d.defaclobjtype WHEN '%c' THEN '%s' WHEN '%c' THEN '%s' WHEN '%c' THEN '%s' WHEN '%c' THEN '%s'WHEN '%c' THEN '%s'END AS \"%s\",\n"
10321032
" ",
10331033
gettext_noop("Owner"),
10341034
gettext_noop("Schema"),
@@ -1040,6 +1040,8 @@ listDefaultACLs(const char *pattern)
10401040
gettext_noop("function"),
10411041
DEFACLOBJ_TYPE,
10421042
gettext_noop("type"),
1043+
DEFACLOBJ_NAMESPACE,
1044+
gettext_noop("schema"),
10431045
gettext_noop("Type"));
10441046

10451047
printACLColumn(&buf,"d.defaclacl");

‎src/bin/psql/tab-complete.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2796,7 +2796,7 @@ psql_completion(const char *text, int start, int end)
27962796
* to the kinds of objects supported.
27972797
*/
27982798
if (HeadMatches3("ALTER","DEFAULT","PRIVILEGES"))
2799-
COMPLETE_WITH_LIST4("TABLES","SEQUENCES","FUNCTIONS","TYPES");
2799+
COMPLETE_WITH_LIST5("TABLES","SEQUENCES","FUNCTIONS","TYPES","SCHEMAS");
28002800
else
28012801
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tsvmf,
28022802
" UNION SELECT 'ALL FUNCTIONS IN SCHEMA'"

‎src/include/catalog/pg_default_acl.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -70,5 +70,6 @@ typedef FormData_pg_default_acl *Form_pg_default_acl;
7070
#defineDEFACLOBJ_SEQUENCE'S'/* sequence */
7171
#defineDEFACLOBJ_FUNCTION'f'/* function */
7272
#defineDEFACLOBJ_TYPE'T'/* type */
73+
#defineDEFACLOBJ_NAMESPACE'n'/* namespace */
7374

7475
#endif/* PG_DEFAULT_ACL_H */

‎src/include/parser/kwlist.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -344,6 +344,7 @@ PG_KEYWORD("rows", ROWS, UNRESERVED_KEYWORD)
344344
PG_KEYWORD("rule",RULE,UNRESERVED_KEYWORD)
345345
PG_KEYWORD("savepoint",SAVEPOINT,UNRESERVED_KEYWORD)
346346
PG_KEYWORD("schema",SCHEMA,UNRESERVED_KEYWORD)
347+
PG_KEYWORD("schemas",SCHEMAS,UNRESERVED_KEYWORD)
347348
PG_KEYWORD("scroll",SCROLL,UNRESERVED_KEYWORD)
348349
PG_KEYWORD("search",SEARCH,UNRESERVED_KEYWORD)
349350
PG_KEYWORD("second",SECOND_P,UNRESERVED_KEYWORD)

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

Lines changed: 62 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1356,6 +1356,64 @@ SELECT has_table_privilege('regress_user1', 'testns.acltest1', 'INSERT'); -- no
13561356
(1 row)
13571357

13581358
ALTER DEFAULT PRIVILEGES FOR ROLE regress_user1 REVOKE EXECUTE ON FUNCTIONS FROM public;
1359+
ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT USAGE ON SCHEMAS TO regress_user2; -- error
1360+
ERROR: cannot use IN SCHEMA clause when using GRANT/REVOKE ON SCHEMAS
1361+
ALTER DEFAULT PRIVILEGES GRANT USAGE ON SCHEMAS TO regress_user2;
1362+
CREATE SCHEMA testns2;
1363+
SELECT has_schema_privilege('regress_user2', 'testns2', 'USAGE'); -- yes
1364+
has_schema_privilege
1365+
----------------------
1366+
t
1367+
(1 row)
1368+
1369+
SELECT has_schema_privilege('regress_user2', 'testns2', 'CREATE'); -- no
1370+
has_schema_privilege
1371+
----------------------
1372+
f
1373+
(1 row)
1374+
1375+
ALTER DEFAULT PRIVILEGES REVOKE USAGE ON SCHEMAS FROM regress_user2;
1376+
CREATE SCHEMA testns3;
1377+
SELECT has_schema_privilege('regress_user2', 'testns3', 'USAGE'); -- no
1378+
has_schema_privilege
1379+
----------------------
1380+
f
1381+
(1 row)
1382+
1383+
SELECT has_schema_privilege('regress_user2', 'testns3', 'CREATE'); -- no
1384+
has_schema_privilege
1385+
----------------------
1386+
f
1387+
(1 row)
1388+
1389+
ALTER DEFAULT PRIVILEGES GRANT ALL ON SCHEMAS TO regress_user2;
1390+
CREATE SCHEMA testns4;
1391+
SELECT has_schema_privilege('regress_user2', 'testns4', 'USAGE'); -- yes
1392+
has_schema_privilege
1393+
----------------------
1394+
t
1395+
(1 row)
1396+
1397+
SELECT has_schema_privilege('regress_user2', 'testns4', 'CREATE'); -- yes
1398+
has_schema_privilege
1399+
----------------------
1400+
t
1401+
(1 row)
1402+
1403+
ALTER DEFAULT PRIVILEGES REVOKE ALL ON SCHEMAS FROM regress_user2;
1404+
CREATE SCHEMA testns5;
1405+
SELECT has_schema_privilege('regress_user2', 'testns5', 'USAGE'); -- no
1406+
has_schema_privilege
1407+
----------------------
1408+
f
1409+
(1 row)
1410+
1411+
SELECT has_schema_privilege('regress_user2', 'testns5', 'CREATE'); -- no
1412+
has_schema_privilege
1413+
----------------------
1414+
f
1415+
(1 row)
1416+
13591417
SET ROLE regress_user1;
13601418
CREATE FUNCTION testns.foo() RETURNS int AS 'select 1' LANGUAGE sql;
13611419
SELECT has_function_privilege('regress_user2', 'testns.foo()', 'EXECUTE'); -- no
@@ -1403,6 +1461,10 @@ SELECT count(*)
14031461

14041462
DROP SCHEMA testns CASCADE;
14051463
NOTICE: drop cascades to table testns.acltest1
1464+
DROP SCHEMA testns2 CASCADE;
1465+
DROP SCHEMA testns3 CASCADE;
1466+
DROP SCHEMA testns4 CASCADE;
1467+
DROP SCHEMA testns5 CASCADE;
14061468
SELECT d.* -- check that entries went away
14071469
FROM pg_default_acl d LEFT JOIN pg_namespace n ON defaclnamespace = n.oid
14081470
WHERE nspname IS NULL AND defaclnamespace != 0;

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

Lines changed: 34 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -816,6 +816,36 @@ SELECT has_table_privilege('regress_user1', 'testns.acltest1', 'INSERT'); -- no
816816

817817
ALTER DEFAULT PRIVILEGES FOR ROLE regress_user1REVOKE EXECUTEON FUNCTIONSFROM public;
818818

819+
ALTER DEFAULT PRIVILEGESIN SCHEMA testnsGRANT USAGEON SCHEMAS TO regress_user2;-- error
820+
821+
ALTER DEFAULT PRIVILEGESGRANT USAGEON SCHEMAS TO regress_user2;
822+
823+
CREATESCHEMAtestns2;
824+
825+
SELECT has_schema_privilege('regress_user2','testns2','USAGE');-- yes
826+
SELECT has_schema_privilege('regress_user2','testns2','CREATE');-- no
827+
828+
ALTER DEFAULT PRIVILEGESREVOKE USAGEON SCHEMASFROM regress_user2;
829+
830+
CREATESCHEMAtestns3;
831+
832+
SELECT has_schema_privilege('regress_user2','testns3','USAGE');-- no
833+
SELECT has_schema_privilege('regress_user2','testns3','CREATE');-- no
834+
835+
ALTER DEFAULT PRIVILEGESGRANT ALLON SCHEMAS TO regress_user2;
836+
837+
CREATESCHEMAtestns4;
838+
839+
SELECT has_schema_privilege('regress_user2','testns4','USAGE');-- yes
840+
SELECT has_schema_privilege('regress_user2','testns4','CREATE');-- yes
841+
842+
ALTER DEFAULT PRIVILEGESREVOKE ALLON SCHEMASFROM regress_user2;
843+
844+
CREATESCHEMAtestns5;
845+
846+
SELECT has_schema_privilege('regress_user2','testns5','USAGE');-- no
847+
SELECT has_schema_privilege('regress_user2','testns5','CREATE');-- no
848+
819849
SET ROLE regress_user1;
820850

821851
CREATEFUNCTIONtestns.foo() RETURNSintAS'select 1' LANGUAGE sql;
@@ -853,6 +883,10 @@ SELECT count(*)
853883
WHERE nspname='testns';
854884

855885
DROPSCHEMA testns CASCADE;
886+
DROPSCHEMA testns2 CASCADE;
887+
DROPSCHEMA testns3 CASCADE;
888+
DROPSCHEMA testns4 CASCADE;
889+
DROPSCHEMA testns5 CASCADE;
856890

857891
SELECT d.*-- check that entries went away
858892
FROM pg_default_acl dLEFT JOIN pg_namespace nON defaclnamespace=n.oid

0 commit comments

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