🌐 AI搜索 & 代理 主页
Skip to content

Commit 4789e98

Browse files
bmomjianbmomjian
committed
Add GRANT ON SEQUENCE syntax to support sequence-only permissions.
Continue to support GRANT ON [TABLE] for sequences for backward compatibility; issue warning for invalid sequence permissions. [Backward compatibility warning message.] Add USAGE permission for sequences that allows only currval() and nextval(), not setval(). Mention object name in grant/revoke warnings because of possible multi-object operations.
1 parent 33feb55 commit 4789e98

File tree

13 files changed

+207
-59
lines changed

13 files changed

+207
-59
lines changed

‎doc/src/sgml/ref/grant.sgml‎

Lines changed: 11 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/grant.sgml,v 1.50 2005/10/20 19:18:01 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/grant.sgml,v 1.51 2006/01/21 02:16:18 momjian Exp $
33
PostgreSQL documentation
44
-->
55

@@ -25,6 +25,11 @@ GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }
2525
ON [ TABLE ] <replaceable class="PARAMETER">tablename</replaceable> [, ...]
2626
TO { <replaceable class="PARAMETER">username</replaceable> | GROUP <replaceable class="PARAMETER">groupname</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
2727

28+
GRANT { { USAGE | SELECT | UPDATE }
29+
[,...] | ALL [ PRIVILEGES ] }
30+
ON SEQUENCE <replaceable class="PARAMETER">sequencename</replaceable> [, ...]
31+
TO { <replaceable class="PARAMETER">username</replaceable> | GROUP <replaceable class="PARAMETER">groupname</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
32+
2833
GRANT { { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
2934
ON DATABASE <replaceable>dbname</replaceable> [, ...]
3035
TO { <replaceable class="PARAMETER">username</replaceable> | GROUP <replaceable class="PARAMETER">groupname</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
@@ -260,6 +265,10 @@ GRANT <replaceable class="PARAMETER">role</replaceable> [, ...]
260265
also met). Essentially this allows the grantee to <quote>look up</>
261266
objects within the schema.
262267
</para>
268+
<para>
269+
For sequences, this privilege allows the use of the
270+
<function>currval</function> and <function>nextval</function> functions.
271+
</para>
263272
</listitem>
264273
</varlistentry>
265274

@@ -511,7 +520,7 @@ GRANT <replaceable class="PARAMETER">privileges</replaceable>
511520

512521
<para>
513522
The <literal>RULE</literal> privilege, and privileges on
514-
databases, tablespaces, schemas, languages, and sequences are
523+
databases, tablespaces, schemas, and languages are
515524
<productname>PostgreSQL</productname> extensions.
516525
</para>
517526
</refsect1>

‎doc/src/sgml/ref/revoke.sgml‎

Lines changed: 8 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/revoke.sgml,v 1.35 2005/10/20 19:18:01 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/revoke.sgml,v 1.36 2006/01/21 02:16:18 momjian Exp $
33
PostgreSQL documentation
44
-->
55

@@ -27,6 +27,13 @@ REVOKE [ GRANT OPTION FOR ]
2727
FROM { <replaceable class="PARAMETER">username</replaceable> | GROUP <replaceable class="PARAMETER">groupname</replaceable> | PUBLIC } [, ...]
2828
[ CASCADE | RESTRICT ]
2929

30+
REVOKE [ GRANT OPTION FOR ]
31+
{ { USAGE | SELECT | UPDATE }
32+
[,...] | ALL [ PRIVILEGES ] }
33+
ON SEQUENCE <replaceable class="PARAMETER">sequencename</replaceable> [, ...]
34+
FROM { <replaceable class="PARAMETER">username</replaceable> | GROUP <replaceable class="PARAMETER">groupname</replaceable> | PUBLIC } [, ...]
35+
[ CASCADE | RESTRICT ]
36+
3037
REVOKE [ GRANT OPTION FOR ]
3138
{ { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
3239
ON DATABASE <replaceable>dbname</replaceable> [, ...]

‎src/backend/catalog/aclchk.c‎

Lines changed: 107 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/catalog/aclchk.c,v 1.123 2005/12/01 02:03:00 alvherre Exp $
11+
* $PostgreSQL: pgsql/src/backend/catalog/aclchk.c,v 1.124 2006/01/21 02:16:18 momjian Exp $
1212
*
1313
* NOTES
1414
* See acl.h.
@@ -164,6 +164,9 @@ restrict_and_check_grant(bool is_grant, AclMode avail_goptions, bool all_privs,
164164
case ACL_KIND_CLASS:
165165
whole_mask = ACL_ALL_RIGHTS_RELATION;
166166
break;
167+
case ACL_KIND_SEQUENCE:
168+
whole_mask = ACL_ALL_RIGHTS_SEQUENCE;
169+
break;
167170
case ACL_KIND_DATABASE:
168171
whole_mask = ACL_ALL_RIGHTS_DATABASE;
169172
break;
@@ -212,22 +215,22 @@ restrict_and_check_grant(bool is_grant, AclMode avail_goptions, bool all_privs,
212215
if (this_privileges == 0)
213216
ereport(WARNING,
214217
(errcode(ERRCODE_WARNING_PRIVILEGE_NOT_GRANTED),
215-
errmsg("no privileges were granted")));
218+
errmsg("no privileges were granted for \"%s\"", objname)));
216219
else if (!all_privs && this_privileges != privileges)
217220
ereport(WARNING,
218221
(errcode(ERRCODE_WARNING_PRIVILEGE_NOT_GRANTED),
219-
errmsg("not all privileges were granted")));
222+
errmsg("not all privileges were granted for \"%s\"", objname)));
220223
}
221224
else
222225
{
223226
if (this_privileges == 0)
224227
ereport(WARNING,
225228
(errcode(ERRCODE_WARNING_PRIVILEGE_NOT_REVOKED),
226-
errmsg("no privileges could be revoked")));
229+
errmsg("no privileges could be revoked for \"%s\"", objname)));
227230
else if (!all_privs && this_privileges != privileges)
228231
ereport(WARNING,
229232
(errcode(ERRCODE_WARNING_PRIVILEGE_NOT_REVOKED),
230-
errmsg("not all privileges could be revoked")));
233+
errmsg("not all privileges could be revoked for \"%s\"", objname)));
231234
}
232235

233236
return this_privileges;
@@ -282,9 +285,18 @@ ExecuteGrantStmt(GrantStmt *stmt)
282285
*/
283286
switch (stmt->objtype)
284287
{
288+
/*
289+
* Because this might be a sequence, we test both relation
290+
* and sequence bits, and later do a more limited test
291+
* when we know the object type.
292+
*/
285293
case ACL_OBJECT_RELATION:
286-
all_privileges = ACL_ALL_RIGHTS_RELATION;
287-
errormsg = _("invalid privilege type %s for table");
294+
all_privileges = ACL_ALL_RIGHTS_RELATION | ACL_ALL_RIGHTS_SEQUENCE;
295+
errormsg = _("invalid privilege type %s for relation");
296+
break;
297+
case ACL_OBJECT_SEQUENCE:
298+
all_privileges = ACL_ALL_RIGHTS_SEQUENCE;
299+
errormsg = _("invalid privilege type %s for sequence");
288300
break;
289301
case ACL_OBJECT_DATABASE:
290302
all_privileges = ACL_ALL_RIGHTS_DATABASE;
@@ -327,6 +339,7 @@ ExecuteGrantStmt(GrantStmt *stmt)
327339
{
328340
istmt.all_privs = false;
329341
istmt.privileges = ACL_NO_RIGHTS;
342+
330343
foreach(cell, stmt->privileges)
331344
{
332345
char *privname = strVal(lfirst(cell));
@@ -356,6 +369,7 @@ ExecGrantStmt_oids(InternalGrant *istmt)
356369
switch (istmt->objtype)
357370
{
358371
case ACL_OBJECT_RELATION:
372+
case ACL_OBJECT_SEQUENCE:
359373
ExecGrant_Relation(istmt);
360374
break;
361375
case ACL_OBJECT_DATABASE:
@@ -395,6 +409,7 @@ objectNamesToOids(GrantObjectType objtype, List *objnames)
395409
switch (objtype)
396410
{
397411
case ACL_OBJECT_RELATION:
412+
case ACL_OBJECT_SEQUENCE:
398413
foreach(cell, objnames)
399414
{
400415
Oid relOid;
@@ -523,15 +538,15 @@ objectNamesToOids(GrantObjectType objtype, List *objnames)
523538
return objects;
524539
}
525540

541+
/*
542+
* This processes both sequences and non-sequences.
543+
*/
526544
static void
527545
ExecGrant_Relation(InternalGrant *istmt)
528546
{
529547
Relation relation;
530548
ListCell *cell;
531549

532-
if (istmt->all_privs && istmt->privileges == ACL_NO_RIGHTS)
533-
istmt->privileges = ACL_ALL_RIGHTS_RELATION;
534-
535550
relation = heap_open(RelationRelationId, RowExclusiveLock);
536551

537552
foreach(cell, istmt->objects)
@@ -577,6 +592,69 @@ ExecGrant_Relation(InternalGrant *istmt)
577592
errmsg("\"%s\" is a composite type",
578593
NameStr(pg_class_tuple->relname))));
579594

595+
/* Used GRANT SEQUENCE on a non-sequence? */
596+
if (istmt->objtype == ACL_OBJECT_SEQUENCE &&
597+
pg_class_tuple->relkind != RELKIND_SEQUENCE)
598+
ereport(ERROR,
599+
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
600+
errmsg("\"%s\" is not a sequence",
601+
NameStr(pg_class_tuple->relname))));
602+
603+
/* Adjust the default permissions based on whether it is a sequence */
604+
if (istmt->all_privs && istmt->privileges == ACL_NO_RIGHTS)
605+
{
606+
if (pg_class_tuple->relkind == RELKIND_SEQUENCE)
607+
this_privileges = ACL_ALL_RIGHTS_SEQUENCE;
608+
else
609+
this_privileges = ACL_ALL_RIGHTS_RELATION;
610+
}
611+
else
612+
this_privileges = istmt->privileges;
613+
614+
/*
615+
* The GRANT TABLE syntax can be used for sequences and
616+
* non-sequences, so we have to look at the relkind to
617+
* determine the supported permissions. The OR of
618+
* table and sequence permissions were already checked.
619+
*/
620+
if (istmt->objtype == ACL_OBJECT_RELATION)
621+
{
622+
if (pg_class_tuple->relkind == RELKIND_SEQUENCE)
623+
{
624+
/*
625+
* For backward compatibility, throw just a warning
626+
* for invalid sequence permissions when using the
627+
* non-sequence GRANT syntax is used.
628+
*/
629+
if (this_privileges & ~((AclMode) ACL_ALL_RIGHTS_SEQUENCE))
630+
{
631+
/*
632+
* Mention the object name because the user needs to
633+
* know which operations succeeded. This is required
634+
* because WARNING allows the command to continue.
635+
*/
636+
ereport(WARNING,
637+
(errcode(ERRCODE_INVALID_GRANT_OPERATION),
638+
errmsg("sequence \"%s\" only supports USAGE, SELECT, and UPDATE",
639+
NameStr(pg_class_tuple->relname))));
640+
this_privileges &= (AclMode) ACL_ALL_RIGHTS_SEQUENCE;
641+
}
642+
}
643+
else
644+
{
645+
if (this_privileges & ~((AclMode) ACL_ALL_RIGHTS_RELATION))
646+
/*
647+
* USAGE is the only permission supported by sequences
648+
* but not by non-sequences. Don't mention the object
649+
* name because we didn't in the combined TABLE |
650+
* SEQUENCE check.
651+
*/
652+
ereport(ERROR,
653+
(errcode(ERRCODE_INVALID_GRANT_OPERATION),
654+
errmsg("invalid privilege type USAGE for table")));
655+
}
656+
}
657+
580658
/*
581659
* Get owner ID and working copy of existing ACL. If there's no ACL,
582660
* substitute the proper default.
@@ -585,12 +663,14 @@ ExecGrant_Relation(InternalGrant *istmt)
585663
aclDatum = SysCacheGetAttr(RELOID, tuple, Anum_pg_class_relacl,
586664
&isNull);
587665
if (isNull)
588-
old_acl = acldefault(ACL_OBJECT_RELATION, ownerId);
666+
old_acl = acldefault(pg_class_tuple->relkind == RELKIND_SEQUENCE ?
667+
ACL_OBJECT_SEQUENCE : ACL_OBJECT_RELATION,
668+
ownerId);
589669
else
590670
old_acl = DatumGetAclPCopy(aclDatum);
591671

592672
/* Determine ID to do the grant as, and available grant options */
593-
select_best_grantor(GetUserId(), istmt->privileges,
673+
select_best_grantor(GetUserId(), this_privileges,
594674
old_acl, ownerId,
595675
&grantorId, &avail_goptions);
596676

@@ -600,8 +680,10 @@ ExecGrant_Relation(InternalGrant *istmt)
600680
*/
601681
this_privileges =
602682
restrict_and_check_grant(istmt->is_grant, avail_goptions,
603-
istmt->all_privs, istmt->privileges,
604-
relOid, grantorId, ACL_KIND_CLASS,
683+
istmt->all_privs, this_privileges,
684+
relOid, grantorId,
685+
pg_class_tuple->relkind == RELKIND_SEQUENCE
686+
? ACL_KIND_SEQUENCE : ACL_KIND_CLASS,
605687
NameStr(pg_class_tuple->relname));
606688

607689
/*
@@ -1336,6 +1418,8 @@ static const char *const no_priv_msg[MAX_ACL_KIND] =
13361418
{
13371419
/* ACL_KIND_CLASS */
13381420
gettext_noop("permission denied for relation %s"),
1421+
/* ACL_KIND_SEQUENCE */
1422+
gettext_noop("permission denied for sequence %s"),
13391423
/* ACL_KIND_DATABASE */
13401424
gettext_noop("permission denied for database %s"),
13411425
/* ACL_KIND_PROC */
@@ -1360,6 +1444,8 @@ static const char *const not_owner_msg[MAX_ACL_KIND] =
13601444
{
13611445
/* ACL_KIND_CLASS */
13621446
gettext_noop("must be owner of relation %s"),
1447+
/* ACL_KIND_SEQUENCE */
1448+
gettext_noop("must be owner of sequence %s"),
13631449
/* ACL_KIND_DATABASE */
13641450
gettext_noop("must be owner of database %s"),
13651451
/* ACL_KIND_PROC */
@@ -1439,6 +1525,7 @@ pg_aclmask(AclObjectKind objkind, Oid table_oid, Oid roleid,
14391525
switch (objkind)
14401526
{
14411527
case ACL_KIND_CLASS:
1528+
case ACL_KIND_SEQUENCE:
14421529
return pg_class_aclmask(table_oid, roleid, mask, how);
14431530
case ACL_KIND_DATABASE:
14441531
return pg_database_aclmask(table_oid, roleid, mask, how);
@@ -1500,9 +1587,9 @@ pg_class_aclmask(Oid table_oid, Oid roleid,
15001587
*
15011588
* As of 7.4 we have some updatable system views; those shouldn't be
15021589
* protected in this way. Assume the view rules can take care of
1503-
* themselves.
1590+
* themselves. ACL_USAGE is if we ever have system sequences.
15041591
*/
1505-
if ((mask & (ACL_INSERT | ACL_UPDATE | ACL_DELETE)) &&
1592+
if ((mask & (ACL_INSERT | ACL_UPDATE | ACL_DELETE | ACL_USAGE)) &&
15061593
IsSystemClass(classForm) &&
15071594
classForm->relkind != RELKIND_VIEW &&
15081595
!has_rolcatupdate(roleid) &&
@@ -1511,7 +1598,7 @@ pg_class_aclmask(Oid table_oid, Oid roleid,
15111598
#ifdef ACLDEBUG
15121599
elog(DEBUG2, "permission denied for system catalog update");
15131600
#endif
1514-
mask &= ~(ACL_INSERT | ACL_UPDATE | ACL_DELETE);
1601+
mask &= ~(ACL_INSERT | ACL_UPDATE | ACL_DELETE | ACL_USAGE);
15151602
}
15161603

15171604
/*
@@ -1536,7 +1623,9 @@ pg_class_aclmask(Oid table_oid, Oid roleid,
15361623
if (isNull)
15371624
{
15381625
/* No ACL, so build default ACL */
1539-
acl = acldefault(ACL_OBJECT_RELATION, ownerId);
1626+
acl = acldefault(classForm->relkind == RELKIND_SEQUENCE ?
1627+
ACL_OBJECT_SEQUENCE : ACL_OBJECT_RELATION,
1628+
ownerId);
15401629
aclDatum = (Datum) 0;
15411630
}
15421631
else

‎src/backend/catalog/pg_shdepend.c‎

Lines changed: 19 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/catalog/pg_shdepend.c,v 1.6 2005/12/01 02:03:00 alvherre Exp $
11+
* $PostgreSQL: pgsql/src/backend/catalog/pg_shdepend.c,v 1.7 2006/01/21 02:16:18 momjian Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -1133,8 +1133,25 @@ shdepDropOwned(List *roleids, DropBehavior behavior)
11331133
switch (sdepForm->classid)
11341134
{
11351135
case RelationRelationId:
1136-
istmt.objtype = ACL_OBJECT_RELATION;
1136+
{
1137+
/* is it a sequence or non-sequence? */
1138+
Form_pg_class pg_class_tuple;
1139+
HeapTuple tuple;
1140+
1141+
tuple = SearchSysCache(RELOID,
1142+
ObjectIdGetDatum(sdepForm->objid),
1143+
0, 0, 0);
1144+
if (!HeapTupleIsValid(tuple))
1145+
elog(ERROR, "cache lookup failed for relation %u",
1146+
sdepForm->objid);
1147+
pg_class_tuple = (Form_pg_class) GETSTRUCT(tuple);
1148+
if (pg_class_tuple->relkind == RELKIND_SEQUENCE)
1149+
istmt.objtype = ACL_OBJECT_SEQUENCE;
1150+
else
1151+
istmt.objtype = ACL_OBJECT_RELATION;
1152+
ReleaseSysCache(tuple);
11371153
break;
1154+
}
11381155
case DatabaseRelationId:
11391156
istmt.objtype = ACL_OBJECT_DATABASE;
11401157
break;

0 commit comments

Comments
 (0)