Bruce Momjian wrote:
> Tom Lane wrote:
> > I wrote:
> > > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > >> Does the standard require USAGE to support currval?
> >
> > > currval isn't in the standard (unless I missed something), so it has
> > > nothing to say one way or the other on the point.
> >
> > Wait, I take that back. Remember our previous discussions about this
> > point: the spec's NEXT VALUE FOR construct is *not* equivalent to
> > nextval, because they specify that the sequence advances just once per
> > command even if the command says NEXT VALUE FOR in multiple places.
> > This means that NEXT VALUE FOR is effectively both nextval and currval;
> > the first one in a command does nextval and the rest do currval.
> >
> > Accordingly, I think it's reasonable to read the spec as saying that
> > USAGE privilege encompasses both nextval and currval.
>
> Here's a patch that more closely matches the ideas proposed.
Here is an updated patch. I hit a few issues.
At first I was just going to continue allowing table-like permissions
for sequences if a GRANT [TABLE] was used, and add the new
USAGE/SELECT/UPDATE capability only for GRANT SEQUENCE. The problem was
that you could create a non-dumpable permission setup if you added
DELETE permission to a sequence using GRANT TABLE, and USAGE permission
using GRANT SEQUENCE. That couldn't be dumped with TABLE or with
SEQUENCE, and I didn't want to do a double-dump of GRANT to fit that,
nor did I want to throw an warning during the dump run.
What I did was to throw a warning if an invalid permission is specified
for a sequence in GRANT TABLE. By doing this, un-dumpable permission
combinations will not be loaded into an 8.2 database. (GRANT ALL ON
TABLE sets the sequence-only permissions.)
test=> GRANT DELETE ON seq TO PUBLIC;
WARNING: invalid privilege type DELETE for sequence
WARNING: no privileges were granted
GRANT
test=> GRANT DELETE,SELECT ON seq TO PUBLIC;
WARNING: invalid privilege type DELETE for sequence
GRANT
This seemed the safest backward-compatible setup. It will have to be
mentioned in the release notes so users know they might get warnings
from loading sequences into 8.2.
You might think that it is unlikely for a DELETE permission to be
assigned to a sequences, but a simple GRANT ALL and REVOKE INSERT in 8.1
will cause:
test=> CREATE TABLE tab(x INTEGER);
CREATE TABLE
test=> GRANT ALL ON tab TO PUBLIC;
GRANT
test=> REVOKE INSERT ON tab FROM PUBLIC;
REVOKE
yields in pg_dump output:
GRANT SELECT,RULE,UPDATE,DELETE,REFERENCES,TRIGGER ON TABLE tab
TO PUBLIC;
This test was done on a table, but in 8.1 the same would appear for a
sequence with these warnings on load into 8.2:
WARNING: invalid privilege type RULE for sequence
WARNING: invalid privilege type DELETE for sequence
WARNING: invalid privilege type REFERENCES for sequence
WARNING: invalid privilege type TRIGGER for sequence
GRANT
Another tricky case was this:
test=> GRANT DELETE ON tab, seq TO PUBLIC;
GRANT allows multiple objects to be listed, as illustrated above. The
current code checks for valid permissions in one place because it
assumes all listed objects are of the same type and accept the same
permissions. Because GRANT TABLE must allow only valid permissions for
sequences (to avoid un-dumpable output) I had to throw an error if a
sequence is mixed with a non-sequence, and the permission did not apply
to both sequences and non-sequences, rather than throw a warning like I
usually do for invalid sequence permissions:
test=> REVOKE DELETE ON seq, tab FROM PUBLIC;
WARNING: invalid privilege type DELETE for sequence
ERROR: DELETE privilege invalid for command mixing sequences and
non-sequences
test=> REVOKE SELECT ON tab, seq FROM PUBLIC;
REVOKE
Because allowing sequences to use GRANT TABLE is only for backward
compatibility, I think this is fine. If not, we would have to split
apart the permission checking for tables from the existing routine, and
lose modularity in the code.
This patch also contains Marko's documentation adjustments.
Would someone look at the change in src/backend/catalog/pg_shdepend.c
for shared dependencies? We don't have any system catalog sequences let
alone any shared catalog sequences, so I assume we are OK with assuming
it is a relation. I added a comment just in case.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Index: doc/src/sgml/ref/grant.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/grant.sgml,v
retrieving revision 1.50
diff -c -c -r1.50 grant.sgml
*** doc/src/sgml/ref/grant.sgml 20 Oct 2005 19:18:01 -0000 1.50
--- doc/src/sgml/ref/grant.sgml 10 Jan 2006 01:18:32 -0000
***************
*** 25,30 ****
--- 25,35 ----
ON [ TABLE ] <replaceable class="PARAMETER">tablename</replaceable> [,
...]
TO { <replaceable class="PARAMETER">username</replaceable> | GROUP
<replaceable class="PARAMETER">groupname</replaceable> | PUBLIC } [, ...] [
WITH GRANT OPTION ]
+ GRANT { { USAGE | SELECT | UPDATE }
+ [,...] | ALL [ PRIVILEGES ] }
+ ON SEQUENCE <replaceable class="PARAMETER">sequencename</replaceable> [,
...]
+ TO { <replaceable class="PARAMETER">username</replaceable> | GROUP
<replaceable class="PARAMETER">groupname</replaceable> | PUBLIC } [, ...] [
WITH GRANT OPTION ]
+
GRANT { { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
ON DATABASE <replaceable>dbname</replaceable> [, ...]
TO { <replaceable class="PARAMETER">username</replaceable> | GROUP
<replaceable class="PARAMETER">groupname</replaceable> | PUBLIC } [, ...] [
WITH GRANT OPTION ]
***************
*** 260,265 ****
--- 265,274 ----
also met). Essentially this allows the grantee to <quote>look up</>
objects within the schema.
</para>
+ <para>
+ For sequences, this privilege allows the use of the
+ <function>currval</function> and <function>nextval</function>
functions.
+ </para>
</listitem>
</varlistentry>
***************
*** 511,517 ****
<para>
The <literal>RULE</literal> privilege, and privileges on
! databases, tablespaces, schemas, languages, and sequences are
<productname>PostgreSQL</productname> extensions.
</para>
</refsect1>
--- 520,526 ----
<para>
The <literal>RULE</literal> privilege, and privileges on
! databases, tablespaces, schemas, and languages are
<productname>PostgreSQL</productname> extensions.
</para>
</refsect1>
Index: doc/src/sgml/ref/revoke.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/revoke.sgml,v
retrieving revision 1.35
diff -c -c -r1.35 revoke.sgml
*** doc/src/sgml/ref/revoke.sgml 20 Oct 2005 19:18:01 -0000 1.35
--- doc/src/sgml/ref/revoke.sgml 10 Jan 2006 01:18:32 -0000
***************
*** 28,33 ****
--- 28,40 ----
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
+ { { USAGE | SELECT | UPDATE }
+ [,...] | ALL [ PRIVILEGES ] }
+ ON SEQUENCE <replaceable class="PARAMETER">sequencename</replaceable> [,
...]
+ FROM { <replaceable class="PARAMETER">username</replaceable> | GROUP
<replaceable class="PARAMETER">groupname</replaceable> | PUBLIC } [, ...]
+ [ CASCADE | RESTRICT ]
+
+ REVOKE [ GRANT OPTION FOR ]
{ { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
ON DATABASE <replaceable>dbname</replaceable> [, ...]
FROM { <replaceable class="PARAMETER">username</replaceable> | GROUP
<replaceable class="PARAMETER">groupname</replaceable> | PUBLIC } [, ...]
Index: src/backend/catalog/aclchk.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/catalog/aclchk.c,v
retrieving revision 1.123
diff -c -c -r1.123 aclchk.c
*** src/backend/catalog/aclchk.c 1 Dec 2005 02:03:00 -0000 1.123
--- src/backend/catalog/aclchk.c 10 Jan 2006 01:18:34 -0000
***************
*** 164,169 ****
--- 164,172 ----
case ACL_KIND_CLASS:
whole_mask = ACL_ALL_RIGHTS_RELATION;
break;
+ case ACL_KIND_SEQUENCE:
+ whole_mask = ACL_ALL_RIGHTS_SEQUENCE;
+ break;
case ACL_KIND_DATABASE:
whole_mask = ACL_ALL_RIGHTS_DATABASE;
break;
***************
*** 277,319 ****
get_roleid_checked(grantee->rolname));
}
- /*
- * Convert stmt->privileges, a textual list, into an AclMode bitmask.
- */
- switch (stmt->objtype)
- {
- case ACL_OBJECT_RELATION:
- all_privileges = ACL_ALL_RIGHTS_RELATION;
- errormsg = _("invalid privilege type %s for table");
- break;
- case ACL_OBJECT_DATABASE:
- all_privileges = ACL_ALL_RIGHTS_DATABASE;
- errormsg = _("invalid privilege type %s for database");
- break;
- case ACL_OBJECT_FUNCTION:
- all_privileges = ACL_ALL_RIGHTS_FUNCTION;
- errormsg = _("invalid privilege type %s for function");
- break;
- case ACL_OBJECT_LANGUAGE:
- all_privileges = ACL_ALL_RIGHTS_LANGUAGE;
- errormsg = _("invalid privilege type %s for language");
- break;
- case ACL_OBJECT_NAMESPACE:
- all_privileges = ACL_ALL_RIGHTS_NAMESPACE;
- errormsg = _("invalid privilege type %s for namespace");
- break;
- case ACL_OBJECT_TABLESPACE:
- all_privileges = ACL_ALL_RIGHTS_TABLESPACE;
- errormsg = _("invalid privilege type %s for
tablespace");
- break;
- default:
- /* keep compiler quiet */
- all_privileges = ACL_NO_RIGHTS;
- errormsg = NULL;
- elog(ERROR, "unrecognized GrantStmt.objtype: %d",
- (int) stmt->objtype);
- }
-
if (stmt->privileges == NIL)
{
istmt.all_privs = true;
--- 280,285 ----
***************
*** 327,343 ****
{
istmt.all_privs = false;
istmt.privileges = ACL_NO_RIGHTS;
foreach(cell, stmt->privileges)
{
char *privname = strVal(lfirst(cell));
AclMode priv = string_to_privilege(privname);
! if (priv & ~((AclMode) all_privileges))
! ereport(ERROR,
!
(errcode(ERRCODE_INVALID_GRANT_OPERATION),
! errmsg(errormsg,
privilege_to_string(priv))));
!
istmt.privileges |= priv;
}
}
--- 293,429 ----
{
istmt.all_privs = false;
istmt.privileges = ACL_NO_RIGHTS;
+
foreach(cell, stmt->privileges)
{
char *privname = strVal(lfirst(cell));
AclMode priv = string_to_privilege(privname);
! /*
! * The GRANT TABLE syntax can be used for
sequences and
! * non-sequences, so we have to look at the
relkind to
! * determine the supported permissions.
! */
! if (stmt->objtype == ACL_OBJECT_RELATION)
! {
! ListCell *cell2;
! bool skip_priv = false;
! bool non_seq_found = false;
!
! /*
! * GRANT can have sequences and
non-sequence objects
! * in the same command, so loop over each
object.
! */
! foreach(cell2, istmt.objects)
! {
! Oid relOid =
lfirst_oid(cell2);
! Form_pg_class pg_class_tuple;
! HeapTuple tuple;
!
! tuple = SearchSysCache(RELOID,
!
ObjectIdGetDatum(relOid),
!
0, 0, 0);
! if (!HeapTupleIsValid(tuple))
! elog(ERROR, "cache lookup
failed for relation %u", relOid);
! pg_class_tuple = (Form_pg_class)
GETSTRUCT(tuple);
!
! if (pg_class_tuple->relkind ==
RELKIND_SEQUENCE)
! {
! all_privileges =
ACL_ALL_RIGHTS_SEQUENCE;
! errormsg = _("invalid privilege
type %s for sequence");
! /*
! * For backward
compatibility, throw just a warning
! * for invalid sequence
permissions when using the
! * non-sequence GRANT
syntax is used.
! */
! if (priv & ~((AclMode)
ACL_ALL_RIGHTS_SEQUENCE))
! {
! ereport(WARNING,
!
(errcode(ERRCODE_INVALID_GRANT_OPERATION),
!
errmsg(_("invalid privilege type %s for sequence"),
!
privilege_to_string(priv))));
! /* Skip assigning this
priviledge */
! skip_priv = true;
! }
! }
! else
! {
! if (priv & ~((AclMode)
ACL_ALL_RIGHTS_RELATION))
! ereport(ERROR,
!
(errcode(ERRCODE_INVALID_GRANT_OPERATION),
!
errmsg(_("invalid privilege type %s for table"),
!
privilege_to_string(priv))));
! non_seq_found = true;
! }
! ReleaseSysCache(tuple);
! }
! /* If we get here, we have issued only warnings
*/
! if (skip_priv)
! {
! if (non_seq_found)
! /*
! * If we get here, someone
has issued a command like:
! *
! * GRANT DELETE ON
tab, seq TO PUBLIC
! *
! * In thise case, the
DELETE is valid for the table
! * but not for the
sequences. We don't want to continue
! * processing with a
permission that will only partly
! * succeed, so we ERROR.
! */
! ereport(ERROR,
!
(errcode(ERRCODE_INVALID_GRANT_OPERATION),
! errmsg(_("%s privilege
invalid for command mixing sequences and non-sequences"),
privilege_to_string(priv))));
! priv = 0;
! }
! }
! else
! {
! /*
! * Convert stmt->privileges, a textual list,
into an AclMode bitmask.
! */
! switch (stmt->objtype)
! {
! /* ACL_OBJECT_RELATION: handled above
*/
! case ACL_OBJECT_SEQUENCE:
! all_privileges =
ACL_ALL_RIGHTS_SEQUENCE;
! errormsg = _("invalid privilege
type %s for sequence");
! break;
! case ACL_OBJECT_DATABASE:
! all_privileges =
ACL_ALL_RIGHTS_DATABASE;
! errormsg = _("invalid privilege
type %s for database");
! break;
! case ACL_OBJECT_FUNCTION:
! all_privileges =
ACL_ALL_RIGHTS_FUNCTION;
! errormsg = _("invalid privilege
type %s for function");
! break;
! case ACL_OBJECT_LANGUAGE:
! all_privileges =
ACL_ALL_RIGHTS_LANGUAGE;
! errormsg = _("invalid privilege
type %s for language");
! break;
! case ACL_OBJECT_NAMESPACE:
! all_privileges =
ACL_ALL_RIGHTS_NAMESPACE;
! errormsg = _("invalid privilege
type %s for namespace");
! break;
! case ACL_OBJECT_TABLESPACE:
! all_privileges =
ACL_ALL_RIGHTS_TABLESPACE;
! errormsg = _("invalid privilege
type %s for tablespace");
! break;
! default:
! /* keep compiler quiet */
! all_privileges = ACL_NO_RIGHTS;
! errormsg = NULL;
! elog(ERROR, "unrecognized
GrantStmt.objtype: %d",
! (int) stmt->objtype);
! }
! if (priv & ~((AclMode) all_privileges))
! ereport(ERROR,
!
(errcode(ERRCODE_INVALID_GRANT_OPERATION),
! errmsg(errormsg,
!
privilege_to_string(priv))));
! }
!
istmt.privileges |= priv;
}
}
***************
*** 356,361 ****
--- 442,448 ----
switch (istmt->objtype)
{
case ACL_OBJECT_RELATION:
+ case ACL_OBJECT_SEQUENCE:
ExecGrant_Relation(istmt);
break;
case ACL_OBJECT_DATABASE:
***************
*** 395,400 ****
--- 482,488 ----
switch (objtype)
{
case ACL_OBJECT_RELATION:
+ case ACL_OBJECT_SEQUENCE:
foreach(cell, objnames)
{
Oid relOid;
***************
*** 523,537 ****
return objects;
}
static void
ExecGrant_Relation(InternalGrant *istmt)
{
Relation relation;
ListCell *cell;
- if (istmt->all_privs && istmt->privileges == ACL_NO_RIGHTS)
- istmt->privileges = ACL_ALL_RIGHTS_RELATION;
-
relation = heap_open(RelationRelationId, RowExclusiveLock);
foreach(cell, istmt->objects)
--- 611,625 ----
return objects;
}
+ /*
+ * This processes both sequences and non-sequences.
+ */
static void
ExecGrant_Relation(InternalGrant *istmt)
{
Relation relation;
ListCell *cell;
relation = heap_open(RelationRelationId, RowExclusiveLock);
foreach(cell, istmt->objects)
***************
*** 577,582 ****
--- 665,689 ----
errmsg("\"%s\" is a composite type",
NameStr(pg_class_tuple->relname))));
+ /* Used GRANT SEQUENCE on a non-sequence? */
+ if (istmt->objtype == ACL_OBJECT_SEQUENCE &&
+ pg_class_tuple->relkind != RELKIND_SEQUENCE)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("\"%s\" is not a sequence",
+
NameStr(pg_class_tuple->relname))));
+
+ /* Adjust the default permissions based on whether it is a
sequence */
+ if (istmt->all_privs && istmt->privileges == ACL_NO_RIGHTS)
+ {
+ if (pg_class_tuple->relkind == RELKIND_SEQUENCE)
+ this_privileges = ACL_ALL_RIGHTS_SEQUENCE;
+ else
+ this_privileges = ACL_ALL_RIGHTS_RELATION;
+ }
+ else
+ this_privileges = istmt->privileges;
+
/*
* Get owner ID and working copy of existing ACL. If there's no
ACL,
* substitute the proper default.
***************
*** 585,596 ****
aclDatum = SysCacheGetAttr(RELOID, tuple, Anum_pg_class_relacl,
&isNull);
if (isNull)
! old_acl = acldefault(ACL_OBJECT_RELATION, ownerId);
else
old_acl = DatumGetAclPCopy(aclDatum);
/* Determine ID to do the grant as, and available grant options
*/
! select_best_grantor(GetUserId(), istmt->privileges,
old_acl, ownerId,
&grantorId,
&avail_goptions);
--- 692,705 ----
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);
/* Determine ID to do the grant as, and available grant options
*/
! select_best_grantor(GetUserId(), this_privileges,
old_acl, ownerId,
&grantorId,
&avail_goptions);
***************
*** 600,607 ****
*/
this_privileges =
restrict_and_check_grant(istmt->is_grant,
avail_goptions,
!
istmt->all_privs, istmt->privileges,
!
relOid, grantorId, ACL_KIND_CLASS,
NameStr(pg_class_tuple->relname));
/*
--- 709,718 ----
*/
this_privileges =
restrict_and_check_grant(istmt->is_grant,
avail_goptions,
!
istmt->all_privs, this_privileges,
!
relOid, grantorId,
!
pg_class_tuple->relkind == RELKIND_SEQUENCE
!
? ACL_KIND_SEQUENCE : ACL_KIND_CLASS,
NameStr(pg_class_tuple->relname));
/*
***************
*** 1336,1341 ****
--- 1447,1454 ----
{
/* ACL_KIND_CLASS */
gettext_noop("permission denied for relation %s"),
+ /* ACL_KIND_SEQUENCE */
+ gettext_noop("permission denied for sequence %s"),
/* ACL_KIND_DATABASE */
gettext_noop("permission denied for database %s"),
/* ACL_KIND_PROC */
***************
*** 1360,1365 ****
--- 1473,1480 ----
{
/* ACL_KIND_CLASS */
gettext_noop("must be owner of relation %s"),
+ /* ACL_KIND_SEQUENCE */
+ gettext_noop("must be owner of sequence %s"),
/* ACL_KIND_DATABASE */
gettext_noop("must be owner of database %s"),
/* ACL_KIND_PROC */
***************
*** 1439,1444 ****
--- 1554,1560 ----
switch (objkind)
{
case ACL_KIND_CLASS:
+ case ACL_KIND_SEQUENCE:
return pg_class_aclmask(table_oid, roleid, mask, how);
case ACL_KIND_DATABASE:
return pg_database_aclmask(table_oid, roleid, mask,
how);
***************
*** 1500,1508 ****
*
* As of 7.4 we have some updatable system views; those shouldn't be
* protected in this way. Assume the view rules can take care of
! * themselves.
*/
! if ((mask & (ACL_INSERT | ACL_UPDATE | ACL_DELETE)) &&
IsSystemClass(classForm) &&
classForm->relkind != RELKIND_VIEW &&
!has_rolcatupdate(roleid) &&
--- 1616,1624 ----
*
* As of 7.4 we have some updatable system views; those shouldn't be
* protected in this way. Assume the view rules can take care of
! * themselves. ACL_USAGE is if we ever have system sequences.
*/
! if ((mask & (ACL_INSERT | ACL_UPDATE | ACL_DELETE | ACL_USAGE)) &&
IsSystemClass(classForm) &&
classForm->relkind != RELKIND_VIEW &&
!has_rolcatupdate(roleid) &&
***************
*** 1511,1517 ****
#ifdef ACLDEBUG
elog(DEBUG2, "permission denied for system catalog update");
#endif
! mask &= ~(ACL_INSERT | ACL_UPDATE | ACL_DELETE);
}
/*
--- 1627,1633 ----
#ifdef ACLDEBUG
elog(DEBUG2, "permission denied for system catalog update");
#endif
! mask &= ~(ACL_INSERT | ACL_UPDATE | ACL_DELETE | ACL_USAGE);
}
/*
***************
*** 1536,1542 ****
if (isNull)
{
/* No ACL, so build default ACL */
! acl = acldefault(ACL_OBJECT_RELATION, ownerId);
aclDatum = (Datum) 0;
}
else
--- 1652,1660 ----
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
Index: src/backend/catalog/pg_shdepend.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/catalog/pg_shdepend.c,v
retrieving revision 1.6
diff -c -c -r1.6 pg_shdepend.c
*** src/backend/catalog/pg_shdepend.c 1 Dec 2005 02:03:00 -0000 1.6
--- src/backend/catalog/pg_shdepend.c 10 Jan 2006 01:18:35 -0000
***************
*** 1133,1138 ****
--- 1133,1139 ----
switch (sdepForm->classid)
{
case RelationRelationId:
+ /* could be a sequence?
*/
istmt.objtype =
ACL_OBJECT_RELATION;
break;
case DatabaseRelationId:
Index: src/backend/commands/sequence.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/commands/sequence.c,v
retrieving revision 1.126
diff -c -c -r1.126 sequence.c
*** src/backend/commands/sequence.c 22 Nov 2005 18:17:09 -0000 1.126
--- src/backend/commands/sequence.c 10 Jan 2006 01:18:37 -0000
***************
*** 422,428 ****
/* open and AccessShareLock sequence */
init_sequence(relid, &elm, &seqrel);
! if (pg_class_aclcheck(elm->relid, GetUserId(), ACL_UPDATE) !=
ACLCHECK_OK)
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("permission denied for sequence %s",
--- 422,429 ----
/* open and AccessShareLock sequence */
init_sequence(relid, &elm, &seqrel);
! if (pg_class_aclcheck(elm->relid, GetUserId(), ACL_USAGE) !=
ACLCHECK_OK &&
! pg_class_aclcheck(elm->relid, GetUserId(), ACL_UPDATE) !=
ACLCHECK_OK)
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("permission denied for sequence %s",
***************
*** 613,619 ****
/* open and AccessShareLock sequence */
init_sequence(relid, &elm, &seqrel);
! if (pg_class_aclcheck(elm->relid, GetUserId(), ACL_SELECT) !=
ACLCHECK_OK)
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("permission denied for sequence %s",
--- 614,621 ----
/* open and AccessShareLock sequence */
init_sequence(relid, &elm, &seqrel);
! if (pg_class_aclcheck(elm->relid, GetUserId(), ACL_SELECT) !=
ACLCHECK_OK &&
! pg_class_aclcheck(elm->relid, GetUserId(), ACL_USAGE) !=
ACLCHECK_OK)
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("permission denied for sequence %s",
***************
*** 657,663 ****
/* nextval() must have already been called for this sequence */
Assert(last_used_seq->increment != 0);
! if (pg_class_aclcheck(last_used_seq->relid, GetUserId(), ACL_SELECT) !=
ACLCHECK_OK)
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("permission denied for sequence %s",
--- 659,666 ----
/* nextval() must have already been called for this sequence */
Assert(last_used_seq->increment != 0);
! if (pg_class_aclcheck(last_used_seq->relid, GetUserId(), ACL_SELECT) !=
ACLCHECK_OK &&
! pg_class_aclcheck(last_used_seq->relid, GetUserId(), ACL_USAGE)
!= ACLCHECK_OK)
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("permission denied for sequence %s",
Index: src/backend/parser/gram.y
===================================================================
RCS file: /cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.521
diff -c -c -r2.521 gram.y
*** src/backend/parser/gram.y 29 Dec 2005 04:53:18 -0000 2.521
--- src/backend/parser/gram.y 10 Jan 2006 01:18:47 -0000
***************
*** 3322,3327 ****
--- 3322,3334 ----
n->objs = $2;
$$ = n;
}
+ | SEQUENCE qualified_name_list
+ {
+ PrivTarget *n = makeNode(PrivTarget);
+ n->objtype = ACL_OBJECT_SEQUENCE;
+ n->objs = $2;
+ $$ = n;
+ }
| FUNCTION function_with_argtypes_list
{
PrivTarget *n = makeNode(PrivTarget);
Index: src/backend/utils/adt/acl.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/acl.c,v
retrieving revision 1.129
diff -c -c -r1.129 acl.c
*** src/backend/utils/adt/acl.c 18 Nov 2005 02:38:23 -0000 1.129
--- src/backend/utils/adt/acl.c 10 Jan 2006 01:18:50 -0000
***************
*** 545,550 ****
--- 545,554 ----
world_default = ACL_NO_RIGHTS;
owner_default = ACL_ALL_RIGHTS_RELATION;
break;
+ case ACL_OBJECT_SEQUENCE:
+ world_default = ACL_NO_RIGHTS;
+ owner_default = ACL_ALL_RIGHTS_SEQUENCE;
+ break;
case ACL_OBJECT_DATABASE:
world_default = ACL_CREATE_TEMP; /* not
NO_RIGHTS! */
owner_default = ACL_ALL_RIGHTS_DATABASE;
Index: src/bin/pg_dump/dumputils.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/pg_dump/dumputils.c,v
retrieving revision 1.23
diff -c -c -r1.23 dumputils.c
*** src/bin/pg_dump/dumputils.c 3 Dec 2005 21:06:18 -0000 1.23
--- src/bin/pg_dump/dumputils.c 10 Jan 2006 01:18:51 -0000
***************
*** 22,28 ****
#define supports_grant_options(version) ((version) >= 70400)
static bool parseAclItem(const char *item, const char *type, const char *name,
! int remoteVersion,
PQExpBuffer grantee, PQExpBuffer grantor,
PQExpBuffer privs, PQExpBuffer privswgo);
static char *copyAclUserName(PQExpBuffer output, char *input);
--- 22,28 ----
#define supports_grant_options(version) ((version) >= 70400)
static bool parseAclItem(const char *item, const char *type, const char *name,
! int remoteVersion, bool *is_valid_for_sequence,
PQExpBuffer grantee, PQExpBuffer grantor,
PQExpBuffer privs, PQExpBuffer privswgo);
static char *copyAclUserName(PQExpBuffer output, char *input);
***************
*** 395,404 ****
--- 395,416 ----
/* Scan individual ACL items */
for (i = 0; i < naclitems; i++)
{
+ const char *outType = type;
+ bool is_valid_for_sequence;
+
if (!parseAclItem(aclitems[i], type, name, remoteVersion,
+ &is_valid_for_sequence,
grantee, grantor, privs,
privswgo))
return false;
+ /*
+ * For backward compatibility, non-SEQUENCE GRANT
statements issue
+ * warnings rather than errors for invalid sequence
permissions.
+ * This should only happen in pre-8.2 databases.
+ */
+ if (strcmp(outType, "SEQUENCE") == 0 && !is_valid_for_sequence)
+ outType = "TABLE";
+
if (grantor->len == 0 && owner)
printfPQExpBuffer(grantor, "%s", owner);
***************
*** 419,433 ****
: strcmp(privs->data, "ALL") != 0)
{
appendPQExpBuffer(firstsql, "REVOKE ALL
ON %s %s FROM %s;\n",
! type,
name,
fmtId(grantee->data));
if (privs->len > 0)
appendPQExpBuffer(firstsql,
"GRANT %s ON %s %s TO %s;\n",
!
privs->data, type, name,
fmtId(grantee->data));
if (privswgo->len > 0)
appendPQExpBuffer(firstsql,
"GRANT %s ON %s %s TO %s WITH GRANT OPTION;\n",
!
privswgo->data, type, name,
fmtId(grantee->data));
}
}
--- 431,445 ----
: strcmp(privs->data, "ALL") != 0)
{
appendPQExpBuffer(firstsql, "REVOKE ALL
ON %s %s FROM %s;\n",
!
outType, name,
fmtId(grantee->data));
if (privs->len > 0)
appendPQExpBuffer(firstsql,
"GRANT %s ON %s %s TO %s;\n",
!
privs->data, outType, name,
fmtId(grantee->data));
if (privswgo->len > 0)
appendPQExpBuffer(firstsql,
"GRANT %s ON %s %s TO %s WITH GRANT OPTION;\n",
!
privswgo->data, outType, name,
fmtId(grantee->data));
}
}
***************
*** 444,450 ****
if (privs->len > 0)
{
appendPQExpBuffer(secondsql, "GRANT %s
ON %s %s TO ",
!
privs->data, type, name);
if (grantee->len == 0)
appendPQExpBuffer(secondsql,
"PUBLIC;\n");
else if (strncmp(grantee->data, "group
",
--- 456,462 ----
if (privs->len > 0)
{
appendPQExpBuffer(secondsql, "GRANT %s
ON %s %s TO ",
!
privs->data, outType, name);
if (grantee->len == 0)
appendPQExpBuffer(secondsql,
"PUBLIC;\n");
else if (strncmp(grantee->data, "group
",
***************
*** 457,463 ****
if (privswgo->len > 0)
{
appendPQExpBuffer(secondsql, "GRANT %s
ON %s %s TO ",
!
privswgo->data, type, name);
if (grantee->len == 0)
appendPQExpBuffer(secondsql,
"PUBLIC");
else if (strncmp(grantee->data, "group
",
--- 469,475 ----
if (privswgo->len > 0)
{
appendPQExpBuffer(secondsql, "GRANT %s
ON %s %s TO ",
!
privswgo->data, outType, name);
if (grantee->len == 0)
appendPQExpBuffer(secondsql,
"PUBLIC");
else if (strncmp(grantee->data, "group
",
***************
*** 480,489 ****
* If we didn't find any owner privs, the owner must have revoked 'em
all
*/
if (!found_owner_privs && owner)
- {
appendPQExpBuffer(firstsql, "REVOKE ALL ON %s %s FROM %s;\n",
type, name, fmtId(owner));
- }
destroyPQExpBuffer(grantee);
destroyPQExpBuffer(grantor);
--- 492,499 ----
***************
*** 517,523 ****
*/
static bool
parseAclItem(const char *item, const char *type, const char *name,
! int remoteVersion,
PQExpBuffer grantee, PQExpBuffer grantor,
PQExpBuffer privs, PQExpBuffer privswgo)
{
--- 527,533 ----
*/
static bool
parseAclItem(const char *item, const char *type, const char *name,
! int remoteVersion, bool *is_valid_for_sequence,
PQExpBuffer grantee, PQExpBuffer grantor,
PQExpBuffer privs, PQExpBuffer privswgo)
{
***************
*** 530,535 ****
--- 540,547 ----
buf = strdup(item);
+ *is_valid_for_sequence = true;
+
/* user or group name is string up to = */
eqpos = copyAclUserName(grantee, buf);
if (*eqpos != '=')
***************
*** 547,554 ****
--- 559,572 ----
else
resetPQExpBuffer(grantor);
+ if (strcmp(type, "SEQUENCE") == 0 &&
+ /* SELECT, USAGE, UPDATE, ALL */
+ strspn(eqpos + 1, "rUw*") != strlen(eqpos + 1))
+ *is_valid_for_sequence = false;
+
/* privilege codes */
#define CONVERT_PRIV(code, keywd) \
+ do { \
if ((pos = strchr(eqpos + 1, code))) \
{ \
if (*(pos + 1) == '*') \
***************
*** 563,578 ****
} \
} \
else \
! all_with_go = all_without_go = false
resetPQExpBuffer(privs);
resetPQExpBuffer(privswgo);
! if (strcmp(type, "TABLE") == 0)
{
CONVERT_PRIV('a', "INSERT");
CONVERT_PRIV('r', "SELECT");
CONVERT_PRIV('R', "RULE");
if (remoteVersion >= 70200)
{
--- 581,599 ----
} \
} \
else \
! all_with_go = all_without_go = false; \
! } while (0)
resetPQExpBuffer(privs);
resetPQExpBuffer(privswgo);
! if (strcmp(type, "TABLE") == 0 || strcmp(type, "SEQUENCE") == 0)
{
CONVERT_PRIV('a', "INSERT");
CONVERT_PRIV('r', "SELECT");
CONVERT_PRIV('R', "RULE");
+ if (strcmp(type, "SEQUENCE") == 0)
+ CONVERT_PRIV('U', "USAGE");
if (remoteVersion >= 70200)
{
Index: src/bin/pg_dump/pg_dump.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/pg_dump/pg_dump.c,v
retrieving revision 1.426
diff -c -c -r1.426 pg_dump.c
*** src/bin/pg_dump/pg_dump.c 9 Jan 2006 21:16:17 -0000 1.426
--- src/bin/pg_dump/pg_dump.c 10 Jan 2006 01:18:56 -0000
***************
*** 6788,6794 ****
/* Handle the ACL here */
namecopy = strdup(fmtId(tbinfo->dobj.name));
! dumpACL(fout, tbinfo->dobj.catId, tbinfo->dobj.dumpId, "TABLE",
namecopy, tbinfo->dobj.name,
tbinfo->dobj.namespace->dobj.name,
tbinfo->rolname,
tbinfo->relacl);
--- 6788,6796 ----
/* Handle the ACL here */
namecopy = strdup(fmtId(tbinfo->dobj.name));
! dumpACL(fout, tbinfo->dobj.catId, tbinfo->dobj.dumpId,
! /* Issue GRANT SEQUENCE, if applicable */
! tbinfo->relkind != RELKIND_SEQUENCE ? "TABLE" :
"SEQUENCE",
namecopy, tbinfo->dobj.name,
tbinfo->dobj.namespace->dobj.name,
tbinfo->rolname,
tbinfo->relacl);
Index: src/include/nodes/parsenodes.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/nodes/parsenodes.h,v
retrieving revision 1.298
diff -c -c -r1.298 parsenodes.h
*** src/include/nodes/parsenodes.h 7 Dec 2005 15:20:55 -0000 1.298
--- src/include/nodes/parsenodes.h 10 Jan 2006 01:18:58 -0000
***************
*** 884,890 ****
*/
typedef enum GrantObjectType
{
! ACL_OBJECT_RELATION, /* table, view, sequence */
ACL_OBJECT_DATABASE, /* database */
ACL_OBJECT_FUNCTION, /* function */
ACL_OBJECT_LANGUAGE, /* procedural language */
--- 884,891 ----
*/
typedef enum GrantObjectType
{
! ACL_OBJECT_RELATION, /* table, view */
! ACL_OBJECT_SEQUENCE, /* sequence */
ACL_OBJECT_DATABASE, /* database */
ACL_OBJECT_FUNCTION, /* function */
ACL_OBJECT_LANGUAGE, /* procedural language */
Index: src/include/utils/acl.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/utils/acl.h,v
retrieving revision 1.91
diff -c -c -r1.91 acl.h
*** src/include/utils/acl.h 1 Dec 2005 02:03:01 -0000 1.91
--- src/include/utils/acl.h 10 Jan 2006 01:18:58 -0000
***************
*** 143,148 ****
--- 143,149 ----
* Bitmasks defining "all rights" for each supported object type
*/
#define ACL_ALL_RIGHTS_RELATION
(ACL_INSERT|ACL_SELECT|ACL_UPDATE|ACL_DELETE|ACL_RULE|ACL_REFERENCES|ACL_TRIGGER)
+ #define ACL_ALL_RIGHTS_SEQUENCE
(ACL_USAGE|ACL_SELECT|ACL_UPDATE)
#define ACL_ALL_RIGHTS_DATABASE (ACL_CREATE|ACL_CREATE_TEMP)
#define ACL_ALL_RIGHTS_FUNCTION (ACL_EXECUTE)
#define ACL_ALL_RIGHTS_LANGUAGE (ACL_USAGE)
***************
*** 169,174 ****
--- 170,176 ----
typedef enum AclObjectKind
{
ACL_KIND_CLASS, /* pg_class */
+ ACL_KIND_SEQUENCE, /* pg_sequence */
ACL_KIND_DATABASE, /* pg_database */
ACL_KIND_PROC, /* pg_proc */
ACL_KIND_OPER, /* pg_operator */
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
