Pivotal Knowledge Base

Follow

Query table failed with "ERROR: permission denied for sequence xxx"

Environment

Product Version
Pivotal HDB 2.0.x
OS RHEL 6.x
Others  

Symptom

When attempting to query a table, it failed with the error message “permission denied for sequence xxx”.

gpadmin=# grant SELECT,USAGE on sequence ns1.t1_c2_seq to ro_user;
[gpadmin@hdm1 ~]$ psql -U ro_user -d gpadmin
select * from ns1.t1; 
ERROR: permission denied for sequence t1_c2_seq

Cause

The table contains a column with a default value from a sequence object. The user has been granted SELECT and USAGE privileges on this sequence to submit the query, which should be sufficient to query the sequence. However, there is a software defect in HDB system which enforces UPDATE privilege required for using a sequence.

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

The query will be successful after UPDATE privilege is granted on the sequence to the user.

gpadmin=# grant UPDATE on sequence ns1.t1_c2_seq to ro_user;
[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)

Resolution

This defect will be fixed in HDB 2.0.2; so upgrade to this release once it's ready to get the permanent fix.

Before the fix is ready, the workaround is to temporarily grant UPDATE privilege on the sequence object to the user for using the sequence.

 

Comments

Powered by Zendesk