postgres-readonly-user-add.sql

CREATE USER readonlyuser WITH ENCRYPTED PASSWORD 'secret';

GRANT USAGE ON SCHEMA public to readonlyuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonlyuser;

-- repeat code below for each database:

GRANT CONNECT ON DATABASE mydbname to readonlyuser;
\c mydbname
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO readonlyuser; --- this grants privileges on new tables generated in new database "foo"
GRANT USAGE ON SCHEMA public to readonlyuser;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonlyuser;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonlyuser;