PostgreSQL: Setup separate users for application, all permissions for DBA and select only BA

postgresql-users.sql

-- The postgres user can create users and grant permissions
-- Change the default postgres user password
ALTER USER postgres PASSWORD '<new-password>';

-- The dba use it for developers or database administrator (DBA)
-- Access is given only to appdb DB.
-- For administering other dbs or users use postgres user
CREATE USER dba WITH PASSWORD '<new-password>';
GRANT ALL PRIVILEGES ON DATABASE appdb TO dba;
GRANT ALL PRIVILEGES ON SCHEMA app TO dba;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA app TO dba;

-- For a business analytics (BA) only allowed only SELECT
CREATE USER ba WITH PASSWORD '<new-password>';
GRANT CONNECT, TEMPORARY ON DATABASE appdb TO ba;
GRANT USAGE ON SCHEMA app TO ba;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO ba;

-- A user for the application service/daemon itself.
-- It's limited to not change a schema
CREATE USER app_service WITH PASSWORD '<new-password>';
GRANT CONNECT, TEMPORARY ON DATABASE appdb TO app_service;
GRANT USAGE ON SCHEMA app TO app_service;
GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA app TO app_service;