List Redshift tables, views and their owners

SELECT n.nspname AS schema_name
 , pg_get_userbyid(c.relowner) AS table_owner
 , c.relname AS table_name
 , CASE WHEN c.relkind = 'v' THEN 'view' ELSE 'table' END 
   AS table_type
 , d.description AS table_description
 FROM pg_class As c
 LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
 LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
 LEFT JOIN pg_description As d 
      ON (d.objoid = c.oid AND d.objsubid = 0)
 WHERE c.relkind IN('r', 'v') 
ORDER BY n.nspname, c.relname ;

Redshift – Script to generate statements to recreate users and groups

--Script to generate statements to re-create users in redshift
select 'create user '+ usename +' password ''Hidden1!'' ;' from pg_user
order by usename asc;

--Script to generate statements to re-create groups 
select 'create group '+ groname +';' from pg_group
order by groname asc;

--Script to generate statements to add users to various groups
SELECT 'alter group '+ groname + ' add user ' + usename + ';'
FROM "admin".v_get_users_in_group
order by groname , usename;

%d bloggers like this: