Grant all on DB to new user

grant_all.sql

GRANT CONNECT ON DATABASE "db-name" TO "user-name";
\c "db-name"; -- REQUIRED!!
GRANT USAGE ON SCHEMA "schema-name" TO "user-name";
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA "schema-name" TO "user-name";
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA "schema-name" TO "user-name";
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA "schema-name" TO "user-name";
GRANT ALL PRIVILEGES ON DATABASE "db-name" TO "user-name";


REVOKE USAGE ON SCHEMA "schema-name" FROM "user-name";
REVOKE SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA "schema-name" FROM "user-name";
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA "schema-name" FROM "user-name";
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA "schema-name" FROM "user-name";
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA "schema-name" FROM "user-name";
REVOKE ALL PRIVILEGES ON DATABASE "postgres" FROM "user-name";
REVOKE CONNECT ON DATABASE "db-name" FROM "user-name";
DROP USER "user-name";

Initial setup:

CREATE DATABASE database_name;
CREATE USER my_username WITH PASSWORD 'my_password';
GRANT ALL PRIVILEGES ON DATABASE "database_name" to my_username;
CREATE SCHEMA IF NOT EXISTS "schema_name";
GRANT usage on schema "schema_name" TO my_username;
GRANT create on schema "schema_name" TO my_username;

There is a simpler way for creating users/DBs if you don't need to set granular permission.

CREATE USER my_username WITH PASSWORD 'my_password';
ALTER ROLE my_username WITH SUPERUSER;
CREATE DATABASE database_name;
GRANT ALL PRIVILEGES ON DATABASE "database_name" to my_username;