Обсуждение: BUG #16018: pg_sequence_last_value() doesn't give results for owners
The following bug has been logged on the website: Bug reference: 16018 Logged by: Robert Treat Email address: rob@xzilla.net PostgreSQL version: 11.5 Operating system: osx Description: I think there is a bug of sorts with pg_sequence_last_value(), in that the security checks prevent a superuser (or owner) from seeing the last_value of a sequence by way of this function. That seems like a use case that should be supported (and since it isn't, it causes problems for a lot of other use cases). As an example: postgres@54321:pagila=# create sequence x; CREATE SEQUENCE postgres@54321:pagila=# select pg_sequence_last_value('x'::regclass); pg_sequence_last_value ------------------------ (1 row) postgres@54321:pagila=# select * from x; last_value | log_cnt | is_called ------------+---------+----------- 1 | 0 | f (1 row) postgres@54321:pagila=# select * from pg_sequences where sequencename = 'x'; -[ RECORD 1 ]-+-------------------- schemaname | public sequencename | x sequenceowner | postgres data_type | bigint start_value | 1 min_value | 1 max_value | 9223372036854775807 increment_by | 1 cycle | f cache_size | 1 last_value | Related, has_sequence_privilege() seems to indicate that this should all work: postgres@54321:pagila=# select has_sequence_privilege('x'::regclass, 'SELECT,USAGE'::text); has_sequence_privilege ------------------------ t (1 row) The backend code for pg_sequence_last_value is checking this: if (pg_class_aclcheck(relid, GetUserId(), ACL_SELECT | ACL_USAGE) != ACLCHECK_OK) Robert Treat https://xzilla.net
Re: BUG #16018: pg_sequence_last_value() doesn't give results for owners
От
"David G. Johnston"
Дата:
On Mon, Sep 23, 2019 at 2:46 PM PG Bug reporting form <noreply@postgresql.org> wrote:
The following bug has been logged on the website:
Bug reference: 16018
Logged by: Robert Treat
Email address: rob@xzilla.net
PostgreSQL version: 11.5
Operating system: osx
Description:
I think there is a bug of sorts with pg_sequence_last_value(), in that the
security checks prevent a superuser (or owner) from seeing the last_value of
a sequence by way of this function. That seems like a use case that should
be supported (and since it isn't, it causes problems for a lot of other use
cases). As an example:
postgres@54321:pagila=# create sequence x;
CREATE SEQUENCE
postgres@54321:pagila=# select pg_sequence_last_value('x'::regclass);
pg_sequence_last_value
------------------------
(1 row)
From pg_sequences:
"""
last_value bigint The last sequence value written to disk. If caching is used, this value can be greater than the last value handed out from the sequence. Null if the sequence has not been read from yet. Also, if the current user does not have USAGE or SELECT privilege on the sequence, the value is null."""
The null in the example seems to be due to the first condition (not been read from) not the second (privileges).
postgres@54321:pagila=# select * from x;
last_value | log_cnt | is_called
------------+---------+-----------
1 | 0 | f
(1 row)
The "last_value" on the sequence object has a different definition than the one on the relatively new pg_sequences catalog.
I'm not seeing where a bug has been shown to exist though I'll agree that the quick reading of the documentation leaves some level of confusion.
David J.
On Mon, Sep 23, 2019 at 6:34 PM David G. Johnston <david.g.johnston@gmail.com> wrote: > > On Mon, Sep 23, 2019 at 2:46 PM PG Bug reporting form <noreply@postgresql.org> wrote: >> >> The following bug has been logged on the website: >> >> Bug reference: 16018 >> Logged by: Robert Treat >> Email address: rob@xzilla.net >> PostgreSQL version: 11.5 >> Operating system: osx >> Description: >> >> I think there is a bug of sorts with pg_sequence_last_value(), in that the >> security checks prevent a superuser (or owner) from seeing the last_value of >> a sequence by way of this function. That seems like a use case that should >> be supported (and since it isn't, it causes problems for a lot of other use >> cases). As an example: >> >> postgres@54321:pagila=# create sequence x; >> CREATE SEQUENCE >> >> postgres@54321:pagila=# select pg_sequence_last_value('x'::regclass); >> pg_sequence_last_value >> ------------------------ >> >> (1 row) >> > > From pg_sequences: > """ > last_value bigint The last sequence value written to disk. If caching is used, this value can be greater than the lastvalue handed out from the sequence. Null if the sequence has not been read from yet. Also, if the current user does nothave USAGE or SELECT privilege on the sequence, the value is null. > """ > Ah, I hadn't really been working with pg_sequences, and there is no documentation on pg_sequence_last_value, but since the former is implemented using the latter, I guess it is safe to assume those limitations would also apply to the underlying function. > The null in the example seems to be due to the first condition (not been read from) not the second (privileges). > Ugh... the situation is further complicated in that setval seems to cause a read, but alter sequence restart does not. Fun. >> postgres@54321:pagila=# select * from x; >> last_value | log_cnt | is_called >> ------------+---------+----------- >> 1 | 0 | f >> (1 row) > > > The "last_value" on the sequence object has a different definition than the one on the relatively new pg_sequences catalog. > Any chance you have a pointer to documentation on the differences? I originally wanted to provide access to the information from the sequence object, but that idea has been fading quite quickly. > I'm not seeing where a bug has been shown to exist though I'll agree that the quick reading of the documentation leavessome level of confusion. > This might still just be a case of my simplified example being deficient, but this gives me more to work with; hopefully enough to either solve my problem or come up with a better test scenario. Thanks for the response. Robert Treat https://xzilla.net
Robert Treat <rob@xzilla.net> writes: > On Mon, Sep 23, 2019 at 6:34 PM David G. Johnston > <david.g.johnston@gmail.com> wrote: >> From pg_sequences: >> last_value bigint The last sequence value written to disk. If caching is used, this value can be greater than the lastvalue handed out from the sequence. Null if the sequence has not been read from yet. Also, if the current user does nothave USAGE or SELECT privilege on the sequence, the value is null. > Ugh... the situation is further complicated in that setval seems to > cause a read, but alter sequence restart does not. Fun. setval can set is_called to either true or false. In the false state, you get a null here. >> The "last_value" on the sequence object has a different definition than the one on the relatively new pg_sequences catalog. > Any chance you have a pointer to documentation on the differences? David quoted the docs about pg_sequences.last_value. pg_sequence_last_value() isn't documented separately because you're not really supposed to call it directly, only through the view. currval()/lastval() give the last sequence value actually returned in the current session, independently of what other sessions did since. regards, tom lane