-- Create a group with read-only access
CREATE ROLE readonly;

-- Grant access on public sheme to existing tables
GRANT USAGE ON SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly; -- grant access to future tables

-- Grant access to specific database, repeat code below for each database
GRANT CONNECT ON DATABASE db_name to readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO readonly; -- grant access to future tables
GRANT USAGE ON SCHEMA public to readonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

-- Create a user with password
CREATE USER ro_user WITH ENCRYPTED PASSWORD 'ro_password';
GRANT readaccess TO ro_user;