Shell script to find out super user list in AWS Redshift

 ################################################################################################

# purpose     :script used to collect PROD  Redshift super user list for audit

# Author      : Bala P

# Developed   :19-sep-2022 V1

################################################################################################

#!/usr/bin/bash

export PGHOST=XXXX.redshift.amazonaws.com

export PGPORT='5454'

export PGDATABASE=RS_DB1

export PGUSER=rsdbadmin

DBA=balamani@abc.com

export DBA

export PGPASSWORD='XXXXXXXXXX'

export wdir=/redshiftadmin/aws/audit/scripts/

export logdir=/redshiftadmin/aws/audit/log

psql  -f $wdir/super_user_list.sql -o $logdir/super_user_list.sql_output.log

mailx -s "Super user list from Redshift PROD cluster " $DBA  < $logdir/super_user_list.sql_output.log

************************************

 super_user_list.sql

\qecho

select usename,usesuper FROM pg_catalog.pg_user_info where usesuper='true';

check_tbl_ownr_compl.sql

select  schemaname,tablename ,tableowner from pg_catalog.pg_tables where schemaname not in ('public','pg_catalog','catalog_history','information_schema','dbainfo') and tableowner not in ('rsdbadmin') and schemaname not like 'pg_%';

 chk_tbl_owner_public.sql

select schemaname,tablename,tableowner  from pg_catalog.pg_tables  where schemaname='public';

************************************