Redshift Cheatsheet

redshift-cheatsheet.sql

## General Stuff

### List of all tables
SELECT * FROM pg_catalog.pg_tables

### Create new user and give it superuser access
create user adminuser createuser password '1234Admin';
alter user adminuser createuser;

### Create user without superuser privilege
create user user1 password 'md537af65b44378ac7a5a1fb187a1969c71'; -- Use the generated md5 as password

### Grant all to specific user
GRANT SELECT ON ALL TABLES IN SCHEMA public TO user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO user;

### Drop User
drop user danny;

### Revoke database access from a user
revoke all on database dwtable from dwuser;

### Change user password
alter user newuser password 'EXAMPLENewPassword11'; 

### Automatically gives access when new table is created
ALTER DEFAULT PRIVILEGES GRANT SELECT ON TABLES TO PUBLIC
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO PUBLIC

### Create group
CREATE GROUP developers;

### Add user to group
ALTER GROUP developers
ADD USER dgallmeier;
    
### Create db
CREATE DATABASE sandbox 
WITH OWNER = dgallmeier;

### grant access to group
GRANT USAGE ON SCHEMA pricing TO group developers;
GRANT SELECT ON ALL TABLES IN SCHEMA pricing TO group developers;