Creating SQL user with limited privileges

user.sql

-- Run these commands to create privileged users.
    -- application: can run all except truncate or delete tables
    -- dev_user: can only read from tables
    -- db_admin: can only create roles or delegate roles to users
-- RDS is on Postgres 10.17

----------------------------
-- Create application user
----------------------------
--- Before running these commands, make sure no tables exist in the database.

--- This is because the tables must be created (and thus owned)
--- by the `application` user for the app to run properly.

--- To check the tables and its owner, type `\dt`.
--- You should see "Did not find any relations."

--- You can always drop all the tables and re-start the app, which
--- will recreate the tables for you as user `application`.

--- Run the following as root user.
-- https://www.postgresql.org/docs/12/sql-alterdefaultprivileges.html

CREATE USER application WITH PASSWORD $PASSWORD;
GRANT USAGE ON SCHEMA public TO application;

-- Define privileges for all schemas + tables moving forward
GRANT application to $ROOT_USER;
ALTER DEFAULT PRIVILEGES
    FOR ROLE application
    GRANT ALL PRIVILEGES ON SCHEMAS
    TO application;

ALTER DEFAULT PRIVILEGES
    FOR ROLE application
    REVOKE DELETE, TRUNCATE
    ON TABLES
    FROM application;

----------------------------
-- Create dev user
----------------------------
-- ***Run the following query as the `application` postgres user.***
-- It will not work if you are the root user, since you don't own
-- the table.

GRANT application to $ROOT_USER;
CREATE USER dev_user WITH PASSWORD $PASSWORD;
ALTER DEFAULT PRIVILEGES
    FOR ROLE dev_user
    GRANT SELECT ON TABLES
    TO dev_user;

-------------------
-- Create db admin
-------------------
--- Run the following as root user.
CREATE ROLE db_admin WITH LOGIN CREATEROLE PASSWORD $PASSWORD;

---- Notes
-- To remove user's membership from role, run:
    -- drop owned by application;
    -- drop user application;

-- To see which user has which GRANTs to a table, run:
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name='customer';

-- When you are giving users permission to a table,
-- you must be logged in as the table's owner first.
-- A table's owner is the user that created the table.
-- To see a table's owner, run `\dt` and see the `Owner` column.

-- To revoke or grant DELETE from table to role (while logged in as the `application` role)
GRANT SELECT ON TABLE $TABLE TO $USER;
REVOKE SELECT ON TABLE $TABLE FROM $USER;
REVOKE SELECT ON TABLE PAYMENT FROM APPLICATION;