You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@hawq.apache.org by "Ming LI (JIRA)" <ji...@apache.org> on 2016/09/26 07:05:21 UTC
[jira] [Comment Edited] (HAWQ-1076) permission denied for using
sequence with SELECT/USUAGE privilege
[ https://issues.apache.org/jira/browse/HAWQ-1076?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15522283#comment-15522283 ]
Ming LI edited comment on HAWQ-1076 at 9/26/16 7:04 AM:
--------------------------------------------------------
Two problems fixed here:
1) Default statement only need when INSERT.
2) setval() need UPDATE privilege, while nextval() need USAGE or UPDATE privilege.
{quotes}
[ro_user] postgres=> select * from t1;
ERROR: permission denied for relation t1
[gpadmin] postgres=# grant SELECT on table t1 to role1;
GRANT
[ro_user] postgres=> select * from t1;
c1 | c2
---+---
1 | 1
1 | 2
(2 rows)
[ro_user] postgres=> insert into t1 (c1) values(11);
ERROR: permission denied for relation t1
[gpadmin] postgres=# grant INSERT on table t1 to role1;
GRANT
[ro_user] postgres=> insert into t1 (c1) values(11);
ERROR: permission denied for sequence seq1
[gpadmin] postgres=# grant USAGE on sequence seq1 to role1;
GRANT
[ro_user] postgres=> insert into t1 (c1) values(11);
INSERT 0 1
[ro_user] postgres=> select setval('seq1', 1, true) ;
ERROR: permission denied for sequence seq1
[gpadmin] postgres=# grant UPDATE on sequence seq1 to role1;
GRANT
[ro_user] postgres=> select setval('seq1', 1, true) ;
setval
--------
1
(1 row)
{/quotes}
was (Author: mli):
Two problems fixed here:
1) Default statement only need when INSERT.
2) setval() need UPDATE privilege, while nextval() need USAGE or UPDATE privilege.
```
[ro_user] postgres=> select * from t1;
ERROR: permission denied for relation t1
[gpadmin] postgres=# grant SELECT on table t1 to role1;
GRANT
[ro_user] postgres=> select * from t1;
c1 | c2
---+---
1 | 1
1 | 2
(2 rows)
[ro_user] postgres=> insert into t1 (c1) values(11);
ERROR: permission denied for relation t1
[gpadmin] postgres=# grant INSERT on table t1 to role1;
GRANT
[ro_user] postgres=> insert into t1 (c1) values(11);
ERROR: permission denied for sequence seq1
[gpadmin] postgres=# grant USAGE on sequence seq1 to role1;
GRANT
[ro_user] postgres=> insert into t1 (c1) values(11);
INSERT 0 1
[ro_user] postgres=> select setval('seq1', 1, true) ;
ERROR: permission denied for sequence seq1
[gpadmin] postgres=# grant UPDATE on sequence seq1 to role1;
GRANT
[ro_user] postgres=> select setval('seq1', 1, true) ;
setval
--------
1
(1 row)
```
> 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)