That this family of functions did not exist earlier was merely an
oversight.
Signed-off-by: Abhijit Menon-Sen <a...@oryx.com>
---
doc/src/sgml/func.sgml | 28 ++++
src/backend/utils/adt/acl.c | 210 ++++++++++++++++++++++++++++++
src/include/catalog/pg_proc.h | 13 ++
src/include/utils/builtins.h | 6 +
src/test/regress/expected/privileges.out | 22 +++
src/test/regress/sql/privileges.sql | 17 +++
6 files changed, 296 insertions(+), 0 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 58a08f3..7e9a9d8 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -11727,6 +11727,21 @@ SET search_path TO <replaceable>schema</> <optional>, <replaceable>schema</>, ..
<entry>does current user have privilege for foreign server</entry>
</row>
<row>
+ <entry><literal><function>has_sequence_privilege</function>(<parameter>user</parameter>,
+ <parameter>sequence</parameter>,
+ <parameter>privilege</parameter>)</literal>
+ </entry>
+ <entry><type>boolean</type></entry>
+ <entry>does user have privilege for sequence</entry>
+ </row>
+ <row>
+ <entry><literal><function>has_sequence_privilege</function>(<parameter>sequence</parameter>,
+ <parameter>privilege</parameter>)</literal>
+ </entry>
+ <entry><type>boolean</type></entry>
+ <entry>does current user have privilege for sequence</entry>
+ </row>
+ <row>
<entry><literal><function>has_table_privilege</function>(<parameter>user</parameter>,
<parameter>table</parameter>,
<parameter>privilege</parameter>)</literal>
@@ -11800,6 +11815,9 @@ SET search_path TO <replaceable>schema</> <optional>, <replaceable>schema</>, ..
<primary>has_server_privilege</primary>
</indexterm>
<indexterm>
+ <primary>has_sequence_privilege</primary>
+ </indexterm>
+ <indexterm>
<primary>has_table_privilege</primary>
</indexterm>
<indexterm>
@@ -11839,6 +11857,16 @@ SELECT has_table_privilege('joe', 'mytable', 'INSERT, SELECT WITH GRANT OPTION')
</para>
<para>
+ <function>has_sequence_privilege</function> checks whether a user
+ can access a sequence in a particular way. The possibilities for its
+ arguments are analogous to <function>has_table_privilege</function>.
+ The desired access privilege type must evaluate to one of
+ <literal>USAGE</literal>,
+ <literal>SELECT</literal>, or
+ <literal>UPDATE</literal>.
+ </para>
+
+ <para>
<function>has_any_column_privilege</function> checks whether a user can
access any column of a table in a particular way. The possibilities for
its arguments are the same as for <function>has_table_privilege</>,
diff --git a/src/backend/utils/adt/acl.c b/src/backend/utils/adt/acl.c
index 06c8550..0129c81 100644
--- a/src/backend/utils/adt/acl.c
+++ b/src/backend/utils/adt/acl.c
@@ -20,6 +20,7 @@
#include "catalog/pg_authid.h"
#include "catalog/pg_auth_members.h"
#include "catalog/pg_type.h"
+#include "catalog/pg_class.h"
#include "commands/dbcommands.h"
#include "commands/tablespace.h"
#include "foreign/foreign.h"
@@ -88,6 +89,8 @@ static AclMode convert_any_priv_string(text *priv_type_text,
static Oid convert_table_name(text *tablename);
static AclMode convert_table_priv_string(text *priv_type_text);
+static bool verify_sequence_oid(Oid sequenceoid);
+static AclMode convert_sequence_priv_string(text *priv_type_text);
static AttrNumber convert_column_name(Oid tableoid, text *column);
static AclMode convert_column_priv_string(text *priv_type_text);
static Oid convert_database_name(text *databasename);
@@ -1705,6 +1708,213 @@ convert_table_priv_string(text *priv_type_text)
}
+static bool
+verify_sequence_oid(Oid sequenceoid)
+{
+ HeapTuple tuple;
+ Form_pg_class pg_class_tuple;
+
+ tuple = SearchSysCache(RELOID, ObjectIdGetDatum(sequenceoid), 0, 0, 0);
+ if (!HeapTupleIsValid(tuple))
+ return false;
+ pg_class_tuple = (Form_pg_class) GETSTRUCT(tuple);
+ ReleaseSysCache(tuple);
+
+ if (pg_class_tuple->relkind != RELKIND_SEQUENCE)
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("\"%s\" is not a sequence",
+ NameStr(pg_class_tuple->relname))));
+ return false;
+ }
+
+ return true;
+}
+
+
+/*
+ * has_sequence_privilege variants
+ * These are all named "has_sequence_privilege" at the SQL level.
+ * They take various combinations of relation name, relation OID,
+ * user name, user OID, or implicit user = current_user.
+ *
+ * The result is a boolean value: true if user has the indicated
+ * privilege, false if not. The variants that take a relation OID
+ * return NULL if the OID doesn't exist (rather than failing, as
+ * they did before Postgres 8.4).
+ */
+
+/*
+ * has_sequence_privilege_name_name
+ * Check user privileges on a sequence given
+ * name username, text sequencename, and text priv name.
+ */
+Datum
+has_sequence_privilege_name_name(PG_FUNCTION_ARGS)
+{
+ Name rolename = PG_GETARG_NAME(0);
+ text *sequencename = PG_GETARG_TEXT_P(1);
+ text *priv_type_text = PG_GETARG_TEXT_P(2);
+ Oid roleid;
+ Oid sequenceoid;
+ AclMode mode;
+ AclResult aclresult;
+
+ roleid = get_roleid_checked(NameStr(*rolename));
+ mode = convert_sequence_priv_string(priv_type_text);
+ sequenceoid = convert_table_name(sequencename);
+ if (!verify_sequence_oid(sequenceoid))
+ PG_RETURN_NULL();
+
+ aclresult = pg_class_aclcheck(sequenceoid, roleid, mode);
+
+ PG_RETURN_BOOL(aclresult == ACLCHECK_OK);
+}
+
+/*
+ * has_sequence_privilege_name
+ * Check user privileges on a sequence given
+ * text sequencename and text priv name.
+ * current_user is assumed
+ */
+Datum
+has_sequence_privilege_name(PG_FUNCTION_ARGS)
+{
+ text *sequencename = PG_GETARG_TEXT_P(0);
+ text *priv_type_text = PG_GETARG_TEXT_P(1);
+ Oid roleid;
+ Oid sequenceoid;
+ AclMode mode;
+ AclResult aclresult;
+
+ roleid = GetUserId();
+ mode = convert_sequence_priv_string(priv_type_text);
+ sequenceoid = convert_table_name(sequencename);
+ if (!verify_sequence_oid(sequenceoid))
+ PG_RETURN_NULL();
+
+ aclresult = pg_class_aclcheck(sequenceoid, roleid, mode);
+
+ PG_RETURN_BOOL(aclresult == ACLCHECK_OK);
+}
+
+/*
+ * has_sequence_privilege_name_id
+ * Check user privileges on a sequence given
+ * name usename, sequence oid, and text priv name.
+ */
+Datum
+has_sequence_privilege_name_id(PG_FUNCTION_ARGS)
+{
+ Name username = PG_GETARG_NAME(0);
+ Oid sequenceoid = PG_GETARG_OID(1);
+ text *priv_type_text = PG_GETARG_TEXT_P(2);
+ Oid roleid;
+ AclMode mode;
+ AclResult aclresult;
+
+ roleid = get_roleid_checked(NameStr(*username));
+ mode = convert_sequence_priv_string(priv_type_text);
+ if (!verify_sequence_oid(sequenceoid))
+ PG_RETURN_NULL();
+
+ aclresult = pg_class_aclcheck(sequenceoid, roleid, mode);
+
+ PG_RETURN_BOOL(aclresult == ACLCHECK_OK);
+}
+
+/*
+ * has_sequence_privilege_id
+ * Check user privileges on a sequence given
+ * sequence oid, and text priv name.
+ * current_user is assumed
+ */
+Datum
+has_sequence_privilege_id(PG_FUNCTION_ARGS)
+{
+ Oid sequenceoid = PG_GETARG_OID(0);
+ text *priv_type_text = PG_GETARG_TEXT_P(1);
+ Oid roleid;
+ AclMode mode;
+ AclResult aclresult;
+
+ roleid = GetUserId();
+ mode = convert_sequence_priv_string(priv_type_text);
+ if (!verify_sequence_oid(sequenceoid))
+ PG_RETURN_NULL();
+
+ aclresult = pg_class_aclcheck(sequenceoid, roleid, mode);
+
+ PG_RETURN_BOOL(aclresult == ACLCHECK_OK);
+}
+
+/*
+ * has_sequence_privilege_id_name
+ * Check user privileges on a sequence given
+ * roleid, text sequencename, and text priv name.
+ */
+Datum
+has_sequence_privilege_id_name(PG_FUNCTION_ARGS)
+{
+ Oid roleid = PG_GETARG_OID(0);
+ text *sequencename = PG_GETARG_TEXT_P(1);
+ text *priv_type_text = PG_GETARG_TEXT_P(2);
+ Oid sequenceoid;
+ AclMode mode;
+ AclResult aclresult;
+
+ mode = convert_sequence_priv_string(priv_type_text);
+ sequenceoid = convert_table_name(sequencename);
+ if (!verify_sequence_oid(sequenceoid))
+ PG_RETURN_NULL();
+
+ aclresult = pg_class_aclcheck(sequenceoid, roleid, mode);
+
+ PG_RETURN_BOOL(aclresult == ACLCHECK_OK);
+}
+
+/*
+ * has_sequence_privilege_id_id
+ * Check user privileges on a sequence given
+ * roleid, sequence oid, and text priv name.
+ */
+Datum
+has_sequence_privilege_id_id(PG_FUNCTION_ARGS)
+{
+ Oid roleid = PG_GETARG_OID(0);
+ Oid sequenceoid = PG_GETARG_OID(1);
+ text *priv_type_text = PG_GETARG_TEXT_P(2);
+ AclMode mode;
+ AclResult aclresult;
+
+ mode = convert_sequence_priv_string(priv_type_text);
+ if (!verify_sequence_oid(sequenceoid))
+ PG_RETURN_NULL();
+
+ aclresult = pg_class_aclcheck(sequenceoid, roleid, mode);
+
+ PG_RETURN_BOOL(aclresult == ACLCHECK_OK);
+}
+
+/*
+ * convert_sequence_priv_string
+ * Convert text string to AclMode value.
+ */
+static AclMode
+convert_sequence_priv_string(text *priv_type_text)
+{
+ static const priv_map sequence_priv_map[] = {
+ { "USAGE", ACL_USAGE },
+ { "SELECT", ACL_SELECT },
+ { "UPDATE", ACL_UPDATE },
+ { NULL, 0 }
+ };
+
+ return convert_any_priv_string(priv_type_text, sequence_priv_map);
+}
+
+
/*
* has_any_column_privilege variants
* These are all named "has_any_column_privilege" at the SQL level.
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 61d24c8..f004796 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -2913,6 +2913,19 @@ DESCR("current user privilege on relation by rel name");
DATA(insert OID = 1927 ( has_table_privilege PGNSP PGUID 12 1 0 0 f f f t f s 2 0 16 "26 25" _null_ _null_ _null_ _null_ has_table_privilege_id _null_ _null_ _null_ ));
DESCR("current user privilege on relation by rel oid");
+DATA(insert OID = 2181 ( has_sequence_privilege PGNSP PGUID 12 1 0 0 f f f t f s 3 0 16 "19 25 25" _null_ _null_ _null_ _null_ has_sequence_privilege_name_name _null_ _null_ _null_ ));
+DESCR("user privilege on sequence by username, seq name");
+DATA(insert OID = 2182 ( has_sequence_privilege PGNSP PGUID 12 1 0 0 f f f t f s 3 0 16 "19 26 25" _null_ _null_ _null_ _null_ has_sequence_privilege_name_id _null_ _null_ _null_ ));
+DESCR("user privilege on sequence by username, seq oid");
+DATA(insert OID = 2183 ( has_sequence_privilege PGNSP PGUID 12 1 0 0 f f f t f s 3 0 16 "26 25 25" _null_ _null_ _null_ _null_ has_sequence_privilege_id_name _null_ _null_ _null_ ));
+DESCR("user privilege on sequence by user oid, seq name");
+DATA(insert OID = 2184 ( has_sequence_privilege PGNSP PGUID 12 1 0 0 f f f t f s 3 0 16 "26 26 25" _null_ _null_ _null_ _null_ has_sequence_privilege_id_id _null_ _null_ _null_ ));
+DESCR("user privilege on sequence by user oid, seq oid");
+DATA(insert OID = 2185 ( has_sequence_privilege PGNSP PGUID 12 1 0 0 f f f t f s 2 0 16 "25 25" _null_ _null_ _null_ _null_ has_sequence_privilege_name _null_ _null_ _null_ ));
+DESCR("current user privilege on sequence by seq name");
+DATA(insert OID = 2186 ( has_sequence_privilege PGNSP PGUID 12 1 0 0 f f f t f s 2 0 16 "26 25" _null_ _null_ _null_ _null_ has_sequence_privilege_id _null_ _null_ _null_ ));
+DESCR("current user privilege on sequence by seq oid");
+
DATA(insert OID = 3012 ( has_column_privilege PGNSP PGUID 12 1 0 0 f f f t f s 4 0 16 "19 25 25 25" _null_ _null_ _null_ _null_ has_column_privilege_name_name_name _null_ _null_ _null_ ));
DESCR("user privilege on column by username, rel name, col name");
DATA(insert OID = 3013 ( has_column_privilege PGNSP PGUID 12 1 0 0 f f f t f s 4 0 16 "19 25 21 25" _null_ _null_ _null_ _null_ has_column_privilege_name_name_attnum _null_ _null_ _null_ ));
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index c1b9393..20127b8 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -46,6 +46,12 @@ extern Datum has_table_privilege_id_name(PG_FUNCTION_ARGS);
extern Datum has_table_privilege_id_id(PG_FUNCTION_ARGS);
extern Datum has_table_privilege_name(PG_FUNCTION_ARGS);
extern Datum has_table_privilege_id(PG_FUNCTION_ARGS);
+extern Datum has_sequence_privilege_name_name(PG_FUNCTION_ARGS);
+extern Datum has_sequence_privilege_name_id(PG_FUNCTION_ARGS);
+extern Datum has_sequence_privilege_id_name(PG_FUNCTION_ARGS);
+extern Datum has_sequence_privilege_id_id(PG_FUNCTION_ARGS);
+extern Datum has_sequence_privilege_name(PG_FUNCTION_ARGS);
+extern Datum has_sequence_privilege_id(PG_FUNCTION_ARGS);
extern Datum has_database_privilege_name_name(PG_FUNCTION_ARGS);
extern Datum has_database_privilege_name_id(PG_FUNCTION_ARGS);
extern Datum has_database_privilege_id_name(PG_FUNCTION_ARGS);
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index a17ff59..809b656 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -815,8 +815,30 @@ SELECT has_table_privilege('regressuser1', 'atest4', 'SELECT WITH GRANT OPTION')
t
(1 row)
+-- has_sequence_privilege tests
+\c -
+CREATE SEQUENCE x_seq;
+GRANT USAGE on x_seq to regressuser2;
+SELECT has_sequence_privilege('regressuser1', 'atest1', 'SELECT');
+ERROR: "atest1" is not a sequence
+SELECT has_sequence_privilege('regressuser1', 'x_seq', 'INSERT');
+ERROR: unrecognized privilege type: "INSERT"
+SELECT has_sequence_privilege('regressuser1', 'x_seq', 'SELECT');
+ has_sequence_privilege
+------------------------
+ f
+(1 row)
+
+SET SESSION AUTHORIZATION regressuser2;
+SELECT has_sequence_privilege('x_seq', 'USAGE');
+ has_sequence_privilege
+------------------------
+ t
+(1 row)
+
-- clean up
\c
+drop sequence x_seq;
DROP FUNCTION testfunc2(int);
DROP FUNCTION testfunc4(boolean);
DROP VIEW atestv1;
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index 5aa1012..917e8e5 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -469,10 +469,27 @@ SELECT has_table_privilege('regressuser3', 'atest4', 'SELECT'); -- false
SELECT has_table_privilege('regressuser1', 'atest4', 'SELECT WITH GRANT OPTION'); -- true
+-- has_sequence_privilege tests
+\c -
+
+CREATE SEQUENCE x_seq;
+
+GRANT USAGE on x_seq to regressuser2;
+
+SELECT has_sequence_privilege('regressuser1', 'atest1', 'SELECT');
+SELECT has_sequence_privilege('regressuser1', 'x_seq', 'INSERT');
+SELECT has_sequence_privilege('regressuser1', 'x_seq', 'SELECT');
+
+SET SESSION AUTHORIZATION regressuser2;
+
+SELECT has_sequence_privilege('x_seq', 'USAGE');
+
-- clean up
\c
+drop sequence x_seq;
+
DROP FUNCTION testfunc2(int);
DROP FUNCTION testfunc4(boolean);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
