script check the all active connections to the db database by using the following query:
select usename,datname FROM pg_stat_activity;
SELECT *
FROM pg_stat_activity
WHERE datname = 'db';
terminate all the connections to the db database by using the following statement:
SELECT
pg_terminate_backend (pid)
FROM
pg_stat_activity
WHERE
datname = 'db';
script to check failed logins in aws redshift database
SELECT *
FROM stl_connection_log
WHERE event='authentication failure'
ORDER BY recordtime;
script to Showing successfully authenticated users with the number of successful authentications:
SELECT username, event, COUNT(*)
FROM stl_connection_log
WHERE event = 'authenticated'
GROUP BY 1, 2
ORDER BY 3 DESC;
username | event | count
----------------------------------------------------+----------------------------------------------------+-------
rdsdb | authenticated | 4927
IAM:test2@us.domain.com | authenticated | 1833
test3 | authenticated | 720
IAMA:test1@us.domain.com | authenticated | 344
IAMA:xyz.123@domain.com | authenticated | 65
script to create database user in aws Redshift database with IAM authentication
authentication will be done using the Single sign on thru IAM and DB access will be done through group .
create user "ABC.123@domain.com" password disable valid until '31-Aug-2022';
alter group redshift_readwrite_dev add user "ABC.123@domain.com" ;
scripts to find view list from the schema
select table_schema as schema_name,
table_name as view_name
from information_schema.views
where table_schema in ('app_username')
order by schema_name,
view_name;
script to check tables under which schema in aws redshift database
SELECT
TRIM(nspname) AS schema_name,
TRIM(relname) AS table_name,
relcreationtime AS creation_time
FROM pg_class_info
LEFT JOIN pg_namespace ON pg_class_info.relnamespace = pg_namespace.oid
WHERE reltype != 0
AND TRIM(nspname) = 'app_username';
script to find the user part of which group in aws redshift database
SELECT pg_group.groname
,pg_group.grosysid
,pg_user.*
FROM pg_group, pg_user
WHERE pg_user.usesysid = ANY(pg_group.grolist)
AND pg_user.usename='redshiftuser1'
ORDER BY 1,2 ;
groname | grosysid | usename | usesysid | usecreatedb | usesuper | usecatupd | passwd | valuntil | useconfig
---------------------------+----------+------------+----------+-------------+----------+-----------+----------+----------+-----------
redshift_readonly_sa | 104 | TESTDB_user| 145 | f | f | f | ******** | |
redshift_readwrite_sa | 105 | TESTDB_user| 145 | f | f | f | ******** | |
script to find Db owner details in aws Redshift DB
SELECT d.datname as "Name",
pg_catalog.pg_get_userbyid(d.datdba) as "Owner"
FROM pg_catalog.pg_database d
WHERE d.datname = 'redshiftDB'
ORDER BY 1;
Name | Owner
-------------+------------------------------
redshiftDB | DB_owner_username
(1 row)
script to find table creation time stamp or date in aws redshift database:
provide the table_name and schema name and get the table created time from Redshift DB
SELECT
nspname AS schema_name,
relname AS table_name,
relcreationtime AS creation_time
FROM pg_class_info c, pg_namespace n
WHERE c.relnamespace=n.oid
AND reltype != 0
AND nspname='<schema_name>'
AND relname='<table_name>';
Syntax For read only access –
grant usage on schema <schemaname> to redshift_readonly_dev;
grant select on ALL tables in schema <schemaname> to redshift_readonly_dev;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name> GRANT select ON TABLES TO group redshift_readonly_dev;
Syntax for Read write access –
grant usage on schema <schemaname> to nga_redshift_readwrite_dev;
grant select,insert,update,delete on ALL tables in schema <schemaname> to redshift_readwrite_dev;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name> GRANT select,insert,update,delete ON TABLES TO group redshift_readwrite_dev;
