agora inbox for pgsql-docs@postgresql.org
help / color / mirror / Atom feedSEQUENCE keyword is option on GRANT
4+ messages / 3 participants
[nested] [flat]
* SEQUENCE keyword is option on GRANT
@ 2025-03-31 10:29 PG Doc comments form <noreply@postgresql.org>
2025-03-31 13:55 ` Re: SEQUENCE keyword is option on GRANT David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: PG Doc comments form @ 2025-03-31 10:29 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org; +Cc: colinthart@gmail.com
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: SEQUENCE keyword is option on GRANT
2025-03-31 10:29 SEQUENCE keyword is option on GRANT PG Doc comments form <noreply@postgresql.org>
@ 2025-03-31 13:55 ` David G. Johnston <david.g.johnston@gmail.com>
2025-04-01 00:20 ` Re: SEQUENCE keyword is option on GRANT jian he <jian.universality@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: David G. Johnston @ 2025-03-31 13:55 UTC (permalink / raw)
To: colinthart@gmail.com <colinthart@gmail.com>; pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: SEQUENCE keyword is option on GRANT
2025-03-31 10:29 SEQUENCE keyword is option on GRANT PG Doc comments form <noreply@postgresql.org>
2025-03-31 13:55 ` Re: SEQUENCE keyword is option on GRANT David G. Johnston <david.g.johnston@gmail.com>
@ 2025-04-01 00:20 ` jian he <jian.universality@gmail.com>
2025-04-01 01:09 ` Re: SEQUENCE keyword is option on GRANT David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: jian he @ 2025-04-01 00:20 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: colinthart@gmail.com <colinthart@gmail.com>; pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
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 [, ...]
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: SEQUENCE keyword is option on GRANT
2025-03-31 10:29 SEQUENCE keyword is option on GRANT PG Doc comments form <noreply@postgresql.org>
2025-03-31 13:55 ` Re: SEQUENCE keyword is option on GRANT David G. Johnston <david.g.johnston@gmail.com>
2025-04-01 00:20 ` Re: SEQUENCE keyword is option on GRANT jian he <jian.universality@gmail.com>
@ 2025-04-01 01:09 ` David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: David G. Johnston @ 2025-04-01 01:09 UTC (permalink / raw)
To: jian he <jian.universality@gmail.com>; +Cc: colinthart@gmail.com <colinthart@gmail.com>; pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-04-01 01:09 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-03-31 10:29 SEQUENCE keyword is option on GRANT PG Doc comments form <noreply@postgresql.org>
2025-03-31 13:55 ` David G. Johnston <david.g.johnston@gmail.com>
2025-04-01 00:20 ` jian he <jian.universality@gmail.com>
2025-04-01 01:09 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox