Pivotal Knowledge Base

Follow

GPText 2.0: Permission denied to non superusers while creating index

Environment

Pivotal Greenplum 4.3.x

GPText 2.x

Symptom

When a non-superuser tries to create index in gptext 2.0, it fails with the following message

Error Message:

gpadmin=> SELECT * FROM gptext.create_index('public', 'email_tbl', 'id', 'english_content');
ERROR:  permission denied for relation gptext_envs
CONTEXT:  SQL statement "SELECT count(*) = 1 FROM gptext.gptext_envs WHERE envname = 'GPTXTHOME'"
PL/pgSQL function "__gptxthome" line 4 at SQL statement
SQL statement "SELECT gptext.__gptxthome()"
PL/pgSQL function "__create_index" line 23 at SQL statement

Cause

By default users apart from gpadmin does not have access privileges over tables present under the gptext schema.

gpadmin=> \z gptext.*
                    Access privileges for database "gpadmin"
 Schema |      Name      | Type  |                Access privileges                
--------+----------------+-------+-------------------------------------------------
 gptext | admin_history  | table | {gpadmin=arwdDxt/gpadmin,=arwDxt/gpadmin}
 gptext | gptext_envs    | table | 
 gptext | solr_instances | table | 
 gptext | zoo_cluster    | table | 
(4 rows)

During index creation, gptext tries to get the count from tables present under the gptext schema, but since the normal user does not have access to these tables, the index creation fails.

How to reproduce this issue

Create user with login privileges. User created in this example - test_gptext

gpadmin=> create table email_tbl (id bigint, english_content text, chinese_content text, timestamp date, username text, age int,number_int int); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Database data distribution key for this table. HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. CREATE TABLE gpadmin=> SELECT * FROM gptext.create_index('public', 'email_tbl', 'id', 'english_content'); ERROR: permission denied for relation gptext_envs CONTEXT: SQL statement "SELECT count(*) = 1 FROM gptext.gptext_envs WHERE envname = 'GPTXTHOME'" PL/pgSQL function "__gptxthome" line 4 at SQL statement SQL statement "SELECT gptext.__gptxthome()" PL/pgSQL function "__create_index" line 23 at SQL statement gpadmin=# grant select on gptext.gptext_envs to test_gptext; GRANT gpadmin=# \q

After granting select on gptext.gptext_envs, it fails with lack of privileges for another table - gptext.zoo_cluster

[gpadmin@mdw gpseg-1]$ psql -U test_gptext; psql (8.2.15) Type "help" for help. gpadmin=> SELECT * FROM gptext.create_index('public', 'email_tbl', 'id', 'english_content'); ERROR: permission denied for relation zoo_cluster CONTEXT: SQL statement "SELECT count(*) != 0 FROM gptext.zoo_cluster" PL/pgSQL function "__zoo_keeper_hosts" line 4 at SQL statement SQL statement "SELECT hosts FROM gptext.__zoo_keeper_hosts()" PL/pgSQL function "index_status" line 5 at SQL statement SQL statement "SELECT count(*) > 0 FROM gptext.index_status( $1 )" PL/pgSQL function "__create_index" line 42 at SQL statement gpadmin=> \q

Following four tables need access privileges by a user


gpadmin=> \z gptext.*
Access privileges for database "gpadmin" Schema | Name | Type | Access privileges --------+----------------+-------+------------------------------------------------- gptext | admin_history | table | {gpadmin=arwdDxt/gpadmin,=arwDxt/gpadmin} gptext | gptext_envs | table | {gpadmin=arwdDxt/gpadmin,test_gptext=r/gpadmin} gptext | solr_instances | table | gptext | zoo_cluster | table | (4 rows)

gpadmin=# grant select on gptext.zoo_cluster to test_gptext;
GRANT gpadmin=# grant select on gptext.solr_instances to test_gptext; GRANT gpadmin=# \q

Granted privileges to the remaining tables and create index worked
[gpadmin@mdw gpseg-1]$ psql -U test_gptext; psql (8.2.15) Type "help" for help. gpadmin=> SELECT * FROM gptext.create_index('public', 'email_tbl', 'id', 'english_content'); INFO: Created index gpadmin.public.email_tbl create_index -------------- t (1 row)

Resolution

Grant select privileges on all the tables present in the gptext schema to the user trying to create an index. 

 

Comments

Powered by Zendesk