Redshift Notes

redshift-notes.md

Redshift Notes

1. Find dependent objects for a table or view in Postgresql or Redshift

SELECT dependent_ns.nspname as dependent_schema
, dependent_view.relname as dependent_view 
, source_ns.nspname as source_schema
, source_table.relname as source_table
, pg_attribute.attname as column_name
FROM pg_depend 
JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid 
JOIN pg_class as dependent_view ON pg_rewrite.ev_class = dependent_view.oid 
JOIN pg_class as source_table ON pg_depend.refobjid = source_table.oid 
JOIN pg_attribute ON pg_depend.refobjid = pg_attribute.attrelid 
    AND pg_depend.refobjsubid = pg_attribute.attnum 
JOIN pg_namespace dependent_ns ON dependent_ns.oid = dependent_view.relnamespace
JOIN pg_namespace source_ns ON source_ns.oid = source_table.relnamespace
WHERE 
source_ns.nspname = 'my_schema'
AND source_table.relname = 'my_table'
AND pg_attribute.attnum > 0 
-- AND pg_attribute.attname = 'my_column'
ORDER BY 1,2;

Source

2. List the tables a user has access to

SELECT 
    schemaname,
    tablename,
    has_table_privilege(usename, schemaname || '.' || tablename, 'select') AS select_priv,
    has_table_privilege(usename, schemaname || '.' || tablename, 'insert') AS insert_priv,
    has_table_privilege(usename, schemaname || '.' || tablename, 'update') AS update_priv,
    has_table_privilege(usename, schemaname || '.' || tablename, 'delete') AS delete_priv
FROM 
    pg_tables t,
    pg_user u
WHERE
    usename = 'specific_username' -- replace 'specific_username' with the username you're checking
    AND (has_table_privilege(usename, schemaname || '.' || tablename, 'select') OR
         has_table_privilege(usename, schemaname || '.' || tablename, 'insert') OR
         has_table_privilege(usename, schemaname || '.' || tablename, 'update') OR
         has_table_privilege(usename, schemaname || '.' || tablename, 'delete'))
ORDER BY
    schemaname, tablename;

3. Get view owner

SELECT 
    n.nspname AS schema_name,
    c.relname AS view_name,
    pg_get_userbyid(c.relowner) AS view_owner
FROM 
    pg_class c
JOIN 
    pg_namespace n ON n.oid = c.relnamespace
WHERE 
    c.relkind = 'v'  -- 'v' stands for view
    AND c.relname = 'your_view_name';  -- replace 'your_view_name' with the name of your view

4. Get the list of users

SELECT 
    usesysid AS user_id,
    usename AS username,
    usecreatedb AS can_create_db,
    usesuper AS is_superuser,
    valuntil AS password_expiration
FROM 
    pg_user
ORDER BY 
    user_id;