Redshift Cheatsheet

redshift-cheatsheet.sql

## General Stuff

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

### Create a new user and give it superuser access
CREATE USER adminuser createuser password '1234Admin';
ALTER USER adminuser createuser;

### Create a user without superuser privilege
SELECT md5('put-your-password-here' || 'put-your-username-here'); -- Generate the md5 for the password
CREATE USER your-username password 'md537af65b44378ac7a5a1fb187a1969c71'; -- Use the generated md5 from command above as password

### Grant all to a 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 user access to a database
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