On Tue, 23 Apr 2024 23:47:38 -0400
Tom Lane <t...@sss.pgh.pa.us> wrote:
> Yugo NAGATA <nag...@sraoss.co.jp> writes:
> > Currently, ALTER DEFAULT PRIVILEGE doesn't support large objects,
> > so if we want to allow users other than the owner to use the large
> > object, we need to grant a privilege on it every time a large object
> > is created. One of our clients feels that this is annoying, so I would
> > like propose to extend ALTER DEFAULT PRIVILEGE to large objects.
>
> I wonder how this plays with pg_dump, and in particular whether it
> breaks the optimizations that a45c78e32 installed for large numbers
> of large objects. The added test cases seem to go out of their way
> to leave no trace behind that the pg_dump/pg_upgrade tests might
> encounter.
Thank you for your comments.
The previous patch did not work with pg_dump since I forgot some fixes.
I attached a updated patch including fixes.
I believe a45c78e32 is about already-existing large objects and does
not directly related to default privileges, so will not be affected
by this patch.
> I think you broke psql's \ddp, too. And some other places; grepping
> for DEFACLOBJ_NAMESPACE finds other oversights.
Yes, I did. The attached patch include fixes for psql, too.
> On the whole I find this proposed feature pretty unexciting
> and dubiously worthy of the implementation/maintenance effort.
I believe this feature is beneficial to some users allows because
this enables to omit GRANT that was necessary every large object
creation. It seems to me that implementation/maintenance cost is not
so high compared to other objects (e.g. default privileges on schemas)
unless I am still missing something wrong.
Regards,
Yugo Nagata
--
Yugo NAGATA <nag...@sraoss.co.jp>
>From 0cfcdc2b297556248cfb64d67779d5fcb8dab227 Mon Sep 17 00:00:00 2001
From: Yugo Nagata <nag...@sraoss.co.jp>
Date: Fri, 8 Mar 2024 17:43:43 +0900
Subject: [PATCH v2] Extend ALTER DEFAULT PRIVILEGES for large objects
Original patch by Haruka Takatsuka, some fixes and tests by
Yugo Nagata.
---
doc/src/sgml/catalogs.sgml | 3 +-
.../sgml/ref/alter_default_privileges.sgml | 15 ++-
src/backend/catalog/aclchk.c | 21 ++++
src/backend/catalog/objectaddress.c | 18 ++-
src/backend/catalog/pg_largeobject.c | 18 ++-
src/backend/parser/gram.y | 5 +-
src/bin/pg_dump/dumputils.c | 3 +-
src/bin/pg_dump/pg_dump.c | 3 +
src/bin/psql/describe.c | 6 +-
src/bin/psql/tab-complete.c | 2 +-
src/include/catalog/pg_default_acl.h | 1 +
src/include/parser/kwlist.h | 1 +
src/test/regress/expected/privileges.out | 104 +++++++++++++++++-
src/test/regress/sql/privileges.sql | 47 ++++++++
14 files changed, 235 insertions(+), 12 deletions(-)
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 2907079e2a..b8cc822aeb 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -3330,7 +3330,8 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
<literal>S</literal> = sequence,
<literal>f</literal> = function,
<literal>T</literal> = type,
- <literal>n</literal> = schema
+ <literal>n</literal> = schema,
+ <literal>L</literal> = large object
</para></entry>
</row>
diff --git a/doc/src/sgml/ref/alter_default_privileges.sgml b/doc/src/sgml/ref/alter_default_privileges.sgml
index 1de4c5c1b4..3b358b7a88 100644
--- a/doc/src/sgml/ref/alter_default_privileges.sgml
+++ b/doc/src/sgml/ref/alter_default_privileges.sgml
@@ -50,6 +50,11 @@ GRANT { USAGE | CREATE | ALL [ PRIVILEGES ] }
ON SCHEMAS
TO { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
+GRANT { { SELECT | UPDATE }
+ [, ...] | ALL [ PRIVILEGES ] }
+ ON LARGE OBJECTS
+ TO { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
+
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | MAINTAIN }
[, ...] | ALL [ PRIVILEGES ] }
@@ -81,6 +86,13 @@ REVOKE [ GRANT OPTION FOR ]
ON SCHEMAS
FROM { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
+
+REVOKE [ GRANT OPTION FOR ]
+ { { SELECT | UPDATE }
+ [, ...] | ALL [ PRIVILEGES ] }
+ ON LARGE OBJECTS
+ FROM { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...]
+ [ CASCADE | RESTRICT ]
</synopsis>
</refsynopsisdiv>
@@ -159,7 +171,8 @@ REVOKE [ GRANT OPTION FOR ]
If <literal>IN SCHEMA</literal> is omitted, the global default privileges
are altered.
<literal>IN SCHEMA</literal> is not allowed when setting privileges
- for schemas, since schemas can't be nested.
+ for schemas and large objects, since schemas can't be nested and
+ large objects don't belong to a schema.
</para>
</listitem>
</varlistentry>
diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c
index 7abf3c2a74..41baf81a1d 100644
--- a/src/backend/catalog/aclchk.c
+++ b/src/backend/catalog/aclchk.c
@@ -1077,6 +1077,10 @@ ExecAlterDefaultPrivilegesStmt(ParseState *pstate, AlterDefaultPrivilegesStmt *s
all_privileges = ACL_ALL_RIGHTS_SCHEMA;
errormsg = gettext_noop("invalid privilege type %s for schema");
break;
+ case OBJECT_LARGEOBJECT:
+ all_privileges = ACL_ALL_RIGHTS_LARGEOBJECT;
+ errormsg = gettext_noop("invalid privilege type %s for large object");
+ break;
default:
elog(ERROR, "unrecognized GrantStmt.objtype: %d",
(int) action->objtype);
@@ -1268,6 +1272,16 @@ SetDefaultACL(InternalDefaultACL *iacls)
this_privileges = ACL_ALL_RIGHTS_SCHEMA;
break;
+ case OBJECT_LARGEOBJECT:
+ if (OidIsValid(iacls->nspid))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_GRANT_OPERATION),
+ errmsg("cannot use IN SCHEMA clause when using GRANT/REVOKE ON LARGE OBJECTS")));
+ objtype = DEFACLOBJ_LARGEOBJECT;
+ if (iacls->all_privs && this_privileges == ACL_NO_RIGHTS)
+ this_privileges = ACL_ALL_RIGHTS_LARGEOBJECT;
+ break;
+
default:
elog(ERROR, "unrecognized object type: %d",
(int) iacls->objtype);
@@ -1499,6 +1513,9 @@ RemoveRoleFromObjectACL(Oid roleid, Oid classid, Oid objid)
case DEFACLOBJ_NAMESPACE:
iacls.objtype = OBJECT_SCHEMA;
break;
+ case DEFACLOBJ_LARGEOBJECT:
+ iacls.objtype = OBJECT_LARGEOBJECT;
+ break;
default:
/* Shouldn't get here */
elog(ERROR, "unexpected default ACL type: %d",
@@ -4324,6 +4341,10 @@ get_user_default_acl(ObjectType objtype, Oid ownerId, Oid nsp_oid)
defaclobjtype = DEFACLOBJ_NAMESPACE;
break;
+ case OBJECT_LARGEOBJECT:
+ defaclobjtype = DEFACLOBJ_LARGEOBJECT;
+ break;
+
default:
return NULL;
}
diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c
index 7b536ac6fd..5b330967a6 100644
--- a/src/backend/catalog/objectaddress.c
+++ b/src/backend/catalog/objectaddress.c
@@ -2000,16 +2000,20 @@ get_object_address_defacl(List *object, bool missing_ok)
case DEFACLOBJ_NAMESPACE:
objtype_str = "schemas";
break;
+ case DEFACLOBJ_LARGEOBJECT:
+ objtype_str = "large objects";
+ break;
default:
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("unrecognized default ACL object type \"%c\"", objtype),
- errhint("Valid object types are \"%c\", \"%c\", \"%c\", \"%c\", \"%c\".",
+ errhint("Valid object types are \"%c\", \"%c\", \"%c\", \"%c\", \"%c\", \"%c\".",
DEFACLOBJ_RELATION,
DEFACLOBJ_SEQUENCE,
DEFACLOBJ_FUNCTION,
DEFACLOBJ_TYPE,
- DEFACLOBJ_NAMESPACE)));
+ DEFACLOBJ_NAMESPACE,
+ DEFACLOBJ_LARGEOBJECT)));
}
/*
@@ -3798,6 +3802,12 @@ getObjectDescription(const ObjectAddress *object, bool missing_ok)
_("default privileges on new schemas belonging to role %s"),
rolename);
break;
+ case DEFACLOBJ_LARGEOBJECT:
+ Assert(!nspname);
+ appendStringInfo(&buffer,
+ _("default privileges on new large objects belonging to role %s"),
+ rolename);
+ break;
default:
/* shouldn't get here */
if (nspname)
@@ -5651,6 +5661,10 @@ getObjectIdentityParts(const ObjectAddress *object,
appendStringInfoString(&buffer,
" on schemas");
break;
+ case DEFACLOBJ_LARGEOBJECT:
+ appendStringInfoString(&buffer,
+ " on large objects");
+ break;
}
if (objname)
diff --git a/src/backend/catalog/pg_largeobject.c b/src/backend/catalog/pg_largeobject.c
index e235f7c5e6..578589b457 100644
--- a/src/backend/catalog/pg_largeobject.c
+++ b/src/backend/catalog/pg_largeobject.c
@@ -22,6 +22,7 @@
#include "catalog/pg_largeobject.h"
#include "catalog/pg_largeobject_metadata.h"
#include "miscadmin.h"
+#include "utils/acl.h"
#include "utils/fmgroids.h"
#include "utils/rel.h"
@@ -41,6 +42,8 @@ LargeObjectCreate(Oid loid)
Oid loid_new;
Datum values[Natts_pg_largeobject_metadata];
bool nulls[Natts_pg_largeobject_metadata];
+ Oid ownerId;
+ Acl *lomacl;
pg_lo_meta = table_open(LargeObjectMetadataRelationId,
RowExclusiveLock);
@@ -57,11 +60,18 @@ LargeObjectCreate(Oid loid)
loid_new = GetNewOidWithIndex(pg_lo_meta,
LargeObjectMetadataOidIndexId,
Anum_pg_largeobject_metadata_oid);
+ ownerId = GetUserId();
+ lomacl = get_user_default_acl(OBJECT_LARGEOBJECT, ownerId, InvalidOid);
values[Anum_pg_largeobject_metadata_oid - 1] = ObjectIdGetDatum(loid_new);
values[Anum_pg_largeobject_metadata_lomowner - 1]
- = ObjectIdGetDatum(GetUserId());
- nulls[Anum_pg_largeobject_metadata_lomacl - 1] = true;
+ = ObjectIdGetDatum(ownerId);
+
+ if (lomacl != NULL)
+ values[Anum_pg_largeobject_metadata_lomacl - 1]
+ = PointerGetDatum(lomacl);
+ else
+ nulls[Anum_pg_largeobject_metadata_lomacl - 1] = true;
ntup = heap_form_tuple(RelationGetDescr(pg_lo_meta),
values, nulls);
@@ -72,6 +82,10 @@ LargeObjectCreate(Oid loid)
table_close(pg_lo_meta, RowExclusiveLock);
+ /* dependencies on roles mentioned in default ACL */
+ recordDependencyOnNewAcl(LargeObjectRelationId, loid_new, 0,
+ ownerId, lomacl);
+
return loid_new;
}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index e8b619926e..35535ab390 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -760,7 +760,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
NOT NOTHING NOTIFY NOTNULL NOWAIT NULL_P NULLIF
NULLS_P NUMERIC
- OBJECT_P OF OFF OFFSET OIDS OLD OMIT ON ONLY OPERATOR OPTION OPTIONS OR
+ OBJECT_P OBJECTS_P OF OFF OFFSET OIDS OLD OMIT ON ONLY OPERATOR OPTION OPTIONS OR
ORDER ORDINALITY OTHERS OUT_P OUTER_P
OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER
@@ -8170,6 +8170,7 @@ defacl_privilege_target:
| SEQUENCES { $$ = OBJECT_SEQUENCE; }
| TYPES_P { $$ = OBJECT_TYPE; }
| SCHEMAS { $$ = OBJECT_SCHEMA; }
+ | LARGE_P OBJECTS_P { $$ = OBJECT_LARGEOBJECT; }
;
@@ -17788,6 +17789,7 @@ unreserved_keyword:
| NOWAIT
| NULLS_P
| OBJECT_P
+ | OBJECTS_P
| OF
| OFF
| OIDS
@@ -18411,6 +18413,7 @@ bare_label_keyword:
| NULLS_P
| NUMERIC
| OBJECT_P
+ | OBJECTS_P
| OF
| OFF
| OIDS
diff --git a/src/bin/pg_dump/dumputils.c b/src/bin/pg_dump/dumputils.c
index 5649859aa1..0fad29cbaf 100644
--- a/src/bin/pg_dump/dumputils.c
+++ b/src/bin/pg_dump/dumputils.c
@@ -506,7 +506,8 @@ do { \
CONVERT_PRIV('s', "SET");
CONVERT_PRIV('A', "ALTER SYSTEM");
}
- else if (strcmp(type, "LARGE OBJECT") == 0)
+ else if (strcmp(type, "LARGE OBJECT") == 0 ||
+ strcmp(type, "LARGE OBJECTS") == 0)
{
CONVERT_PRIV('r', "SELECT");
CONVERT_PRIV('w', "UPDATE");
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index b8acdd7355..298cbb3d56 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -15224,6 +15224,9 @@ dumpDefaultACL(Archive *fout, const DefaultACLInfo *daclinfo)
case DEFACLOBJ_NAMESPACE:
type = "SCHEMAS";
break;
+ case DEFACLOBJ_LARGEOBJECT:
+ type = "LARGE OBJECTS";
+ break;
default:
/* shouldn't get here */
pg_fatal("unrecognized object type in default privileges: %d",
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 4a9ee4a54d..12d002b709 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -1184,7 +1184,9 @@ listDefaultACLs(const char *pattern)
printfPQExpBuffer(&buf,
"SELECT pg_catalog.pg_get_userbyid(d.defaclrole) AS \"%s\",\n"
" n.nspname AS \"%s\",\n"
- " 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"
+ " CASE d.defaclobjtype "
+ " WHEN '%c' THEN '%s' WHEN '%c' THEN '%s' WHEN '%c' THEN '%s'"
+ " WHEN '%c' THEN '%s' WHEN '%c' THEN '%s' WHEN '%c' THEN '%s' END AS \"%s\",\n"
" ",
gettext_noop("Owner"),
gettext_noop("Schema"),
@@ -1198,6 +1200,8 @@ listDefaultACLs(const char *pattern)
gettext_noop("type"),
DEFACLOBJ_NAMESPACE,
gettext_noop("schema"),
+ DEFACLOBJ_LARGEOBJECT,
+ gettext_noop("large object"),
gettext_noop("Type"));
printACLColumn(&buf, "d.defaclacl");
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 6fee3160f0..03889fc52f 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -4035,7 +4035,7 @@ psql_completion(const char *text, int start, int end)
* objects supported.
*/
if (HeadMatches("ALTER", "DEFAULT", "PRIVILEGES"))
- COMPLETE_WITH("TABLES", "SEQUENCES", "FUNCTIONS", "PROCEDURES", "ROUTINES", "TYPES", "SCHEMAS");
+ COMPLETE_WITH("TABLES", "SEQUENCES", "FUNCTIONS", "PROCEDURES", "ROUTINES", "TYPES", "SCHEMAS", "LARGE OBJECTS");
else
COMPLETE_WITH_SCHEMA_QUERY_PLUS(Query_for_list_of_grantables,
"ALL FUNCTIONS IN SCHEMA",
diff --git a/src/include/catalog/pg_default_acl.h b/src/include/catalog/pg_default_acl.h
index d272cdf08b..f9f002fa45 100644
--- a/src/include/catalog/pg_default_acl.h
+++ b/src/include/catalog/pg_default_acl.h
@@ -68,6 +68,7 @@ MAKE_SYSCACHE(DEFACLROLENSPOBJ, pg_default_acl_role_nsp_obj_index, 8);
#define DEFACLOBJ_FUNCTION 'f' /* function */
#define DEFACLOBJ_TYPE 'T' /* type */
#define DEFACLOBJ_NAMESPACE 'n' /* namespace */
+#define DEFACLOBJ_LARGEOBJECT 'L' /* large object */
#endif /* EXPOSE_TO_CLIENT_CODE */
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index f9a4afd472..6299c29389 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -307,6 +307,7 @@ PG_KEYWORD("nullif", NULLIF, COL_NAME_KEYWORD, BARE_LABEL)
PG_KEYWORD("nulls", NULLS_P, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("numeric", NUMERIC, COL_NAME_KEYWORD, BARE_LABEL)
PG_KEYWORD("object", OBJECT_P, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("objects", OBJECTS_P, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("of", OF, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("off", OFF, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("offset", OFFSET, RESERVED_KEYWORD, AS_LABEL)
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index eb4b762ea1..29f0775373 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -2357,11 +2357,110 @@ SELECT has_schema_privilege('regress_priv_user2', 'testns4', 'CREATE'); -- yes
ALTER DEFAULT PRIVILEGES REVOKE ALL ON SCHEMAS FROM regress_priv_user2;
COMMIT;
+--
+-- Test for default privileges on large objects. This is done in a
+-- separate, rollbacked, transaction to avoid any trouble with other
+-- regression sessions.
+--
+\c -
+SET SESSION AUTHORIZATION regress_priv_user1;
+SELECT lo_create(1007);
+ lo_create
+-----------
+ 1007
+(1 row)
+
+SET SESSION AUTHORIZATION regress_priv_user2;
+SELECT loread(lo_open(1007, x'40000'::int), 32); -- to be denied
+ERROR: permission denied for large object 1007
+SELECT lowrite(lo_open(1007, x'20000'::int), 'abcd'); -- to be denied
+ERROR: permission denied for large object 1007
+BEGIN;
+SET SESSION AUTHORIZATION regress_priv_user1;
+ALTER DEFAULT PRIVILEGES GRANT SELECT ON LARGE OBJECTS TO public;
+SELECT lo_unlink(1007);
+ lo_unlink
+-----------
+ 1
+(1 row)
+
+SELECT lo_create(1007);
+ lo_create
+-----------
+ 1007
+(1 row)
+
+SET SESSION AUTHORIZATION regress_priv_user2;
+SELECT loread(lo_open(1007, x'40000'::int), 32); -- ok
+ loread
+--------
+ \x
+(1 row)
+
+SELECT lowrite(lo_open(1007, x'20000'::int), 'abcd'); -- to be denied
+ERROR: permission denied for large object 1007
+ROLLBACK;
+BEGIN;
+SET SESSION AUTHORIZATION regress_priv_user1;
+ALTER DEFAULT PRIVILEGES GRANT SELECT, UPDATE ON LARGE OBJECTS TO regress_priv_user2;
+SELECT lo_unlink(1007);
+ lo_unlink
+-----------
+ 1
+(1 row)
+
+SELECT lo_create(1007);
+ lo_create
+-----------
+ 1007
+(1 row)
+
+SET SESSION AUTHORIZATION regress_priv_user2;
+SELECT loread(lo_open(1007, x'40000'::int), 32); -- ok
+ loread
+--------
+ \x
+(1 row)
+
+SELECT lowrite(lo_open(1007, x'20000'::int), 'abcd'); -- ok
+ lowrite
+---------
+ 4
+(1 row)
+
+SET SESSION AUTHORIZATION regress_priv_user1;
+ALTER DEFAULT PRIVILEGES REVOKE UPDATE ON LARGE OBJECTS FROM regress_priv_user2;
+SELECT lo_unlink(1007);
+ lo_unlink
+-----------
+ 1
+(1 row)
+
+SELECT lo_create(1007);
+ lo_create
+-----------
+ 1007
+(1 row)
+
+SET SESSION AUTHORIZATION regress_priv_user2;
+SELECT loread(lo_open(1007, x'40000'::int), 32); -- ok
+ loread
+--------
+ \x
+(1 row)
+
+SELECT lowrite(lo_open(1007, x'20000'::int), 'abcd'); -- to be denied
+ERROR: permission denied for large object 1007
+ROLLBACK;
+ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON LARGE OBJECTS TO public; -- error
+ERROR: cannot use IN SCHEMA clause when using GRANT/REVOKE ON LARGE OBJECTS
+\c -
-- Test for DROP OWNED BY with shared dependencies. This is done in a
-- separate, rollbacked, transaction to avoid any trouble with other
-- regression sessions.
BEGIN;
ALTER DEFAULT PRIVILEGES GRANT ALL ON FUNCTIONS TO regress_priv_user2;
+ALTER DEFAULT PRIVILEGES GRANT ALL ON LARGE OBJECTS TO regress_priv_user2;
ALTER DEFAULT PRIVILEGES GRANT ALL ON SCHEMAS TO regress_priv_user2;
ALTER DEFAULT PRIVILEGES GRANT ALL ON SEQUENCES TO regress_priv_user2;
ALTER DEFAULT PRIVILEGES GRANT ALL ON TABLES TO regress_priv_user2;
@@ -2372,7 +2471,7 @@ SELECT count(*) FROM pg_shdepend
classid = 'pg_default_acl'::regclass;
count
-------
- 5
+ 6
(1 row)
DROP OWNED BY regress_priv_user2, regress_priv_user2;
@@ -2708,7 +2807,8 @@ SELECT lo_unlink(oid) FROM pg_largeobject_metadata WHERE oid >= 1000 AND oid < 3
1
1
1
-(5 rows)
+ 1
+(6 rows)
DROP GROUP regress_priv_group1;
DROP GROUP regress_priv_group2;
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index eeb4c00292..e8db20573c 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -1487,11 +1487,58 @@ ALTER DEFAULT PRIVILEGES REVOKE ALL ON SCHEMAS FROM regress_priv_user2;
COMMIT;
+--
+-- Test for default privileges on large objects. This is done in a
+-- separate, rollbacked, transaction to avoid any trouble with other
+-- regression sessions.
+--
+\c -
+SET SESSION AUTHORIZATION regress_priv_user1;
+SELECT lo_create(1007);
+SET SESSION AUTHORIZATION regress_priv_user2;
+SELECT loread(lo_open(1007, x'40000'::int), 32); -- to be denied
+SELECT lowrite(lo_open(1007, x'20000'::int), 'abcd'); -- to be denied
+
+BEGIN;
+SET SESSION AUTHORIZATION regress_priv_user1;
+ALTER DEFAULT PRIVILEGES GRANT SELECT ON LARGE OBJECTS TO public;
+SELECT lo_unlink(1007);
+SELECT lo_create(1007);
+SET SESSION AUTHORIZATION regress_priv_user2;
+SELECT loread(lo_open(1007, x'40000'::int), 32); -- ok
+SELECT lowrite(lo_open(1007, x'20000'::int), 'abcd'); -- to be denied
+ROLLBACK;
+
+BEGIN;
+
+SET SESSION AUTHORIZATION regress_priv_user1;
+ALTER DEFAULT PRIVILEGES GRANT SELECT, UPDATE ON LARGE OBJECTS TO regress_priv_user2;
+SELECT lo_unlink(1007);
+SELECT lo_create(1007);
+SET SESSION AUTHORIZATION regress_priv_user2;
+SELECT loread(lo_open(1007, x'40000'::int), 32); -- ok
+SELECT lowrite(lo_open(1007, x'20000'::int), 'abcd'); -- ok
+
+SET SESSION AUTHORIZATION regress_priv_user1;
+ALTER DEFAULT PRIVILEGES REVOKE UPDATE ON LARGE OBJECTS FROM regress_priv_user2;
+SELECT lo_unlink(1007);
+SELECT lo_create(1007);
+SET SESSION AUTHORIZATION regress_priv_user2;
+SELECT loread(lo_open(1007, x'40000'::int), 32); -- ok
+SELECT lowrite(lo_open(1007, x'20000'::int), 'abcd'); -- to be denied
+
+ROLLBACK;
+
+ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON LARGE OBJECTS TO public; -- error
+
+\c -
+
-- Test for DROP OWNED BY with shared dependencies. This is done in a
-- separate, rollbacked, transaction to avoid any trouble with other
-- regression sessions.
BEGIN;
ALTER DEFAULT PRIVILEGES GRANT ALL ON FUNCTIONS TO regress_priv_user2;
+ALTER DEFAULT PRIVILEGES GRANT ALL ON LARGE OBJECTS TO regress_priv_user2;
ALTER DEFAULT PRIVILEGES GRANT ALL ON SCHEMAS TO regress_priv_user2;
ALTER DEFAULT PRIVILEGES GRANT ALL ON SEQUENCES TO regress_priv_user2;
ALTER DEFAULT PRIVILEGES GRANT ALL ON TABLES TO regress_priv_user2;
--
2.25.1
