RedShift – Cheat Sheet


General Admin

-- Show running queries
    SELECT user_name, db_name, pid, query
    FROM stv_recents
    WHERE status = 'Running';

-- Show recent queries (td, join with user table)
    SELECT userid,query,pid,substring,starttime,endtime,elapsed,aborted
    FROM svl_qlog 
    ORDER BY starttime DESC
    LIMIT 100; 

-- Show recent connections
    select recordtime, username, dbname, remotehost, remoteport
    from stl_connection_log
    where event = 'initiating session'
    and pid not in 
    (select pid from stl_connection_log
    where event = 'disconnecting session')
    order by 1 desc;

-- Move a table from one schema to another (remaps data, drop dependecies)
    CREATE TABLE pwtest.customer (LIKE public.customer);
    ALTER TABLE pwtest.customer APPEND FROM public.customer;
    DROP TABLE public.customer;

Users

-- Show current user
    SELECT current_user;

-- Show all Users
    SELECT * FROM pg_user;

-- Impersonate User (requires supeuser)
    SET SESSION AUTHORIZATION 'finance';
    RESET SESSION AUTHORIZATION;

-- Create/drop new user
    CREATE USER pete PASSWORD 'r4nd0mZ1KpasswUrd0';
    DROP USER pete;

-- Change a users password
    ALTER USER pete PASSWORD 'r4nd0mqeXpasswUrd0';

-- Create new temporary user (with expiry date) ++ with timestamp
    CREATE USER pete_expire PASSWORD 'r4nd0mdj9passwUrd0' VALID UNTIL '2020-05-19 06:15:00+00';

-- Create a new user that expires tomorrow
    CREATE USER pete_expire_tomorrow PASSWORD 'r4nd0mD0JpasswUrd0' VALID UNTIL sysdate+1;

-- Create new superuser
    CREATE USER pete CREATEUSER PASSWORD 'r4nd0mX8vpasswUrd0'

-- Amend user, give/remove superuser access
    ALTER USER pete CREATEUSER;
    ALTER USER pete NOCREATEUSER;

Groups

-- Show all Groups
    SELECT * 
    FROM pg_group;

-- Create a Group
    CREATE GROUP test_group WITH USER pete;

-- Create a Group and Add Users
    CREATE GROUP test_group WITH USER pete1, pete2;

-- Add a User to a Group
    ALTER GROUP test_group ADD USER pete1;

-- Amend a Group Name
    ALTER GROUP test_group RENAME TO group_test

-- Show Users & their accosiated Groups
    SELECT usename, groname 
    FROM pg_user, pg_group
    WHERE pg_user.usesysid = ANY(pg_group.grolist)
    AND pg_group.groname in (SELECT DISTINCT pg_group.groname from pg_group);

Create Tables/Views/MViews

-- Show all Schemas
    SELECT * FROM pg_namespace;

-- Create a Schema
    CREATE SCHEMA pwtest

-- Show all tables
    SELECT * FROM pg_table_def 

-- Create a Table
    CREATE TABLE pwtest.customer 
    (
    customerid       INT8 NOT NULL,
    customername     VARCHAR(25) NOT NULL,
    phone            CHAR(15) NOT NULL,
    nationid        INT4 NOT NULL,
    marketsegment    CHAR(10) NOT NULL,
    accountbalance   NUMERIC(12,2) NOT NULL
    );
    CREATE TABLE pwtest.nation 
    (
    nationid    INT4 NOT NULL,
    nationname   CHAR(25) NOT NULL
    );

-- Drop a table
    DROP TABLE pwtest.customer

-- Insert rows to Table
    INSERT INTO pwtest.customer VALUES
    (1, 'Customer#000000001', '33-687-542-7601', 3, 'HOUSEHOLD', 2788.52),
    (2, 'Customer#000000002', '13-806-545-9701', 1, 'MACHINERY', 591.98),
    (3, 'Customer#000000003', '13-312-472-8245', 1, 'HOUSEHOLD', 3332.02),
    (4, 'Customer#000000004', '23-127-851-8031', 2, 'MACHINERY', 9255.67),
    (5, 'Customer#000000005', '13-137-193-2709', 1, 'BUILDING', 5679.84)
    ;
    INSERT INTO pwtest.nation VALUES
    (1, 'UNITED STATES'),
    (2, 'AUSTRALIA'),
    (3, 'UNITED KINGDOM');

-- Updates to tables
    UPDATE customer 
    SET accountbalance = 1000 
    WHERE marketsegment = 'BUILDING';

-- Create View
    CREATE OR REPLACE VIEW pwtest.customer_vw 
        AS SELECT customername, phone, marketsegment, accountbalance, 
        CASE WHEN accountbalance < 1000 THEN 'low' WHEN accountbalance > 1000 AND accountbalance < 5000 THEN 'middle' 
        ELSE 'high' END AS incomegroup 
        FROM pwtest.customer;

-- Drop a View
    DROP VIEW pwtest.customer_vw

-- Create Materialized View
    CREATE MATERIALIZED VIEW pwtest.customernation_mv 
        AS SELECT customername, phone, nationname, marketsegment, sum(accountbalance) AS accountbalance 
        FROM pwtest.customer c 
        INNER JOIN pwtest.nation n 
        ON c.nationid = n.nationid 
        GROUP BY customername, phone, nationname, marketsegment;

-- Drop a Materialized View
    DROP MATERIALIZED VIEW pwtest.customernation_mv

Permissions (Schema/Table/Column)

-- Grant usage on a schema for a user (public schema is allowed by default)
    GRANT USAGE ON SCHEMA pwtest.customer TO pete_restricted; 
    REVOKE USAGE ON TABLE pwtest.customer FROM pete_restricted;

-- Grant & revoke select permissions on a table for a user
    GRANT SELECT ON pwtest.customer TO pete_restricted;
    REVOKE SELECT ON TABLE pwtest.customer FROM pete_restricted;

-- Grant & revoke select & update permissions for a group
    GRANT SELECT, UPDATE ON pwtest.customer TO GROUP devs;
    REVOKE SELECT, UPDATE ON TABLE pwtest.customer FROM GROUP devs;

-- Grant  & revoke select & update on 2 columns of a table for a user
    GRANT SELECT (marketsegment, accountbalance), UPDATE (marketsegment, accountbalance) ON pwtest.customer TO pete_restricted;
    REVOKE SELECT (marketsegment, accountbalance), UPDATE (marketsegment, accountbalance) ON pwtest.customer FROM pete_restricted;

Permissions (Auditing)

-- Impersonate User (requires supeuser)
    SET SESSION AUTHORIZATION 'finance';
    RESET SESSION AUTHORIZATION;

-- Show which users have column-level access control (table-scoped)
    SELECT b.attacl, b.attname, c.relname 
    FROM pg_catalog.pg_attribute_info b 
    JOIN pg_class c ON c.oid=b.attrelid 
    WHERE c.relname in ('customer','customer_vw','customernation_mv') 
    AND b.attacl IS NOT NULL 
    ORDER BY c.relname, b.attname;

-- View all Grants by Schema or User
    SELECT * 
    FROM 
        (
        SELECT 
            schemaname
            ,objectname
            ,usename
            ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'select') AND has_schema_privilege(usrs.usename, schemaname, 'usage')  AS sel
            ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'insert') AND has_schema_privilege(usrs.usename, schemaname, 'usage')  AS ins
            ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'update') AND has_schema_privilege(usrs.usename, schemaname, 'usage')  AS upd
            ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'delete') AND has_schema_privilege(usrs.usename, schemaname, 'usage')  AS del
            ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'references') AND has_schema_privilege(usrs.usename, schemaname, 'usage')  AS ref
        FROM
            (
            SELECT schemaname, 't' AS obj_type, tablename AS objectname, schemaname + '.' + tablename AS fullobj FROM pg_tables
            WHERE schemaname not in ('pg_internal')
            UNION
            SELECT schemaname, 'v' AS obj_type, viewname AS objectname, schemaname + '.' + viewname AS fullobj FROM pg_views
            WHERE schemaname not in ('pg_internal')
            ) AS objs
            ,(SELECT * FROM pg_user) AS usrs
        ORDER BY fullobj
        )
    WHERE (sel = true or ins = true or upd = true or del = true or ref = true)
    and schemaname='<opt schema>'
    and usename = '<opt username>';

-- View all Grants by Group
    select relacl , 
    'grant ' || substring(
                case when charindex('r',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',select ' else '' end 
            ||case when charindex('w',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',update ' else '' end 
            ||case when charindex('a',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',insert ' else '' end 
            ||case when charindex('d',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',delete ' else '' end 
            ||case when charindex('R',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',rule ' else '' end 
            ||case when charindex('x',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',references ' else '' end 
            ||case when charindex('t',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',trigger ' else '' end 
            ||case when charindex('X',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',execute ' else '' end 
            ||case when charindex('U',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',usage ' else '' end 
            ||case when charindex('C',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',create ' else '' end 
            ||case when charindex('T',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',temporary ' else '' end 
        , 2,10000)
    || ' on '||namespace||'.'||item ||' to "'||pu.groname||'";' as grantsql
    from 
    (SELECT 
    use.usename as subject, 
    nsp.nspname as namespace, 
    c.relname as item, 
    c.relkind as type, 
    use2.usename as owner, 
    c.relacl 
    FROM 
    pg_user use 
    cross join pg_class c 
    left join pg_namespace nsp on (c.relnamespace = nsp.oid) 
    left join pg_user use2 on (c.relowner = use2.usesysid)
    WHERE 
    c.relowner = use.usesysid  
    and  nsp.nspname   NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
    ORDER BY 
    subject,   namespace,   item 
    ) join pg_group pu on array_to_string(relacl, '|') like '%'||pu.groname||'%' 
    where relacl is not null
    and pu.groname='devs'
    order by 2