agora inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feedFrom: Yugo Nagata <nagata@sraoss.co.jp>
Subject: [PATCH 2/2] Add has_large_object_privilege function
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
view thread (4+ messages)
Message-ID: <no-message-id-1839292@localhost>
Permalink: ../../no-message-id-1839292@localhost/
Also on: postgresql.org/message-id/no-message-id-1839292@localhost
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: nagata@sraoss.co.jp
Subject: Re: [PATCH 2/2] Add has_large_object_privilege function
In-Reply-To: <no-message-id-1839292@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox