Ming LI created HAWQ-1076:
-----------------------------
Summary: permission denied for using sequence with SELECT/USUAGE
privilege
Key: HAWQ-1076
URL: https://issues.apache.org/jira/browse/HAWQ-1076
Project: Apache HAWQ
Issue Type: Bug
Components: Catalog
Reporter: Ming LI
Assignee: Lei Chang
Fix For: backlog
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
{code:java}
[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)
{code}
2. Connect to database as user with readonly access and run SELECT query
against the table. It will fail with "permission denied" error
{code:java}
[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
{code}
3. grant ALL privilege on the sequence to that user, which makes it be able to
SELECT out data from the table
{code:java}
[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)
{code}
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?
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
