Re: [HACKERS] pg_sequences bug ?

[Peter Eisentraut]

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] pg_sequences bug ?

[Michael Paquier]

On Sat, Feb 4, 2017 at 2:50 PM, Shinoda, Noriyoshi
 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 just like 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
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 28be27a07e..907e0fb630 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -175,7 +175,11 @@ CREATE OR REPLACE VIEW pg_sequences AS
 S.seqincrement AS increment_by,
 S.seqcycle AS cycle,
 S.seqcache AS cache_size,
-pg_sequence_last_value(C.oid) AS last_value
+CASE
+WHEN has_sequence_privilege(C.oid, 'SELECT,USAGE'::text)
+THEN pg_sequence_last_value(C.oid)
+ELSE NULL
+END AS last_value
 FROM pg_sequence S JOIN pg_class C ON (C.oid = S.seqrelid)
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
 WHERE NOT pg_is_other_temp_schema(N.oid)
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index de5ae00970..d7a165eb42 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1647,7 +1647,10 @@ pg_sequences| SELECT n.nspname AS schemaname,
 s.seqincrement AS increment_by,
 s.seqcycle AS cycle,
 s.seqcache AS cache_size,
-pg_sequence_last_value((c.oid)::regclass) AS last_value
+CASE
+WHEN has_sequence_privilege(c.oid, 'SELECT,USAGE'::text) THEN pg_sequence_last_value((c.oid)::regclass)
+ELSE NULL::bigint
+END AS last_value
FROM ((pg_sequence s
  JOIN pg_class c ON ((c.oid = s.seqrelid)))
  LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] pg_sequences bug ?

[Shinoda, Noriyoshi]

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 like 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?

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.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers