PG - DML only Role Creation script

postgres-dml-role.sql

-- first create a new user if not exists for your app
-- this user will have access only to DML queries (select, update, insert, delete)
DO
$do$
BEGIN
   IF NOT EXISTS (
      SELECT FROM pg_catalog.pg_roles
      WHERE  rolname = 'app_user') THEN
      CREATE ROLE app_user LOGIN PASSWORD 'PASS';
   END IF;
END
$do$;

-- then change default privileges on the schema to grant DML permissions
-- to this new user. 
alter default privileges
   for user postgres -- here goes the role that will create new tables
   IN SCHEMA public
   grant SELECT, INSERT, UPDATE, DELETE ON TABLES to app_user; -- here is role for the new user to be granted 
alter default privileges
   for user postgres -- here goes the role that will create new tables
   IN SCHEMA public
   GRANT USAGE, SELECT ON SEQUENCES to app_user; -- here is role for the new user to be granted 
   
grant SELECT, INSERT, UPDATE, DELETE on ALL TABLES in schema public to app_user; -- then grant access to this new role for the existing tables
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;  -- then grant access to this new role for the existing tables