Major Description
Customer had a table with a column taking default value from a sequence. And they want a role have readonly access to the table as well as the sequence. However they have to grant ALL privilege on the sequence to the user for running SELECT query. Otherwise it will fail with "ERROR: permission denied for sequence xxx".
Following are the steps to reproduce the issue in house.
1. Create a table with column taking default value from a sequence. And grant SELECT/USAGE privilege on the sequence to a user
[gpadmin@hdm1 ~]$ psql psql (8.2.15) Type "help" for help. gpadmin=# \d ns1.t1 Append-Only Table "ns1.t1" Column | Type | Modifiers --------+---------+----------------------------------------------------- c1 | text | c2 | integer | not null default nextval('ns1.t1_c2_seq'::regclass) Compression Type: None Compression Level: 0 Block Size: 32768 Checksum: f Distributed randomly gpadmin=# grant SELECT,usage on sequence ns1.t1_c2_seq to ro_user; GRANT gpadmin=# select * from pg_class where relname='t1_c2_seq'; relname | relnamespace | reltype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | reltoast relid | reltoastidxid | relaosegrelid | relaosegidxid | relhasindex | relisshared | relkind | relstorage | relnatts | relchecks | reltriggers | relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules | relhassubclass | rel frozenxid | relacl | reloptions -----------+--------------+---------+----------+-------+-------------+---------------+----------+-----------+--------- ------+---------------+---------------+---------------+-------------+-------------+---------+------------+----------+- ----------+-------------+----------+----------+---------+------------+------------+-------------+----------------+---- ----------+------------------------------------------+------------ t1_c2_seq | 17638 | 17650 | 10 | 0 | 17649 | 0 | 1 | 1 | 0 | 0 | 0 | 0 | f | f | S | h | 9 | 0 | 0 | 0 | 0 | 0 | f | f | f | f | 0 | {gpadmin=rwU/gpadmin,ro_user=rU/gpadmin} | (1 row) gpadmin=# insert into ns1.t1(c1) values('abc'); INSERT 0 1 gpadmin=# select * from ns1.t1; c1 | c2 -----+---- abc | 3 (1 row)
2. Connect to database as user with readonly access and run SELECT query against the table. It will fail with "permission denied" error
[gpadmin@hdm1 ~]$ psql -U ro_user -d gpadmin psql (8.2.15) Type "help" for help. gpadmin=> select * from ns1.t1; ERROR: permission denied for sequence t1_c2_seq
3. grant ALL privilege on the sequence to that user, which makes it be able to SELECT out data from the table
[gpadmin@hdm1 ~]$ psql gpadmin-# psql (8.2.15) gpadmin-# Type "help" for help. gpadmin-# gpadmin=# grant update on sequence ns1.t1_c2_seq to ro_user; GRANT gpadmin=# select * from pg_class where relname='t1_c2_seq'; relname | relnamespace | reltype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | reltoast relid | reltoastidxid | relaosegrelid | relaosegidxid | relhasindex | relisshared | relkind | relstorage | relnatts | relchecks | reltriggers | relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules | relhassubclass | rel frozenxid | relacl | reloptions -----------+--------------+---------+----------+-------+-------------+---------------+----------+-----------+--------- ------+---------------+---------------+---------------+-------------+-------------+---------+------------+----------+- ----------+-------------+----------+----------+---------+------------+------------+-------------+----------------+---- ----------+-------------------------------------------+------------ t1_c2_seq | 17638 | 17650 | 10 | 0 | 17649 | 0 | 1 | 1 | 0 | 0 | 0 | 0 | f | f | S | h | 9 | 0 | 0 | 0 | 0 | 0 | f | f | f | f | 0 | {gpadmin=rwU/gpadmin,ro_user=rwU/gpadmin} | (1 row) gpadmin=# \q [gpadmin@hdm1 ~]$ psql -U ro_user -d gpadmin psql (8.2.15) Type "help" for help. gpadmin=> select * from ns1.t1; c1 | c2 -----+---- abc | 3 (1 row)
It doesn't seem reasonable for a user to have FULL privilege on a sequence to merely SELECT data from a table. Is it a software defect or a designed behavior?
