Author: Vinay

-- Table level permissions
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='reporting'
AND usename = 'emilybrown';

-- check perms for a table
\dp units



-- show all privs for role
SELECT
    u.usename,
    s.schemaname,
    has_schema_privilege(u.usename,s.schemaname,'create') AS user_has_select_permission,
    has_schema_privilege(u.usename,s.schemaname,'usage') AS user_has_usage_permission
FROM
    pg_user u
CROSS JOIN
    (SELECT DISTINCT schemaname FROM pg_tables) s
WHERE
    u.usename = 'job_analyst'
    AND s.schemaname = 'marketo'
;

-- grants for a specific table
SELECT
    u.usename,
    t.schemaname||'.'||t.tablename,
    has_table_privilege(u.usename,t.tablename,'select') AS user_has_select_permission,
    has_table_privilege(u.usename,t.tablename,'insert') AS user_has_insert_permission,
    has_table_privilege(u.usename,t.tablename,'update') AS user_has_update_permission,
    has_table_privilege(u.usename,t.tablename,'delete') AS user_has_delete_permission,
    has_table_privilege(u.usename,t.tablename,'references') AS user_has_references_permission
FROM
    pg_user u
CROSS JOIN
    pg_tables t
WHERE
    u.usename = 'marcurbaitel'
    AND t.tablename = 'trust_rev_payments'
;

-- Users and their 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) order by usename;

-- Users in a particular group
select usename from pg_user , pg_group  where
pg_user.usesysid = ANY(pg_group.grolist) and 
pg_group.groname='grp_maxbi'
ORDER BY BY usename;

-- Grant and future objects
ALTER DEFAULT PRIVILEGES IN SCHEMA hubspot GRANT SELECT ON  TABLES TO GROUP grp_fivetran_analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA hubspot TO GROUP grp_fivetran_analyst; 
GRANT USAGE ON SCHEMA hubspot TO GROUP grp_fivetran_analyst;

-- Same for single user
ALTER DEFAULT PRIVILEGES IN SCHEMA reporting GRANT SELECT ON TABLES TO looker;
GRANT SELECT ON ALL TABLES IN SCHEMA reporting TO looker; 
GRANT USAGE ON SCHEMA reporting TO looker;

/*Check active permissions for a certain user*/
SELECT distinct a1.schemaname, a1.groupname,a2.usename, a1.sel, a1.upd, a1.ins, a1.del, a1.drp, a1."ref"
FROM "admin".v_get_tbl_priv_by_group a1
inner join "admin".v_get_users_in_group a2 
on a2.groname = a1.groupname 
and a2.usename = '<username>';

cd /usr/local/cron/dump_rs_grants
PGPASSWORD=xxxxxxxxx psql -h  redshiftFQDN -p 5439 -Uxxxxxx -dyyyyyy < dump_rs_grants.sql > current_rs_grants.txt

WITH object_list(schema_name,object_name,permission_info) 
 AS (
    SELECT N.nspname, C.relname, array_to_string(relacl,',')
    FROM pg_class AS C
        INNER JOIN pg_namespace AS N
        ON C.relnamespace = N.oid
    WHERE C.relkind in ('v','r')
    AND  N.nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
    AND C.relacl[1] IS NOT NULL
  ),
  object_permissions(schema_name,object_name,permission_string)
  AS (
    SELECT schema_name,object_name, SPLIT_PART(permission_info,',',1) FROM object_list
    UNION ALL
    SELECT schema_name,object_name, SPLIT_PART(permission_info,',',2) FROM object_list
    UNION ALL
    SELECT schema_name,object_name, SPLIT_PART(permission_info,',',3) FROM object_list
    UNION ALL
    SELECT schema_name,object_name, SPLIT_PART(permission_info,',',4) FROM object_list
    UNION ALL
    SELECT schema_name,object_name, SPLIT_PART(permission_info,',',5) FROM object_list
    UNION ALL
    SELECT schema_name,object_name, SPLIT_PART(permission_info,',',6) FROM object_list
    UNION ALL
    SELECT schema_name,object_name, SPLIT_PART(permission_info,',',7) FROM object_list
    UNION ALL
    SELECT schema_name,object_name, SPLIT_PART(permission_info,',',8) FROM object_list
    UNION ALL
    SELECT schema_name,object_name, SPLIT_PART(permission_info,',',9) FROM object_list
    UNION ALL
    SELECT schema_name,object_name, SPLIT_PART(permission_info,',',10) FROM object_list
  ),
  permission_parts(schema_name, object_name,security_principal, permission_pattern)
  AS (
      SELECT
          schema_name,
          object_name,
          LEFT(permission_string ,CHARINDEX('=',permission_string)-1),
          SPLIT_PART(SPLIT_PART(permission_string,'=',2),'/',1)
      FROM object_permissions
      WHERE permission_string >''
  )
SELECT
    schema_name,
    object_name,
    'GRANT ' ||
    SUBSTRING(
        case when charindex('r',permission_pattern) > 0 then ',SELECT ' else '' end
      ||case when charindex('w',permission_pattern) > 0 then ',UPDATE ' else '' end
      ||case when charindex('a',permission_pattern) > 0 then ',INSERT ' else '' end
      ||case when charindex('d',permission_pattern) > 0 then ',DELETE ' else '' end
      ||case when charindex('R',permission_pattern) > 0 then ',RULE ' else '' end
      ||case when charindex('x',permission_pattern) > 0 then ',REFERENCES ' else '' end
      ||case when charindex('t',permission_pattern) > 0 then ',TRIGGER ' else '' end
      ||case when charindex('X',permission_pattern) > 0 then ',EXECUTE ' else '' end
      ||case when charindex('U',permission_pattern) > 0 then ',USAGE ' else '' end
      ||case when charindex('C',permission_pattern) > 0 then ',CREATE ' else '' end
      ||case when charindex('T',permission_pattern) > 0 then ',TEMPORARY ' else '' end
    ,2,10000
    )
    || ' ON ' || schema_name||'.'||object_name
     || ' TO ' || security_principal
     || ';' as grantsql
FROM permission_parts

;

SELECT
   *
FROM
   stl_scan ss
   JOIN pg_user pu
      ON ss.userid = pu.usesysid
   JOIN svl_query_metrics_summary sqms
      ON ss.query = sqms.query
   JOIN temp_mone_tables tmt
      ON tmt.table_id = ss.tbl
      AND tmt.table = ss.perm_table_name;

SELECT
   perm_table_name,
   SUM(ROWS),
   SUM(bytes) SUM(fetches)
FROM
   stl_scan
WHERE starttime >= '2018-09-01 00:00:00'
GROUP BY perm_table_name
ORDER BY SUM(bytes) DESC
LIMIT 40;