pg.ddx.io pgsql-hackers@postgresql.org mailing list archive help / color / mirror / Atom feed From: Petr Jelinek <pjmodos@pjmodos.net>
To: PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: GRANT ON ALL IN schema
Date: Wed, 17 Jun 2009 10:29:10 +0200
Message-ID: <4A38A956.8080600@pjmodos.net> (raw )
In-Reply-To: <4A37E122.8070303@pjmodos.net >
References: <4A37BF63.50008@pjmodos.net >
<4A37E122.8070303@pjmodos.net >
So, here is the first version of the patch.
It includes functionality itself, simple regression test and also very
simple documentation.
The patch allows "GRANT ON ALL TABLES/VIEWS/FUNCTIONS/SEQUENCES IN
schemaname, schemaname2 TO username" and same thing for REVOKE.
Words TABLES, VIEWS, FUNCTIONS and SEQUENCES were added as unreserved
keywords. Unfortunately I was unable to create syntax with optional
SCHEMA keyword after IN (shift/reduce conflicts), if it's needed maybe
somebody with better bison knowledge might add it.
Also since this patch introduces VIEWS as object with grantable
privileges, I added GRANT ON VIEW foo syntax which is more or less
synonymous to GRANT ON TABLE foo syntax. It felt weird to have GRANT ON
ALL VIEWS but not GRANT ON VIEW.
Any comments/suggestions are welcome (I especially wonder if the use of
list_union_ptr is acceptable).
--
Regards
Petr Jelinek (PJMODOS)
diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml
index bf963b8..7ddbd25 100644
*** a/doc/src/sgml/ref/grant.sgml
--- b/doc/src/sgml/ref/grant.sgml
*************** PostgreSQL documentation
*** 23,39 ****
<synopsis>
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
! ON [ TABLE ] <replaceable class="PARAMETER">tablename</replaceable> [, ...]
TO { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( <replaceable class="PARAMETER">column</replaceable> [, ...] )
[,...] | ALL [ PRIVILEGES ] ( <replaceable class="PARAMETER">column</replaceable> [, ...] ) }
! ON [ TABLE ] <replaceable class="PARAMETER">tablename</replaceable> [, ...]
TO { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { USAGE | SELECT | UPDATE }
[,...] | ALL [ PRIVILEGES ] }
! ON SEQUENCE <replaceable class="PARAMETER">sequencename</replaceable> [, ...]
TO { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
--- 23,41 ----
<synopsis>
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
! ON { { [ TABLE | VIEW ] <replaceable class="PARAMETER">tablename</replaceable> [, ...] }
! | ALL [ TABLES | VIEWS ] IN <replaceable>schemaname</replaceable> [, ...] }
TO { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( <replaceable class="PARAMETER">column</replaceable> [, ...] )
[,...] | ALL [ PRIVILEGES ] ( <replaceable class="PARAMETER">column</replaceable> [, ...] ) }
! ON [ TABLE | VIEW ] <replaceable class="PARAMETER">tablename</replaceable> [, ...]
TO { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { USAGE | SELECT | UPDATE }
[,...] | ALL [ PRIVILEGES ] }
! ON { SEQUENCE <replaceable class="PARAMETER">sequencename</replaceable> [, ...]
! | ALL SEQUENCES IN <replaceable>schemaname</replaceable> [, ...] }
TO { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
*************** GRANT { USAGE | ALL [ PRIVILEGES ] }
*** 49,55 ****
TO { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
! ON FUNCTION <replaceable>funcname</replaceable> ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">argtype</replaceable> [, ...] ] ) [, ...]
TO { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
--- 51,58 ----
TO { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
! ON { FUNCTION <replaceable>funcname</replaceable> ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">argtype</replaceable> [, ...] ] ) [, ...]
! | ALL FUNCTIONS IN <replaceable>schemaname</replaceable> [, ...] }
TO { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
*************** GRANT <replaceable class="PARAMETER">rol
*** 143,148 ****
--- 146,158 ----
</para>
<para>
+ There is also the posibility of granting permissions to all objects of
+ given type inside one or multiple schemas. This functionality is supported
+ for tables, views, sequences and functions and can done by using
+ ALL TABLES IN schemanema syntax in place of object name.
+ </para>
+
+ <para>
The possible privileges are:
<variablelist>
diff --git a/doc/src/sgml/ref/revoke.sgml b/doc/src/sgml/ref/revoke.sgml
index 8d62580..ac0905f 100644
*** a/doc/src/sgml/ref/revoke.sgml
--- b/doc/src/sgml/ref/revoke.sgml
*************** PostgreSQL documentation
*** 24,44 ****
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
! ON [ TABLE ] <replaceable class="PARAMETER">tablename</replaceable> [, ...]
FROM { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | REFERENCES } ( <replaceable class="PARAMETER">column</replaceable> [, ...] )
[,...] | ALL [ PRIVILEGES ] ( <replaceable class="PARAMETER">column</replaceable> [, ...] ) }
! ON [ TABLE ] <replaceable class="PARAMETER">tablename</replaceable> [, ...]
FROM { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { USAGE | SELECT | UPDATE }
[,...] | ALL [ PRIVILEGES ] }
! ON SEQUENCE <replaceable class="PARAMETER">sequencename</replaceable> [, ...]
FROM { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
--- 24,46 ----
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
! ON { { [ TABLE | VIEW ] <replaceable class="PARAMETER">tablename</replaceable> [, ...] }
! | ALL [ TABLES | VIEWS ] IN <replaceable>schemaname</replaceable> [, ...] }
FROM { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | REFERENCES } ( <replaceable class="PARAMETER">column</replaceable> [, ...] )
[,...] | ALL [ PRIVILEGES ] ( <replaceable class="PARAMETER">column</replaceable> [, ...] ) }
! ON [ TABLE | VIEW ] <replaceable class="PARAMETER">tablename</replaceable> [, ...]
FROM { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { USAGE | SELECT | UPDATE }
[,...] | ALL [ PRIVILEGES ] }
! ON { SEQUENCE <replaceable class="PARAMETER">sequencename</replaceable> [, ...]
! | ALL SEQUENCES IN <replaceable>schemaname</replaceable> [, ...] }
FROM { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
*************** REVOKE [ GRANT OPTION FOR ]
*** 62,68 ****
REVOKE [ GRANT OPTION FOR ]
{ EXECUTE | ALL [ PRIVILEGES ] }
! ON FUNCTION <replaceable>funcname</replaceable> ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">argtype</replaceable> [, ...] ] ) [, ...]
FROM { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
--- 64,71 ----
REVOKE [ GRANT OPTION FOR ]
{ EXECUTE | ALL [ PRIVILEGES ] }
! ON { FUNCTION <replaceable>funcname</replaceable> ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">argtype</replaceable> [, ...] ] ) [, ...]
! | ALL FUNCTIONS IN <replaceable>schemaname</replaceable> [, ...] }
FROM { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c
index ec4aaf0..98fbd27 100644
*** a/src/backend/catalog/aclchk.c
--- b/src/backend/catalog/aclchk.c
*************** static void ExecGrant_Namespace(Internal
*** 61,66 ****
--- 61,68 ----
static void ExecGrant_Tablespace(InternalGrant *grantStmt);
static List *objectNamesToOids(GrantObjectType objtype, List *objnames);
+ static List *getNamespacesObjectsOids(GrantObjectType objtype, List *nspnames);
+ static List *getRelationsInNamespace(Oid namespaceId, char relkind);
static void expand_col_privileges(List *colnames, Oid table_oid,
AclMode this_privileges,
AclMode *col_privileges,
*************** ExecuteGrantStmt(GrantStmt *stmt)
*** 286,292 ****
*/
istmt.is_grant = stmt->is_grant;
istmt.objtype = stmt->objtype;
! istmt.objects = objectNamesToOids(stmt->objtype, stmt->objects);
/* all_privs to be filled below */
/* privileges to be filled below */
istmt.col_privs = NIL; /* may get filled below */
--- 288,297 ----
*/
istmt.is_grant = stmt->is_grant;
istmt.objtype = stmt->objtype;
! if (stmt->is_schema)
! istmt.objects = getNamespacesObjectsOids(stmt->objtype, stmt->objects);
! else
! istmt.objects = objectNamesToOids(stmt->objtype, stmt->objects);
/* all_privs to be filled below */
/* privileges to be filled below */
istmt.col_privs = NIL; /* may get filled below */
*************** ExecuteGrantStmt(GrantStmt *stmt)
*** 325,330 ****
--- 330,336 ----
* the object type.
*/
case ACL_OBJECT_RELATION:
+ case ACL_OBJECT_VIEW:
all_privileges = ACL_ALL_RIGHTS_RELATION | ACL_ALL_RIGHTS_SEQUENCE;
errormsg = gettext_noop("invalid privilege type %s for relation");
break;
*************** ExecuteGrantStmt(GrantStmt *stmt)
*** 394,400 ****
*/
if (privnode->cols)
{
! if (stmt->objtype != ACL_OBJECT_RELATION)
ereport(ERROR,
(errcode(ERRCODE_INVALID_GRANT_OPERATION),
errmsg("column privileges are only valid for relations")));
--- 400,406 ----
*/
if (privnode->cols)
{
! if (stmt->objtype != ACL_OBJECT_RELATION && stmt->objtype != ACL_OBJECT_VIEW)
ereport(ERROR,
(errcode(ERRCODE_INVALID_GRANT_OPERATION),
errmsg("column privileges are only valid for relations")));
*************** ExecGrantStmt_oids(InternalGrant *istmt)
*** 431,436 ****
--- 437,443 ----
switch (istmt->objtype)
{
case ACL_OBJECT_RELATION:
+ case ACL_OBJECT_VIEW:
case ACL_OBJECT_SEQUENCE:
ExecGrant_Relation(istmt);
break;
*************** objectNamesToOids(GrantObjectType objtyp
*** 477,482 ****
--- 484,490 ----
switch (objtype)
{
case ACL_OBJECT_RELATION:
+ case ACL_OBJECT_VIEW:
case ACL_OBJECT_SEQUENCE:
foreach(cell, objnames)
{
*************** objectNamesToOids(GrantObjectType objtyp
*** 609,614 ****
--- 617,756 ----
return objects;
}
+
+ /*
+ * getNamespacesObjectsOids
+ *
+ * Get all objects of a given type from specified schema list into an Oid list.
+ */
+ static List *
+ getNamespacesObjectsOids(GrantObjectType objtype, List *nspnames)
+ {
+ List *objects = NIL;
+ ListCell *cell;
+ char *nspname;
+ Oid namespaceId;
+
+ switch (objtype)
+ {
+ case ACL_OBJECT_RELATION:
+ foreach(cell, nspnames)
+ {
+ List *relations = NIL;
+
+ nspname = strVal(lfirst(cell));
+ namespaceId = LookupExplicitNamespace(nspname);
+
+ relations = getRelationsInNamespace(namespaceId, RELKIND_RELATION);
+
+ objects = list_union_ptr(relations, objects);
+ }
+ break;
+ case ACL_OBJECT_VIEW:
+ foreach(cell, nspnames)
+ {
+ List *relations = NIL;
+
+ nspname = strVal(lfirst(cell));
+ namespaceId = LookupExplicitNamespace(nspname);
+
+ relations = getRelationsInNamespace(namespaceId, RELKIND_VIEW);
+
+ objects = list_union_ptr(relations, objects);
+ }
+ break;
+ case ACL_OBJECT_SEQUENCE:
+ foreach(cell, nspnames)
+ {
+ List *relations = NIL;
+
+ nspname = strVal(lfirst(cell));
+ namespaceId = LookupExplicitNamespace(nspname);
+
+ relations = getRelationsInNamespace(namespaceId, RELKIND_SEQUENCE);
+
+ objects = list_union_ptr(relations, objects);
+ }
+ break;
+ case ACL_OBJECT_FUNCTION:
+ foreach(cell, nspnames)
+ {
+ ScanKeyData key[1];
+ HeapScanDesc scan;
+ HeapTuple tuple;
+ Relation rel;
+
+ nspname = strVal(lfirst(cell));
+ namespaceId = LookupExplicitNamespace(nspname);
+
+ ScanKeyInit(&key[0],
+ Anum_pg_proc_pronamespace,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(namespaceId));
+
+ rel = heap_open(ProcedureRelationId, AccessShareLock);
+
+ scan = heap_beginscan(rel, SnapshotNow, 1, key);
+
+ while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
+ {
+ objects = lappend_oid(objects, HeapTupleGetOid(tuple));
+ }
+
+ heap_endscan(scan);
+
+ heap_close(rel, AccessShareLock);
+ }
+ break;
+ default:
+ elog(ERROR, "unrecognized GrantStmt.objtype: %d",
+ (int) objtype);
+ }
+
+ return objects;
+ }
+
+ /*
+ * getRelationsInNamespace
+ *
+ * Return list of relations in given namespace filtered by relation kind
+ */
+ static List *
+ getRelationsInNamespace(Oid namespaceId, char relkind)
+ {
+ List *relations = NIL;
+ ScanKeyData key[2];
+ HeapScanDesc scan;
+ HeapTuple tuple;
+ Relation rel;
+
+ ScanKeyInit(&key[0],
+ Anum_pg_class_relnamespace,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(namespaceId));
+
+ ScanKeyInit(&key[1],
+ Anum_pg_class_relkind,
+ BTEqualStrategyNumber, F_CHAREQ,
+ CharGetDatum(relkind));
+
+ rel = heap_open(RelationRelationId, AccessShareLock);
+
+ scan = heap_beginscan(rel, SnapshotNow, 2, key);
+
+ while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
+ {
+ relations = lappend_oid(relations, HeapTupleGetOid(tuple));
+ }
+
+ heap_endscan(scan);
+
+ heap_close(rel, AccessShareLock);
+
+ return relations;
+ }
+
+
/*
* expand_col_privileges
*
*************** ExecGrant_Relation(InternalGrant *istmt)
*** 912,918 ****
* permissions. The OR of table and sequence permissions were already
* checked.
*/
! if (istmt->objtype == ACL_OBJECT_RELATION)
{
if (pg_class_tuple->relkind == RELKIND_SEQUENCE)
{
--- 1054,1060 ----
* permissions. The OR of table and sequence permissions were already
* checked.
*/
! if (istmt->objtype == ACL_OBJECT_RELATION || istmt->objtype == ACL_OBJECT_VIEW)
{
if (pg_class_tuple->relkind == RELKIND_SEQUENCE)
{
*************** ExecGrant_Relation(InternalGrant *istmt)
*** 986,996 ****
aclDatum = SysCacheGetAttr(RELOID, tuple, Anum_pg_class_relacl,
&isNull);
if (isNull)
! old_acl = acldefault(pg_class_tuple->relkind == RELKIND_SEQUENCE ?
! ACL_OBJECT_SEQUENCE : ACL_OBJECT_RELATION,
! ownerId);
else
old_acl = DatumGetAclPCopy(aclDatum);
/* Need an extra copy of original rel ACL for column handling */
old_rel_acl = aclcopy(old_acl);
--- 1128,1150 ----
aclDatum = SysCacheGetAttr(RELOID, tuple, Anum_pg_class_relacl,
&isNull);
if (isNull)
! {
! switch (pg_class_tuple->relkind)
! {
! case RELKIND_SEQUENCE:
! old_acl = acldefault(ACL_OBJECT_SEQUENCE, ownerId);
! break;
! case RELKIND_VIEW:
! old_acl = acldefault(ACL_OBJECT_VIEW, ownerId);
! break;
! default:
! old_acl = acldefault(ACL_OBJECT_RELATION, ownerId);
! }
! }
else
+ {
old_acl = DatumGetAclPCopy(aclDatum);
+ }
/* Need an extra copy of original rel ACL for column handling */
old_rel_acl = aclcopy(old_acl);
*************** pg_class_aclmask(Oid table_oid, Oid role
*** 2434,2442 ****
if (isNull)
{
/* No ACL, so build default ACL */
! acl = acldefault(classForm->relkind == RELKIND_SEQUENCE ?
! ACL_OBJECT_SEQUENCE : ACL_OBJECT_RELATION,
! ownerId);
aclDatum = (Datum) 0;
}
else
--- 2588,2604 ----
if (isNull)
{
/* No ACL, so build default ACL */
! switch (classForm->relkind)
! {
! case RELKIND_SEQUENCE:
! acl = acldefault(ACL_OBJECT_SEQUENCE, ownerId);
! break;
! case RELKIND_VIEW:
! acl = acldefault(ACL_OBJECT_VIEW, ownerId);
! break;
! default:
! acl = acldefault(ACL_OBJECT_RELATION, ownerId);
! }
aclDatum = (Datum) 0;
}
else
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 9a45355..3bc5dc5 100644
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
*************** static bool QueryIsRule = FALSE;
*** 98,103 ****
--- 98,104 ----
typedef struct PrivTarget
{
GrantObjectType objtype;
+ bool is_schema;
List *objs;
} PrivTarget;
*************** static TypeName *TableFuncTypeName(List
*** 449,455 ****
EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN EXTERNAL EXTRACT
FALSE_P FAMILY FETCH FIRST_P FLOAT_P FOLLOWING FOR FORCE FOREIGN FORWARD
! FREEZE FROM FULL FUNCTION
GLOBAL GRANT GRANTED GREATEST GROUP_P
--- 450,456 ----
EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN EXTERNAL EXTRACT
FALSE_P FAMILY FETCH FIRST_P FLOAT_P FOLLOWING FOR FORCE FOREIGN FORWARD
! FREEZE FROM FULL FUNCTION FUNCTIONS
GLOBAL GRANT GRANTED GREATEST GROUP_P
*************** static TypeName *TableFuncTypeName(List
*** 487,499 ****
RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA RESET RESTART
RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROW ROWS RULE
! SAVEPOINT SCHEMA SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE
SERIALIZABLE SERVER SESSION SESSION_USER SET SETOF SHARE
SHOW SIMILAR SIMPLE SMALLINT SOME STABLE STANDALONE_P START STATEMENT
STATISTICS STDIN STDOUT STORAGE STRICT_P STRIP_P SUBSTRING SUPERUSER_P
SYMMETRIC SYSID SYSTEM_P
! TABLE TABLESPACE TEMP TEMPLATE TEMPORARY TEXT_P THEN TIME TIMESTAMP
TO TRAILING TRANSACTION TREAT TRIGGER TRIM TRUE_P
TRUNCATE TRUSTED TYPE_P
--- 488,500 ----
RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA RESET RESTART
RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROW ROWS RULE
! SAVEPOINT SCHEMA SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE SEQUENCES
SERIALIZABLE SERVER SESSION SESSION_USER SET SETOF SHARE
SHOW SIMILAR SIMPLE SMALLINT SOME STABLE STANDALONE_P START STATEMENT
STATISTICS STDIN STDOUT STORAGE STRICT_P STRIP_P SUBSTRING SUPERUSER_P
SYMMETRIC SYSID SYSTEM_P
! TABLE TABLES TABLESPACE TEMP TEMPLATE TEMPORARY TEXT_P THEN TIME TIMESTAMP
TO TRAILING TRANSACTION TREAT TRIGGER TRIM TRUE_P
TRUNCATE TRUSTED TYPE_P
*************** static TypeName *TableFuncTypeName(List
*** 501,507 ****
UPDATE USER USING
VACUUM VALID VALIDATOR VALUE_P VALUES VARCHAR VARIADIC VARYING
! VERBOSE VERSION_P VIEW VOLATILE
WHEN WHERE WHITESPACE_P WINDOW WITH WITHOUT WORK WRAPPER WRITE
--- 502,508 ----
UPDATE USER USING
VACUUM VALID VALIDATOR VALUE_P VALUES VARCHAR VARIADIC VARYING
! VERBOSE VERSION_P VIEW VIEWS VOLATILE
WHEN WHERE WHITESPACE_P WINDOW WITH WITHOUT WORK WRAPPER WRITE
*************** GrantStmt: GRANT privileges ON privilege
*** 4227,4232 ****
--- 4228,4234 ----
n->is_grant = true;
n->privileges = $2;
n->objtype = ($4)->objtype;
+ n->is_schema = ($4)->is_schema;
n->objects = ($4)->objs;
n->grantees = $6;
n->grant_option = $7;
*************** RevokeStmt:
*** 4243,4248 ****
--- 4245,4251 ----
n->grant_option = false;
n->privileges = $2;
n->objtype = ($4)->objtype;
+ n->is_schema = ($4)->is_schema;
n->objects = ($4)->objs;
n->grantees = $6;
n->behavior = $7;
*************** RevokeStmt:
*** 4256,4261 ****
--- 4259,4265 ----
n->grant_option = true;
n->privileges = $5;
n->objtype = ($7)->objtype;
+ n->is_schema = ($7)->is_schema;
n->objects = ($7)->objs;
n->grantees = $9;
n->behavior = $10;
*************** privilege_target:
*** 4338,4343 ****
--- 4342,4348 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_RELATION;
+ n->is_schema = FALSE;
n->objs = $1;
$$ = n;
}
*************** privilege_target:
*** 4345,4350 ****
--- 4350,4364 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_RELATION;
+ n->is_schema = FALSE;
+ n->objs = $2;
+ $$ = n;
+ }
+ | VIEW qualified_name_list
+ {
+ PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
+ n->objtype = ACL_OBJECT_VIEW;
+ n->is_schema = FALSE;
n->objs = $2;
$$ = n;
}
*************** privilege_target:
*** 4352,4357 ****
--- 4366,4372 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_SEQUENCE;
+ n->is_schema = FALSE;
n->objs = $2;
$$ = n;
}
*************** privilege_target:
*** 4359,4364 ****
--- 4374,4380 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_FDW;
+ n->is_schema = FALSE;
n->objs = $4;
$$ = n;
}
*************** privilege_target:
*** 4366,4371 ****
--- 4382,4388 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_FOREIGN_SERVER;
+ n->is_schema = FALSE;
n->objs = $3;
$$ = n;
}
*************** privilege_target:
*** 4373,4378 ****
--- 4390,4396 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_FUNCTION;
+ n->is_schema = FALSE;
n->objs = $2;
$$ = n;
}
*************** privilege_target:
*** 4380,4385 ****
--- 4398,4404 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_DATABASE;
+ n->is_schema = FALSE;
n->objs = $2;
$$ = n;
}
*************** privilege_target:
*** 4387,4392 ****
--- 4406,4412 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_LANGUAGE;
+ n->is_schema = FALSE;
n->objs = $2;
$$ = n;
}
*************** privilege_target:
*** 4394,4399 ****
--- 4414,4420 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_NAMESPACE;
+ n->is_schema = FALSE;
n->objs = $2;
$$ = n;
}
*************** privilege_target:
*** 4401,4409 ****
--- 4422,4463 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_TABLESPACE;
+ n->is_schema = FALSE;
n->objs = $2;
$$ = n;
}
+ | ALL TABLES IN_P name_list
+ {
+ PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
+ n->objtype = ACL_OBJECT_RELATION;
+ n->is_schema = TRUE;
+ n->objs = $4;
+ $$ = n;
+ }
+ | ALL VIEWS IN_P name_list
+ {
+ PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
+ n->objtype = ACL_OBJECT_VIEW;
+ n->is_schema = TRUE;
+ n->objs = $4;
+ $$ = n;
+ }
+ | ALL SEQUENCES IN_P name_list
+ {
+ PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
+ n->objtype = ACL_OBJECT_SEQUENCE;
+ n->is_schema = TRUE;
+ n->objs = $4;
+ $$ = n;
+ }
+ | ALL FUNCTIONS IN_P name_list
+ {
+ PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
+ n->objtype = ACL_OBJECT_FUNCTION;
+ n->is_schema = TRUE;
+ n->objs = $4;
+ $$ = n;
+ }
;
*************** unreserved_keyword:
*** 10212,10217 ****
--- 10266,10272 ----
| FORCE
| FORWARD
| FUNCTION
+ | FUNCTIONS
| GLOBAL
| GRANTED
| HANDLER
*************** unreserved_keyword:
*** 10321,10326 ****
--- 10376,10382 ----
| SECOND_P
| SECURITY
| SEQUENCE
+ | SEQUENCES
| SERIALIZABLE
| SERVER
| SESSION
*************** unreserved_keyword:
*** 10341,10346 ****
--- 10397,10403 ----
| SUPERUSER_P
| SYSID
| SYSTEM_P
+ | TABLES
| TABLESPACE
| TEMP
| TEMPLATE
*************** unreserved_keyword:
*** 10365,10370 ****
--- 10422,10428 ----
| VARYING
| VERSION_P
| VIEW
+ | VIEWS
| VOLATILE
| WHITESPACE_P
| WITHOUT
diff --git a/src/backend/utils/adt/acl.c b/src/backend/utils/adt/acl.c
index 334823b..ddd92e7 100644
*** a/src/backend/utils/adt/acl.c
--- b/src/backend/utils/adt/acl.c
*************** acldefault(GrantObjectType objtype, Oid
*** 609,614 ****
--- 609,615 ----
owner_default = ACL_NO_RIGHTS;
break;
case ACL_OBJECT_RELATION:
+ case ACL_OBJECT_VIEW:
world_default = ACL_NO_RIGHTS;
owner_default = ACL_ALL_RIGHTS_RELATION;
break;
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 71c864a..3c79a1c 100644
*** a/src/include/nodes/parsenodes.h
--- b/src/include/nodes/parsenodes.h
*************** typedef struct AlterDomainStmt
*** 1180,1186 ****
typedef enum GrantObjectType
{
ACL_OBJECT_COLUMN, /* column */
! ACL_OBJECT_RELATION, /* table, view */
ACL_OBJECT_SEQUENCE, /* sequence */
ACL_OBJECT_DATABASE, /* database */
ACL_OBJECT_FDW, /* foreign-data wrapper */
--- 1180,1186 ----
typedef enum GrantObjectType
{
ACL_OBJECT_COLUMN, /* column */
! ACL_OBJECT_RELATION, /* table */
ACL_OBJECT_SEQUENCE, /* sequence */
ACL_OBJECT_DATABASE, /* database */
ACL_OBJECT_FDW, /* foreign-data wrapper */
*************** typedef enum GrantObjectType
*** 1188,1194 ****
ACL_OBJECT_FUNCTION, /* function */
ACL_OBJECT_LANGUAGE, /* procedural language */
ACL_OBJECT_NAMESPACE, /* namespace */
! ACL_OBJECT_TABLESPACE /* tablespace */
} GrantObjectType;
typedef struct GrantStmt
--- 1188,1195 ----
ACL_OBJECT_FUNCTION, /* function */
ACL_OBJECT_LANGUAGE, /* procedural language */
ACL_OBJECT_NAMESPACE, /* namespace */
! ACL_OBJECT_TABLESPACE, /* tablespace */
! ACL_OBJECT_VIEW, /* view */
} GrantObjectType;
typedef struct GrantStmt
*************** typedef struct GrantStmt
*** 1196,1201 ****
--- 1197,1204 ----
NodeTag type;
bool is_grant; /* true = GRANT, false = REVOKE */
GrantObjectType objtype; /* kind of object being operated on */
+ bool is_schema; /* if true we want all objects
+ * of objtype in schema */
List *objects; /* list of RangeVar nodes, FuncWithArgs nodes,
* or plain names (as Value strings) */
List *privileges; /* list of AccessPriv nodes */
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 67e9cb4..a6ae56c 100644
*** a/src/include/parser/kwlist.h
--- b/src/include/parser/kwlist.h
*************** PG_KEYWORD("freeze", FREEZE, TYPE_FUNC_N
*** 163,168 ****
--- 163,169 ----
PG_KEYWORD("from", FROM, RESERVED_KEYWORD)
PG_KEYWORD("full", FULL, TYPE_FUNC_NAME_KEYWORD)
PG_KEYWORD("function", FUNCTION, UNRESERVED_KEYWORD)
+ PG_KEYWORD("functions", FUNCTIONS, UNRESERVED_KEYWORD)
PG_KEYWORD("global", GLOBAL, UNRESERVED_KEYWORD)
PG_KEYWORD("grant", GRANT, RESERVED_KEYWORD)
PG_KEYWORD("granted", GRANTED, UNRESERVED_KEYWORD)
*************** PG_KEYWORD("second", SECOND_P, UNRESERVE
*** 328,333 ****
--- 329,335 ----
PG_KEYWORD("security", SECURITY, UNRESERVED_KEYWORD)
PG_KEYWORD("select", SELECT, RESERVED_KEYWORD)
PG_KEYWORD("sequence", SEQUENCE, UNRESERVED_KEYWORD)
+ PG_KEYWORD("sequences", SEQUENCES, UNRESERVED_KEYWORD)
PG_KEYWORD("serializable", SERIALIZABLE, UNRESERVED_KEYWORD)
PG_KEYWORD("server", SERVER, UNRESERVED_KEYWORD)
PG_KEYWORD("session", SESSION, UNRESERVED_KEYWORD)
*************** PG_KEYWORD("symmetric", SYMMETRIC, RESER
*** 356,361 ****
--- 358,364 ----
PG_KEYWORD("sysid", SYSID, UNRESERVED_KEYWORD)
PG_KEYWORD("system", SYSTEM_P, UNRESERVED_KEYWORD)
PG_KEYWORD("table", TABLE, RESERVED_KEYWORD)
+ PG_KEYWORD("tables", TABLES, UNRESERVED_KEYWORD)
PG_KEYWORD("tablespace", TABLESPACE, UNRESERVED_KEYWORD)
PG_KEYWORD("temp", TEMP, UNRESERVED_KEYWORD)
PG_KEYWORD("template", TEMPLATE, UNRESERVED_KEYWORD)
*************** PG_KEYWORD("varying", VARYING, UNRESERVE
*** 396,401 ****
--- 399,405 ----
PG_KEYWORD("verbose", VERBOSE, TYPE_FUNC_NAME_KEYWORD)
PG_KEYWORD("version", VERSION_P, UNRESERVED_KEYWORD)
PG_KEYWORD("view", VIEW, UNRESERVED_KEYWORD)
+ PG_KEYWORD("views", VIEWS, UNRESERVED_KEYWORD)
PG_KEYWORD("volatile", VOLATILE, UNRESERVED_KEYWORD)
PG_KEYWORD("when", WHEN, RESERVED_KEYWORD)
PG_KEYWORD("where", WHERE, RESERVED_KEYWORD)
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index a17ff59..043c0f3 100644
*** a/src/test/regress/expected/privileges.out
--- b/src/test/regress/expected/privileges.out
*************** SELECT has_table_privilege('regressuser1
*** 815,820 ****
--- 815,849 ----
t
(1 row)
+ -- Grant on all objects of given type in a schema
+ RESET SESSION AUTHORIZATION;
+ REVOKE ALL ON ALL TABLES IN public FROM regressuser1;
+ SELECT has_table_privilege('regressuser1', 'atest1', 'SELECT'); -- false
+ has_table_privilege
+ ---------------------
+ f
+ (1 row)
+
+ REVOKE ALL ON ALL FUNCTIONS IN public FROM regressuser1;
+ SET SESSION AUTHORIZATION regressuser1;
+ SELECT testfunc2(5); -- fail
+ ERROR: permission denied for function testfunc2
+ RESET SESSION AUTHORIZATION;
+ GRANT ALL ON ALL TABLES IN public TO regressuser1;
+ SELECT has_table_privilege('regressuser1', 'atest2', 'SELECT'); -- true
+ has_table_privilege
+ ---------------------
+ t
+ (1 row)
+
+ GRANT ALL ON ALL FUNCTIONS IN public TO regressuser1;
+ SET SESSION AUTHORIZATION regressuser1;
+ SELECT testfunc2(5); -- ok
+ testfunc2
+ -----------
+ 15
+ (1 row)
+
-- clean up
\c
DROP FUNCTION testfunc2(int);
*************** DROP TABLE atestp2;
*** 839,844 ****
--- 868,875 ----
DROP GROUP regressgroup1;
DROP GROUP regressgroup2;
REVOKE USAGE ON LANGUAGE sql FROM regressuser1;
+ REVOKE ALL ON ALL TABLES IN public FROM regressuser1;
+ REVOKE ALL ON ALL FUNCTIONS IN public FROM regressuser1;
DROP USER regressuser1;
DROP USER regressuser2;
DROP USER regressuser3;
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index 5aa1012..e574c4d 100644
*** a/src/test/regress/sql/privileges.sql
--- b/src/test/regress/sql/privileges.sql
*************** SELECT has_table_privilege('regressuser3
*** 469,474 ****
--- 469,500 ----
SELECT has_table_privilege('regressuser1', 'atest4', 'SELECT WITH GRANT OPTION'); -- true
+ -- Grant on all objects of given type in a schema
+
+ RESET SESSION AUTHORIZATION;
+
+ REVOKE ALL ON ALL TABLES IN public FROM regressuser1;
+
+ SELECT has_table_privilege('regressuser1', 'atest1', 'SELECT'); -- false
+
+ REVOKE ALL ON ALL FUNCTIONS IN public FROM regressuser1;
+
+ SET SESSION AUTHORIZATION regressuser1;
+
+ SELECT testfunc2(5); -- fail
+
+ RESET SESSION AUTHORIZATION;
+
+ GRANT ALL ON ALL TABLES IN public TO regressuser1;
+
+ SELECT has_table_privilege('regressuser1', 'atest2', 'SELECT'); -- true
+
+ GRANT ALL ON ALL FUNCTIONS IN public TO regressuser1;
+
+ SET SESSION AUTHORIZATION regressuser1;
+
+ SELECT testfunc2(5); -- ok
+
-- clean up
\c
*************** DROP GROUP regressgroup1;
*** 497,502 ****
--- 523,530 ----
DROP GROUP regressgroup2;
REVOKE USAGE ON LANGUAGE sql FROM regressuser1;
+ REVOKE ALL ON ALL TABLES IN public FROM regressuser1;
+ REVOKE ALL ON ALL FUNCTIONS IN public FROM regressuser1;
DROP USER regressuser1;
DROP USER regressuser2;
DROP USER regressuser3;
Attachments:
[text/plain] grant-on-all.diff (31.3K, ../4A38A956.8080600@pjmodos.net/2-grant-on-all.diff)
download | inline diff:
diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml
index bf963b8..7ddbd25 100644
*** a/doc/src/sgml/ref/grant.sgml
--- b/doc/src/sgml/ref/grant.sgml
*************** PostgreSQL documentation
*** 23,39 ****
<synopsis>
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
! ON [ TABLE ] <replaceable class="PARAMETER">tablename</replaceable> [, ...]
TO { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( <replaceable class="PARAMETER">column</replaceable> [, ...] )
[,...] | ALL [ PRIVILEGES ] ( <replaceable class="PARAMETER">column</replaceable> [, ...] ) }
! ON [ TABLE ] <replaceable class="PARAMETER">tablename</replaceable> [, ...]
TO { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { USAGE | SELECT | UPDATE }
[,...] | ALL [ PRIVILEGES ] }
! ON SEQUENCE <replaceable class="PARAMETER">sequencename</replaceable> [, ...]
TO { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
--- 23,41 ----
<synopsis>
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
! ON { { [ TABLE | VIEW ] <replaceable class="PARAMETER">tablename</replaceable> [, ...] }
! | ALL [ TABLES | VIEWS ] IN <replaceable>schemaname</replaceable> [, ...] }
TO { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( <replaceable class="PARAMETER">column</replaceable> [, ...] )
[,...] | ALL [ PRIVILEGES ] ( <replaceable class="PARAMETER">column</replaceable> [, ...] ) }
! ON [ TABLE | VIEW ] <replaceable class="PARAMETER">tablename</replaceable> [, ...]
TO { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { USAGE | SELECT | UPDATE }
[,...] | ALL [ PRIVILEGES ] }
! ON { SEQUENCE <replaceable class="PARAMETER">sequencename</replaceable> [, ...]
! | ALL SEQUENCES IN <replaceable>schemaname</replaceable> [, ...] }
TO { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
*************** GRANT { USAGE | ALL [ PRIVILEGES ] }
*** 49,55 ****
TO { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
! ON FUNCTION <replaceable>funcname</replaceable> ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">argtype</replaceable> [, ...] ] ) [, ...]
TO { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
--- 51,58 ----
TO { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
! ON { FUNCTION <replaceable>funcname</replaceable> ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">argtype</replaceable> [, ...] ] ) [, ...]
! | ALL FUNCTIONS IN <replaceable>schemaname</replaceable> [, ...] }
TO { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
*************** GRANT <replaceable class="PARAMETER">rol
*** 143,148 ****
--- 146,158 ----
</para>
<para>
+ There is also the posibility of granting permissions to all objects of
+ given type inside one or multiple schemas. This functionality is supported
+ for tables, views, sequences and functions and can done by using
+ ALL TABLES IN schemanema syntax in place of object name.
+ </para>
+
+ <para>
The possible privileges are:
<variablelist>
diff --git a/doc/src/sgml/ref/revoke.sgml b/doc/src/sgml/ref/revoke.sgml
index 8d62580..ac0905f 100644
*** a/doc/src/sgml/ref/revoke.sgml
--- b/doc/src/sgml/ref/revoke.sgml
*************** PostgreSQL documentation
*** 24,44 ****
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
! ON [ TABLE ] <replaceable class="PARAMETER">tablename</replaceable> [, ...]
FROM { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | REFERENCES } ( <replaceable class="PARAMETER">column</replaceable> [, ...] )
[,...] | ALL [ PRIVILEGES ] ( <replaceable class="PARAMETER">column</replaceable> [, ...] ) }
! ON [ TABLE ] <replaceable class="PARAMETER">tablename</replaceable> [, ...]
FROM { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { USAGE | SELECT | UPDATE }
[,...] | ALL [ PRIVILEGES ] }
! ON SEQUENCE <replaceable class="PARAMETER">sequencename</replaceable> [, ...]
FROM { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
--- 24,46 ----
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
! ON { { [ TABLE | VIEW ] <replaceable class="PARAMETER">tablename</replaceable> [, ...] }
! | ALL [ TABLES | VIEWS ] IN <replaceable>schemaname</replaceable> [, ...] }
FROM { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | REFERENCES } ( <replaceable class="PARAMETER">column</replaceable> [, ...] )
[,...] | ALL [ PRIVILEGES ] ( <replaceable class="PARAMETER">column</replaceable> [, ...] ) }
! ON [ TABLE | VIEW ] <replaceable class="PARAMETER">tablename</replaceable> [, ...]
FROM { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { USAGE | SELECT | UPDATE }
[,...] | ALL [ PRIVILEGES ] }
! ON { SEQUENCE <replaceable class="PARAMETER">sequencename</replaceable> [, ...]
! | ALL SEQUENCES IN <replaceable>schemaname</replaceable> [, ...] }
FROM { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
*************** REVOKE [ GRANT OPTION FOR ]
*** 62,68 ****
REVOKE [ GRANT OPTION FOR ]
{ EXECUTE | ALL [ PRIVILEGES ] }
! ON FUNCTION <replaceable>funcname</replaceable> ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">argtype</replaceable> [, ...] ] ) [, ...]
FROM { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
--- 64,71 ----
REVOKE [ GRANT OPTION FOR ]
{ EXECUTE | ALL [ PRIVILEGES ] }
! ON { FUNCTION <replaceable>funcname</replaceable> ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">argtype</replaceable> [, ...] ] ) [, ...]
! | ALL FUNCTIONS IN <replaceable>schemaname</replaceable> [, ...] }
FROM { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c
index ec4aaf0..98fbd27 100644
*** a/src/backend/catalog/aclchk.c
--- b/src/backend/catalog/aclchk.c
*************** static void ExecGrant_Namespace(Internal
*** 61,66 ****
--- 61,68 ----
static void ExecGrant_Tablespace(InternalGrant *grantStmt);
static List *objectNamesToOids(GrantObjectType objtype, List *objnames);
+ static List *getNamespacesObjectsOids(GrantObjectType objtype, List *nspnames);
+ static List *getRelationsInNamespace(Oid namespaceId, char relkind);
static void expand_col_privileges(List *colnames, Oid table_oid,
AclMode this_privileges,
AclMode *col_privileges,
*************** ExecuteGrantStmt(GrantStmt *stmt)
*** 286,292 ****
*/
istmt.is_grant = stmt->is_grant;
istmt.objtype = stmt->objtype;
! istmt.objects = objectNamesToOids(stmt->objtype, stmt->objects);
/* all_privs to be filled below */
/* privileges to be filled below */
istmt.col_privs = NIL; /* may get filled below */
--- 288,297 ----
*/
istmt.is_grant = stmt->is_grant;
istmt.objtype = stmt->objtype;
! if (stmt->is_schema)
! istmt.objects = getNamespacesObjectsOids(stmt->objtype, stmt->objects);
! else
! istmt.objects = objectNamesToOids(stmt->objtype, stmt->objects);
/* all_privs to be filled below */
/* privileges to be filled below */
istmt.col_privs = NIL; /* may get filled below */
*************** ExecuteGrantStmt(GrantStmt *stmt)
*** 325,330 ****
--- 330,336 ----
* the object type.
*/
case ACL_OBJECT_RELATION:
+ case ACL_OBJECT_VIEW:
all_privileges = ACL_ALL_RIGHTS_RELATION | ACL_ALL_RIGHTS_SEQUENCE;
errormsg = gettext_noop("invalid privilege type %s for relation");
break;
*************** ExecuteGrantStmt(GrantStmt *stmt)
*** 394,400 ****
*/
if (privnode->cols)
{
! if (stmt->objtype != ACL_OBJECT_RELATION)
ereport(ERROR,
(errcode(ERRCODE_INVALID_GRANT_OPERATION),
errmsg("column privileges are only valid for relations")));
--- 400,406 ----
*/
if (privnode->cols)
{
! if (stmt->objtype != ACL_OBJECT_RELATION && stmt->objtype != ACL_OBJECT_VIEW)
ereport(ERROR,
(errcode(ERRCODE_INVALID_GRANT_OPERATION),
errmsg("column privileges are only valid for relations")));
*************** ExecGrantStmt_oids(InternalGrant *istmt)
*** 431,436 ****
--- 437,443 ----
switch (istmt->objtype)
{
case ACL_OBJECT_RELATION:
+ case ACL_OBJECT_VIEW:
case ACL_OBJECT_SEQUENCE:
ExecGrant_Relation(istmt);
break;
*************** objectNamesToOids(GrantObjectType objtyp
*** 477,482 ****
--- 484,490 ----
switch (objtype)
{
case ACL_OBJECT_RELATION:
+ case ACL_OBJECT_VIEW:
case ACL_OBJECT_SEQUENCE:
foreach(cell, objnames)
{
*************** objectNamesToOids(GrantObjectType objtyp
*** 609,614 ****
--- 617,756 ----
return objects;
}
+
+ /*
+ * getNamespacesObjectsOids
+ *
+ * Get all objects of a given type from specified schema list into an Oid list.
+ */
+ static List *
+ getNamespacesObjectsOids(GrantObjectType objtype, List *nspnames)
+ {
+ List *objects = NIL;
+ ListCell *cell;
+ char *nspname;
+ Oid namespaceId;
+
+ switch (objtype)
+ {
+ case ACL_OBJECT_RELATION:
+ foreach(cell, nspnames)
+ {
+ List *relations = NIL;
+
+ nspname = strVal(lfirst(cell));
+ namespaceId = LookupExplicitNamespace(nspname);
+
+ relations = getRelationsInNamespace(namespaceId, RELKIND_RELATION);
+
+ objects = list_union_ptr(relations, objects);
+ }
+ break;
+ case ACL_OBJECT_VIEW:
+ foreach(cell, nspnames)
+ {
+ List *relations = NIL;
+
+ nspname = strVal(lfirst(cell));
+ namespaceId = LookupExplicitNamespace(nspname);
+
+ relations = getRelationsInNamespace(namespaceId, RELKIND_VIEW);
+
+ objects = list_union_ptr(relations, objects);
+ }
+ break;
+ case ACL_OBJECT_SEQUENCE:
+ foreach(cell, nspnames)
+ {
+ List *relations = NIL;
+
+ nspname = strVal(lfirst(cell));
+ namespaceId = LookupExplicitNamespace(nspname);
+
+ relations = getRelationsInNamespace(namespaceId, RELKIND_SEQUENCE);
+
+ objects = list_union_ptr(relations, objects);
+ }
+ break;
+ case ACL_OBJECT_FUNCTION:
+ foreach(cell, nspnames)
+ {
+ ScanKeyData key[1];
+ HeapScanDesc scan;
+ HeapTuple tuple;
+ Relation rel;
+
+ nspname = strVal(lfirst(cell));
+ namespaceId = LookupExplicitNamespace(nspname);
+
+ ScanKeyInit(&key[0],
+ Anum_pg_proc_pronamespace,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(namespaceId));
+
+ rel = heap_open(ProcedureRelationId, AccessShareLock);
+
+ scan = heap_beginscan(rel, SnapshotNow, 1, key);
+
+ while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
+ {
+ objects = lappend_oid(objects, HeapTupleGetOid(tuple));
+ }
+
+ heap_endscan(scan);
+
+ heap_close(rel, AccessShareLock);
+ }
+ break;
+ default:
+ elog(ERROR, "unrecognized GrantStmt.objtype: %d",
+ (int) objtype);
+ }
+
+ return objects;
+ }
+
+ /*
+ * getRelationsInNamespace
+ *
+ * Return list of relations in given namespace filtered by relation kind
+ */
+ static List *
+ getRelationsInNamespace(Oid namespaceId, char relkind)
+ {
+ List *relations = NIL;
+ ScanKeyData key[2];
+ HeapScanDesc scan;
+ HeapTuple tuple;
+ Relation rel;
+
+ ScanKeyInit(&key[0],
+ Anum_pg_class_relnamespace,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(namespaceId));
+
+ ScanKeyInit(&key[1],
+ Anum_pg_class_relkind,
+ BTEqualStrategyNumber, F_CHAREQ,
+ CharGetDatum(relkind));
+
+ rel = heap_open(RelationRelationId, AccessShareLock);
+
+ scan = heap_beginscan(rel, SnapshotNow, 2, key);
+
+ while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
+ {
+ relations = lappend_oid(relations, HeapTupleGetOid(tuple));
+ }
+
+ heap_endscan(scan);
+
+ heap_close(rel, AccessShareLock);
+
+ return relations;
+ }
+
+
/*
* expand_col_privileges
*
*************** ExecGrant_Relation(InternalGrant *istmt)
*** 912,918 ****
* permissions. The OR of table and sequence permissions were already
* checked.
*/
! if (istmt->objtype == ACL_OBJECT_RELATION)
{
if (pg_class_tuple->relkind == RELKIND_SEQUENCE)
{
--- 1054,1060 ----
* permissions. The OR of table and sequence permissions were already
* checked.
*/
! if (istmt->objtype == ACL_OBJECT_RELATION || istmt->objtype == ACL_OBJECT_VIEW)
{
if (pg_class_tuple->relkind == RELKIND_SEQUENCE)
{
*************** ExecGrant_Relation(InternalGrant *istmt)
*** 986,996 ****
aclDatum = SysCacheGetAttr(RELOID, tuple, Anum_pg_class_relacl,
&isNull);
if (isNull)
! old_acl = acldefault(pg_class_tuple->relkind == RELKIND_SEQUENCE ?
! ACL_OBJECT_SEQUENCE : ACL_OBJECT_RELATION,
! ownerId);
else
old_acl = DatumGetAclPCopy(aclDatum);
/* Need an extra copy of original rel ACL for column handling */
old_rel_acl = aclcopy(old_acl);
--- 1128,1150 ----
aclDatum = SysCacheGetAttr(RELOID, tuple, Anum_pg_class_relacl,
&isNull);
if (isNull)
! {
! switch (pg_class_tuple->relkind)
! {
! case RELKIND_SEQUENCE:
! old_acl = acldefault(ACL_OBJECT_SEQUENCE, ownerId);
! break;
! case RELKIND_VIEW:
! old_acl = acldefault(ACL_OBJECT_VIEW, ownerId);
! break;
! default:
! old_acl = acldefault(ACL_OBJECT_RELATION, ownerId);
! }
! }
else
+ {
old_acl = DatumGetAclPCopy(aclDatum);
+ }
/* Need an extra copy of original rel ACL for column handling */
old_rel_acl = aclcopy(old_acl);
*************** pg_class_aclmask(Oid table_oid, Oid role
*** 2434,2442 ****
if (isNull)
{
/* No ACL, so build default ACL */
! acl = acldefault(classForm->relkind == RELKIND_SEQUENCE ?
! ACL_OBJECT_SEQUENCE : ACL_OBJECT_RELATION,
! ownerId);
aclDatum = (Datum) 0;
}
else
--- 2588,2604 ----
if (isNull)
{
/* No ACL, so build default ACL */
! switch (classForm->relkind)
! {
! case RELKIND_SEQUENCE:
! acl = acldefault(ACL_OBJECT_SEQUENCE, ownerId);
! break;
! case RELKIND_VIEW:
! acl = acldefault(ACL_OBJECT_VIEW, ownerId);
! break;
! default:
! acl = acldefault(ACL_OBJECT_RELATION, ownerId);
! }
aclDatum = (Datum) 0;
}
else
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 9a45355..3bc5dc5 100644
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
*************** static bool QueryIsRule = FALSE;
*** 98,103 ****
--- 98,104 ----
typedef struct PrivTarget
{
GrantObjectType objtype;
+ bool is_schema;
List *objs;
} PrivTarget;
*************** static TypeName *TableFuncTypeName(List
*** 449,455 ****
EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN EXTERNAL EXTRACT
FALSE_P FAMILY FETCH FIRST_P FLOAT_P FOLLOWING FOR FORCE FOREIGN FORWARD
! FREEZE FROM FULL FUNCTION
GLOBAL GRANT GRANTED GREATEST GROUP_P
--- 450,456 ----
EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN EXTERNAL EXTRACT
FALSE_P FAMILY FETCH FIRST_P FLOAT_P FOLLOWING FOR FORCE FOREIGN FORWARD
! FREEZE FROM FULL FUNCTION FUNCTIONS
GLOBAL GRANT GRANTED GREATEST GROUP_P
*************** static TypeName *TableFuncTypeName(List
*** 487,499 ****
RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA RESET RESTART
RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROW ROWS RULE
! SAVEPOINT SCHEMA SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE
SERIALIZABLE SERVER SESSION SESSION_USER SET SETOF SHARE
SHOW SIMILAR SIMPLE SMALLINT SOME STABLE STANDALONE_P START STATEMENT
STATISTICS STDIN STDOUT STORAGE STRICT_P STRIP_P SUBSTRING SUPERUSER_P
SYMMETRIC SYSID SYSTEM_P
! TABLE TABLESPACE TEMP TEMPLATE TEMPORARY TEXT_P THEN TIME TIMESTAMP
TO TRAILING TRANSACTION TREAT TRIGGER TRIM TRUE_P
TRUNCATE TRUSTED TYPE_P
--- 488,500 ----
RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA RESET RESTART
RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROW ROWS RULE
! SAVEPOINT SCHEMA SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE SEQUENCES
SERIALIZABLE SERVER SESSION SESSION_USER SET SETOF SHARE
SHOW SIMILAR SIMPLE SMALLINT SOME STABLE STANDALONE_P START STATEMENT
STATISTICS STDIN STDOUT STORAGE STRICT_P STRIP_P SUBSTRING SUPERUSER_P
SYMMETRIC SYSID SYSTEM_P
! TABLE TABLES TABLESPACE TEMP TEMPLATE TEMPORARY TEXT_P THEN TIME TIMESTAMP
TO TRAILING TRANSACTION TREAT TRIGGER TRIM TRUE_P
TRUNCATE TRUSTED TYPE_P
*************** static TypeName *TableFuncTypeName(List
*** 501,507 ****
UPDATE USER USING
VACUUM VALID VALIDATOR VALUE_P VALUES VARCHAR VARIADIC VARYING
! VERBOSE VERSION_P VIEW VOLATILE
WHEN WHERE WHITESPACE_P WINDOW WITH WITHOUT WORK WRAPPER WRITE
--- 502,508 ----
UPDATE USER USING
VACUUM VALID VALIDATOR VALUE_P VALUES VARCHAR VARIADIC VARYING
! VERBOSE VERSION_P VIEW VIEWS VOLATILE
WHEN WHERE WHITESPACE_P WINDOW WITH WITHOUT WORK WRAPPER WRITE
*************** GrantStmt: GRANT privileges ON privilege
*** 4227,4232 ****
--- 4228,4234 ----
n->is_grant = true;
n->privileges = $2;
n->objtype = ($4)->objtype;
+ n->is_schema = ($4)->is_schema;
n->objects = ($4)->objs;
n->grantees = $6;
n->grant_option = $7;
*************** RevokeStmt:
*** 4243,4248 ****
--- 4245,4251 ----
n->grant_option = false;
n->privileges = $2;
n->objtype = ($4)->objtype;
+ n->is_schema = ($4)->is_schema;
n->objects = ($4)->objs;
n->grantees = $6;
n->behavior = $7;
*************** RevokeStmt:
*** 4256,4261 ****
--- 4259,4265 ----
n->grant_option = true;
n->privileges = $5;
n->objtype = ($7)->objtype;
+ n->is_schema = ($7)->is_schema;
n->objects = ($7)->objs;
n->grantees = $9;
n->behavior = $10;
*************** privilege_target:
*** 4338,4343 ****
--- 4342,4348 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_RELATION;
+ n->is_schema = FALSE;
n->objs = $1;
$$ = n;
}
*************** privilege_target:
*** 4345,4350 ****
--- 4350,4364 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_RELATION;
+ n->is_schema = FALSE;
+ n->objs = $2;
+ $$ = n;
+ }
+ | VIEW qualified_name_list
+ {
+ PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
+ n->objtype = ACL_OBJECT_VIEW;
+ n->is_schema = FALSE;
n->objs = $2;
$$ = n;
}
*************** privilege_target:
*** 4352,4357 ****
--- 4366,4372 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_SEQUENCE;
+ n->is_schema = FALSE;
n->objs = $2;
$$ = n;
}
*************** privilege_target:
*** 4359,4364 ****
--- 4374,4380 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_FDW;
+ n->is_schema = FALSE;
n->objs = $4;
$$ = n;
}
*************** privilege_target:
*** 4366,4371 ****
--- 4382,4388 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_FOREIGN_SERVER;
+ n->is_schema = FALSE;
n->objs = $3;
$$ = n;
}
*************** privilege_target:
*** 4373,4378 ****
--- 4390,4396 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_FUNCTION;
+ n->is_schema = FALSE;
n->objs = $2;
$$ = n;
}
*************** privilege_target:
*** 4380,4385 ****
--- 4398,4404 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_DATABASE;
+ n->is_schema = FALSE;
n->objs = $2;
$$ = n;
}
*************** privilege_target:
*** 4387,4392 ****
--- 4406,4412 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_LANGUAGE;
+ n->is_schema = FALSE;
n->objs = $2;
$$ = n;
}
*************** privilege_target:
*** 4394,4399 ****
--- 4414,4420 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_NAMESPACE;
+ n->is_schema = FALSE;
n->objs = $2;
$$ = n;
}
*************** privilege_target:
*** 4401,4409 ****
--- 4422,4463 ----
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_TABLESPACE;
+ n->is_schema = FALSE;
n->objs = $2;
$$ = n;
}
+ | ALL TABLES IN_P name_list
+ {
+ PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
+ n->objtype = ACL_OBJECT_RELATION;
+ n->is_schema = TRUE;
+ n->objs = $4;
+ $$ = n;
+ }
+ | ALL VIEWS IN_P name_list
+ {
+ PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
+ n->objtype = ACL_OBJECT_VIEW;
+ n->is_schema = TRUE;
+ n->objs = $4;
+ $$ = n;
+ }
+ | ALL SEQUENCES IN_P name_list
+ {
+ PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
+ n->objtype = ACL_OBJECT_SEQUENCE;
+ n->is_schema = TRUE;
+ n->objs = $4;
+ $$ = n;
+ }
+ | ALL FUNCTIONS IN_P name_list
+ {
+ PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
+ n->objtype = ACL_OBJECT_FUNCTION;
+ n->is_schema = TRUE;
+ n->objs = $4;
+ $$ = n;
+ }
;
*************** unreserved_keyword:
*** 10212,10217 ****
--- 10266,10272 ----
| FORCE
| FORWARD
| FUNCTION
+ | FUNCTIONS
| GLOBAL
| GRANTED
| HANDLER
*************** unreserved_keyword:
*** 10321,10326 ****
--- 10376,10382 ----
| SECOND_P
| SECURITY
| SEQUENCE
+ | SEQUENCES
| SERIALIZABLE
| SERVER
| SESSION
*************** unreserved_keyword:
*** 10341,10346 ****
--- 10397,10403 ----
| SUPERUSER_P
| SYSID
| SYSTEM_P
+ | TABLES
| TABLESPACE
| TEMP
| TEMPLATE
*************** unreserved_keyword:
*** 10365,10370 ****
--- 10422,10428 ----
| VARYING
| VERSION_P
| VIEW
+ | VIEWS
| VOLATILE
| WHITESPACE_P
| WITHOUT
diff --git a/src/backend/utils/adt/acl.c b/src/backend/utils/adt/acl.c
index 334823b..ddd92e7 100644
*** a/src/backend/utils/adt/acl.c
--- b/src/backend/utils/adt/acl.c
*************** acldefault(GrantObjectType objtype, Oid
*** 609,614 ****
--- 609,615 ----
owner_default = ACL_NO_RIGHTS;
break;
case ACL_OBJECT_RELATION:
+ case ACL_OBJECT_VIEW:
world_default = ACL_NO_RIGHTS;
owner_default = ACL_ALL_RIGHTS_RELATION;
break;
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 71c864a..3c79a1c 100644
*** a/src/include/nodes/parsenodes.h
--- b/src/include/nodes/parsenodes.h
*************** typedef struct AlterDomainStmt
*** 1180,1186 ****
typedef enum GrantObjectType
{
ACL_OBJECT_COLUMN, /* column */
! ACL_OBJECT_RELATION, /* table, view */
ACL_OBJECT_SEQUENCE, /* sequence */
ACL_OBJECT_DATABASE, /* database */
ACL_OBJECT_FDW, /* foreign-data wrapper */
--- 1180,1186 ----
typedef enum GrantObjectType
{
ACL_OBJECT_COLUMN, /* column */
! ACL_OBJECT_RELATION, /* table */
ACL_OBJECT_SEQUENCE, /* sequence */
ACL_OBJECT_DATABASE, /* database */
ACL_OBJECT_FDW, /* foreign-data wrapper */
*************** typedef enum GrantObjectType
*** 1188,1194 ****
ACL_OBJECT_FUNCTION, /* function */
ACL_OBJECT_LANGUAGE, /* procedural language */
ACL_OBJECT_NAMESPACE, /* namespace */
! ACL_OBJECT_TABLESPACE /* tablespace */
} GrantObjectType;
typedef struct GrantStmt
--- 1188,1195 ----
ACL_OBJECT_FUNCTION, /* function */
ACL_OBJECT_LANGUAGE, /* procedural language */
ACL_OBJECT_NAMESPACE, /* namespace */
! ACL_OBJECT_TABLESPACE, /* tablespace */
! ACL_OBJECT_VIEW, /* view */
} GrantObjectType;
typedef struct GrantStmt
*************** typedef struct GrantStmt
*** 1196,1201 ****
--- 1197,1204 ----
NodeTag type;
bool is_grant; /* true = GRANT, false = REVOKE */
GrantObjectType objtype; /* kind of object being operated on */
+ bool is_schema; /* if true we want all objects
+ * of objtype in schema */
List *objects; /* list of RangeVar nodes, FuncWithArgs nodes,
* or plain names (as Value strings) */
List *privileges; /* list of AccessPriv nodes */
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 67e9cb4..a6ae56c 100644
*** a/src/include/parser/kwlist.h
--- b/src/include/parser/kwlist.h
*************** PG_KEYWORD("freeze", FREEZE, TYPE_FUNC_N
*** 163,168 ****
--- 163,169 ----
PG_KEYWORD("from", FROM, RESERVED_KEYWORD)
PG_KEYWORD("full", FULL, TYPE_FUNC_NAME_KEYWORD)
PG_KEYWORD("function", FUNCTION, UNRESERVED_KEYWORD)
+ PG_KEYWORD("functions", FUNCTIONS, UNRESERVED_KEYWORD)
PG_KEYWORD("global", GLOBAL, UNRESERVED_KEYWORD)
PG_KEYWORD("grant", GRANT, RESERVED_KEYWORD)
PG_KEYWORD("granted", GRANTED, UNRESERVED_KEYWORD)
*************** PG_KEYWORD("second", SECOND_P, UNRESERVE
*** 328,333 ****
--- 329,335 ----
PG_KEYWORD("security", SECURITY, UNRESERVED_KEYWORD)
PG_KEYWORD("select", SELECT, RESERVED_KEYWORD)
PG_KEYWORD("sequence", SEQUENCE, UNRESERVED_KEYWORD)
+ PG_KEYWORD("sequences", SEQUENCES, UNRESERVED_KEYWORD)
PG_KEYWORD("serializable", SERIALIZABLE, UNRESERVED_KEYWORD)
PG_KEYWORD("server", SERVER, UNRESERVED_KEYWORD)
PG_KEYWORD("session", SESSION, UNRESERVED_KEYWORD)
*************** PG_KEYWORD("symmetric", SYMMETRIC, RESER
*** 356,361 ****
--- 358,364 ----
PG_KEYWORD("sysid", SYSID, UNRESERVED_KEYWORD)
PG_KEYWORD("system", SYSTEM_P, UNRESERVED_KEYWORD)
PG_KEYWORD("table", TABLE, RESERVED_KEYWORD)
+ PG_KEYWORD("tables", TABLES, UNRESERVED_KEYWORD)
PG_KEYWORD("tablespace", TABLESPACE, UNRESERVED_KEYWORD)
PG_KEYWORD("temp", TEMP, UNRESERVED_KEYWORD)
PG_KEYWORD("template", TEMPLATE, UNRESERVED_KEYWORD)
*************** PG_KEYWORD("varying", VARYING, UNRESERVE
*** 396,401 ****
--- 399,405 ----
PG_KEYWORD("verbose", VERBOSE, TYPE_FUNC_NAME_KEYWORD)
PG_KEYWORD("version", VERSION_P, UNRESERVED_KEYWORD)
PG_KEYWORD("view", VIEW, UNRESERVED_KEYWORD)
+ PG_KEYWORD("views", VIEWS, UNRESERVED_KEYWORD)
PG_KEYWORD("volatile", VOLATILE, UNRESERVED_KEYWORD)
PG_KEYWORD("when", WHEN, RESERVED_KEYWORD)
PG_KEYWORD("where", WHERE, RESERVED_KEYWORD)
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index a17ff59..043c0f3 100644
*** a/src/test/regress/expected/privileges.out
--- b/src/test/regress/expected/privileges.out
*************** SELECT has_table_privilege('regressuser1
*** 815,820 ****
--- 815,849 ----
t
(1 row)
+ -- Grant on all objects of given type in a schema
+ RESET SESSION AUTHORIZATION;
+ REVOKE ALL ON ALL TABLES IN public FROM regressuser1;
+ SELECT has_table_privilege('regressuser1', 'atest1', 'SELECT'); -- false
+ has_table_privilege
+ ---------------------
+ f
+ (1 row)
+
+ REVOKE ALL ON ALL FUNCTIONS IN public FROM regressuser1;
+ SET SESSION AUTHORIZATION regressuser1;
+ SELECT testfunc2(5); -- fail
+ ERROR: permission denied for function testfunc2
+ RESET SESSION AUTHORIZATION;
+ GRANT ALL ON ALL TABLES IN public TO regressuser1;
+ SELECT has_table_privilege('regressuser1', 'atest2', 'SELECT'); -- true
+ has_table_privilege
+ ---------------------
+ t
+ (1 row)
+
+ GRANT ALL ON ALL FUNCTIONS IN public TO regressuser1;
+ SET SESSION AUTHORIZATION regressuser1;
+ SELECT testfunc2(5); -- ok
+ testfunc2
+ -----------
+ 15
+ (1 row)
+
-- clean up
\c
DROP FUNCTION testfunc2(int);
*************** DROP TABLE atestp2;
*** 839,844 ****
--- 868,875 ----
DROP GROUP regressgroup1;
DROP GROUP regressgroup2;
REVOKE USAGE ON LANGUAGE sql FROM regressuser1;
+ REVOKE ALL ON ALL TABLES IN public FROM regressuser1;
+ REVOKE ALL ON ALL FUNCTIONS IN public FROM regressuser1;
DROP USER regressuser1;
DROP USER regressuser2;
DROP USER regressuser3;
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index 5aa1012..e574c4d 100644
*** a/src/test/regress/sql/privileges.sql
--- b/src/test/regress/sql/privileges.sql
*************** SELECT has_table_privilege('regressuser3
*** 469,474 ****
--- 469,500 ----
SELECT has_table_privilege('regressuser1', 'atest4', 'SELECT WITH GRANT OPTION'); -- true
+ -- Grant on all objects of given type in a schema
+
+ RESET SESSION AUTHORIZATION;
+
+ REVOKE ALL ON ALL TABLES IN public FROM regressuser1;
+
+ SELECT has_table_privilege('regressuser1', 'atest1', 'SELECT'); -- false
+
+ REVOKE ALL ON ALL FUNCTIONS IN public FROM regressuser1;
+
+ SET SESSION AUTHORIZATION regressuser1;
+
+ SELECT testfunc2(5); -- fail
+
+ RESET SESSION AUTHORIZATION;
+
+ GRANT ALL ON ALL TABLES IN public TO regressuser1;
+
+ SELECT has_table_privilege('regressuser1', 'atest2', 'SELECT'); -- true
+
+ GRANT ALL ON ALL FUNCTIONS IN public TO regressuser1;
+
+ SET SESSION AUTHORIZATION regressuser1;
+
+ SELECT testfunc2(5); -- ok
+
-- clean up
\c
*************** DROP GROUP regressgroup1;
*** 497,502 ****
--- 523,530 ----
DROP GROUP regressgroup2;
REVOKE USAGE ON LANGUAGE sql FROM regressuser1;
+ REVOKE ALL ON ALL TABLES IN public FROM regressuser1;
+ REVOKE ALL ON ALL FUNCTIONS IN public FROM regressuser1;
DROP USER regressuser1;
DROP USER regressuser2;
DROP USER regressuser3;
view thread (21+ messages) latest in thread
Message-ID: <4A38A956.8080600@pjmodos.net>
Permalink: ../4A38A956.8080600@pjmodos.net/
Also on: postgresql.org/message-id/4A38A956.8080600@pjmodos.net
copy link · copy postgr.es · copy git am
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: pjmodos@pjmodos.net
Subject: Re: GRANT ON ALL IN schema
In-Reply-To: <4A38A956.8080600@pjmodos.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox