aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--doc/src/sgml/ref/grant.sgml25
-rw-r--r--doc/src/sgml/ref/revoke.sgml13
-rw-r--r--src/backend/catalog/aclchk.c16
-rw-r--r--src/backend/catalog/sql_features.txt2
-rw-r--r--src/backend/nodes/copyfuncs.c1
-rw-r--r--src/backend/nodes/equalfuncs.c1
-rw-r--r--src/backend/parser/gram.y13
-rw-r--r--src/include/nodes/parsenodes.h1
-rw-r--r--src/test/regress/expected/privileges.out6
-rw-r--r--src/test/regress/sql/privileges.sql6
10 files changed, 71 insertions, 13 deletions
diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml
index c3db393bdea..a897712de2e 100644
--- a/doc/src/sgml/ref/grant.sgml
+++ b/doc/src/sgml/ref/grant.sgml
@@ -26,58 +26,71 @@ GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
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 ]
@@ -134,6 +147,12 @@ GRANT <replaceable class="parameter">role_name</replaceable> [, ...] TO <replace
</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),
as the owner has all privileges by default. (The owner could,
@@ -410,9 +429,9 @@ GRANT admins TO joe;
<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 35ff87a4f5e..3014c864ea3 100644
--- a/doc/src/sgml/ref/revoke.sgml
+++ b/doc/src/sgml/ref/revoke.sgml
@@ -27,6 +27,7 @@ REVOKE [ GRANT OPTION FOR ]
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 @@ REVOKE [ GRANT OPTION FOR ]
[, ...] | 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 @@ REVOKE [ GRANT OPTION FOR ]
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 @@ REVOKE [ GRANT OPTION FOR ]
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 f3c1ca18ae7..add3d147e76 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 caa971c4356..86519ad2974 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 ba3ccc712c8..21e09c667a3 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -3270,6 +3270,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 a2ef853dc2a..5a5237c6c30 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -1145,6 +1145,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 7574d545e0e..b2f447bf9a2 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -6772,7 +6772,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;
@@ -6782,13 +6782,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;
@@ -6798,11 +6799,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;
@@ -6812,7 +6814,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 dc2bb40926a..068c6ec4401 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1949,6 +1949,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;
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index 8f2fc89851b..ed98fa8376f 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -70,8 +70,10 @@ SELECT * FROM atest1;
CREATE TABLE atest2 (col1 varchar(10), col2 boolean);
GRANT SELECT ON atest2 TO regress_priv_user2;
GRANT UPDATE ON atest2 TO regress_priv_user3;
-GRANT INSERT ON atest2 TO regress_priv_user4;
-GRANT TRUNCATE ON atest2 TO regress_priv_user5;
+GRANT INSERT ON atest2 TO regress_priv_user4 GRANTED BY CURRENT_USER;
+GRANT TRUNCATE ON atest2 TO regress_priv_user5 GRANTED BY CURRENT_ROLE;
+GRANT TRUNCATE ON atest2 TO regress_priv_user4 GRANTED BY regress_priv_user5; -- error
+ERROR: grantor must be current user
SET SESSION AUTHORIZATION regress_priv_user2;
SELECT session_user, current_user;
session_user | current_user
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index 1c250a11fe1..becbc196713 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -64,8 +64,10 @@ SELECT * FROM atest1;
CREATE TABLE atest2 (col1 varchar(10), col2 boolean);
GRANT SELECT ON atest2 TO regress_priv_user2;
GRANT UPDATE ON atest2 TO regress_priv_user3;
-GRANT INSERT ON atest2 TO regress_priv_user4;
-GRANT TRUNCATE ON atest2 TO regress_priv_user5;
+GRANT INSERT ON atest2 TO regress_priv_user4 GRANTED BY CURRENT_USER;
+GRANT TRUNCATE ON atest2 TO regress_priv_user5 GRANTED BY CURRENT_ROLE;
+
+GRANT TRUNCATE ON atest2 TO regress_priv_user4 GRANTED BY regress_priv_user5; -- error
SET SESSION AUTHORIZATION regress_priv_user2;
generated by cgit v1.2.3 (git 2.25.1) at 2025-06-25 07:58:44 +0000