hawq-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Ming LI (JIRA)" <j...@apache.org>
Subject [jira] [Created] (HAWQ-1076) permission denied for using sequence with SELECT/USUAGE privilege
Date Mon, 26 Sep 2016 06:45:20 GMT
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)

Mime
View raw message