Category: Uncategorized

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 = 'tylerbullen'
   AND s.schemaname = 'vacasa';

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 = 'tylerbullen' --    AND t.tablename = 'myTableName'
    AND t.schemaname = 'vacasa';

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 = 'tylerbullen'
--    AND t.tablename = 'myTableName'
    user_has_insert_permission = 't'
   AND t.schemaname = 'vacasa';

#SCHEMA="${2}"

STARTIME=`date`

echo "Starting rowcount update at $STARTIME"

# fivetran database
DBASE="fivetran"
SCHEMALIST="greenhouse hubspot jira mandrill marketo"
# Truncate rollup table
echo "Deleting from rollup table for ${DBASE}  schema..."
PGPASSWORD=xxxxxxxxxx psql -t -h  warehouse.vacasa.services -p 5439 -Uvacasaroot -d${DBASE} -n -q -c "TRUNCATE TABLE admin.overall_rowcount;"


# Iterate through schemata
for SCHEMA in ${SCHEMALIST}
do

	TABLES=`PGPASSWORD=xxxxxxxxxx psql -t -h  warehouse.vacasa.services -p 5439 -Uvacasaroot -d${DBASE} -n -q -c "select table_name from information_schema.tables where table_schema='${SCHEMA}' and table_type='BASE TABLE' ORDER BY table_name;"`

	for TAB in $TABLES
	do
		echo ">>> Working on ${DBASE}.${SCHEMA}.${TAB}..."

		TABLINE=`PGPASSWORD=xxxxxxxxxx psql -t -h  warehouse.vacasa.services -p 5439 -Uvacasaroot -n -q -d${DBASE} -c "insert into admin.overall_rowcount select '${SCHEMA}','$TAB',count(*) from ${SCHEMA}.${TAB}"`
	done
done
echo "*** All done with database ${DBASE}."



# eng_metrics database
DBASE="eng_metrics"
SCHEMALIST="jira"
# Truncate rollup table
echo "Deleting from rollup table for ${DBASE}  schema..."
PGPASSWORD=xxxxxxxxxx psql -t -h  warehouse.vacasa.services -p 5439 -Uvacasaroot -d${DBASE} -n -q -c "TRUNCATE TABLE admin.overall_rowcount;"

# Iterate through schemata
for SCHEMA in ${SCHEMALIST}
do
	TABLES=`PGPASSWORD=xxxxxxxxxx psql -t -h  warehouse.vacasa.services -p 5439 -Uvacasaroot -d${DBASE} -n -q -c "select table_name from information_schema.tables where table_schema='${SCHEMA}' and table_type='BASE TABLE' ORDER BY table_name;"`

	for TAB in $TABLES
	do
		echo ">>> Working on ${DBASE}.${SCHEMA}.${TAB}..."

		TABLINE=`PGPASSWORD=xxxxxxxxxx psql -t -h  warehouse.vacasa.services -p 5439 -Uvacasaroot -n -q -d${DBASE} -c "insert into admin.overall_rowcount select '${SCHEMA}','$TAB',count(*) from ${SCHEMA}.${TAB}"`
	done
done
echo "*** All done with database ${DBASE}."




# warehouse database
DBASE="warehouse"

SCHEMALIST="adwords_master adwords_owner aircall analysis_ready bing_ads common data_staging etl_testing facebook_ads finance google_adwords google_analytics high_risk housekeeping hubspot looker mailing marketo owner_portal_prod phones property_nexus prospect public rates reporting reporting_prod s3_files secure secure_scrubbed segment segment_marketo split_io_production splitio vacasa vacasa_gps_service vacasa_prod vacasa_units_svc_prod"
# Truncate rollup table
echo "Deleting from rollup table for ${DBASE}  schema..."
PGPASSWORD=xxxxxxxxxx psql -t -h  warehouse.vacasa.services -p 5439 -Uvacasaroot -d${DBASE} -n -q -c "TRUNCATE TABLE admin.overall_rowcount;"

# Iterate through schemata
for SCHEMA in ${SCHEMALIST}
do

	TABLES=`PGPASSWORD=xxxxxxxxxx psql -t -h  warehouse.vacasa.services -p 5439 -Uvacasaroot -d${DBASE} -n -q -c "select table_name from information_schema.tables where table_schema='${SCHEMA}' and table_type='BASE TABLE' ORDER BY table_name;"`

	for TAB in $TABLES
	do
		echo ">>> Working on ${DBASE}.${SCHEMA}.${TAB}..."

		TABLINE=`PGPASSWORD=xxxxxxxxxx psql -t -h  warehouse.vacasa.services -p 5439 -Uvacasaroot -n -q -d${DBASE} -c "insert into admin.overall_rowcount select '${SCHEMA}','$TAB',count(*) from ${SCHEMA}.${TAB}"`
	done
done
echo "*** All done with database ${DBASE}."

ENDTIME=`date`

echo "All done with every damned thing."
echo "Startime was: $STARTIME"
echo "Endtime was : $ENDTIME"

drop table if exists example.reservation_finance_item_dedupe;

create table example.reservation_finance_item_dedupe as (select distinct * from example.reservation_finance_item);

select count(*) from example.reservation_finance_item;
select count(*) from example.reservation_finance_item_dedupe;

drop table if exists example.reservation_finance_item_old;

set search_path=example;

alter table reservation_finance_item rename to reservation_finance_item_old;
alter table reservation_finance_item_dedupe rename to reservation_finance_item;

select count(*) from example.reservation_finance_item;
select count(*) from example.reservation_finance_item_old;

-- 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

;