Thursday, February 11, 2016

Useful PostgreSQL setup recipes

CREATE USER foo;
(or equivalently: CREATE ROLE foo WITH login;)
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO foo;
or
GRANT SELECT ON ALL TABLES IN SCHEMA public TO foo;

I think these are all pretty self explanatory. Then the new user is up and running!
If you want the user to have a password:
CREATE USER foo WITH PASSWORD 'bar';

To get rid of the user: 
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM foo;
DROP ROLE foo;