Thread: [HACKERS] pg_sequences bug ?
Hi hackers, I tried a committed pg_sequences for PostgreSQL 10dev (https://commitfest.postgresql.org/12/771/). I found that when multiple users create SEQUENCE, I cannot see the pg_sequences catalog. I think that should work just likepg_tables. $ psql -U user1 postgres=> CREATE SEQUENCE seq1 ; CREATE SEQUENCE $ psql -U user2 postgres=> CREATE SEQUENCE seq2 ; CREATE SEQUENCE postgres=> SELECT * FROM pg_sequences ; ERROR: permission denied for sequence seq1 Apparently it seems that the pg_sequence_last_value function included in the pg_sequences view definition cannot be executed. Is this behavior supposed? Snapshot: https://ftp.postgresql.org/pub/snapshot/dev/postgresql-snapshot.tar.gz 2017-02-04 00:29:04 Operating System: Red Hat Enterprise Linux 7 Update 2 (x86-64) Regards.
On Sat, Feb 4, 2017 at 2:50 PM, Shinoda, Noriyoshi <noriyoshi.shinoda@hpe.com> wrote: > I tried a committed pg_sequences for PostgreSQL 10dev (https://commitfest.postgresql.org/12/771/). > I found that when multiple users create SEQUENCE, I cannot see the pg_sequences catalog. I think that should work justlike pg_tables. > > $ psql -U user1 > postgres=> CREATE SEQUENCE seq1 ; > CREATE SEQUENCE > > $ psql -U user2 > postgres=> CREATE SEQUENCE seq2 ; > CREATE SEQUENCE > postgres=> SELECT * FROM pg_sequences ; > ERROR: permission denied for sequence seq1 > > Apparently it seems that the pg_sequence_last_value function included in the pg_sequences view definition cannot be executed. > Is this behavior supposed? That seems user-unfriendly to me. We could perhaps just use has_sequence_privilege() and return NULL if the caller of pg_sequences does not have select and usage access to a given sequence? Please see the patch attached. -- Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Attachment
On 2/4/17 7:30 AM, Michael Paquier wrote: > We could perhaps just use has_sequence_privilege() and return NULL if > the caller of pg_sequences does not have select and usage access to a > given sequence? Please see the patch attached. Committed with documentation updates. Thanks. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services