-- NewDBScript.sql
-- Assign variables:

set dbowner 'xxxxxxxx'
set dbname 'xxxxxxxx'
set dbuser 'xxxxxxxx'

-- Create the users:
CREATE USER :dbowner;
password :dbowner

CREATE USER :dbuser;
password :dbuser

-- Create the database:
CREATE DATABASE :dbname WITH OWNER :dbowner ENCODING 'UTF8' CONNECTION LIMIT -1;

-- Connect to  database using postgres
c :dbname postgres


-- Create the application schema & drop the public schema
CREATE SCHEMA IF NOT EXISTS :dbuser AUTHORIZATION :dbowner;
DROP SCHEMA IF EXISTS public CASCADE;

-- Set the schema for the database to be the hero account schema
alter database :dbname set search_path=:dbuser;
show search_path;


-- Connect to the database using database owner
c :dbname :dbowner
show search_path;

...

-- After all the objects were created, I granted the following permissions to the application user:

GRANT usage on SCHEMA :dbuser to :dbuser;
GRANT select, insert, update, delete on ALL TABLES IN SCHEMA :dbuser to :dbuser;
GRANT usage on ALL SEQUENCES IN SCHEMA :dbuser to :dbuser;

%% part of init/1
%% Open a database connection to PostgreSQL
pgsql_connect(Server, Port, DB, Username, Password) ->
     case pgsql:connect([{host, Server},
                         {database, DB},
                         {user, Username},
                         {password, Password},
                         {port, Port},
                         {as_binary, true}]) of
         {ok, Ref} ->
             pgsql:squery(Ref, [<<"alter database ">>, DB, <<" set ">>,
                                <<"standard_conforming_strings='off';">>]),
             pgsql:squery(Ref, [<<"set standard_conforming_strings to
'off';">>]),
             {ok, Ref};
         Err ->
             Err
     end.