postgresql-read-access.sql

-- 1. Create a group
CREATE ROLE readaccess;

-- 2. Grant usage on schema:
GRANT USAGE ON SCHEMA schema_name TO readaccess;

-- 3.1 Grant select for a specific table:
GRANT SELECT ON table_name TO readaccess;

-- 3.2 Grant select for multiple tables:
GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO readaccess;

-- 4. If you want to grant access to the new table in the future automatically, you have to alter default:
ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name GRANT SELECT ON TABLES TO readaccess;

-- 5. To create a new user in PostgreSQL
CREATE USER username WITH PASSWORD 'your_password';

-- 6 Grant the connect access:
GRANT CONNECT ON DATABASE database_name TO username;

-- 7 Grant the read access:
GRANT readaccess TO username;