public inbox for [email protected]
help / color / mirror / Atom feedExtend ALTER DEFAULT PRIVILEGES for large objects
8+ messages / 5 participants
[nested] [flat]
* Extend ALTER DEFAULT PRIVILEGES for large objects
@ 2024-04-24 02:52 Yugo NAGATA <[email protected]>
2024-04-24 03:47 ` Re: Extend ALTER DEFAULT PRIVILEGES for large objects Tom Lane <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Yugo NAGATA @ 2024-04-24 02:52 UTC (permalink / raw)
To: pgsql-hackers
Hi,
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.
Here are the new actions allowed in abbreviated_grant_or_revoke;
+GRANT { { SELECT | UPDATE }
+ [, ...] | ALL [ PRIVILEGES ] }
+ ON LARGE OBJECTS
+ TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
+REVOKE [ GRANT OPTION FOR ]
+ { { SELECT | UPDATE }
+ [, ...] | ALL [ PRIVILEGES ] }
+ ON LARGE OBJECTS
+ FROM { [ GROUP ] role_name | PUBLIC } [, ...]
+ [ CASCADE | RESTRICT ]
A new keyword OBJECTS is introduced for using plural form in the syntax
as other supported objects. A schema name is not allowed to be specified
for large objects since any large objects don't belong to a schema.
The attached patch is originally proposed by Haruka Takatsuka
and some fixes and tests are made by me.
Regards,
Yugo Nagata
--
Yugo NAGATA <[email protected]>
Attachments:
[text/x-diff] 0001-Extend-ALTER-DEFAULT-PRIVILEGES-for-large-objects.patch (14.9K, ../../[email protected]/2-0001-Extend-ALTER-DEFAULT-PRIVILEGES-for-large-objects.patch)
download | inline diff:
From fe2cb39bd83d09a052d5d63889acd0968c1817b6 Mon Sep 17 00:00:00 2001
From: Yugo Nagata <[email protected]>
Date: Fri, 8 Mar 2024 17:43:43 +0900
Subject: [PATCH] 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/pg_largeobject.c | 18 ++-
src/backend/parser/gram.y | 5 +-
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 ++++++++
9 files changed, 208 insertions(+), 7 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/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/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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Extend ALTER DEFAULT PRIVILEGES for large objects
2024-04-24 02:52 Extend ALTER DEFAULT PRIVILEGES for large objects Yugo NAGATA <[email protected]>
@ 2024-04-24 03:47 ` Tom Lane <[email protected]>
2024-04-24 06:32 ` Re: Extend ALTER DEFAULT PRIVILEGES for large objects Yugo NAGATA <[email protected]>
2024-04-24 21:08 ` Re: Extend ALTER DEFAULT PRIVILEGES for large objects Nathan Bossart <[email protected]>
0 siblings, 2 replies; 8+ messages in thread
From: Tom Lane @ 2024-04-24 03:47 UTC (permalink / raw)
To: Yugo NAGATA <[email protected]>; +Cc: pgsql-hackers
Yugo NAGATA <[email protected]> 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.
I think you broke psql's \ddp, too. And some other places; grepping
for DEFACLOBJ_NAMESPACE finds other oversights.
On the whole I find this proposed feature pretty unexciting
and dubiously worthy of the implementation/maintenance effort.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Extend ALTER DEFAULT PRIVILEGES for large objects
2024-04-24 02:52 Extend ALTER DEFAULT PRIVILEGES for large objects Yugo NAGATA <[email protected]>
2024-04-24 03:47 ` Re: Extend ALTER DEFAULT PRIVILEGES for large objects Tom Lane <[email protected]>
@ 2024-04-24 06:32 ` Yugo NAGATA <[email protected]>
1 sibling, 0 replies; 8+ messages in thread
From: Yugo NAGATA @ 2024-04-24 06:32 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: pgsql-hackers
On Tue, 23 Apr 2024 23:47:38 -0400
Tom Lane <[email protected]> wrote:
> Yugo NAGATA <[email protected]> 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 <[email protected]>
Attachments:
[text/x-diff] v2-0001-Extend-ALTER-DEFAULT-PRIVILEGES-for-large-objects.patch (19.6K, ../../[email protected]/2-v2-0001-Extend-ALTER-DEFAULT-PRIVILEGES-for-large-objects.patch)
download | inline diff:
From 0cfcdc2b297556248cfb64d67779d5fcb8dab227 Mon Sep 17 00:00:00 2001
From: Yugo Nagata <[email protected]>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Extend ALTER DEFAULT PRIVILEGES for large objects
2024-04-24 02:52 Extend ALTER DEFAULT PRIVILEGES for large objects Yugo NAGATA <[email protected]>
2024-04-24 03:47 ` Re: Extend ALTER DEFAULT PRIVILEGES for large objects Tom Lane <[email protected]>
@ 2024-04-24 21:08 ` Nathan Bossart <[email protected]>
2024-04-26 08:54 ` Re: Extend ALTER DEFAULT PRIVILEGES for large objects Yugo NAGATA <[email protected]>
1 sibling, 1 reply; 8+ messages in thread
From: Nathan Bossart @ 2024-04-24 21:08 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Yugo NAGATA <[email protected]>; pgsql-hackers
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
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Extend ALTER DEFAULT PRIVILEGES for large objects
2024-04-24 02:52 Extend ALTER DEFAULT PRIVILEGES for large objects Yugo NAGATA <[email protected]>
2024-04-24 03:47 ` Re: Extend ALTER DEFAULT PRIVILEGES for large objects Tom Lane <[email protected]>
2024-04-24 21:08 ` Re: Extend ALTER DEFAULT PRIVILEGES for large objects Nathan Bossart <[email protected]>
@ 2024-04-26 08:54 ` Yugo NAGATA <[email protected]>
2024-04-26 10:23 ` Re: Extend ALTER DEFAULT PRIVILEGES for large objects Matthias van de Meent <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Yugo NAGATA @ 2024-04-26 08:54 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; +Cc: Tom Lane <[email protected]>; pgsql-hackers
On Wed, 24 Apr 2024 16:08:39 -0500
Nathan Bossart <[email protected]> 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.
Apart from that, extending TOAST to support more than 1GB data and
stream-style access seems a good challenge. I don't know if there was a
proposal for this in past. This is just a thought, for this purpose, we
will need a new type of varlena that can contains large size information,
and a new toast table schema that can store offset information or some way
to convert a offset to chunk_seq.
Regards,
Yugo Nagata
> --
> Nathan Bossart
> Amazon Web Services: https://aws.amazon.com
--
Yugo NAGATA <[email protected]>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Extend ALTER DEFAULT PRIVILEGES for large objects
2024-04-24 02:52 Extend ALTER DEFAULT PRIVILEGES for large objects Yugo NAGATA <[email protected]>
2024-04-24 03:47 ` Re: Extend ALTER DEFAULT PRIVILEGES for large objects Tom Lane <[email protected]>
2024-04-24 21:08 ` Re: Extend ALTER DEFAULT PRIVILEGES for large objects Nathan Bossart <[email protected]>
2024-04-26 08:54 ` Re: Extend ALTER DEFAULT PRIVILEGES for large objects Yugo NAGATA <[email protected]>
@ 2024-04-26 10:23 ` Matthias van de Meent <[email protected]>
2024-05-02 09:00 ` Re: Extend ALTER DEFAULT PRIVILEGES for large objects Yugo NAGATA <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Matthias van de Meent @ 2024-04-26 10:23 UTC (permalink / raw)
To: Yugo NAGATA <[email protected]>; +Cc: Nathan Bossart <[email protected]>; Tom Lane <[email protected]>; pgsql-hackers
On Fri, 26 Apr 2024 at 10:54, Yugo NAGATA <[email protected]> wrote:
>
> On Wed, 24 Apr 2024 16:08:39 -0500
> Nathan Bossart <[email protected]> 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.
>
>
> Apart from that, extending TOAST to support more than 1GB data and
> stream-style access seems a good challenge. I don't know if there was a
> proposal for this in past. This is just a thought, for this purpose, we
> will need a new type of varlena that can contains large size information,
> and a new toast table schema that can store offset information or some way
> to convert a offset to chunk_seq.
If you're interested in this, you may want to check out [0] and [1] as
threads on the topic of improving TOAST handling of large values ([1]
being a thread where the limitations of our current external TOAST
pointer became clear once more), and maybe talk with Aleksander
Alekseev and Nikita Malakhov. They've been working closely with
systems that involve toast pointers and their limitations.
The most recent update on the work of Nikita (reworking TOAST
handling) [2] is that he got started adapting their externally
pluggable toast into type-internal methods only, though I've not yet
noticed any updated patches appear on the list.
As for other issues with creating larger TOAST values:
TOAST has a value limit of ~1GB, which means a single large value (or
two, for that matter) won't break anything in the wire protocol, as
DataRow messages have a message size field of uint32 [^3]. However, if
we're going to allow even larger values to be stored in table's
attributes, we'll have to figure out how we're going to transfer those
larger values to (and from) clients. For large objects, this is much
less of an issue because the IO operations are already chunked by
design, but this may not work well for types that you want to use in
your table's columns.
Kind regards,
Matthias van de Meent
[0] https://postgr.es/m/flat/CAN-LCVMq2X%3Dfhx7KLxfeDyb3P%2BBXuCkHC0g%3D9GF%2BJD4izfVa0Q%40mail.gmail.co...
[1] https://postgr.es/m/flat/CAJ7c6TOtAB0z1UrksvGTStNE-herK-43bj22%3D5xVBg7S4vr5rQ%40mail.gmail.com
[2] https://postgr.es/m/CAN-LCVOgMrda9hOdzGkCMdwY6dH0JQa13QvPsqUwY57TEn6jww%40mail.gmail.com
[^3] Most, if not all PostgreSQL wire protocol messages have this
uint32 message size field, but the DataRow one is relevant here as
it's the one way users get their data out of the database.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Extend ALTER DEFAULT PRIVILEGES for large objects
2024-04-24 02:52 Extend ALTER DEFAULT PRIVILEGES for large objects Yugo NAGATA <[email protected]>
2024-04-24 03:47 ` Re: Extend ALTER DEFAULT PRIVILEGES for large objects Tom Lane <[email protected]>
2024-04-24 21:08 ` Re: Extend ALTER DEFAULT PRIVILEGES for large objects Nathan Bossart <[email protected]>
2024-04-26 08:54 ` Re: Extend ALTER DEFAULT PRIVILEGES for large objects Yugo NAGATA <[email protected]>
2024-04-26 10:23 ` Re: Extend ALTER DEFAULT PRIVILEGES for large objects Matthias van de Meent <[email protected]>
@ 2024-05-02 09:00 ` Yugo NAGATA <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: Yugo NAGATA @ 2024-05-02 09:00 UTC (permalink / raw)
To: Matthias van de Meent <[email protected]>; +Cc: Nathan Bossart <[email protected]>; Tom Lane <[email protected]>; pgsql-hackers
On Fri, 26 Apr 2024 12:23:45 +0200
Matthias van de Meent <[email protected]> wrote:
> On Fri, 26 Apr 2024 at 10:54, Yugo NAGATA <[email protected]> wrote:
> >
> > On Wed, 24 Apr 2024 16:08:39 -0500
> > Nathan Bossart <[email protected]> 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.
> >
> >
> > Apart from that, extending TOAST to support more than 1GB data and
> > stream-style access seems a good challenge. I don't know if there was a
> > proposal for this in past. This is just a thought, for this purpose, we
> > will need a new type of varlena that can contains large size information,
> > and a new toast table schema that can store offset information or some way
> > to convert a offset to chunk_seq.
>
> If you're interested in this, you may want to check out [0] and [1] as
> threads on the topic of improving TOAST handling of large values ([1]
> being a thread where the limitations of our current external TOAST
> pointer became clear once more), and maybe talk with Aleksander
> Alekseev and Nikita Malakhov. They've been working closely with
> systems that involve toast pointers and their limitations.
>
> The most recent update on the work of Nikita (reworking TOAST
> handling) [2] is that he got started adapting their externally
> pluggable toast into type-internal methods only, though I've not yet
> noticed any updated patches appear on the list.
Thank you for your information. I'll check the threads you mentioned.
> As for other issues with creating larger TOAST values:
> TOAST has a value limit of ~1GB, which means a single large value (or
> two, for that matter) won't break anything in the wire protocol, as
> DataRow messages have a message size field of uint32 [^3]. However, if
> we're going to allow even larger values to be stored in table's
> attributes, we'll have to figure out how we're going to transfer those
> larger values to (and from) clients. For large objects, this is much
> less of an issue because the IO operations are already chunked by
> design, but this may not work well for types that you want to use in
> your table's columns.
I overlooked this issue. I faced the similar issue when I tried to
pg_dump large text values, although the error was raised from
enlargeStringInfo() in that case....
Regards,
Yugo Nagata
> Kind regards,
>
> Matthias van de Meent
>
> [0] https://postgr.es/m/flat/CAN-LCVMq2X%3Dfhx7KLxfeDyb3P%2BBXuCkHC0g%3D9GF%2BJD4izfVa0Q%40mail.gmail.co...
> [1] https://postgr.es/m/flat/CAJ7c6TOtAB0z1UrksvGTStNE-herK-43bj22%3D5xVBg7S4vr5rQ%40mail.gmail.com
> [2] https://postgr.es/m/CAN-LCVOgMrda9hOdzGkCMdwY6dH0JQa13QvPsqUwY57TEn6jww%40mail.gmail.com
>
> [^3] Most, if not all PostgreSQL wire protocol messages have this
> uint32 message size field, but the DataRow one is relevant here as
> it's the one way users get their data out of the database.
--
Yugo NAGATA <[email protected]>
^ permalink raw reply [nested|flat] 8+ messages in thread
* [PATCH v2 1/1] Add list of major features to the v19 release notes.
@ 2026-07-01 21:33 Nathan Bossart <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: Nathan Bossart @ 2026-07-01 21:33 UTC (permalink / raw)
---
doc/src/sgml/release-19.sgml | 79 +++++++++++++++++++++++++++++++++++-
1 file changed, 78 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/release-19.sgml b/doc/src/sgml/release-19.sgml
index d8d3758d5ba..7065a0be549 100644
--- a/doc/src/sgml/release-19.sgml
+++ b/doc/src/sgml/release-19.sgml
@@ -19,7 +19,84 @@
<itemizedlist>
<listitem>
- <para><emphasis>fill in later</emphasis></para>
+ <para>
+ Support for
+ <link linkend="ddl-property-graphs">property graph queries</link>
+ (<acronym>SQL/PGQ</acronym>).
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ A new <link linkend="sql-repack"><command>REPACK</command></link> command
+ that reclaims disk space and reorganizes table contents, combining the
+ functionality of the existing <command>VACUUM FULL</command> and
+ <command>CLUSTER</command> commands. Its <literal>CONCURRENTLY</literal>
+ option allows repacking without blocking reads and writes to the table.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Logical replication now
+ <link linkend="logical-replication-sequences">replicates sequence values</link>
+ and can be enabled without a server restart when
+ <xref linkend="guc-wal-level"/> is set to <literal>replica</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Autovacuum can now use
+ <link linkend="guc-autovacuum-max-parallel-workers">multiple worker processes</link>
+ to vacuum a single table in parallel, and a
+ <link linkend="autovacuum-priority">new scoring system</link> prioritizes
+ the tables that most need vacuuming or analyzing.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Data checksums can now be
+ <link linkend="checksums-online-enable-disable">enabled or disabled while the database server is running</link>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ A new <link linkend="sql-wait-for"><command>WAIT FOR</command></link>
+ command that lets an application pause until a standby has replayed
+ changes up to a chosen point, thereby supporting
+ <quote>read-your-writes</quote> query patterns on standbys.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Support for temporal updates and deletes via the new
+ <link linkend="dml-application-time-update-delete"><literal>FOR PORTION OF</literal></link>
+ clause.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ A new
+ <link linkend="pgplanadvice"><application>pg_plan_advice</application></link>
+ extension for stabilizing and controlling the query planner's decisions,
+ together with the companion
+ <link linkend="pgstashadvice"><application>pg_stash_advice</application></link>
+ extension that applies this advice automatically based on the query.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Faster performance in many areas, including automatic scaling of the
+ number of <link linkend="guc-io-max-workers">I/O worker processes</link>,
+ quicker foreign-key checks, and further planning and execution
+ optimizations.
+ </para>
</listitem>
</itemizedlist>
--
2.50.1 (Apple Git-155)
--2UFzCKAzbQ/xYRnU--
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2026-07-01 21:33 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-04-24 02:52 Extend ALTER DEFAULT PRIVILEGES for large objects Yugo NAGATA <[email protected]>
2024-04-24 03:47 ` Tom Lane <[email protected]>
2024-04-24 06:32 ` Yugo NAGATA <[email protected]>
2024-04-24 21:08 ` Nathan Bossart <[email protected]>
2024-04-26 08:54 ` Yugo NAGATA <[email protected]>
2024-04-26 10:23 ` Matthias van de Meent <[email protected]>
2024-05-02 09:00 ` Yugo NAGATA <[email protected]>
2026-07-01 21:33 [PATCH v2 1/1] Add list of major features to the v19 release notes. Nathan Bossart <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox