Your SUPERUSER account may be deployed to the database as part of provisioning your instance. This is the case when you use AWS RDS. If so you manage admin rights using a different tooling (Puppet, Terraform, Ansible).
A basic security model in an idempotent parameterized script may look like this
-- ROLES WE USE FOR SCHEMA ACCESS GRANTS
--Role
DO $$
BEGIN
CREATE ROLE sisense_or_somthing_read_role;
EXCEPTION
WHEN duplicate_object THEN
RAISE NOTICE 'Role already exists. Ignoring...';
END$$;
--Role
DO $$
BEGIN
CREATE ROLE sisense_or_somthing_readwrite_role IN ROLE sisense_or_somthing_read_role;
EXCEPTION
WHEN duplicate_object THEN
RAISE NOTICE 'Role already exists. Ignoring...';
END$$;
--Role
DO $$
BEGIN
CREATE ROLE sisense_or_somthing_exececutor IN ROLE sisense_or_somthing_readwrite_role;
EXCEPTION
WHEN duplicate_object THEN
RAISE NOTICE 'Role already exists. Ignoring...';
END$$;
--Role
DO $$
BEGIN
CREATE USER sisense_or_somthing_reader WITH PASSWORD :var_pwd1 IN ROLE sisense_or_somthing_read_role;
EXCEPTION
WHEN duplicate_object THEN
RAISE NOTICE 'Role already exists. Ignoring...';
END$$;
--Role
DO $$
BEGIN
CREATE USER sisense_or_somthing_readerwriter WITH PASSWORD :var_pwd2 IN ROLE sisense_or_somthing_readwrite_role;
EXCEPTION
WHEN duplicate_object THEN
RAISE NOTICE 'Role already exists. Ignoring...';
END$$;
--Role
DO $$
BEGIN
CREATE USER sisense_or_somthing_maintenance WITH PASSWORD :var_pwd3 IN ROLE sisense_or_somthing_exececutor;
EXCEPTION
WHEN duplicate_object THEN
RAISE NOTICE 'Role already exists. Ignoring...';
END$$;
CREATE SCHEMA IF NOT EXISTS sisense_or_somthing;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO sisense_or_somthing_read_role;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO sisense_or_somthing_exececutor;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA sisense_or_somthing TO sisense_or_somthing_exececutor;
GRANT SELECT ON ALL TABLES IN SCHEMA sisense_or_somthing TO sisense_or_somthing_read_role;
GRANT INSERT ON ALL TABLES IN SCHEMA sisense_or_somthing TO sisense_or_somthing_readwrite_role;
GRANT UPDATE ON ALL TABLES IN SCHEMA sisense_or_somthing TO sisense_or_somthing_readwrite_role;
GRANT DELETE ON ALL TABLES IN SCHEMA sisense_or_somthing TO sisense_or_somthing_readwrite_role;
GRANT TRUNCATE ON ALL TABLES IN SCHEMA sisense_or_somthing TO sisense_or_somthing_readwrite_role;
A validation query or two can help
SELECT
pr.oid role_oid, pr.rolname role_name, pr.rolcanlogin, pr.rolinherit, pr_p.rolname parent_role,
m.roleid role_member_oid, m.grantor, m.admin_option,
pr_g.rolname grantor_name, pr_g.rolcanlogin, pr_g.rolinherit
FROM
pg_roles pr
left join pg_auth_members m ON m.member = pr.oid
left join pg_roles pr_g on pr_g.oid = m.grantor
left join pg_roles pr_p on pr_p.oid = m.roleid
WHERE pr.rolname in (
'kuras_or_something_reader', 'kuras_or_something_readerwriter',
'kuras_or_something_writer', 'kuras_or_something_wrtie_role', 'kuras_or_something_exececutor',
'kuras_or_something_maintenance')
SELECT grantee, string_agg(privilege_type, ', ') AS privileges
FROM information_schema.role_table_grants
WHERE table_name=‘model_results_4’
GROUP BY grantee;
And the revert migration script would be something like this.
DROP SCHEMA IF EXISTS sisense_or_somthing;
DROP USER IF EXISTS sisense_or_somthing_admin;
DROP USER IF EXISTS sisense_or_somthing_reader;
DROP USER IF EXISTS sisense_or_somthing_readerwriter;
DROP USER IF EXISTS sisense_or_somthing_maintenance;
DROP ROLE IF EXISTS sisense_or_somthing_read_role;
DROP ROLE IF EXISTS sisense_or_somthing_readwrite_role;
DROP ROLE IF EXISTS sisense_or_somthing_exececutor;
