Redshift Database SQL Query for Users with Specific Table Permissions

As Amazon Redshift Data Warehouse administrators, frequently we require to query the users list who has specific privileges like read, write or delete permissions on a Redshift database table. In this Redshift tutorial for SQL developers, I want to share SQL codes where PostgreSQL access privilege inquiry functions are used like has_schema_privilege and has_table_privilege.

PostgreSQL provides more system information functions for administrators and SQL programmers building and running applications on top of it as a data platform. Since Amazon Redshift DWH analytical platform is also build on PostgreSQL like many other databases, Redshift database administrator and SQL programmers can use these PostgreSQL functions too.

Redshift Database Permissions SQL Query Functions

First of all, I want to give brief information about PostgreSQL access privilege inquiry functions. These SQL functions enable database users to query object access privileges programmatically by using SQL. Below list contains a few of the system information functions that can be used for querying specific permissions on Redshift database schema and table objects.

has_table_privilege returns a boolean value indicating that the user queried has the privilege on the table.
Here is a sample usage in SQL code

SELECT
has_table_privilege('eralperyilmaz', 'public.test01', 'select') as select_permission,
has_table_privilege('eralperyilmaz', 'public.test01', 'insert') as insert_permission,
has_table_privilege('eralperyilmaz', 'public.test01', 'update') as update_permission,
has_table_privilege('eralperyilmaz', 'public.test01', 'delete') as delete_permission,
has_table_privilege('eralperyilmaz', 'public.test01', 'references') as references_permission

The output is as seen in below screenshot

Another build-in PostgreSQL system function which can be easily used on Amazon Redshift SQL developments is has_schema_privilege.

has_database_privilege is one step broader of the has_schema_privilege SQL function.
As in the sample SQL code I shared for has_table_privilege, I passed select, insert, update, delete and references privileges as an argument to the function.
For has_database_privilege, PostgreSQL database developers can use the "connect" privilege if the user has authorization to establish a connection to a target database.

So for the PostgreSQL developer, I would suggest them to check the official documentation for the Grant command. There they will find the possible privileges and their meanings according to the usage area.

Unfortunately, not every privilege is not available for theses permission querying functions on Amazon Redshift.
For Redshift, SQL developers can refer to official documentation.

has_database_privilege has create, temp and temporary pirivileges defined.

Create: permission to create new schema under target database
Temp, Temporary: privilege to create temporary tables on that Redshift database

has_schema_privilege has the create and usage privileges.

Create: permission to create new database objects under selected database schema.
Usage: lookup permission for database user to access objects created under that database schema

has_table_privilege can be used with select, insert, update, delete and references privileges.

Select: permission to select table column data
Insert: permission to add a new database table row
Update: privilege for updating the contents of the records of a table
Delete: privilege to delete data from a table
References: permission to create a foreign key constraint

Another permission check function is has_assumerole_privilege which is used for identifying if the user has the specified IAM role privilege.

Here is a sample SQL code for executing has_database_privilege premission check function on Amazon Redshift database

SELECT
has_database_privilege('eralperyilmaz', 'sampledb', 'create') as create_permission,
has_database_privilege('eralperyilmaz', 'sampledb', 'temp') as temp_permission,
has_database_privilege('eralperyilmaz', 'sampledb', 'temporary') as temporary_permission

The returned output information of the above SQL query on Redshift database is:

For a specific Redshift database user's permissions on a specific schema, SQL function has_schema_privilege is used as follows

SELECT
has_schema_privilege('test0001', 'spectrumdb', 'create') as create_permission,
has_schema_privilege('test0001', 'spectrumdb', 'usage') as usage_permission

Above SQL code returns following information about the test0001 user permissions on spectrumdb schema:

Query Redshift Database Users for Specific Permissions on Selected Tables

All above SQL code sample queries are for specific users and for specific database and database objects.
If you need a list of users or list database objects list, we can combine the SQL query with pg_user database users table and pg_tables database tables table.
These system tables can be combined with above samples as shown in below common table expression, CTE query

This SQL CTE query returns the list of all Redshift database users with specific permissions (in this case Read permission or "select" privilege) on a given Redshift database table.

WITH cte as (
SELECT
 usename as username,
 t.schemaname,
 has_schema_privilege(u.usename, t.schemaname, 'create') as user_has_schema_select_permission,
 has_schema_privilege(u.usename, t.schemaname, 'usage') as user_has_schema__usage_permission,
 t.tablename,
 has_table_privilege(u.usename, t.tablename, 'select') as user_has_table_select_permission,
 has_table_privilege(u.usename, t.tablename, 'insert') as user_has_table_insert_permission,
 has_table_privilege(u.usename, t.tablename, 'update') as user_has_table_update_permission,
 has_table_privilege(u.usename, t.tablename, 'delete') as user_has_table_delete_permission,
 has_table_privilege(u.usename, t.tablename, 'references') as user_has_table_references_permission
FROM pg_user u
CROSS JOIN pg_tables t
WHERE
 t.schemaname = 'storm'
 and t.tablename = 'makt'
)
SELECT * FROM cte
WHERE user_has_table_select_permission = true;

If SQL developers run above SELECT statement on an Amazon Redshift database by customizing the WHERE clause of the CTE expression according to their environment, they will get the list of all Redshift database users created on that server with "select" privilege on targeted schema table.