Postgres Database Initialization

postgres-db-init.sql

--Create user xxx_dba 
create user xxx_dba with encrypted password 'Dba12345';

--Create app user
create user xxx_user with encrypted password 'User12345';

--Create xxx database (db owner = xxx_dba)
create database xxx with owner xxx_dba;

--Change db to xxx without change user
\c xxx

--Only allow rcdb_oper and xxx_dba role to create objects on xxx database public schema
revoke all on schema public from public;
grant all on schema public to xxx_dba with grant option;

--Alter privileges for xxx_dba to xxx_user 
grant usage on schema public to xxx_user;
grant connect on database xxx to xxx_user;
alter default privileges for role xxx_dba in schema public grant select, insert, update, delete on tables to xxx_user;
alter default privileges for role xxx_dba in schema public grant select, update on sequences to xxx_user;