[jira] [Resolved] (HAWQ-1076) permission denied for using sequence with SELECT/USUAGE privilege

["Ming LI (JIRA)" <ji...@apache.org>]

     [ 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)