SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for AWS Amazon Web Services, Redshift, AWS Lambda Functions, S3 Buckets, VPC, EC2, IAM

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

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
SQL has_table_privilege function sample code

The output is as seen in below screenshot

Use of SQL has_table_privilege on Redshift Database
Use of SQL has_table_privilege on Redshift Database

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

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
SQL has_database_privilege function sample code

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

sample SQL code for has_database_privilege function on Redshift Database
Use of SQL has_database_privilege on Redshift Database

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

has_schema_privilege('test0001', 'spectrumdb', 'create') as create_permission,
has_schema_privilege('test0001', 'spectrumdb', 'usage') as usage_permission
Redshift database SQL has_schema_privilege function code sample

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

Redshift Database SQL has_schema_privilege function
Redshift Database SQL has_schema_privilege function

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 (
 usename as username,
 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,
 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
 t.schemaname = 'storm'
 and t.tablename = 'makt'
WHERE user_has_table_select_permission = true;
Redshift database SQL has_schema_privilege function code sample

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.


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.