Skip to content

Commit 0d6c477

Browse files
MasaoFujiiyugo-nlaurenz
committed
Extend ALTER DEFAULT PRIVILEGES to define default privileges for large objects.
Previously, ALTER DEFAULT PRIVILEGES did not support large objects. This meant that to grant privileges to users other than the owner, permissions had to be manually assigned each time a large object was created, which was inconvenient. This commit extends ALTER DEFAULT PRIVILEGES to allow defining default access privileges for large objects. With this change, specified privileges will automatically apply to newly created large objects, making privilege management more efficient. As a side effect, this commit introduces the new keyword OBJECTS since it's used in the syntax of ALTER DEFAULT PRIVILEGES. Original patch by Haruka Takatsuka, with some fixes and tests by Yugo Nagata, and rebased by Laurenz Albe. Author: Takatsuka Haruka <harukat@sraoss.co.jp> Co-authored-by: Yugo Nagata <nagata@sraoss.co.jp> Co-authored-by: Laurenz Albe <laurenz.albe@cybertec.at> Reviewed-by: Masao Fujii <masao.fujii@gmail.com> Discussion: https://postgr.es/m/20240424115242.236b499b2bed5b7a27f7a418@sraoss.co.jp
1 parent 6e9c818 commit 0d6c477

File tree

14 files changed

+217
-13
lines changed

14 files changed

+217
-13
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3360,7 +3360,8 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
33603360
<literal>S</literal> = sequence,
33613361
<literal>f</literal> = function,
33623362
<literal>T</literal> = type,
3363-
<literal>n</literal> = schema
3363+
<literal>n</literal> = schema,
3364+
<literal>L</literal> = large object
33643365
</para></entry>
33653366
</row>
33663367

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

Lines changed: 16 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -51,6 +51,11 @@ GRANT { { USAGE | CREATE }
5151
ON SCHEMAS
5252
TO { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
5353

54+
GRANT { { SELECT | UPDATE }
55+
[, ...] | ALL [ PRIVILEGES ] }
56+
ON LARGE OBJECTS
57+
TO { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
58+
5459
REVOKE [ GRANT OPTION FOR ]
5560
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | MAINTAIN }
5661
[, ...] | ALL [ PRIVILEGES ] }
@@ -83,6 +88,13 @@ REVOKE [ GRANT OPTION FOR ]
8388
ON SCHEMAS
8489
FROM { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...]
8590
[ CASCADE | RESTRICT ]
91+
92+
REVOKE [ GRANT OPTION FOR ]
93+
{ { SELECT | UPDATE }
94+
[, ...] | ALL [ PRIVILEGES ] }
95+
ON LARGE OBJECTS
96+
FROM { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...]
97+
[ CASCADE | RESTRICT ]
8698
</synopsis>
8799
</refsynopsisdiv>
88100

@@ -117,8 +129,8 @@ REVOKE [ GRANT OPTION FOR ]
117129
<para>
118130
Currently,
119131
only the privileges for schemas, tables (including views and foreign
120-
tables), sequences, functions, and types (including domains) can be
121-
altered. For this command, functions include aggregates and procedures.
132+
tables), sequences, functions, types (including domains), and large objects
133+
can be altered. For this command, functions include aggregates and procedures.
122134
The words <literal>FUNCTIONS</literal> and <literal>ROUTINES</literal> are
123135
equivalent in this command. (<literal>ROUTINES</literal> is preferred
124136
going forward as the standard term for functions and procedures taken
@@ -161,7 +173,8 @@ REVOKE [ GRANT OPTION FOR ]
161173
If <literal>IN SCHEMA</literal> is omitted, the global default privileges
162174
are altered.
163175
<literal>IN SCHEMA</literal> is not allowed when setting privileges
164-
for schemas, since schemas can't be nested.
176+
for schemas and large objects, since schemas can't be nested and
177+
large objects don't belong to a schema.
165178
</para>
166179
</listitem>
167180
</varlistentry>

‎src/backend/catalog/aclchk.c

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1005,6 +1005,10 @@ ExecAlterDefaultPrivilegesStmt(ParseState *pstate, AlterDefaultPrivilegesStmt *s
10051005
all_privileges = ACL_ALL_RIGHTS_SCHEMA;
10061006
errormsg = gettext_noop("invalid privilege type %s for schema");
10071007
break;
1008+
case OBJECT_LARGEOBJECT:
1009+
all_privileges = ACL_ALL_RIGHTS_LARGEOBJECT;
1010+
errormsg = gettext_noop("invalid privilege type %s for large object");
1011+
break;
10081012
default:
10091013
elog(ERROR, "unrecognized GrantStmt.objtype: %d",
10101014
(int) action->objtype);
@@ -1196,6 +1200,16 @@ SetDefaultACL(InternalDefaultACL *iacls)
11961200
this_privileges = ACL_ALL_RIGHTS_SCHEMA;
11971201
break;
11981202

1203+
case OBJECT_LARGEOBJECT:
1204+
if (OidIsValid(iacls->nspid))
1205+
ereport(ERROR,
1206+
(errcode(ERRCODE_INVALID_GRANT_OPERATION),
1207+
errmsg("cannot use IN SCHEMA clause when using GRANT/REVOKE ON LARGE OBJECTS")));
1208+
objtype = DEFACLOBJ_LARGEOBJECT;
1209+
if (iacls->all_privs && this_privileges == ACL_NO_RIGHTS)
1210+
this_privileges = ACL_ALL_RIGHTS_LARGEOBJECT;
1211+
break;
1212+
11991213
default:
12001214
elog(ERROR, "unrecognized object type: %d",
12011215
(int) iacls->objtype);
@@ -1439,6 +1453,9 @@ RemoveRoleFromObjectACL(Oid roleid, Oid classid, Oid objid)
14391453
case DEFACLOBJ_NAMESPACE:
14401454
iacls.objtype = OBJECT_SCHEMA;
14411455
break;
1456+
case DEFACLOBJ_LARGEOBJECT:
1457+
iacls.objtype = OBJECT_LARGEOBJECT;
1458+
break;
14421459
default:
14431460
/* Shouldn't get here */
14441461
elog(ERROR, "unexpected default ACL type: %d",
@@ -4250,6 +4267,10 @@ get_user_default_acl(ObjectType objtype, Oid ownerId, Oid nsp_oid)
42504267
defaclobjtype = DEFACLOBJ_NAMESPACE;
42514268
break;
42524269

4270+
case OBJECT_LARGEOBJECT:
4271+
defaclobjtype = DEFACLOBJ_LARGEOBJECT;
4272+
break;
4273+
42534274
default:
42544275
return NULL;
42554276
}

‎src/backend/catalog/objectaddress.c

Lines changed: 16 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -2005,16 +2005,20 @@ get_object_address_defacl(List *object, bool missing_ok)
20052005
case DEFACLOBJ_NAMESPACE:
20062006
objtype_str = "schemas";
20072007
break;
2008+
case DEFACLOBJ_LARGEOBJECT:
2009+
objtype_str = "large objects";
2010+
break;
20082011
default:
20092012
ereport(ERROR,
20102013
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
20112014
errmsg("unrecognized default ACL object type \"%c\"", objtype),
2012-
errhint("Valid object types are \"%c\", \"%c\", \"%c\", \"%c\", \"%c\".",
2015+
errhint("Valid object types are \"%c\", \"%c\", \"%c\", \"%c\", \"%c\", \"%c\".",
20132016
DEFACLOBJ_RELATION,
20142017
DEFACLOBJ_SEQUENCE,
20152018
DEFACLOBJ_FUNCTION,
20162019
DEFACLOBJ_TYPE,
2017-
DEFACLOBJ_NAMESPACE)));
2020+
DEFACLOBJ_NAMESPACE,
2021+
DEFACLOBJ_LARGEOBJECT)));
20182022
}
20192023

20202024
/*
@@ -3844,6 +3848,12 @@ getObjectDescription(const ObjectAddress *object, bool missing_ok)
38443848
_("default privileges on new schemas belonging to role %s"),
38453849
rolename);
38463850
break;
3851+
case DEFACLOBJ_LARGEOBJECT:
3852+
Assert(!nspname);
3853+
appendStringInfo(&buffer,
3854+
_("default privileges on new large objects belonging to role %s"),
3855+
rolename);
3856+
break;
38473857
default:
38483858
/* shouldn't get here */
38493859
if (nspname)
@@ -5766,6 +5776,10 @@ getObjectIdentityParts(const ObjectAddress *object,
57665776
appendStringInfoString(&buffer,
57675777
" on schemas");
57685778
break;
5779+
case DEFACLOBJ_LARGEOBJECT:
5780+
appendStringInfoString(&buffer,
5781+
" on large objects");
5782+
break;
57695783
}
57705784

57715785
if (objname)

‎src/backend/catalog/pg_largeobject.c

Lines changed: 16 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -20,6 +20,7 @@
2020
#include "catalog/pg_largeobject.h"
2121
#include "catalog/pg_largeobject_metadata.h"
2222
#include "miscadmin.h"
23+
#include "utils/acl.h"
2324
#include "utils/fmgroids.h"
2425
#include "utils/rel.h"
2526

@@ -39,6 +40,8 @@ LargeObjectCreate(Oid loid)
3940
Oid loid_new;
4041
Datum values[Natts_pg_largeobject_metadata];
4142
bool nulls[Natts_pg_largeobject_metadata];
43+
Oid ownerId;
44+
Acl *lomacl;
4245

4346
pg_lo_meta = table_open(LargeObjectMetadataRelationId,
4447
RowExclusiveLock);
@@ -55,11 +58,18 @@ LargeObjectCreate(Oid loid)
5558
loid_new = GetNewOidWithIndex(pg_lo_meta,
5659
LargeObjectMetadataOidIndexId,
5760
Anum_pg_largeobject_metadata_oid);
61+
ownerId = GetUserId();
62+
lomacl = get_user_default_acl(OBJECT_LARGEOBJECT, ownerId, InvalidOid);
5863

5964
values[Anum_pg_largeobject_metadata_oid - 1] = ObjectIdGetDatum(loid_new);
6065
values[Anum_pg_largeobject_metadata_lomowner - 1]
61-
= ObjectIdGetDatum(GetUserId());
62-
nulls[Anum_pg_largeobject_metadata_lomacl - 1] = true;
66+
= ObjectIdGetDatum(ownerId);
67+
68+
if (lomacl != NULL)
69+
values[Anum_pg_largeobject_metadata_lomacl - 1]
70+
= PointerGetDatum(lomacl);
71+
else
72+
nulls[Anum_pg_largeobject_metadata_lomacl - 1] = true;
6373

6474
ntup = heap_form_tuple(RelationGetDescr(pg_lo_meta),
6575
values, nulls);
@@ -70,6 +80,10 @@ LargeObjectCreate(Oid loid)
7080

7181
table_close(pg_lo_meta, RowExclusiveLock);
7282

83+
/* dependencies on roles mentioned in default ACL */
84+
recordDependencyOnNewAcl(LargeObjectRelationId, loid_new, 0,
85+
ownerId, lomacl);
86+
7387
return loid_new;
7488
}
7589

‎src/backend/parser/gram.y

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -752,7 +752,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
752752
NOT NOTHING NOTIFY NOTNULL NOWAIT NULL_P NULLIF
753753
NULLS_P NUMERIC
754754

755-
OBJECT_P OF OFF OFFSET OIDS OLD OMIT ON ONLY OPERATOR OPTION OPTIONS OR
755+
OBJECT_P OBJECTS_P OF OFF OFFSET OIDS OLD OMIT ON ONLY OPERATOR OPTION OPTIONS OR
756756
ORDER ORDINALITY OTHERS OUT_P OUTER_P
757757
OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER
758758

@@ -8177,6 +8177,7 @@ defacl_privilege_target:
81778177
| SEQUENCES { $$ = OBJECT_SEQUENCE; }
81788178
| TYPES_P { $$ = OBJECT_TYPE; }
81798179
| SCHEMAS { $$ = OBJECT_SCHEMA; }
8180+
| LARGE_P OBJECTS_P { $$ = OBJECT_LARGEOBJECT; }
81808181
;
81818182

81828183

@@ -17882,6 +17883,7 @@ unreserved_keyword:
1788217883
| NOWAIT
1788317884
| NULLS_P
1788417885
| OBJECT_P
17886+
| OBJECTS_P
1788517887
| OF
1788617888
| OFF
1788717889
| OIDS
@@ -18504,6 +18506,7 @@ bare_label_keyword:
1850418506
| NULLS_P
1850518507
| NUMERIC
1850618508
| OBJECT_P
18509+
| OBJECTS_P
1850718510
| OF
1850818511
| OFF
1850918512
| OIDS

‎src/bin/pg_dump/dumputils.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -506,7 +506,8 @@ do { \
506506
CONVERT_PRIV('s', "SET");
507507
CONVERT_PRIV('A', "ALTER SYSTEM");
508508
}
509-
else if (strcmp(type, "LARGE OBJECT") == 0)
509+
else if (strcmp(type, "LARGE OBJECT") == 0 ||
510+
strcmp(type, "LARGE OBJECTS") == 0)
510511
{
511512
CONVERT_PRIV('r', "SELECT");
512513
CONVERT_PRIV('w', "UPDATE");

‎src/bin/pg_dump/pg_dump.c

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -15679,6 +15679,9 @@ dumpDefaultACL(Archive *fout, const DefaultACLInfo *daclinfo)
1567915679
case DEFACLOBJ_NAMESPACE:
1568015680
type = "SCHEMAS";
1568115681
break;
15682+
case DEFACLOBJ_LARGEOBJECT:
15683+
type = "LARGE OBJECTS";
15684+
break;
1568215685
default:
1568315686
/* shouldn't get here */
1568415687
pg_fatal("unrecognized object type in default privileges: %d",

‎src/bin/psql/describe.c

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1222,7 +1222,9 @@ listDefaultACLs(const char *pattern)
12221222
printfPQExpBuffer(&buf,
12231223
"SELECT pg_catalog.pg_get_userbyid(d.defaclrole) AS \"%s\",\n"
12241224
" n.nspname AS \"%s\",\n"
1225-
" 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"
1225+
" CASE d.defaclobjtype "
1226+
" WHEN '%c' THEN '%s' WHEN '%c' THEN '%s' WHEN '%c' THEN '%s'"
1227+
" WHEN '%c' THEN '%s' WHEN '%c' THEN '%s' WHEN '%c' THEN '%s' END AS \"%s\",\n"
12261228
" ",
12271229
gettext_noop("Owner"),
12281230
gettext_noop("Schema"),
@@ -1236,6 +1238,8 @@ listDefaultACLs(const char *pattern)
12361238
gettext_noop("type"),
12371239
DEFACLOBJ_NAMESPACE,
12381240
gettext_noop("schema"),
1241+
DEFACLOBJ_LARGEOBJECT,
1242+
gettext_noop("large object"),
12391243
gettext_noop("Type"));
12401244

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

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

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -4457,7 +4457,7 @@ match_previous_words(int pattern_id,
44574457
* objects supported.
44584458
*/
44594459
if (HeadMatches("ALTER", "DEFAULT", "PRIVILEGES"))
4460-
COMPLETE_WITH("TABLES", "SEQUENCES", "FUNCTIONS", "PROCEDURES", "ROUTINES", "TYPES", "SCHEMAS");
4460+
COMPLETE_WITH("TABLES", "SEQUENCES", "FUNCTIONS", "PROCEDURES", "ROUTINES", "TYPES", "SCHEMAS", "LARGE OBJECTS");
44614461
else
44624462
COMPLETE_WITH_SCHEMA_QUERY_PLUS(Query_for_list_of_grantables,
44634463
"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
@@ -68,6 +68,7 @@ MAKE_SYSCACHE(DEFACLROLENSPOBJ, pg_default_acl_role_nsp_obj_index, 8);
6868
#define DEFACLOBJ_FUNCTION 'f' /* function */
6969
#define DEFACLOBJ_TYPE 'T' /* type */
7070
#define DEFACLOBJ_NAMESPACE 'n' /* namespace */
71+
#define DEFACLOBJ_LARGEOBJECT 'L' /* large object */
7172

7273
#endif /* EXPOSE_TO_CLIENT_CODE */
7374

‎src/include/parser/kwlist.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -308,6 +308,7 @@ PG_KEYWORD("nullif", NULLIF, COL_NAME_KEYWORD, BARE_LABEL)
308308
PG_KEYWORD("nulls", NULLS_P, UNRESERVED_KEYWORD, BARE_LABEL)
309309
PG_KEYWORD("numeric", NUMERIC, COL_NAME_KEYWORD, BARE_LABEL)
310310
PG_KEYWORD("object", OBJECT_P, UNRESERVED_KEYWORD, BARE_LABEL)
311+
PG_KEYWORD("objects", OBJECTS_P, UNRESERVED_KEYWORD, BARE_LABEL)
311312
PG_KEYWORD("of", OF, UNRESERVED_KEYWORD, BARE_LABEL)
312313
PG_KEYWORD("off", OFF, UNRESERVED_KEYWORD, BARE_LABEL)
313314
PG_KEYWORD("offset", OFFSET, RESERVED_KEYWORD, AS_LABEL)

0 commit comments

Comments
 (0)