[
https://issues.apache.org/jira/browse/HAWQ-1076?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Ming LI resolved HAWQ-1076.
---------------------------
Resolution: Fixed
> 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: Ming LI
> 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)
