-- ensure we have the database
SELECT 'CREATE DATABASE ' || :'db_name'
WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = :'db_name')\gexec
-- create the user (if it doesn't exist)
CREATE OR REPLACE FUNCTION add_user_if_not_exists(username NAME) RETURNS integer
    AS $$
BEGIN
    IF NOT EXISTS (SELECT FROM pg_roles
        WHERE rolname = username) THEN

        EXECUTE FORMAT('CREATE ROLE "%I" LOGIN', username);

    END IF;
    RETURN NULL;
END;
$$ language plpgsql;
CREATE FUNCTION
SELECT add_user_if_not_exists(:'db_user');
 add_user_if_not_exists 
------------------------
                       
(1 row)

-- now set the password
ALTER USER :db_user WITH PASSWORD :'db_password';
ALTER ROLE
-- grant permission to connect
GRANT CONNECT ON DATABASE :db_name TO :db_user;
GRANT
-- grant permissions on database
\connect :db_name
You are now connected to database "tpa" as user "tpa".
GRANT USAGE ON SCHEMA public TO :db_user;
GRANT
-- grant on existing tables
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO :db_user;
GRANT
-- grant on future tables
ALTER DEFAULT PRIVILEGES
    IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO :db_user;
ALTER DEFAULT PRIVILEGES