Amazon Redshift Daily:
SELECT CURRENT_AWS_ACCOUNT;
SELECT CURRENT_DATABASE();
SELECT CURRENT_NAMESPACE();
SELECT CURRENT_SCHEMA();
SELECT CURRENT_USER_ID();
SELECT CURRENT_USER;
-- Metadata
select distinct(nspname) from pg_catalog.pg_namespace where nspname not like 'pg_temp%';
-- Describe Table
select * from information_schema.columns where table_name='columns';
select column_name, data_type, character_maximum_length from information_schema.columns where table_name='columns';
-- Create Read-only Group
Create group ro_group;
-- Create User
Crate user ro_user with password <password>;
-- Add user to Read-only Group
alter group ro_group ADD user ro_user;
-- Grant Usage permission to Read-Only Group to specific schema
grant usage on schema "ro_schema" to group ro_group;
-- Grant select permisson to read-only group to specific schema
grant select on all tables in schema "ro_schema" to group "ro_group";
-- Alter default privileges to maintain the permission on new tables
alter default privileges in schema "ro_schema" grant select on tables to group ro_group;
-- Revoke crate privileges from group
revoke create on schema "ro_schema" from group ro_group;
Example:
create group testgroup;
create user test1 password 'test123' in group testgroup;
grant usage on schema "rs_public" to group testgroup;
grant select on all tables in schema rs_public to group testgroup;
grant select on all tables in schema grant select on all tables in schema public to group data_viewers;
select * from pg_user where usename like 't%';
select groname from pg_catalog.pg_group pg where groname like 'test%';
select groname from pg_catalog.pg_group pg where groname like 'l%';
call rs_public.idm_ndew_revoke_from_login_role ('testgroup', 'test1');
grant all on procedure public. idm_new_revoke_from_login_role_bkup (in varchar, inout varchar) to test1;
create group testgroup;
create user test1 password 'test123' in group testgroup;
grant usage on schema "rs_public_ to group testgroup;
grant select on all tables in schema rs_public to group testgroup;
Rename Table:
alter table db_name.schema_name.table_name rename to new_table_name;
Note: Remember not to include db_name.schema_name while giving new_table_name.
Troubleshoot ETL Extract and ETL Load:
To troubleshoot ETL Extract, and ETL Load query the system tables stl_load_errors, and stl_loaderror_detail;
select * from stl_load_errors;
select * from stl_loaderror_detail;
Comments
Post a Comment