Petr Jelinek wrote:
So, here is the first version of the patch.
Attached is v2 with slightly improved code, nothing has changed
feature-wise.
--
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..0bf4eb1 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_concat(objects, relations);
+ }
+ 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_concat(objects, relations);
+ }
+ 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_concat(objects, relations);
+ }
+ 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 ac17b93..8d543b4 100644
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
*************** static bool QueryIsRule = FALSE;
*** 99,104 ****
--- 99,105 ----
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
*** 4216,4221 ****
--- 4217,4223 ----
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:
*** 4232,4237 ****
--- 4234,4240 ----
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:
*** 4245,4250 ****
--- 4248,4254 ----
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:
*** 4327,4332 ****
--- 4331,4337 ----
{
PrivTarget *n = (PrivTarget *)
palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_RELATION;
+ n->is_schema = FALSE;
n->objs = $1;
$$ = n;
}
*************** privilege_target:
*** 4334,4339 ****
--- 4339,4353 ----
{
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:
*** 4341,4346 ****
--- 4355,4361 ----
{
PrivTarget *n = (PrivTarget *)
palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_SEQUENCE;
+ n->is_schema = FALSE;
n->objs = $2;
$$ = n;
}
*************** privilege_target:
*** 4348,4353 ****
--- 4363,4369 ----
{
PrivTarget *n = (PrivTarget *)
palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_FDW;
+ n->is_schema = FALSE;
n->objs = $4;
$$ = n;
}
*************** privilege_target:
*** 4355,4360 ****
--- 4371,4377 ----
{
PrivTarget *n = (PrivTarget *)
palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_FOREIGN_SERVER;
+ n->is_schema = FALSE;
n->objs = $3;
$$ = n;
}
*************** privilege_target:
*** 4362,4367 ****
--- 4379,4385 ----
{
PrivTarget *n = (PrivTarget *)
palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_FUNCTION;
+ n->is_schema = FALSE;
n->objs = $2;
$$ = n;
}
*************** privilege_target:
*** 4369,4374 ****
--- 4387,4393 ----
{
PrivTarget *n = (PrivTarget *)
palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_DATABASE;
+ n->is_schema = FALSE;
n->objs = $2;
$$ = n;
}
*************** privilege_target:
*** 4376,4381 ****
--- 4395,4401 ----
{
PrivTarget *n = (PrivTarget *)
palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_LANGUAGE;
+ n->is_schema = FALSE;
n->objs = $2;
$$ = n;
}
*************** privilege_target:
*** 4383,4388 ****
--- 4403,4409 ----
{
PrivTarget *n = (PrivTarget *)
palloc(sizeof(PrivTarget));
n->objtype = ACL_OBJECT_NAMESPACE;
+ n->is_schema = FALSE;
n->objs = $2;
$$ = n;
}
*************** privilege_target:
*** 4390,4398 ****
--- 4411,4452 ----
{
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:
*** 10201,10206 ****
--- 10255,10261 ----
| FORCE
| FORWARD
| FUNCTION
+ | FUNCTIONS
| GLOBAL
| GRANTED
| HANDLER
*************** unreserved_keyword:
*** 10310,10315 ****
--- 10365,10371 ----
| SECOND_P
| SECURITY
| SEQUENCE
+ | SEQUENCES
| SERIALIZABLE
| SERVER
| SESSION
*************** unreserved_keyword:
*** 10330,10335 ****
--- 10386,10392 ----
| SUPERUSER_P
| SYSID
| SYSTEM_P
+ | TABLES
| TABLESPACE
| TEMP
| TEMPLATE
*************** unreserved_keyword:
*** 10354,10359 ****
--- 10411,10417 ----
| 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 a108b80..fa040df 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;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
