pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feedGRANT ON ALL IN schema
21+ messages / 8 participants
[nested] [flat]
* GRANT ON ALL IN schema
@ 2009-06-16 15:50 Petr Jelinek <pjmodos@pjmodos.net>
0 siblings, 1 reply; 21+ messages in thread
From: Petr Jelinek @ 2009-06-16 15:50 UTC (permalink / raw)
To: pgsql-hackers
Hi all,
I am thinking about implementing GRANT ON ALL TABLES IN schema TODO
item. I saw many people sending proposals to the list but nobody seemed
to actually do anything. I have few questions and problems to iron out
before I can start the implementation. I would also like to note that I
am not going to implement the second part (GRANT ON NEW TABLES) of the
proposed TODO item as there seems to be better solution to this which is
Default ACLs (http://wiki.postgresql.org/wiki/DefaultACL) - btw is
anybody working on that ? If not I am interested in doing it also as a
complementary patch to this one.
Anyway back to my thoughts about this patch. First of all I see problem
with the proposed syntax. For this syntax I think TABLES (FUNCTIONS,
SEQUENCES, etc) would have to be added to keywords which is problematic
because there are views named tables, sequences, views in
information_schema so we can't really make them keywords. I have no idea
how to get around this and I don't see good alternative syntax either.
This is main and only real problem I have.
The other stuff is minor, like do we want this only for tables,
sequences, functions and views or do we want it for every object for
which we have GRANT command. Also in standard GRANT there is no
distinction between table and view, I guess in this case there should be.
--
Regards
Petr Jelinek (PJMODOS)
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: GRANT ON ALL IN schema
@ 2009-06-16 18:14 Petr Jelinek <pjmodos@pjmodos.net>
parent: Petr Jelinek <pjmodos@pjmodos.net>
0 siblings, 1 reply; 21+ messages in thread
From: Petr Jelinek @ 2009-06-16 18:14 UTC (permalink / raw)
To: pgsql-hackers
Petr Jelinek wrote:
> Anyway back to my thoughts about this patch. First of all I see problem
> with the proposed syntax. For this syntax I think TABLES (FUNCTIONS,
> SEQUENCES, etc) would have to be added to keywords which is problematic
> because there are views named tables, sequences, views in
> information_schema so we can't really make them keywords. I have no idea
> how to get around this and I don't see good alternative syntax either.
> This is main and only real problem I have.
Erm, seems like the problem was just me overlooking something in gram.y
(I forgot to add those keywords to unreserved_keyword) so no real
problems, but I'd still like to hear answers to those other questions in
my previous email.
--
Regards
Petr Jelinek (PJMODOS)
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: GRANT ON ALL IN schema
@ 2009-06-17 08:29 Petr Jelinek <pjmodos@pjmodos.net>
parent: Petr Jelinek <pjmodos@pjmodos.net>
0 siblings, 2 replies; 21+ messages in thread
From: Petr Jelinek @ 2009-06-17 08:29 UTC (permalink / raw)
To: pgsql-hackers
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;
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: GRANT ON ALL IN schema
@ 2009-06-17 13:44 Peter Eisentraut <peter_e@gmx.net>
parent: Petr Jelinek <pjmodos@pjmodos.net>
1 sibling, 4 replies; 21+ messages in thread
From: Peter Eisentraut @ 2009-06-17 13:44 UTC (permalink / raw)
To: pgsql-hackers; +Cc: Petr Jelinek <pjmodos@pjmodos.net>
On Wednesday 17 June 2009 11:29:10 Petr Jelinek wrote:
> 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.
I think you should design this with a bit wider scope. Instead of just "all
tables in this schema", think "all tables satisfying some condition". It has
been requested, for example, to be able to grant on all tables that match a
pattern.
> 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.
As far as GRANT is concerned, a view is a table, so I would omit the
VIEW/VIEWS stuff completely.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: GRANT ON ALL IN schema
@ 2009-06-17 14:09 Stephen Frost <sfrost@snowman.net>
parent: Peter Eisentraut <peter_e@gmx.net>
3 siblings, 1 reply; 21+ messages in thread
From: Stephen Frost @ 2009-06-17 14:09 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: pgsql-hackers@postgresql.org, Petr Jelinek <pjmodos@pjmodos.net>
Peter,
* Peter Eisentraut (peter_e@gmx.net) wrote:
> > 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.
>
> As far as GRANT is concerned, a view is a table, so I would omit the
> VIEW/VIEWS stuff completely.
I would disagree with this. While an explicit GRANT doesn't need to
care, because you can't have a view and a table with the same name, I
feel *users* (like me) make a distinction there and may want to limit
the grant to just views or just tables.
What we do here will also impact the DefaultACL system that I'm working
on since I think we should be consistant between these two systems.
http://wiki.postgresql.org/wiki/DefaultACL
I don't like the idea that a 'GRANT ALL' would actually change default
ACLs for a schema though. These are two separate and distinct things-
one is implementing a change to existing objects, the other is setting a
default for new objects. Mixing them would lead to confusion.
Thanks,
Stephen
Attachments:
[application/pgp-signature] signature.asc (196B, ../../20090617140904.GL20436@tamriel.snowman.net/2-signature.asc)
download
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: GRANT ON ALL IN schema
@ 2009-06-17 14:15 Tom Lane <tgl@sss.pgh.pa.us>
parent: Peter Eisentraut <peter_e@gmx.net>
3 siblings, 1 reply; 21+ messages in thread
From: Tom Lane @ 2009-06-17 14:15 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: pgsql-hackers@postgresql.org, Petr Jelinek <pjmodos@pjmodos.net>
Peter Eisentraut <peter_e@gmx.net> writes:
> I think you should design this with a bit wider scope. Instead of just "all
> tables in this schema", think "all tables satisfying some condition". It has
> been requested, for example, to be able to grant on all tables that match a
> pattern.
I'm against that. Functionality of that sort is available now if you
really need it (write a plpgsql loop around an EXECUTE) and it's fairly
hard to see a clean syntax that is significantly more general than
"GRANT ON schema.*". In particular I strongly advise against getting
into supporting user-defined predicates in GRANT. There are good
reasons for not having utility statements evaluate random expressions.
regards, tom lane
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: GRANT ON ALL IN schema
@ 2009-06-17 14:31 Petr Jelinek <pjmodos@pjmodos.net>
parent: Peter Eisentraut <peter_e@gmx.net>
3 siblings, 0 replies; 21+ messages in thread
From: Petr Jelinek @ 2009-06-17 14:31 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: pgsql-hackers
Peter Eisentraut wrote:
> I think you should design this with a bit wider scope. Instead of just "all
> tables in this schema", think "all tables satisfying some condition". It has
> been requested, for example, to be able to grant on all tables that match a
> pattern.
>
Well, that's certainly possible to do. But I am not sure what kind of
conditions (besides the name), nor I don't see any sane grammar for this
(maybe something like GRANT SELECT ON TABLE WHERE NAME LIKE '%foo' but
that's far to weird). That all tables in this schema thing was agreed on
on this mailing list and put on TODO so I thought it's something people
want in this form (I know I needed it myself).
> As far as GRANT is concerned, a view is a table, so I would omit the
> VIEW/VIEWS stuff completely.
>
This maybe true for underlying implementation and for granting
permissions to a single object, but you might want to grant select on
all views without granting it to all tables in the schema. And as I said
having VIEWS and not VIEW just seems weird. Also I don't see why you
would want to add possibility of specifying stricter conditions for
objects and at the same time remove possibility of distinguishing
between tables and views.
--
Regards
Petr Jelinek (PJMODOS)
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: GRANT ON ALL IN schema
@ 2009-06-17 14:35 Stephen Frost <sfrost@snowman.net>
parent: Petr Jelinek <pjmodos@pjmodos.net>
1 sibling, 1 reply; 21+ messages in thread
From: Stephen Frost @ 2009-06-17 14:35 UTC (permalink / raw)
To: Petr Jelinek <pjmodos@pjmodos.net>; +Cc: pgsql-hackers
Petr,
* Petr Jelinek (pjmodos@pjmodos.net) wrote:
> So, here is the first version of the patch.
Neat, thanks! Some initial comments:
You should read through this:
http://wiki.postgresql.org/wiki/Submitting_a_Patch
First big thing is that the patch should be a context diff. I would
also recommend you put it up on the CommitFest wiki if it's not there
yet. You might also write up a wiki page on it and link to it from the
8.5 WIP section under
http://wiki.postgresql.org/wiki/Developer_and_Contributor_Resources
The http://wiki.postgresql.org/wiki/Developer_FAQ can also help if you
havn't checked it out yet.
> It includes functionality itself, simple regression test and also very
> simple documentation.
Excellent!
> Any comments/suggestions are welcome (I especially wonder if the use of
> list_union_ptr is acceptable).
I'll try to take a look at the actual patch in more detail later this
week.
Thanks!
Stephen
Attachments:
[application/pgp-signature] signature.asc (196B, ../../20090617143555.GM20436@tamriel.snowman.net/2-signature.asc)
download
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: GRANT ON ALL IN schema
@ 2009-06-17 14:40 Petr Jelinek <pjmodos@pjmodos.net>
parent: Stephen Frost <sfrost@snowman.net>
0 siblings, 1 reply; 21+ messages in thread
From: Petr Jelinek @ 2009-06-17 14:40 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; +Cc: Peter Eisentraut <peter_e@gmx.net>; pgsql-hackers
Stephen Frost wrote:
> I don't like the idea that a 'GRANT ALL' would actually change default
> ACLs for a schema though. These are two separate and distinct things-
> one is implementing a change to existing objects, the other is setting a
> default for new objects. Mixing them would lead to confusion.
>
It doesn't. I stated that I am not implementing the second part of the
TODO item in my first email specifically for this reason (the second
part was GRANT ON NEW TABLES).
--
Regards
Petr Jelinek (PJMODOS)
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: GRANT ON ALL IN schema
@ 2009-06-17 14:42 Petr Jelinek <pjmodos@pjmodos.net>
parent: Stephen Frost <sfrost@snowman.net>
0 siblings, 1 reply; 21+ messages in thread
From: Petr Jelinek @ 2009-06-17 14:42 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; +Cc: pgsql-hackers
Stephen Frost wrote:
> http://wiki.postgresql.org/wiki/Submitting_a_Patch
>
> First big thing is that the patch should be a context diff. I would
>
It is context diff, at least I think so, I followed the instructions on
wiki on how to make context patch from git repo.
> also recommend you put it up on the CommitFest wiki if it's not there
> yet. You might also write up a wiki page on it and link to it from the
> 8.5 WIP section under
> http://wiki.postgresql.org/wiki/Developer_and_Contributor_Resources
>
Will do.
> I'll try to take a look at the actual patch in more detail later this
> week.
>
Thanks.
--
Regards
Petr Jelinek (PJMODOS)
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: GRANT ON ALL IN schema
@ 2009-06-17 14:44 Stephen Frost <sfrost@snowman.net>
parent: Petr Jelinek <pjmodos@pjmodos.net>
0 siblings, 0 replies; 21+ messages in thread
From: Stephen Frost @ 2009-06-17 14:44 UTC (permalink / raw)
To: Petr Jelinek <pjmodos@pjmodos.net>; +Cc: Peter Eisentraut <peter_e@gmx.net>; pgsql-hackers
* Petr Jelinek (pjmodos@pjmodos.net) wrote:
> Stephen Frost wrote:
>> I don't like the idea that a 'GRANT ALL' would actually change default
>> ACLs for a schema though. These are two separate and distinct things-
>> one is implementing a change to existing objects, the other is setting a
>> default for new objects. Mixing them would lead to confusion.
>
> It doesn't. I stated that I am not implementing the second part of the
> TODO item in my first email specifically for this reason (the second
> part was GRANT ON NEW TABLES).
Right.. I was arguing against a few folks who had mentioned they'd like
to see that on IRC and in the past, not against your implementation.
Thanks,
Stephen
Attachments:
[application/pgp-signature] signature.asc (196B, ../../20090617144407.GO20436@tamriel.snowman.net/2-signature.asc)
download
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: GRANT ON ALL IN schema
@ 2009-06-17 14:44 Peter Eisentraut <peter_e@gmx.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 21+ messages in thread
From: Peter Eisentraut @ 2009-06-17 14:44 UTC (permalink / raw)
To: pgsql-hackers; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Petr Jelinek <pjmodos@pjmodos.net>
On Wednesday 17 June 2009 17:15:04 Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
> > I think you should design this with a bit wider scope. Instead of just
> > "all tables in this schema", think "all tables satisfying some
> > condition". It has been requested, for example, to be able to grant on
> > all tables that match a pattern.
>
> I'm against that. Functionality of that sort is available now if you
> really need it (write a plpgsql loop around an EXECUTE) and it's fairly
> hard to see a clean syntax that is significantly more general than
> "GRANT ON schema.*". In particular I strongly advise against getting
> into supporting user-defined predicates in GRANT. There are good
> reasons for not having utility statements evaluate random expressions.
Why don't we tell people to write a plpgsql loop for the schema.* case as
well?
I haven't seen any evidence that the schema.* case is more common than other
bulk DDL cases like "matches pattern" or "owned by $user" or "grant on all
functions that are not security definer" etc.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: GRANT ON ALL IN schema
@ 2009-06-17 14:47 Tom Lane <tgl@sss.pgh.pa.us>
parent: Peter Eisentraut <peter_e@gmx.net>
0 siblings, 1 reply; 21+ messages in thread
From: Tom Lane @ 2009-06-17 14:47 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: pgsql-hackers@postgresql.org, Petr Jelinek <pjmodos@pjmodos.net>
Peter Eisentraut <peter_e@gmx.net> writes:
> Why don't we tell people to write a plpgsql loop for the schema.* case as
> well?
Indeed, why not? This all seems much more like gilding the lily than
delivering useful new capability. The default-ACL stuff that Stephen
is working on seems far more important in practice.
regards, tom lane
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: GRANT ON ALL IN schema
@ 2009-06-17 14:48 Stephen Frost <sfrost@snowman.net>
parent: Petr Jelinek <pjmodos@pjmodos.net>
0 siblings, 0 replies; 21+ messages in thread
From: Stephen Frost @ 2009-06-17 14:48 UTC (permalink / raw)
To: Petr Jelinek <pjmodos@pjmodos.net>; +Cc: pgsql-hackers
* Petr Jelinek (pjmodos@pjmodos.net) wrote:
> It is context diff, at least I think so, I followed the instructions on
> wiki on how to make context patch from git repo.
err, sorry, tbh I just looked at the 'diff --git' line and didn't see
any '-c'.. Trying to do too much at one time, I'm afraid. :)
Thanks,
Stephen
Attachments:
[application/pgp-signature] signature.asc (196B, ../../20090617144815.GP20436@tamriel.snowman.net/2-signature.asc)
download
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: GRANT ON ALL IN schema
@ 2009-06-17 14:59 Petr Jelinek <pjmodos@pjmodos.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 21+ messages in thread
From: Petr Jelinek @ 2009-06-17 14:59 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Peter Eisentraut <peter_e@gmx.net>; pgsql-hackers
Tom Lane erote:
> Peter Eisentraut <peter_e@gmx.net> writes:
>
>> Why don't we tell people to write a plpgsql loop for the schema.* case as
>> well?
>>
>
> Indeed, why not? This all seems much more like gilding the lily than
> delivering useful new capability. The default-ACL stuff that Stephen
> is working on seems far more important in practice.
>
I agree that Default ACLs are more important and I already offered
Stephen help on that. But I've seen countless requests for granting on
all tables to a user and I already got some positive feedback outside of
the list, so I believe there is demand for this. Also to paraphrase you
Tom, by that logic you can tell people to write half of administration
functionality as plpgsql functions.
--
Regards
Petr Jelinek (PJMODOS)
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: GRANT ON ALL IN schema
@ 2009-06-17 16:25 Guillaume Smet <guillaume.smet@gmail.com>
parent: Petr Jelinek <pjmodos@pjmodos.net>
0 siblings, 2 replies; 21+ messages in thread
From: Guillaume Smet @ 2009-06-17 16:25 UTC (permalink / raw)
To: Petr Jelinek <pjmodos@pjmodos.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Peter Eisentraut <peter_e@gmx.net>; pgsql-hackers
2009/6/17 Petr Jelinek <pjmodos@pjmodos.net>:
> I agree that Default ACLs are more important and I already offered Stephen
> help on that. But I've seen countless requests for granting on all tables to
> a user and I already got some positive feedback outside of the list, so I
> believe there is demand for this. Also to paraphrase you Tom, by that logic
> you can tell people to write half of administration functionality as plpgsql
> functions.
Indeed.
How to do default ACLs and wildcards for GRANT is by far the most
common question asked by our customers. And they don't understand why
it's not by default in PostgreSQL.
Installing a script/function for that on every database is just painful.
--
Guillaume
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: GRANT ON ALL IN schema
@ 2009-06-17 16:45 Greg Stark <greg.stark@enterprisedb.com>
parent: Guillaume Smet <guillaume.smet@gmail.com>
1 sibling, 1 reply; 21+ messages in thread
From: Greg Stark @ 2009-06-17 16:45 UTC (permalink / raw)
To: Guillaume Smet <guillaume.smet@gmail.com>; +Cc: Petr Jelinek <pjmodos@pjmodos.net>; Tom Lane <tgl@sss.pgh.pa.us>; Peter Eisentraut <peter_e@gmx.net>; pgsql-hackers
Isn't the answer to grant permissions to a role and then just put
people in that role?
--
Greg
On 17 Jun 2009, at 17:25, Guillaume Smet <guillaume.smet@gmail.com>
wrote:
> 2009/6/17 Petr Jelinek <pjmodos@pjmodos.net>:
>> I agree that Default ACLs are more important and I already offered
>> Stephen
>> help on that. But I've seen countless requests for granting on all
>> tables to
>> a user and I already got some positive feedback outside of the
>> list, so I
>> believe there is demand for this. Also to paraphrase you Tom, by
>> that logic
>> you can tell people to write half of administration functionality
>> as plpgsql
>> functions.
>
> Indeed.
>
> How to do default ACLs and wildcards for GRANT is by far the most
> common question asked by our customers. And they don't understand why
> it's not by default in PostgreSQL.
>
> Installing a script/function for that on every database is just
> painful.
>
> --
> Guillaume
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: GRANT ON ALL IN schema
@ 2009-06-17 16:52 Petr Jelinek <pjmodos@pjmodos.net>
parent: Greg Stark <greg.stark@enterprisedb.com>
0 siblings, 0 replies; 21+ messages in thread
From: Petr Jelinek @ 2009-06-17 16:52 UTC (permalink / raw)
To: Greg Stark <greg.stark@enterprisedb.com>; +Cc: Guillaume Smet <guillaume.smet@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; Peter Eisentraut <peter_e@gmx.net>; pgsql-hackers
Greg Stark wrote:
> Isn't the answer to grant permissions to a role and then just put
> people in that role?
>
Still have to give permissions at least to that role.
--
Regards
Petr Jelinek (PJMODOS)
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: GRANT ON ALL IN schema
@ 2009-06-17 17:27 Robert Haas <robertmhaas@gmail.com>
parent: Guillaume Smet <guillaume.smet@gmail.com>
1 sibling, 1 reply; 21+ messages in thread
From: Robert Haas @ 2009-06-17 17:27 UTC (permalink / raw)
To: Guillaume Smet <guillaume.smet@gmail.com>; +Cc: Petr Jelinek <pjmodos@pjmodos.net>; Tom Lane <tgl@sss.pgh.pa.us>; Peter Eisentraut <peter_e@gmx.net>; pgsql-hackers
On Wed, Jun 17, 2009 at 12:25 PM, Guillaume
Smet<guillaume.smet@gmail.com> wrote:
> 2009/6/17 Petr Jelinek <pjmodos@pjmodos.net>:
>> I agree that Default ACLs are more important and I already offered Stephen
>> help on that. But I've seen countless requests for granting on all tables to
>> a user and I already got some positive feedback outside of the list, so I
>> believe there is demand for this. Also to paraphrase you Tom, by that logic
>> you can tell people to write half of administration functionality as plpgsql
>> functions.
>
> Indeed.
>
> How to do default ACLs and wildcards for GRANT is by far the most
> common question asked by our customers. And they don't understand why
> it's not by default in PostgreSQL.
>
> Installing a script/function for that on every database is just painful.
It's not just GRANT, either. I have a script that synchronizes data
from <other database product> into PostgreSQL. It runs out of cron.
I actually had to set it up so that it counts the total number of rows
that it has inserted and fires of an ANALYZE when it hits a certain
threshold (that might not be necessary with autovacuum, but this is
8.1); otherwise, the statistics can get so far from reality that the
sync script never finishes, because the later stages of the sync query
local data modified by earlier stages of the sync. This is not a
joke; when there are heavy data modifications, the script MUST fire an
ANALYZE midway through to complete in a reasonable amount of time.
Now it just so happens that this application runs inside its own
schema, and that it doesn't have permission to vacuum any of the other
schemas, including the catalog tables. So what do you think happens
when it kicks off an ANALYZE? A huge pile of warning messages.
Now, since I've been reading pgsql-hackers religiously for a year now,
I know that it's very easy to solve this problem by writing a table to
issue a query against pg_class and then use quote_ident() to build up
a query that we can EXECUTE from within a pl/pgsql loop. However, I
certainly didn't know how to do that when I wrote the script two and a
half years ago, at which time I had only about six years of experience
with the product. Before I started reading -hackers, I relied on
reading the fine manual:
http://www.postgresql.org/docs/8.3/static/sql-analyze.html
...which doesn't describe how to do this. So I didn't know. But if
the file manual had included the syntax "ANALYZE SCHEMA blat", I
certainly would have used it, and thus avoided getting 10 emails a
week from my cron job for the past two-and-half years.
What to do about wildcards is a stickier wicket, and maybe we need to
decide that first, but I really don't think we should be discouraging
anyone from investigating this stuff and trying to come up with good
solutions. There will always be some people for whom a custom
PL/pgsql function that directly accesses the catalog tables is the
only workable answer, but we can make PostgreSQL a whole lot easier to
use by reducing the need to do that for simple cases.
...Robert
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: GRANT ON ALL IN schema
@ 2009-06-18 07:21 Peter Eisentraut <peter_e@gmx.net>
parent: Robert Haas <robertmhaas@gmail.com>
0 siblings, 0 replies; 21+ messages in thread
From: Peter Eisentraut @ 2009-06-18 07:21 UTC (permalink / raw)
To: pgsql-hackers; +Cc: Robert Haas <robertmhaas@gmail.com>; Guillaume Smet <guillaume.smet@gmail.com>; Petr Jelinek <pjmodos@pjmodos.net>; Tom Lane <tgl@sss.pgh.pa.us>
On Wednesday 17 June 2009 20:27:20 Robert Haas wrote:
> What to do about wildcards is a stickier wicket, and maybe we need to
> decide that first, but I really don't think we should be discouraging
> anyone from investigating this stuff and trying to come up with good
> solutions. There will always be some people for whom a custom
> PL/pgsql function that directly accesses the catalog tables is the
> only workable answer, but we can make PostgreSQL a whole lot easier to
> use by reducing the need to do that for simple cases.
I'm all for investigating it. I just have my doubts that "grant on all tables
in schema X" is a sufficiently general use case, even if you only concentrate
on the simple cases.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: GRANT ON ALL IN schema
@ 2009-06-18 09:26 Bernd Helmle <mailings@oopsware.de>
parent: Peter Eisentraut <peter_e@gmx.net>
3 siblings, 0 replies; 21+ messages in thread
From: Bernd Helmle @ 2009-06-18 09:26 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; pgsql-hackers; +Cc: Petr Jelinek <pjmodos@pjmodos.net>
--On Mittwoch, Juni 17, 2009 16:44:53 +0300 Peter Eisentraut
<peter_e@gmx.net> wrote:
> I think you should design this with a bit wider scope. Instead of just
> "all tables in this schema", think "all tables satisfying some
> condition". It has been requested, for example, to be able to grant on
> all tables that match a pattern.
>
My experience shows that having such a thing is often leading to "bad
practices". People tend to grant everything to every login role instead of
using an intelligent role privilege mechanism.
MySQL for example has such wildcards (using '_' and '%' wildcard patterns),
which often confuses people when having such characters in their
table/database names (of course, i forgot to escape them more than once).
The unpredictable results of messing up a complete schema when using a
broken pattern expression is going to reduce the usefulness of such a
feature, i think.
>> 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.
>
> As far as GRANT is concerned, a view is a table, so I would omit the
> VIEW/VIEWS stuff completely.
We have ALTER VIEW now, so why don't implement the same synonym for GRANT?
--
Thanks
Bernd
^ permalink raw reply [nested|flat] 21+ messages in thread
end of thread, other threads:[~2009-06-18 09:26 UTC | newest]
Thread overview: 21+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-06-16 15:50 GRANT ON ALL IN schema Petr Jelinek <pjmodos@pjmodos.net>
2009-06-16 18:14 ` Petr Jelinek <pjmodos@pjmodos.net>
2009-06-17 08:29 ` Petr Jelinek <pjmodos@pjmodos.net>
2009-06-17 13:44 ` Peter Eisentraut <peter_e@gmx.net>
2009-06-17 14:09 ` Stephen Frost <sfrost@snowman.net>
2009-06-17 14:40 ` Petr Jelinek <pjmodos@pjmodos.net>
2009-06-17 14:44 ` Stephen Frost <sfrost@snowman.net>
2009-06-17 14:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-06-17 14:44 ` Peter Eisentraut <peter_e@gmx.net>
2009-06-17 14:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-06-17 14:59 ` Petr Jelinek <pjmodos@pjmodos.net>
2009-06-17 16:25 ` Guillaume Smet <guillaume.smet@gmail.com>
2009-06-17 16:45 ` Greg Stark <greg.stark@enterprisedb.com>
2009-06-17 16:52 ` Petr Jelinek <pjmodos@pjmodos.net>
2009-06-17 17:27 ` Robert Haas <robertmhaas@gmail.com>
2009-06-18 07:21 ` Peter Eisentraut <peter_e@gmx.net>
2009-06-17 14:31 ` Petr Jelinek <pjmodos@pjmodos.net>
2009-06-18 09:26 ` Bernd Helmle <mailings@oopsware.de>
2009-06-17 14:35 ` Stephen Frost <sfrost@snowman.net>
2009-06-17 14:42 ` Petr Jelinek <pjmodos@pjmodos.net>
2009-06-17 14:48 ` Stephen Frost <sfrost@snowman.net>
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