HCatalog Queries for Hive Tables fails for relation pg_authid permission denied

Products

• Services Suite

Issue/Introduction

Symptoms:

When creating local users without superuser privileges, those local users may run into permission errors when querying external HIVE tables using HCatalog integration.

Steps to Reproduce

1. Create an OS account on all cluster nodes: 

[root@hdm1 ~]# id kroberts
uid=1006(kroberts) gid=1006(kroberts) groups=1006(kroberts),500(hadoop)
[root@hdm1 ~]#

2. su to the new user:

su - kroberts

3. Open Hive CLI and create a new database and a table to access from HAWQ:

[kroberts@hdm2 ~]$ hive
WARNING: Use "yarn jar" to launch YARN applications.
Logging initialized using configuration in file:/etc/hive/2.4.2.0-258/0/hive-log4j.properties
hive>
hive> create database kroberts_hive;
hive> use kroberts_hive;
hive> CREATE TABLE test_hcatalog_newdb_table (name string, type string, supplier_key int, full_price double) row format delimited fields terminated by ',';
hive> LOAD DATA local inpath '/tmp/data.txt' into table test_hcatalog_newdb_table;
hive> select * from test_hcatalog_newdb_table;
OK
kyle engineer 1 10.49
sara sister 2 99.99
zak brother 3 100.01
skip father 4 99.49
carol mother 5 999.48
Time taken: 0.995 seconds, Fetched: 5 row(s)
hive> 

4. The GPADMIN user can access the table via HCATALOG:

[gpadmin@hdm1 ~]$ psql postgres
psql (8.2.15)
Type "help" for help. 
postgres=# SELECT * from hcatalog.kroberts_hive.test_hcatalog_newdb_table;
name | type | supplier_key | full_price
-------+----------+--------------+------------
kyle | engineer | 1 | 10.49
sara | sister | 2 | 99.99
zak | brother | 3 | 100.01
skip | father | 4 | 99.49
carol | mother | 5 | 999.48
(5 rows) 
postgres=#

5. However, the non-superuser role cannot be accessed via HCATALOG:

[gpadmin@hdm1 ~]$ psql postgres
psql (8.2.15)
Type "help" for help.
postgres=# CREATE ROLE kroberts LOGIN;
postgres=# GRANT ALL ON PROTOCOL pxf TO "kroberts"; 
[kroberts@hdm1 ~]$ psql -U kroberts -d postgres -h 127.0.0.1
psql (8.2.15)
Type "help" for help.
postgres=>
postgres=> SELECT * from hcatalog.kroberts_hive.test_hcatalog_newdb_table;
ERROR: permission denied for relation pg_authid
LINE 1: SELECT * from hcatalog.kroberts_hive.test_hcatalog_newdb_tab.  

Environment

Cause

HAWQ's usage of PXF protocol currently requires read (SELECT) privileges for at least two catalog tables and only GPADMIN and HAWQ super users have access to the system tables. This issue has been reported to engineering via Apache HAWQ JIRA HAWQ-1130.

Resolution

Note: This workaround may cause a security issue in certain environments:

1. As GPADMIN role:

postgres=# \dp pg_catalog.pg_authid
Access privileges for database "postgres"
Schema | Name | Type | Access privileges
------------+-----------+-------+--------------------------
pg_catalog | pg_authid | table | {gpadmin=arwdxt/gpadmin}
(1 row)
 
postgres=# GRANT SELECT ON TABLE pg_catalog.pg_authid TO kroberts;
GRANT
postgres=#
 
postgres=# \dpS+ pg_catalog.pg_authid
Access privileges for database "postgres"
-[ RECORD 1 ]-----+--------------------------------------------
Schema | pg_catalog
Name | pg_authid
Type | table
Access privileges | {gpadmin=arwdxt/gpadmin,kroberts=r/gpadmin}= 

2. As having the GPADMIN role:

postgres=# \dpS+ pg_catalog.pg_user_mapping
Access privileges for database "postgres"
-[ RECORD 1 ]-----+-------------------------
Schema | pg_catalog
Name | pg_user_mapping
Type | table
Access privileges | {gpadmin=arwdxt/gpadmin}
 
postgres=#
 
postgres=# GRANT SELECT ON TABLE pg_catalog.pg_user_mapping TO kroberts;
GRANT
postgres=#
postgres=#
postgres=# \dpS+ pg_catalog.pg_user_mapping
Access privileges for database "postgres"
-[ RECORD 1 ]-----+--------------------------------------------
Schema | pg_catalog
Name | pg_user_mapping
Type | table
Access privileges | {gpadmin=arwdxt/gpadmin,kroberts=r/gpadmin} 

3. Test as the non-superuser role: 

postgres=# set role kroberts ;
SET
postgres=> SELECT * from hcatalog.kroberts_hive.test_hcatalog_newdb_table;
name | type | supplier_key | full_price
-------+----------+--------------+------------
kyle | engineer | 1 | 10.49
sara | sister | 2 | 99.99
zak | brother | 3 | 100.01
skip | father | 4 | 99.49
carol | mother | 5 | 999.48
(5 rows)