Re: SEQUENCE keyword is option on GRANT

On Mon, Mar 31, 2025 at 5:21 PM jian he <jian(dot)universality(at)gmail(dot)com> wrote:

> On Mon, Mar 31, 2025 at 9:55 PM David G. Johnston
> <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> >
> >
> > On Monday, March 31, 2025, PG Doc comments form <noreply(at)postgresql(dot)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(at)[local]:colin=> create sequence x;
> >> CREATE SEQUENCE
> >> colin(at)[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.