January 12, 2016

Add Read-Only Postgres User

After the MySQL read only user created the other day, I now needed one for Postgres, so here’s how.

psql databasehere
CREATE USER moodle_readonly WITH ENCRYPTED PASSWORD 'blablablapasswordhere';
GRANT CONNECT ON DATABASE moodle TO moodle_readonly;
GRANT USAGE ON SCHEMA public TO moodle_readonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO moodle_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public to moodle_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO moodle_readonly;

This will give read access to the database moodle, but unlike most read only how-to’s on the internet, this will also give read-only access to new any tables created in that database.

Leave a Reply

Your email address will not be published. Required fields are marked *

css.php