setup DDL Group in Redshift database
1.create group <group_name>
2.load the DDL Privilege's to group
3.add the users to DDL group
create schema <schema_name>;
create group group_<schema>_DDL;
GRANT CREATE ON database <DB name> to group group_<schema>_DDL;
GRANT all ON schema <schema_name> to group group_<schema>_DDL;
setup Read-only and Read-write Group in Redshift database:
1.create group <group_name>
2.load the select /DML Privilege's to group
3.add the users to Read-only or Read-write group
Please find the syntax for Read-Only:
grant usage on schema <schema_name> to group Redshift_readonly_group;
grant select on all tables in schema <schema_name> to group Redshift_readonly_group;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name> GRANT select ON tables TO group Redshift_readonly_group;
Please find the syntax for Read-Write:
grant usage on schema <schema_name> to group Redshift_read-write_group;
grant select,insert,update,delete on all tables in schema <schema_name> to group Redshift_read-write_group;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name> GRANT select,insert,update,delete ON tables TO group Redshift_read-write_group;
script to find currently running queries in Redshift:
select userid , query , pid , starttime , text from stv_inflight order by starttime desc;
script to find currently locks in Redshift:
select table_id, last_update, lock_owner, lock_owner_pid from stv_locks;
SELECT * FROM STV_SESSIONS;
Redshift interesting interview questions:
1) can we make the SSO user as super user in Redshift?
No,you cant make the SSO authenticated user as super user in Redshift Database.
alter user "user@ABC.COM" CREATEUSER; ==> not working
SQL Error [42601]: ERROR: Superusers cannot have disabled passwords
2)How to Lock the Redshift Database users?
we can disable the user based on the life cycle using super user.
alter user "user@ABC.COM" valid until '10-aug-2022';
This will make the user as disabled and wont be able to login to Redshift database.
script to Unload Redshift table data to S3 bucket:
we can unload the data using 2 credential from Redshift to S3 bucket.
1)access key & secret key
2) ARN roles
UNLOAD ('SELECT * FROM DB_name.schema1.table2')
TO 's3://s3_bucket_name/table2.csv'
iam_role 'arn:aws:iam::123456789:role/s3_to_Redshift_load_bucket'
KMS_KEY_ID '123456789'
encrypted PARALLEL on
CSV;
s3_bucket_name--> s3 bucket name
ARN name --> iam_role 'arn:aws:iam::123456789:role/s3_to_Redshift_load_bucket
KMS_KEY_ID --> '123456789'
CSV --> format
Issue:
we got the below error when Redshift SSO user login to Redshift database:
IAM error retrieving temp credentials: User: arn:aws:sts::123456789:assumed-role/AzureSSOuser/user@abc.com is not authorized to perform: redshift:JoinGroup on resource: arn:aws:redshift:us-east-1:123456789:dbgroup:Redshift-prod/User.assignedroles because no identity-based policy allows the redshift:JoinGroup action (Service: AmazonRedshift; Status Code: 403; Error Code: AccessDenied; Request ID:123456789)
User: arn:aws:sts::123456789:assumed-role/AzureSSOuser/user@abc.com is not authorized to perform: redshift:JoinGroup on resource: arn:aws:redshift:us-east-1:123456789:dbgroup:Redshift-prod/User.assignedroles because no identity-based policy allows the redshift:JoinGroup action (Service: AmazonRedshift; Status Code: 403; Error Code: AccessDenied; Request ID:123456789)
Root cause & Solution:
when a Redshift SSO user tried login to database using SSO authentication got the above error,because dbgroup parameter is NOT mentioned in the connection string,user should add the dbgroups parameter in the connection staring and it worked.
script to find out unload and copy counts on Redshift database:
The below script will provide the count of copy/unload process on the same session.
select pg_last_copy_count();
select pg_last_unload_count();
script to find out if the user granted assumrole privilege on database for unload/copy:
select has_assumerole_privilege('user@abc.com', 'arn:aws:iam::123456789:role/redshift-s3-unload-service-role', 'copy');
has_assumerole_privilege
------------------------
false
select has_assumerole_privilege('user@abc.com', 'arn:aws:iam::123456789:role/redshift-s3-unload-service-role', 'unload');
has_assumerole_privilege
------------------------
false
Issue:
when I tried to unload some data from Redshift to S3 bucket using the Redshift SSO user got the below error.
SQL Error [XX000]: ERROR: User user@abc.com does not have ASSUMEROLE permission on IAM role "arn:aws:iam::123456789:role/Redshift-s3-unload-role" for UNLOAD
Solution:
after some investigation found that ,we should grant the assumrole privilege's to the user ,after granting the privilege issue resolved.
Syntax: ==>Unload
grant assumerole on 'arn:aws:iam::123456789:role/Redshift-s3-unload-role' to "user@abc.com" for unload;
Syntax: ==>copy
grant assumerole on 'arn:aws:iam::123456789:role/Redshift-s3-unload-role' to "user@abc.com" for copy;
