SEQUENCE keyword is option on GRANT

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.

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 [, ...]

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.