On 2020-06-24 20:21, Peter Eisentraut wrote:
On 2020-06-24 10:12, Vik Fearing wrote:
On 6/24/20 8:35 AM, Peter Eisentraut wrote:
I was checking some loose ends in SQL conformance, when I noticed: We
support GRANT role ... GRANTED BY CURRENT_USER, but we don't support
CURRENT_ROLE in that place, even though in PostgreSQL they are
equivalent. Here is a trivial patch to add that.
The only thing that isn't dead-obvious about this patch is the commit
message says "[PATCH 1/2]". What is in the other part?
Hehe. The second patch is some in-progress work to add the GRANTED BY
clause to the regular GRANT command. More on that perhaps at a later date.
Here is the highly anticipated and quite underwhelming second part of
this patch set.
--
Peter Eisentraut
2ndQuadrant, an EDB company
https://www.2ndquadrant.com/
From 72b1e99337b6fc9e72bcca003eb2d18351079261 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Thu, 10 Dec 2020 19:38:21 +0100
Subject: [PATCH] Allow GRANTED BY clause in normal GRANT and REVOKE statements
The SQL standard allows a GRANTED BY clause on GRANT and
REVOKE (privilege) statements that can specify CURRENT_USER or
CURRENT_ROLE. In PostgreSQL, both of these are the default behavior.
Since we already have all the parsing support for this for the
GRANT (role) statement, we might as well add basic support for this
for the privilege variant as well. This allows us to check of SQL
feature T332. In the future, perhaps more interesting things could be
done with this, too.
---
doc/src/sgml/ref/grant.sgml | 25 ++++++++++++++++++++++---
doc/src/sgml/ref/revoke.sgml | 13 +++++++++++++
src/backend/catalog/aclchk.c | 16 ++++++++++++++++
src/backend/catalog/sql_features.txt | 2 +-
src/backend/nodes/copyfuncs.c | 1 +
src/backend/nodes/equalfuncs.c | 1 +
src/backend/parser/gram.y | 13 ++++++++-----
src/include/nodes/parsenodes.h | 1 +
8 files changed, 63 insertions(+), 9 deletions(-)
diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml
index c3db393bde..a897712de2 100644
--- a/doc/src/sgml/ref/grant.sgml
+++ b/doc/src/sgml/ref/grant.sgml
@@ -26,58 +26,71 @@
ON { [ TABLE ] <replaceable class="parameter">table_name</replaceable> [,
...]
| ALL TABLES IN SCHEMA <replaceable
class="parameter">schema_name</replaceable> [, ...] }
TO <replaceable class="parameter">role_specification</replaceable> [, ...]
[ WITH GRANT OPTION ]
+ [ GRANTED BY <replaceable
class="parameter">role_specification</replaceable> ]
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( <replaceable
class="parameter">column_name</replaceable> [, ...] )
[, ...] | ALL [ PRIVILEGES ] ( <replaceable
class="parameter">column_name</replaceable> [, ...] ) }
ON [ TABLE ] <replaceable class="parameter">table_name</replaceable> [,
...]
TO <replaceable class="parameter">role_specification</replaceable> [, ...]
[ WITH GRANT OPTION ]
+ [ GRANTED BY <replaceable
class="parameter">role_specification</replaceable> ]
GRANT { { USAGE | SELECT | UPDATE }
[, ...] | ALL [ PRIVILEGES ] }
ON { SEQUENCE <replaceable class="parameter">sequence_name</replaceable>
[, ...]
| ALL SEQUENCES IN SCHEMA <replaceable
class="parameter">schema_name</replaceable> [, ...] }
TO <replaceable class="parameter">role_specification</replaceable> [, ...]
[ WITH GRANT OPTION ]
+ [ GRANTED BY <replaceable
class="parameter">role_specification</replaceable> ]
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
ON DATABASE <replaceable>database_name</replaceable> [, ...]
TO <replaceable class="parameter">role_specification</replaceable> [, ...]
[ WITH GRANT OPTION ]
+ [ GRANTED BY <replaceable
class="parameter">role_specification</replaceable> ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON DOMAIN <replaceable>domain_name</replaceable> [, ...]
TO <replaceable class="parameter">role_specification</replaceable> [, ...]
[ WITH GRANT OPTION ]
+ [ GRANTED BY <replaceable
class="parameter">role_specification</replaceable> ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN DATA WRAPPER <replaceable>fdw_name</replaceable> [, ...]
TO <replaceable class="parameter">role_specification</replaceable> [, ...]
[ WITH GRANT OPTION ]
+ [ GRANTED BY <replaceable
class="parameter">role_specification</replaceable> ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN SERVER <replaceable>server_name</replaceable> [, ...]
TO <replaceable class="parameter">role_specification</replaceable> [, ...]
[ WITH GRANT OPTION ]
+ [ GRANTED BY <replaceable
class="parameter">role_specification</replaceable> ]
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
ON { { FUNCTION | PROCEDURE | ROUTINE }
<replaceable>routine_name</replaceable> [ ( [ [ <replaceable
class="parameter">argmode</replaceable> ] [ <replaceable
class="parameter">arg_name</replaceable> ] <replaceable
class="parameter">arg_type</replaceable> [, ...] ] ) ] [, ...]
| ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA <replaceable
class="parameter">schema_name</replaceable> [, ...] }
TO <replaceable class="parameter">role_specification</replaceable> [, ...]
[ WITH GRANT OPTION ]
+ [ GRANTED BY <replaceable
class="parameter">role_specification</replaceable> ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON LANGUAGE <replaceable>lang_name</replaceable> [, ...]
TO <replaceable class="parameter">role_specification</replaceable> [, ...]
[ WITH GRANT OPTION ]
+ [ GRANTED BY <replaceable
class="parameter">role_specification</replaceable> ]
GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
ON LARGE OBJECT <replaceable class="parameter">loid</replaceable> [, ...]
TO <replaceable class="parameter">role_specification</replaceable> [, ...]
[ WITH GRANT OPTION ]
+ [ GRANTED BY <replaceable
class="parameter">role_specification</replaceable> ]
GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
ON SCHEMA <replaceable>schema_name</replaceable> [, ...]
TO <replaceable class="parameter">role_specification</replaceable> [, ...]
[ WITH GRANT OPTION ]
+ [ GRANTED BY <replaceable
class="parameter">role_specification</replaceable> ]
GRANT { CREATE | ALL [ PRIVILEGES ] }
ON TABLESPACE <replaceable>tablespace_name</replaceable> [, ...]
TO <replaceable class="parameter">role_specification</replaceable> [, ...]
[ WITH GRANT OPTION ]
+ [ GRANTED BY <replaceable
class="parameter">role_specification</replaceable> ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON TYPE <replaceable>type_name</replaceable> [, ...]
TO <replaceable class="parameter">role_specification</replaceable> [, ...]
[ WITH GRANT OPTION ]
+ [ GRANTED BY <replaceable
class="parameter">role_specification</replaceable> ]
GRANT <replaceable class="parameter">role_name</replaceable> [, ...] TO
<replaceable class="parameter">role_specification</replaceable> [, ...]
[ WITH ADMIN OPTION ]
@@ -133,6 +146,12 @@ <title>GRANT on Database Objects</title>
to <literal>PUBLIC</literal>.
</para>
+ <para>
+ If <literal>GRANTED BY</literal> is specified, the specified grantor must
+ be the current user. This clause is currently present in this form only
+ for SQL compatibility.
+ </para>
+
<para>
There is no need to grant privileges to the owner of an object
(usually the user that created it),
@@ -410,9 +429,9 @@ <title>Compatibility</title>
<para>
The SQL standard allows the <literal>GRANTED BY</literal> option to
- be used in all forms of <command>GRANT</command>. PostgreSQL only
- supports it when granting role membership, and even then only superusers
- may use it in nontrivial ways.
+ specify only <literal>CURRENT_USER</literal> or
+ <literal>CURRENT_ROLE</literal>. The other variants are PostgreSQL
+ extensions.
</para>
<para>
diff --git a/doc/src/sgml/ref/revoke.sgml b/doc/src/sgml/ref/revoke.sgml
index 35ff87a4f5..3014c864ea 100644
--- a/doc/src/sgml/ref/revoke.sgml
+++ b/doc/src/sgml/ref/revoke.sgml
@@ -27,6 +27,7 @@
ON { [ TABLE ] <replaceable class="parameter">table_name</replaceable> [,
...]
| ALL TABLES IN SCHEMA <replaceable>schema_name</replaceable> [, ...]
}
FROM <replaceable class="parameter">role_specification</replaceable> [,
...]
+ [ GRANTED BY <replaceable
class="parameter">role_specification</replaceable> ]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
@@ -34,6 +35,7 @@
[, ...] | ALL [ PRIVILEGES ] ( <replaceable
class="parameter">column_name</replaceable> [, ...] ) }
ON [ TABLE ] <replaceable class="parameter">table_name</replaceable> [,
...]
FROM <replaceable class="parameter">role_specification</replaceable> [,
...]
+ [ GRANTED BY <replaceable
class="parameter">role_specification</replaceable> ]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
@@ -42,30 +44,35 @@
ON { SEQUENCE <replaceable class="parameter">sequence_name</replaceable>
[, ...]
| ALL SEQUENCES IN SCHEMA <replaceable>schema_name</replaceable> [,
...] }
FROM <replaceable class="parameter">role_specification</replaceable> [,
...]
+ [ GRANTED BY <replaceable
class="parameter">role_specification</replaceable> ]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
ON DATABASE <replaceable>database_name</replaceable> [, ...]
FROM <replaceable class="parameter">role_specification</replaceable> [,
...]
+ [ GRANTED BY <replaceable
class="parameter">role_specification</replaceable> ]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON DOMAIN <replaceable>domain_name</replaceable> [, ...]
FROM <replaceable class="parameter">role_specification</replaceable> [,
...]
+ [ GRANTED BY <replaceable
class="parameter">role_specification</replaceable> ]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN DATA WRAPPER <replaceable>fdw_name</replaceable> [, ...]
FROM <replaceable class="parameter">role_specification</replaceable> [,
...]
+ [ GRANTED BY <replaceable
class="parameter">role_specification</replaceable> ]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN SERVER <replaceable>server_name</replaceable> [, ...]
FROM <replaceable class="parameter">role_specification</replaceable> [,
...]
+ [ GRANTED BY <replaceable
class="parameter">role_specification</replaceable> ]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
@@ -73,36 +80,42 @@
ON { { FUNCTION | PROCEDURE | ROUTINE }
<replaceable>function_name</replaceable> [ ( [ [ <replaceable
class="parameter">argmode</replaceable> ] [ <replaceable
class="parameter">arg_name</replaceable> ] <replaceable
class="parameter">arg_type</replaceable> [, ...] ] ) ] [, ...]
| ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA
<replaceable>schema_name</replaceable> [, ...] }
FROM <replaceable class="parameter">role_specification</replaceable> [,
...]
+ [ GRANTED BY <replaceable
class="parameter">role_specification</replaceable> ]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON LANGUAGE <replaceable>lang_name</replaceable> [, ...]
FROM <replaceable class="parameter">role_specification</replaceable> [,
...]
+ [ GRANTED BY <replaceable
class="parameter">role_specification</replaceable> ]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
ON LARGE OBJECT <replaceable class="parameter">loid</replaceable> [, ...]
FROM <replaceable class="parameter">role_specification</replaceable> [,
...]
+ [ GRANTED BY <replaceable
class="parameter">role_specification</replaceable> ]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
ON SCHEMA <replaceable>schema_name</replaceable> [, ...]
FROM <replaceable class="parameter">role_specification</replaceable> [,
...]
+ [ GRANTED BY <replaceable
class="parameter">role_specification</replaceable> ]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ CREATE | ALL [ PRIVILEGES ] }
ON TABLESPACE <replaceable>tablespace_name</replaceable> [, ...]
FROM <replaceable class="parameter">role_specification</replaceable> [,
...]
+ [ GRANTED BY <replaceable
class="parameter">role_specification</replaceable> ]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON TYPE <replaceable>type_name</replaceable> [, ...]
FROM <replaceable class="parameter">role_specification</replaceable> [,
...]
+ [ GRANTED BY <replaceable
class="parameter">role_specification</replaceable> ]
[ CASCADE | RESTRICT ]
REVOKE [ ADMIN OPTION FOR ]
diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c
index c4594b0b09..1fa8f4d575 100644
--- a/src/backend/catalog/aclchk.c
+++ b/src/backend/catalog/aclchk.c
@@ -363,6 +363,22 @@ ExecuteGrantStmt(GrantStmt *stmt)
const char *errormsg;
AclMode all_privileges;
+ if (stmt->grantor)
+ {
+ Oid grantor;
+
+ grantor = get_rolespec_oid(stmt->grantor, false);
+
+ /*
+ * Currently, this clause is only for SQL compatibility, not
very
+ * interesting otherwise.
+ */
+ if (grantor != GetUserId())
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("grantor must be current
user")));
+ }
+
/*
* Turn the regular GrantStmt into the InternalGrant form.
*/
diff --git a/src/backend/catalog/sql_features.txt
b/src/backend/catalog/sql_features.txt
index caa971c435..86519ad297 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -475,7 +475,7 @@ T324 Explicit security for SQL routines
NO
T325 Qualified SQL parameter references YES
T326 Table functions NO
T331 Basic roles YES
-T332 Extended roles NO mostly supported
+T332 Extended roles YES
T341 Overloading of SQL-invoked functions and procedures
YES
T351 Bracketed SQL comments (/*...*/ comments) YES
T431 Extended grouping capabilities YES
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 70f8b718e0..fe4082adbc 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -3256,6 +3256,7 @@ _copyGrantStmt(const GrantStmt *from)
COPY_NODE_FIELD(privileges);
COPY_NODE_FIELD(grantees);
COPY_SCALAR_FIELD(grant_option);
+ COPY_NODE_FIELD(grantor);
COPY_SCALAR_FIELD(behavior);
return newnode;
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 541e0e6b48..7f898e4b36 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -1133,6 +1133,7 @@ _equalGrantStmt(const GrantStmt *a, const GrantStmt *b)
COMPARE_NODE_FIELD(privileges);
COMPARE_NODE_FIELD(grantees);
COMPARE_SCALAR_FIELD(grant_option);
+ COMPARE_NODE_FIELD(grantor);
COMPARE_SCALAR_FIELD(behavior);
return true;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 8f341ac006..95c397ecd2 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -6722,7 +6722,7 @@ opt_from_in: from_in
*****************************************************************************/
GrantStmt: GRANT privileges ON privilege_target TO grantee_list
- opt_grant_grant_option
+ opt_grant_grant_option opt_granted_by
{
GrantStmt *n = makeNode(GrantStmt);
n->is_grant = true;
@@ -6732,13 +6732,14 @@ GrantStmt: GRANT privileges ON privilege_target TO
grantee_list
n->objects = ($4)->objs;
n->grantees = $6;
n->grant_option = $7;
+ n->grantor = $8;
$$ = (Node*)n;
}
;
RevokeStmt:
REVOKE privileges ON privilege_target
- FROM grantee_list opt_drop_behavior
+ FROM grantee_list opt_granted_by opt_drop_behavior
{
GrantStmt *n = makeNode(GrantStmt);
n->is_grant = false;
@@ -6748,11 +6749,12 @@ RevokeStmt:
n->objtype = ($4)->objtype;
n->objects = ($4)->objs;
n->grantees = $6;
- n->behavior = $7;
+ n->grantor = $7;
+ n->behavior = $8;
$$ = (Node *)n;
}
| REVOKE GRANT OPTION FOR privileges ON privilege_target
- FROM grantee_list opt_drop_behavior
+ FROM grantee_list opt_granted_by opt_drop_behavior
{
GrantStmt *n = makeNode(GrantStmt);
n->is_grant = false;
@@ -6762,7 +6764,8 @@ RevokeStmt:
n->objtype = ($7)->objtype;
n->objects = ($7)->objs;
n->grantees = $9;
- n->behavior = $10;
+ n->grantor = $10;
+ n->behavior = $11;
$$ = (Node *)n;
}
;
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 48a79a7657..41d6f4f191 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1930,6 +1930,7 @@ typedef struct GrantStmt
/* privileges == NIL denotes ALL PRIVILEGES */
List *grantees; /* list of RoleSpec nodes */
bool grant_option; /* grant or revoke grant option */
+ RoleSpec *grantor;
DropBehavior behavior; /* drop behavior (for REVOKE) */
} GrantStmt;
--
2.29.2
