Database

The application uses three database roles:

Role	Purpose
admin_<PREFIX>	Owns the schema. Used for installation and upgrades.
server_<PREFIX>	The role the application connects as.
ro_<PREFIX>	Read-only access, for reporting and ad-hoc queries.

<PREFIX> is the prefix from Configuration. The role names follow this pattern and are not configurable — the application’s upgrade tooling derives them from it.

Make sure the variables are set in your shell:

source ./rap.config.sh

Extract the schema files

The SQL files ship inside the application image. Copy them out:

docker compose --profile tools run --rm --no-deps \
  --entrypoint /bin/sh -v "$PWD/sql:/sql" cms -c \
  'cp /ro/xin/sql/pg.sql            /sql/pg.sql &&
   cp /ro/xin/sql/pg-fun/header.sql /sql/header.sql'

ls -l sql/

Both files must be present: pg.sql is the schema, header.sql the stored functions.

Create the roles and the database

Creating roles and the database itself requires superuser rights. If the database is managed by someone else, hand them this block — the values come from rap.config.sh.

The commands below connect over the network, which is the usual case. Run them from the container host, where rap.config.sh and the extracted sql/ files are.

psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_SUPERUSER" -q -v ON_ERROR_STOP=1 \
  -d "$DB_SUPERUSER_DB_NAME" \
  -v db="$DB_NAME" \
  -v admin_user="admin_$PREFIX"   -v admin_pw="$DB_ADMIN_PASSWORD" \
  -v server_user="server_$PREFIX" -v server_pw="$DB_SERVER_PASSWORD" \
  -v ro_user="ro_$PREFIX"         -v ro_pw="$DB_RO_PASSWORD" <<'EOF'
set client_min_messages = WARNING;

SELECT format('%s ROLE %I LOGIN PASSWORD %L',
              CASE WHEN EXISTS (SELECT FROM pg_roles WHERE rolname = v.name)
                   THEN 'ALTER' ELSE 'CREATE' END,
              v.name, v.pw)
  FROM (VALUES (:'admin_user',  :'admin_pw'),
               (:'server_user', :'server_pw'),
               (:'ro_user',     :'ro_pw')) AS v(name, pw)
\gexec

SELECT format('CREATE DATABASE %I TEMPLATE template0 '
              'ENCODING ''UTF8'' LOCALE_PROVIDER icu '
              'ICU_LOCALE ''de-DE'' LOCALE ''C.UTF-8''', :'db')
 WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = :'db')
\gexec

SELECT format('ALTER DATABASE %I OWNER TO %I', :'db', :'admin_user')
\gexec
EOF

psql prompts for the DB_SUPERUSER password. It is deliberately not part of rap.config.sh — superuser credentials have no reason to sit in a file on the application host. The next step connects as the superuser again and asks a second time.

Grant access to the schema

Still as superuser, but now connected to the new database.

psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_SUPERUSER" -q -v ON_ERROR_STOP=1 \
  -d "$DB_NAME" \
  -v admin_user="admin_$PREFIX" \
  -v server_user="server_$PREFIX" \
  -v ro_user="ro_$PREFIX" <<'EOF'
set client_min_messages = WARNING;
GRANT ALL   ON SCHEMA public TO :"admin_user";
GRANT USAGE ON SCHEMA public TO :"server_user";
GRANT USAGE ON SCHEMA public TO :"ro_user";
EOF

Load the schema

This step runs as admin_<PREFIX>, so the tables end up owned by that role and not by the superuser.

The schema is loaded only when _ktbsversion is missing — the database itself decides, not a marker file. The grants at the end run every time, which is what makes this block the one to repeat after an upgrade.

PGPASSWORD="$DB_ADMIN_PASSWORD" \
psql -h "$DB_HOST" -p "$DB_PORT" -U "admin_$PREFIX" -q -v ON_ERROR_STOP=1 \
  -d "$DB_NAME" \
  -v server_user="server_$PREFIX" \
  -v ro_user="ro_$PREFIX" \
  -v pgsql="$PWD/sql/pg.sql" \
  -v headersql="$PWD/sql/header.sql" <<'EOF'
set client_min_messages = WARNING;

SELECT NOT EXISTS (SELECT FROM information_schema.tables
                    WHERE table_schema = 'public'
                      AND table_name = '_ktbsversion')
       AS load_schema \gset
\if :load_schema
  \echo 'Loading schema...'
  \i :pgsql
  \i :headersql
\else
  \echo '_ktbsversion present, skipping schema load'
\endif

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public
  TO :"server_user";
GRANT SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public
  TO :"server_user";
GRANT SELECT ON ALL TABLES IN SCHEMA public
  TO :"ro_user";
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public
  TO :"ro_user";
EOF

Verify

PGPASSWORD="$DB_SERVER_PASSWORD" psql -h "$DB_HOST" -p "$DB_PORT" \
  -U "server_$PREFIX" -d "$DB_NAME" \
  -c "select count(*) from _ktbsversion"

Expected result: 0 — the table exists and is reachable, and is filled later during Finalization.