TalkingQuickly's Today I Learned

Creating a read only user for Postgres

One of my most common DBA tasks is adding read only users for hooking up analytics systems, this is the most concise approach I've found to this. Since Redshift mirrors Postgres for most of these things, the same approach should also work on Redshift.

-- Create a group
CREATE ROLE readaccess;

-- Grant access to existing tables
GRANT USAGE ON SCHEMA public TO readaccess;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readaccess;

-- Grant access to future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readaccess;

-- Create a final user with password
CREATE USER some_username WITH PASSWORD 'secret';
GRANT readaccess TO some_username;

This came from this gist: https://gist.github.com/oinopion/4a207726edba8b99fd0be31cb28124d0