aws_rds_new_user_with_db.sql

-- via https://wiki.postgresql.org/wiki/Shared_Database_Hosting


-- prep template1

-- \c template1 <ROOTUSER>
REVOKE ALL ON DATABASE template1 FROM public;
GRANT ALL ON DATABASE template1 TO rds_superuser WITH GRANT OPTION;
REVOKE ALL ON SCHEMA public FROM public;
GRANT ALL ON SCHEMA public TO rds_superuser WITH GRANT OPTION;
CREATE LANGUAGE plpgsql;


-- new database + main use

-- \c template1 <ROOTUSER>
CREATE ROLE <DBNAME> NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT NOLOGIN;
CREATE ROLE <DBMAINUSER> NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT LOGIN ENCRYPTED PASSWORD '<DBMAINUSERPASS>';
GRANT <DBNAME> TO <DBMAINUSER>;
GRANT <DBMAINUSER> TO current_user;
CREATE DATABASE <DBNAME> WITH OWNER=<DBMAINUSER>;
REVOKE ALL ON DATABASE <DBNAME> FROM public;
GRANT ALL ON DATABASE <DBNAME> TO rds_superuser WITH GRANT OPTION;
REVOKE <DBMAINUSER> FROM current_user;

-- \c <DBNAME> <ROOTUSER>
GRANT ALL ON SCHEMA public TO <DBMAINUSER> WITH GRANT OPTION;