How to check get the privilege to check the view gp_toolkit.gp_skew_coefficients with normal user ?
Article ID: 296819
Updated On:
Products
Issue/Introduction
Normally the records of view gp_toolkit.gp_skew_coefficients can only be seen by admin user like gpadmin. A normal user can't even if it has the privilege on this view:
gpadmin=# select * from gp_toolkit.gp_skew_coefficients; skcoid | skcnamespace | skcrelname | skccoeff --------+--------------+-------------+--------------------------- 16431 | public | test1 | 200.000000000000000000000 16818 | public | sbd_ifxt001 | 0 16908 | public | base_table | 0 16422 | public | test_backup | 0 17011 | public | t1 | 141.421356237309500000000 16944 | public | country | 81.649658092772603273000 (6 rows) gpadmin=# \c - ####### You are now connected to database "gpadmin" as user "#######". gpadmin=> select * from gp_toolkit.gp_skew_coefficients; skcoid | skcnamespace | skcrelname | skccoeff --------+--------------+-------------+---------- (0 row)
So what's the reason?
Environment
Resolution
If we check the DDL of the view, we can get some base views or functions of it, and with deeper dive into it, we will finally get this view:
gpadmin=> \c - gpadmin You are now connected to database "gpadmin" as user "gpadmin". gpadmin=# SELECT autoid from gp_toolkit.__gp_user_data_tables_readable; autoid -------- 16422 16431 16818 16908 16944 (5 rows)
We will not see any record of this view using a normal user:
gpadmin=# \c - ####### You are now connected to database "gpadmin" as user "#######". gpadmin=> SELECT autoid from gp_toolkit.__gp_user_data_tables_readable; autoid -------- (0 row)
The key is hiding in the view definition:
View definition:
SELECT aut.autnspname,
aut.autrelname,
aut.autrelkind,
aut.autreltuples,
aut.autrelpages,
aut.autrelacl,
aut.autoid,
aut.auttoastoid,
aut.autrelstorage
FROM gp_toolkit.__gp_user_tables aut
WHERE has_table_privilege(aut.autoid, 'select'::text);
The WHERE clause in the view definition means the user needs to have SELECT privileges on the tables shown in the result of the view gp_toolkit.__gp_user_tables. Please note this view can be seen by any user. Then let's check the view:
gpadmin=> select * from gp_toolkit.__gp_user_tables;
autnspname | autrelname | autrelkind | autreltuples | autrelpages | autrelacl | autoid | auttoastoid | autrelstorage
------------+-------------+------------+--------------+-------------+---------------------------------------------+--------+-------------+---------------
public | test1 | r | 1 | 1 | | 16431 | 0 | h
public | sbd_ifxt001 | r | 0 | 1 | | 16818 | 0 | h
public | base_table | r | 0 | 1 | | 16908 | 0 | h
public | test_backup | r | 0 | 1 | {gpadmin=arwdDxt/gpadmin,#######=r/gpadmin} | 16422 | 0 | h
public | t1 | r | 0 | 0 | | 17011 | 17014 | h
public | country | r | 4 | 3 | | 16944 | 17026 | h
(6 rows)
Currently, let's grant privilege on any one of the 6 tables to the normal user jimmy11, then jimmy11 will see that table from the view gp_toolkit.gp_skew_coefficients:
gpadmin=> \c - gpadmin You are now connected to database "gpadmin" as user "gpadmin". gpadmin=# grant select on public.test_backup to #######; GRANT Time: 10.861 ms gpadmin=# \c - ####### You are now connected to database "gpadmin" as user "#######". gpadmin=> select * from gp_toolkit.gp_skew_coefficients; skcoid | skcnamespace | skcrelname | skccoeff --------+--------------+-------------+---------- 16422 | public | test_backup | 0 (1 row)
This explains why a normal user could not see the records of the view gp_toolkit.gp_skew_coefficients, and how to make it viewable for a normal user(although it's not a good practice and not so straightforward).
Feedback
