Preparing the database

Now we can setup the database and migrate the database schema. In the database we are creating three users for the application:

• admin_$PREFIX - the admin user for managing the database
• server_$PREFIX - the regular user for the server
• ro_$PREFIX - an additional read-only user

The $PREFIX is replaced by the prefix configured in the environment configuration.

sudo -u postgres psql -q -v ON_ERROR_STOP=1 postgres <<EOF
set client_min_messages=WARNING;
begin;
  create user admin_$PREFIX  password '$DB_ADMIN_PASSWORD';
  create user server_$PREFIX password '$DB_SERVER_PASSWORD';
  create user ro_$PREFIX     password '$DB_RO_PASSWORD';
commit;
create database ktbs_$PREFIX TEMPLATE template0 
encoding 'UTF8' lc_ctype 'de_DE.UTF-8' lc_collate 'de_DE.UTF-8';
alter database ktbs_$PREFIX owner to admin_$PREFIX;
\c ktbs_$PREFIX postgres
set client_min_messages=WARNING;
begin;
  grant all   on schema public to admin_$PREFIX;
  grant usage on schema public to server_$PREFIX;
  grant usage on schema public to ro_$PREFIX;
commit;
\c ktbs_$PREFIX admin_$PREFIX
set client_min_messages=WARNING;
\i pg.sql
\i header.sql
begin;
  grant select, update, delete, insert   on all tables
    in schema public to server_$PREFIX;
  grant select, update                   on all sequences 
    in schema public to server_$PREFIX;
  grant select                           on all tables
    in schema public to ro_$PREFIX;
  grant select                           on all sequences 
    in schema public to ro_$PREFIX;
commit;
EOF

Note in case you have a remote database server, you can use psql with the DBHOST and DBPORT from environment configuration and will be asked for passwords for postgres and the admin_ user (as set in the environment configuration):

psql -h $DBHOST -p $DBPORT -U postgres -q -v ON_ERROR_STOP=1 postgres <<EOF
...
EOF