On Fri, 26 Apr 2024 17:54:06 +0900
Yugo NAGATA <nag...@sraoss.co.jp> wrote:
> On Wed, 24 Apr 2024 16:08:39 -0500
> Nathan Bossart <nathandboss...@gmail.com> wrote:
>
> > On Tue, Apr 23, 2024 at 11:47:38PM -0400, Tom Lane wrote:
> > > On the whole I find this proposed feature pretty unexciting
> > > and dubiously worthy of the implementation/maintenance effort.
> >
> > I don't have any particularly strong feelings on $SUBJECT, but I'll admit
> > I'd be much more interested in resolving any remaining reasons folks are
> > using large objects over TOAST. I see a couple of reasons listed in the
> > docs [0] that might be worth examining.
> >
> > [0] https://www.postgresql.org/docs/devel/lo-intro.html
>
> If we could replace large objects with BYTEA in any use cases, large objects
> would be completely obsolete. However, currently some users use large objects
> in fact, so improvement in this feature seems beneficial for them.
I've attached a updated patch. The test is rewritten using
has_largeobject_privilege()
function instead of calling loread & lowrite, which makes the test a bit
simpler.
Thare are no other changes.
Regards,
Yugo Nagata
--
Yugo Nagata <nag...@sraoss.co.jp>
>From a27680b9f3031b0995fe20dd2d166e07a17ffeca 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 v3] Extend ALTER DEFAULT PRIVILEGES for large objects
Previously, ALTER DEFAULT PRIVILEGE didn'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.
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 | 94 ++++++++++++++++++-
src/test/regress/sql/privileges.sql | 36 +++++++
14 files changed, 215 insertions(+), 11 deletions(-)
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index b654fae1b2..c8cf56c666 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -3322,7 +3322,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 89aacec4fa..3ab695892d 100644
--- a/doc/src/sgml/ref/alter_default_privileges.sgml
+++ b/doc/src/sgml/ref/alter_default_privileges.sgml
@@ -51,6 +51,11 @@ GRANT { { USAGE | CREATE }
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 ] }
@@ -83,6 +88,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>
@@ -161,7 +173,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 d2abc48fd8..6463ae921a 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);
@@ -1511,6 +1525,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",
@@ -4334,6 +4351,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 85a7b7e641..94f38c2333 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)
@@ -5720,6 +5730,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 5d9fdfbd4c..ddadb3224a 100644
--- a/src/backend/catalog/pg_largeobject.c
+++ b/src/backend/catalog/pg_largeobject.c
@@ -20,6 +20,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"
@@ -39,6 +40,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);
@@ -55,11 +58,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);
@@ -70,6 +80,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 84cef57a70..a77df8f028 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -756,7 +756,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
@@ -8054,6 +8054,7 @@ defacl_privilege_target:
| SEQUENCES { $$ = OBJECT_SEQUENCE; }
| TYPES_P { $$ = OBJECT_TYPE; }
| SCHEMAS { $$ = OBJECT_SCHEMA; }
+ | LARGE_P OBJECTS_P { $$ = OBJECT_LARGEOBJECT; }
;
@@ -17672,6 +17673,7 @@ unreserved_keyword:
| NOWAIT
| NULLS_P
| OBJECT_P
+ | OBJECTS_P
| OF
| OFF
| OIDS
@@ -18291,6 +18293,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 546e7e4ce1..97484a1b1b 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -15009,6 +15009,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 7c9a1f234c..ee1b96b0b9 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 a7ccde6d7d..7979fd32c6 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -4032,7 +4032,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 f8659078ce..cbc7f2e870 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 1d903babd3..270d82112b 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -2563,11 +2563,103 @@ 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.
+--
+BEGIN;
+SELECT lo_create(1007);
+ lo_create
+-----------
+ 1007
+(1 row)
+
+SELECT has_largeobject_privilege('regress_priv_user2', 1007, 'SELECT'); -- no
+ has_largeobject_privilege
+---------------------------
+ f
+(1 row)
+
+SELECT has_largeobject_privilege('regress_priv_user2', 1007, 'UPDATE'); -- no
+ has_largeobject_privilege
+---------------------------
+ f
+(1 row)
+
+ALTER DEFAULT PRIVILEGES GRANT SELECT ON LARGE OBJECTS TO regress_priv_user2;
+SELECT lo_create(1008);
+ lo_create
+-----------
+ 1008
+(1 row)
+
+SELECT has_largeobject_privilege('regress_priv_user2', 1008, 'SELECT'); -- yes
+ has_largeobject_privilege
+---------------------------
+ t
+(1 row)
+
+SELECT has_largeobject_privilege('regress_priv_user6', 1008, 'SELECT'); -- no
+ has_largeobject_privilege
+---------------------------
+ f
+(1 row)
+
+SELECT has_largeobject_privilege('regress_priv_user2', 1008, 'UPDATE'); -- no
+ has_largeobject_privilege
+---------------------------
+ f
+(1 row)
+
+ALTER DEFAULT PRIVILEGES GRANT ALL ON LARGE OBJECTS TO regress_priv_user2;
+SELECT lo_create(1009);
+ lo_create
+-----------
+ 1009
+(1 row)
+
+SELECT has_largeobject_privilege('regress_priv_user2', 1009, 'SELECT'); -- true
+ has_largeobject_privilege
+---------------------------
+ t
+(1 row)
+
+SELECT has_largeobject_privilege('regress_priv_user2', 1009, 'UPDATE'); -- true
+ has_largeobject_privilege
+---------------------------
+ t
+(1 row)
+
+ALTER DEFAULT PRIVILEGES REVOKE UPDATE ON LARGE OBJECTS FROM regress_priv_user2;
+SELECT lo_create(1010);
+ lo_create
+-----------
+ 1010
+(1 row)
+
+SELECT has_largeobject_privilege('regress_priv_user2', 1010, 'SELECT'); -- true
+ has_largeobject_privilege
+---------------------------
+ t
+(1 row)
+
+SELECT has_largeobject_privilege('regress_priv_user2', 1010, 'UPDATE'); -- false
+ has_largeobject_privilege
+---------------------------
+ f
+(1 row)
+
+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;
@@ -2578,7 +2670,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;
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index 3f54b0f8f0..5598410e44 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -1535,11 +1535,47 @@ 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.
+--
+
+BEGIN;
+
+SELECT lo_create(1007);
+SELECT has_largeobject_privilege('regress_priv_user2', 1007, 'SELECT'); -- no
+SELECT has_largeobject_privilege('regress_priv_user2', 1007, 'UPDATE'); -- no
+
+ALTER DEFAULT PRIVILEGES GRANT SELECT ON LARGE OBJECTS TO regress_priv_user2;
+
+SELECT lo_create(1008);
+SELECT has_largeobject_privilege('regress_priv_user2', 1008, 'SELECT'); -- yes
+SELECT has_largeobject_privilege('regress_priv_user6', 1008, 'SELECT'); -- no
+SELECT has_largeobject_privilege('regress_priv_user2', 1008, 'UPDATE'); -- no
+
+ALTER DEFAULT PRIVILEGES GRANT ALL ON LARGE OBJECTS TO regress_priv_user2;
+SELECT lo_create(1009);
+SELECT has_largeobject_privilege('regress_priv_user2', 1009, 'SELECT'); -- true
+SELECT has_largeobject_privilege('regress_priv_user2', 1009, 'UPDATE'); -- true
+
+ALTER DEFAULT PRIVILEGES REVOKE UPDATE ON LARGE OBJECTS FROM regress_priv_user2;
+SELECT lo_create(1010);
+SELECT has_largeobject_privilege('regress_priv_user2', 1010, 'SELECT'); -- true
+SELECT has_largeobject_privilege('regress_priv_user2', 1010, 'UPDATE'); -- false
+
+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.34.1
