Method 1:
\dp <schemaname.tablename>
Method 2:
using information_schema.role_table_grants.
View "information_schema.role_table_grants"
Column | Type | Modifiers
---------------- +----------------------------------- +-----------
grantor | information_schema.sql_identifier |
grantee | information_schema.sql_identifier |
table_catalog | information_schema.sql_identifier |
table_schema | information_schema.sql_identifier |
table_name | information_schema.sql_identifier |
privilege_type | information_schema.character_data |
is_grantable | information_schema.yes_or_no |
with_hierarchy | information_schema.yes_or_no |
There are few information schema views which can be used to see grants.
gpadmin=# \d information_schema.*gran*
View "information_schema.role_column_grants"
Column | Type | Modifiers
---------------- +----------------------------------- +-----------
grantor | information_schema.sql_identifier |
grantee | information_schema.sql_identifier |
table_catalog | information_schema.sql_identifier |
table_schema | information_schema.sql_identifier |
table_name | information_schema.sql_identifier |
column_name | information_schema.sql_identifier |
privilege_type | information_schema.character_data |
is_grantable | information_schema.yes_or_no |
View "information_schema.role_routine_grants"
Column | Type | Modifiers
------------------ +----------------------------------- +-----------
grantor | information_schema.sql_identifier |
grantee | information_schema.sql_identifier |
specific_catalog | information_schema.sql_identifier |
specific_schema | information_schema.sql_identifier |
specific_name | information_schema.sql_identifier |
routine_catalog | information_schema.sql_identifier |
routine_schema | information_schema.sql_identifier |
routine_name | information_schema.sql_identifier |
privilege_type | information_schema.character_data |
is_grantable | information_schema.yes_or_no |
View "information_schema.role_udt_grants"
Column | Type | Modifiers
---------------- +----------------------------------- +-----------
grantor | information_schema.sql_identifier |
grantee | information_schema.sql_identifier |
udt_catalog | information_schema.sql_identifier |
udt_schema | information_schema.sql_identifier |
udt_name | information_schema.sql_identifier |
privilege_type | information_schema.character_data |
is_grantable | information_schema.yes_or_no |
View "information_schema.role_usage_grants"
Column | Type | Modifiers
---------------- +----------------------------------- +-----------
grantor | information_schema.sql_identifier |
grantee | information_schema.sql_identifier |
object_catalog | information_schema.sql_identifier |
object_schema | information_schema.sql_identifier |
object_name | information_schema.sql_identifier |
object_type | information_schema.character_data |
privilege_type | information_schema.character_data |
is_grantable | information_schema.yes_or_no |
Method 3:
SELECT nspname, relname, relkind, unnest(relacl) relacl FROM pg_class AS c, pg_namespace AS n WHERE n.oid=c.relnamespace AND n.nspname=schema_name AND relname in (list tables) ;
SELECT nspname, relname, relkind, relacl FROM pg_class AS c, pg_namespace AS n WHERE n.oid=c.relnamespace AND n.nspname='schemaname' AND relname =’tablename’ ;
SELECT nspname, relname, relkind, rolname AS rel_owner, unnest(relacl) AS rel_acl
FROM pg_class AS c
LEFT OUTER JOIN pg_namespace AS n ON n.oid=c.relnamespace
LEFT OUTER JOIN pg_roles r ON r.oid=c.relowner
WHERE n.nspname='schemaname'
AND relname =’table_name’
SELECT has_schema_privilege('username', 'schemaname', 'USAGE') ;
SELECT has_table_privilege('username', ‘schemaname.tablename’, 'SELECT') ;
Method 4:
select
t.grantor ,
t.grantee,
t.table_schema,
t.table_name ,
array_to_string(array_agg(privilege_type::varchar order by privilege_type),',') as privileges
from information_schema.table_privileges t
left outer join pg_catalog.pg_partitions p
on t.table_schema = partitionschemaname
and t.table_name = partitiontablename
where rtrim(t.table_schema) like lower('%')
and p.partitiontablename is null
and privilege_type != 'TRUNCATE'
and t.table_schema = 'schemaname'
group by 1
order by 1;