Postgres Pro
Downloads
Home   >   mailing lists

Thread: SEQUENCE keyword is option on GRANT

SEQUENCE keyword is option on GRANT

From
PG Doc comments form
Date:
The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/17/sql-grant.html
Description:

The documentation indicates that they keyword SEQUENCE must precede the
sequence_name in a GRANT, but in my experience it is optional:

colin@adinkra:~$ psql
psql (17.4 (Debian 17.4-1.pgdg120+2))
Type "help" for help.

colin@[local]:colin=> create sequence x;
CREATE SEQUENCE
colin@[local]:colin=> grant select on x to public; -- should fail but
works
GRANT
colin@[local]:colin=> grant usage on sequence x to public; -- works as
expected
GRANT
colin@[local]:colin=> rollback;
ROLLBACK
colin@[local]:colin=> 

I'm not sure how intentional this is, and whether or not this is mandated by
the SQL standard.

Colin 't Hart

Re: SEQUENCE keyword is option on GRANT

From
"David G. Johnston"
Date:

On Monday, March 31, 2025, PG Doc comments form <noreply@postgresql.org> wrote:
The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/17/sql-grant.html
Description:

The documentation indicates that they keyword SEQUENCE must precede the
sequence_name in a GRANT, but in my experience it is optional:

colin@adinkra:~$ psql
psql (17.4 (Debian 17.4-1.pgdg120+2))
Type "help" for help.

colin@[local]:colin=> create sequence x;
CREATE SEQUENCE
colin@[local]:colin=> grant select on x to public; -- should fail but
works


Sequences are relations (see pg_class) so the [TABLE] variant is able to target them, which is what you’ve written here.

David J.

Re: SEQUENCE keyword is option on GRANT

From
jian he
Date:
On Mon, Mar 31, 2025 at 9:55 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
>
> On Monday, March 31, 2025, PG Doc comments form <noreply@postgresql.org> wrote:
>>
>> The following documentation comment has been logged on the website:
>>
>> Page: https://www.postgresql.org/docs/17/sql-grant.html
>> Description:
>>
>> The documentation indicates that they keyword SEQUENCE must precede the
>> sequence_name in a GRANT, but in my experience it is optional:
>>
>> colin@adinkra:~$ psql
>> psql (17.4 (Debian 17.4-1.pgdg120+2))
>> Type "help" for help.
>>
>> colin@[local]:colin=> create sequence x;
>> CREATE SEQUENCE
>> colin@[local]:colin=> grant select on x to public; -- should fail but
>> works
>>
>
> Sequences are relations (see pg_class) so the [TABLE] variant is able to target them, which is what you’ve written
here.
>

but

 ON { SEQUENCE sequence_name [, ...]

need change to

 ON { [SEQUENCE] sequence_name [, ...]

in grant.sgml

but play around more.

src6=# create table y();
src6=# create sequence ys;

grant select on table ys to public; --ok
grant usage on table ys to public; --ok
grant update on table ys to public; ---ok.

 grant insert  on table ys to public; ---ok.
WARNING:  sequence "ys" only supports USAGE, SELECT, and UPDATE privileges

grant update on sequence y to public;
ERROR:  "y" is not a sequence

grant insert  on sequence y to public;
ERROR:  invalid privilege type INSERT for sequence


so
GRANT { { USAGE | SELECT | UPDATE }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { SEQUENCE sequence_name [, ...]

really should be

GRANT { { USAGE | SELECT | UPDATE }
    [, ...] | ALL [ PRIVILEGES ] }
 ON { [{SEQUENCE | TABLE}] sequence_name [, ...]

Re: SEQUENCE keyword is option on GRANT

From
"David G. Johnston"
Date:
On Mon, Mar 31, 2025 at 5:21 PM jian he <jian.universality@gmail.com> wrote:
On Mon, Mar 31, 2025 at 9:55 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
>
> On Monday, March 31, 2025, PG Doc comments form <noreply@postgresql.org> wrote:
>>
>> The following documentation comment has been logged on the website:
>>
>> Page: https://www.postgresql.org/docs/17/sql-grant.html
>> Description:
>>
>> The documentation indicates that they keyword SEQUENCE must precede the
>> sequence_name in a GRANT, but in my experience it is optional:
>>

>> colin@[local]:colin=> create sequence x;
>> CREATE SEQUENCE
>> colin@[local]:colin=> grant select on x to public; -- should fail but
>> works
>>
>
> Sequences are relations (see pg_class) so the [TABLE] variant is able to target them, which is what you’ve written here.
>


so
GRANT { { USAGE | SELECT | UPDATE }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { SEQUENCE sequence_name [, ...]

really should be

GRANT { { USAGE | SELECT | UPDATE }
    [, ...] | ALL [ PRIVILEGES ] }
 ON { [{SEQUENCE | TABLE}] sequence_name [, ...]

Technically, that fails to cover the non-error produced by:

grant insert  on table ys to public; ---ok.
WARNING:  sequence "ys" only supports USAGE, SELECT, and UPDATE privileges

But I could maybe be convinced to live with that.

As an alternative I suggest adding this to Compatibility:

   <para>
    In PostgreSQL, the concept of relation encompassess the various
    table-like objects it supports.  Sequences are one of these types; and
    therefore the <literal>GRANT ... ON [ TABLE ]</literal> variant will
    accept a sequence name as the target object.  It will produce a warning
    if the permission being granted is not appropriate for a sequence, and
    will accept and process the undocumented <literal>USAGE</literal> privilege.
   </para>


But overall I'm having trouble getting enthused about trying to document the weird side-effects of our generalization of relation and attempts to simplify the parser.  (We'd need to research revoke as well.)

We are documenting what people should be writing. I'm kinda fine with that.  If you know a grant command for sequences exists you should be using it.  We aren't apt to break your code in the future if you don't write the word sequence, but it also is using an undocumented feature.  Which is why I probably prefer the more verbose, but complete, compatibility note rather than messing around with the syntax; if we do anything at all.

David J.