Pivotal Knowledge Base

Follow

Insert statement failed in Pivotal HDB if inserting rows by selecting them from another table.

Environment

HDB 1.1.3 / PHD 1.1.1

Problem

 A database user has select permission on table B. But the user gets permission denied error while inserting into table A by selecting rows from table B.

gpadmin=> INSERT INTO test1.test1table SELECT * from test2.test2table ;
ERROR: permission denied for relation test2table

The user won’t have this issue if the user also has insert permission on table B.

 Steps to reproduce the problem:

 1: Create two users – test1 and test2. Grant database access to both users.

gpadmin=# CREATE USER test1;
NOTICE: resource queue required -- using default resource queue "pg_default"
CREATE ROLE
gpadmin=# create user test2;
NOTICE: resource queue required -- using default resource queue "pg_default"
CREATE ROLE
gpadmin=# GRANT ALL ON DATABASE gpadmin to test1, test2;
GRANT

2: Switch to user test1 and create a schema named test1 and table named test1table.

gpadmin=# SET session AUTHORIZATION test1;
SET
gpadmin=> CREATE SCHEMA test1;
CREATE SCHEMA
gpadmin=> CREATE TABLE test1.test1table(id int) distributed by (id);
CREATE TABLE

 3: Switch to user test2 and create a schema named test2 and table named test2table. Insert 1000 rows to the table.

gpadmin=> SET session AUTHORIZATION test2;
SET
gpadmin=> CREATE SCHEMA test2;
CREATE SCHEMA
gpadmin=> CREATE TABLE test2.test2table(id int) distributed by (id);
CREATE TABLE
gpadmin=> INSERT INTO test2.test2table VALUES (generate_series(1,1000));
INSERT 0 1000

 4: Grant usage permission on the schema and select permission of the table created by user test2 to user test1.

gpadmin=> GRANT USAGE ON SCHEMA test2 to test1;
GRANT
gpadmin=> GRANT SELECT ON test2.test2table TO test1;
GRANT

5: Switch to user test1. If user test1 run select from test2.test2table, it worked ok. But when inserting into test1.test1table by selecting from test2.test, it failed.

gpadmin=> SET session AUTHORIZATION test1; 
SET
gpadmin=> SELECT * from test2.test2table limit 5;
id
----
4
8
12
16
20 <(5 rows)
gpadmin=> INSERT INTO test1.test1table SELECT * from test2.test2table ;
ERROR: permission denied for relation test2table

 Solution / Workaround:

-  Grant insert privileges on test2.test2table.

- This problem was observed in hawq 1.1.3 only. To fix the problem, upgrade to hawq 1.2.x (PHD 2.0.1) and later.

Comments

Powered by Zendesk