Subject: [PATCH 2/2] Add has_large_object_privilege function

From: Yugo Nagata <[email-masked]>
Date: Tue, 2 Jul 2024 15:12:17 +0900

This function is for checking whether a user has the privilege on a
large object. There are three variations whose arguments are combinations
of large object OID with user name, user OID, or implicit user (current_user).
It returns NULL if not-existing large object id is specified, and false if
non-existing user id is specified, and raises an error if non-existing user
name is specified. These behavior is similar with has_table_privilege.
---
 doc/src/sgml/func.sgml                   |  18 +++
 src/backend/utils/adt/acl.c              | 140 +++++++++++++++++++
 src/include/catalog/pg_proc.dat          |  13 ++
 src/test/regress/expected/privileges.out | 169 +++++++++++++++++++++++
 src/test/regress/sql/privileges.sql      |  44 ++++++
 5 files changed, 384 insertions(+)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index f1f22a1960..e06135ca9a 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -24975,6 +24975,24 @@ SELECT has_function_privilege('joeuser', 'myfunc(int, text)', 'execute');
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>has_large_object_privilege</primary>
+        </indexterm>
+        <function>has_large_object_privilege</function> (
+          <optional> <parameter>user</parameter> <type>name</type> or <type>oid</type>, </optional>
+          <parameter>largeobject</parameter> <type>oid</type>,
+          <parameter>privilege</parameter> <type>text</type> )
+        <returnvalue>boolean</returnvalue>
+       </para>
+       <para>
+        Does user have privilege for large object?
+        Allowable privilege types are
+        <literal>SELECT</literal> and <literal>UPDATE</literal>.
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
diff --git a/src/backend/utils/adt/acl.c b/src/backend/utils/adt/acl.c
index d7b39140b3..87f2b6c212 100644
--- a/src/backend/utils/adt/acl.c
+++ b/src/backend/utils/adt/acl.c
@@ -26,6 +26,7 @@
 #include "catalog/pg_foreign_data_wrapper.h"
 #include "catalog/pg_foreign_server.h"
 #include "catalog/pg_language.h"
+#include "catalog/pg_largeobject.h"
 #include "catalog/pg_namespace.h"
 #include "catalog/pg_proc.h"
 #include "catalog/pg_tablespace.h"
@@ -39,6 +40,7 @@
 #include "lib/bloomfilter.h"
 #include "lib/qunique.h"
 #include "miscadmin.h"
+#include "storage/large_object.h"
 #include "utils/acl.h"
 #include "utils/array.h"
 #include "utils/builtins.h"
@@ -46,6 +48,7 @@
 #include "utils/inval.h"
 #include "utils/lsyscache.h"
 #include "utils/memutils.h"
+#include "utils/snapmgr.h"
 #include "utils/syscache.h"
 #include "utils/varlena.h"
 
@@ -124,6 +127,7 @@ static AclMode convert_tablespace_priv_string(text *priv_type_text);
 static Oid	convert_type_name(text *typename);
 static AclMode convert_type_priv_string(text *priv_type_text);
 static AclMode convert_parameter_priv_string(text *priv_text);
+static AclMode convert_large_object_priv_string(text *priv_text);
 static AclMode convert_role_priv_string(text *priv_type_text);
 static AclResult pg_role_aclcheck(Oid role_oid, Oid roleid, AclMode mode);
 
@@ -4669,6 +4673,142 @@ convert_parameter_priv_string(text *priv_text)
 	return convert_any_priv_string(priv_text, parameter_priv_map);
 }
 
+/*
+ * has_large_objec_privilege variants
+ *		These are all named "has_large_object_privilege" at the SQL level.
+ *		They take various combinations of large object OID with
+ *		user name, user OID, or implicit user = current_user.
+ *
+ *		The result is a boolean value: true if user has been granted
+ *		the indicated privilege or false if not.
+ */
+
+/*
+ * has_large_object_privilege_name_id
+ *		Check user privileges on a large object given
+ *		name username, large object oid, and text priv name.
+ */
+Datum
+has_large_object_privilege_name_id(PG_FUNCTION_ARGS)
+{
+	Name		username = PG_GETARG_NAME(0);
+	Oid			roleid = get_role_oid_or_public(NameStr(*username));
+	Oid			lobjId = PG_GETARG_OID(1);
+	text	   *priv_type_text = PG_GETARG_TEXT_PP(2);
+	AclMode		mode;
+	AclResult	aclresult;
+	Snapshot	snapshot = NULL;
+
+	mode = convert_large_object_priv_string(priv_type_text);
+
+	if (mode & ACL_UPDATE)
+		snapshot = NULL;
+	else
+		snapshot = GetActiveSnapshot();
+
+	if (!LargeObjectExistsWithSnapshot(lobjId, snapshot))
+		PG_RETURN_NULL();
+
+	if (lo_compat_privileges)
+		PG_RETURN_BOOL(true);
+
+	aclresult = pg_largeobject_aclcheck_snapshot(lobjId,
+												 roleid,
+												 mode,
+												 snapshot);
+
+	PG_RETURN_BOOL(aclresult == ACLCHECK_OK);
+}
+
+/*
+okui chiba * has_large_object_privilege_id
+ *		Check user privileges on a large object given
+ *		large object oid, and text priv name.
+ *		current_user is assumed
+ */
+Datum
+has_large_object_privilege_id(PG_FUNCTION_ARGS)
+{
+	Oid			lobjId = PG_GETARG_OID(0);
+	Oid			roleid = GetUserId();
+	text	   *priv_type_text = PG_GETARG_TEXT_PP(1);
+	AclMode		mode;
+	AclResult	aclresult;
+	Snapshot	snapshot = NULL;
+
+	mode = convert_large_object_priv_string(priv_type_text);
+
+	if (mode & ACL_UPDATE)
+		snapshot = NULL;
+	else
+		snapshot = GetActiveSnapshot();
+
+	if (!LargeObjectExistsWithSnapshot(lobjId, snapshot))
+		PG_RETURN_NULL();
+
+	if (lo_compat_privileges)
+		PG_RETURN_BOOL(true);
+
+	aclresult = pg_largeobject_aclcheck_snapshot(lobjId,
+												 roleid,
+												 mode,
+												 snapshot);
+
+	PG_RETURN_BOOL(aclresult == ACLCHECK_OK);
+}
+
+/*
+ * has_large_object_privilege_id_id
+ *		Check user privileges on a large object given
+ *		roleid, large object oid, and text priv name.
+ */
+Datum
+has_large_object_privilege_id_id(PG_FUNCTION_ARGS)
+{
+	Oid			roleid = PG_GETARG_OID(0);
+	Oid			lobjId = PG_GETARG_OID(1);
+	text	   *priv_type_text = PG_GETARG_TEXT_PP(2);
+	AclMode		mode;
+	AclResult	aclresult;
+	Snapshot	snapshot = NULL;
+
+	mode = convert_large_object_priv_string(priv_type_text);
+
+	if (mode & ACL_UPDATE)
+		snapshot = NULL;
+	else
+		snapshot = GetActiveSnapshot();
+
+	if (!LargeObjectExistsWithSnapshot(lobjId, snapshot))
+		PG_RETURN_NULL();
+
+	if (lo_compat_privileges)
+		PG_RETURN_BOOL(true);
+
+	aclresult = pg_largeobject_aclcheck_snapshot(lobjId,
+												 roleid,
+												 mode,
+												 snapshot);
+
+	PG_RETURN_BOOL(aclresult == ACLCHECK_OK);
+}
+
+/*
+ * convert_large_object_priv_string
+ *		Convert text string to AclMode value.
+ */
+static AclMode
+convert_large_object_priv_string(text *priv_text)
+{
+	static const priv_map parameter_priv_map[] = {
+		{"SELECT", ACL_SELECT},
+		{"UPDATE", ACL_UPDATE},
+		{NULL, 0}
+	};
+
+	return convert_any_priv_string(priv_text, parameter_priv_map);
+}
+
 /*
  * pg_has_role variants
  *		These are all named "pg_has_role" at the SQL level.
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index d4ac578ae6..5b0ef8df68 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5300,6 +5300,19 @@
   prorettype => 'bool', proargtypes => 'oid text',
   prosrc => 'has_any_column_privilege_id' },
 
+{ oid => '4551', descr => 'user privilege on large objct by username, large object oid',
+  proname => 'has_large_object_privilege', procost => '10', provolatile => 's',
+  prorettype => 'bool', proargtypes => 'name oid text',
+  prosrc => 'has_large_object_privilege_name_id' },
+{ oid => '4552', descr => 'current privilege on large objct by large object oid',
+  proname => 'has_large_object_privilege', procost => '10', provolatile => 's',
+  prorettype => 'bool', proargtypes => 'oid text',
+  prosrc => 'has_large_object_privilege_id' },
+{ oid => '4553', descr => 'user privilege on large objct by user oid, large object oid',
+  proname => 'has_large_object_privilege', procost => '10', provolatile => 's',
+  prorettype => 'bool', proargtypes => 'oid oid text',
+  prosrc => 'has_large_object_privilege_id_id' },
+
 { oid => '3355', descr => 'I/O',
   proname => 'pg_ndistinct_in', prorettype => 'pg_ndistinct',
   proargtypes => 'cstring', prosrc => 'pg_ndistinct_in' },
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index eb4b762ea1..7933601590 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -2024,10 +2024,167 @@ SELECT lo_truncate(lo_open(2001, x'20000'::int), 10);
            0
 (1 row)
 
+-- has_large_object_privilege function
+-- superuser
+\c -
+SELECT has_large_object_privilege(1001, 'SELECT');
+ has_large_object_privilege 
+----------------------------
+ t
+(1 row)
+
+SELECT has_large_object_privilege(1002, 'SELECT');
+ has_large_object_privilege 
+----------------------------
+ t
+(1 row)
+
+SELECT has_large_object_privilege(1003, 'SELECT');
+ has_large_object_privilege 
+----------------------------
+ t
+(1 row)
+
+SELECT has_large_object_privilege(1004, 'SELECT');
+ has_large_object_privilege 
+----------------------------
+ t
+(1 row)
+
+SELECT has_large_object_privilege(1001, 'UPDATE');
+ has_large_object_privilege 
+----------------------------
+ t
+(1 row)
+
+SELECT has_large_object_privilege(1002, 'UPDATE');
+ has_large_object_privilege 
+----------------------------
+ t
+(1 row)
+
+SELECT has_large_object_privilege(1003, 'UPDATE');
+ has_large_object_privilege 
+----------------------------
+ t
+(1 row)
+
+SELECT has_large_object_privilege(1004, 'UPDATE');
+ has_large_object_privilege 
+----------------------------
+ t
+(1 row)
+
+-- not-existing large object
+SELECT has_large_object_privilege(9999, 'SELECT');	-- NULL
+ has_large_object_privilege 
+----------------------------
+ 
+(1 row)
+
+-- not-existing user
+SELECT has_large_object_privilege(-99999, 1001, 'SELECT');	-- false
+ has_large_object_privilege 
+----------------------------
+ t
+(1 row)
+
+-- non-superuser
+SET SESSION AUTHORIZATION regress_priv_user2;
+SELECT has_large_object_privilege(1001, 'SELECT');
+ has_large_object_privilege 
+----------------------------
+ t
+(1 row)
+
+SELECT has_large_object_privilege(1002, 'SELECT');	-- false
+ has_large_object_privilege 
+----------------------------
+ f
+(1 row)
+
+SELECT has_large_object_privilege(1003, 'SELECT');
+ has_large_object_privilege 
+----------------------------
+ t
+(1 row)
+
+SELECT has_large_object_privilege(1004, 'SELECT');
+ has_large_object_privilege 
+----------------------------
+ t
+(1 row)
+
+SELECT has_large_object_privilege(1001, 'UPDATE');
+ has_large_object_privilege 
+----------------------------
+ t
+(1 row)
+
+SELECT has_large_object_privilege(1002, 'UPDATE');	-- false
+ has_large_object_privilege 
+----------------------------
+ f
+(1 row)
+
+SELECT has_large_object_privilege(1003, 'UPDATE');	-- false
+ has_large_object_privilege 
+----------------------------
+ f
+(1 row)
+
+SELECT has_large_object_privilege(1004, 'UPDATE');
+ has_large_object_privilege 
+----------------------------
+ t
+(1 row)
+
+SELECT has_large_object_privilege('regress_priv_user3', 1001, 'SELECT');
+ has_large_object_privilege 
+----------------------------
+ t
+(1 row)
+
+SELECT has_large_object_privilege('regress_priv_user3', 1003, 'SELECT');	-- false
+ has_large_object_privilege 
+----------------------------
+ f
+(1 row)
+
+SELECT has_large_object_privilege('regress_priv_user3', 1005, 'SELECT');
+ has_large_object_privilege 
+----------------------------
+ t
+(1 row)
+
+SELECT has_large_object_privilege('regress_priv_user3', 1005, 'UPDATE');	-- false
+ has_large_object_privilege 
+----------------------------
+ f
+(1 row)
+
+SELECT has_large_object_privilege('regress_priv_user3', 2001, 'UPDATE');
+ has_large_object_privilege 
+----------------------------
+ t
+(1 row)
+
 -- compatibility mode in largeobject permission
 \c -
 SET lo_compat_privileges = false;	-- default setting
 SET SESSION AUTHORIZATION regress_priv_user4;
+SELECT has_large_object_privilege(1002, 'SELECT'); -- false
+ has_large_object_privilege 
+----------------------------
+ f
+(1 row)
+
+SELECT has_large_object_privilege(1002, 'UPDATE'); -- false
+ has_large_object_privilege 
+----------------------------
+ f
+(1 row)
+
 SELECT loread(lo_open(1002, x'40000'::int), 32);	-- to be denied
 ERROR:  permission denied for large object 1002
 SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd');	-- to be denied
@@ -2047,6 +2204,18 @@ ERROR:  permission denied for function lo_import
 \c -
 SET lo_compat_privileges = true;	-- compatibility mode
 SET SESSION AUTHORIZATION regress_priv_user4;
+SELECT has_large_object_privilege(1002, 'SELECT'); -- true
+ has_large_object_privilege 
+----------------------------
+ t
+(1 row)
+
+SELECT has_large_object_privilege(1002, 'UPDATE'); -- true
+ has_large_object_privilege 
+----------------------------
+ t
+(1 row)
+
 SELECT loread(lo_open(1002, x'40000'::int), 32);
  loread 
 --------
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index eeb4c00292..6b509e993a 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -1318,11 +1318,52 @@ SELECT loread(lo_open(1005, x'40000'::int), 32);
 SELECT lo_truncate(lo_open(1005, x'20000'::int), 10);	-- to be denied
 SELECT lo_truncate(lo_open(2001, x'20000'::int), 10);
 
+-- has_large_object_privilege function
+
+-- superuser
+\c -
+SELECT has_large_object_privilege(1001, 'SELECT');
+SELECT has_large_object_privilege(1002, 'SELECT');
+SELECT has_large_object_privilege(1003, 'SELECT');
+SELECT has_large_object_privilege(1004, 'SELECT');
+
+SELECT has_large_object_privilege(1001, 'UPDATE');
+SELECT has_large_object_privilege(1002, 'UPDATE');
+SELECT has_large_object_privilege(1003, 'UPDATE');
+SELECT has_large_object_privilege(1004, 'UPDATE');
+
+-- not-existing large object
+SELECT has_large_object_privilege(9999, 'SELECT');	-- NULL
+-- not-existing user
+SELECT has_large_object_privilege(-99999, 1001, 'SELECT');	-- false
+
+-- non-superuser
+SET SESSION AUTHORIZATION regress_priv_user2;
+SELECT has_large_object_privilege(1001, 'SELECT');
+SELECT has_large_object_privilege(1002, 'SELECT');	-- false
+SELECT has_large_object_privilege(1003, 'SELECT');
+SELECT has_large_object_privilege(1004, 'SELECT');
+
+SELECT has_large_object_privilege(1001, 'UPDATE');
+SELECT has_large_object_privilege(1002, 'UPDATE');	-- false
+SELECT has_large_object_privilege(1003, 'UPDATE');	-- false
+SELECT has_large_object_privilege(1004, 'UPDATE');
+
+SELECT has_large_object_privilege('regress_priv_user3', 1001, 'SELECT');
+SELECT has_large_object_privilege('regress_priv_user3', 1003, 'SELECT');	-- false
+SELECT has_large_object_privilege('regress_priv_user3', 1005, 'SELECT');
+
+SELECT has_large_object_privilege('regress_priv_user3', 1005, 'UPDATE');	-- false
+SELECT has_large_object_privilege('regress_priv_user3', 2001, 'UPDATE');
+
 -- compatibility mode in largeobject permission
 \c -
 SET lo_compat_privileges = false;	-- default setting
 SET SESSION AUTHORIZATION regress_priv_user4;
 
+SELECT has_large_object_privilege(1002, 'SELECT'); -- false
+SELECT has_large_object_privilege(1002, 'UPDATE'); -- false
+
 SELECT loread(lo_open(1002, x'40000'::int), 32);	-- to be denied
 SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd');	-- to be denied
 SELECT lo_truncate(lo_open(1002, x'20000'::int), 10);	-- to be denied
@@ -1336,6 +1377,9 @@ SELECT lo_import('/dev/null', 2003);			-- to be denied
 SET lo_compat_privileges = true;	-- compatibility mode
 SET SESSION AUTHORIZATION regress_priv_user4;
 
+SELECT has_large_object_privilege(1002, 'SELECT'); -- true
+SELECT has_large_object_privilege(1002, 'UPDATE'); -- true
+
 SELECT loread(lo_open(1002, x'40000'::int), 32);
 SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd');
 SELECT lo_truncate(lo_open(1002, x'20000'::int), 10);
-- 
2.25.1

--Multipart=_Tue__2_Jul_2024_16_34_44_+0900_Lpc86p3xobkG.QGS
Content-Type: text/x-diff;
 name="0001-Deduplicate-codes-of-LargeObjectExists-and-mvLargeOb.patch"
Content-Disposition: attachment;
 filename="0001-Deduplicate-codes-of-LargeObjectExists-and-mvLargeOb.patch"
Content-Transfer-Encoding: 7bit