Configuring PostgreSQL

Okapi uses PostgreSQL for Web and Oscar application state. This guide covers how to configure Postgres such that its ready to for installing Okapi. Note the scope of this guide is to create the necessary users with the right permissions. Creating tables is already handled by ops as a migration step.

Scope

This page covers PostgreSQL configuration only.

A k8s deployment requires that credentials be stored as secrets. This is described and documented in the production deployment guide.

Users and permissions

Okapi uses separate users for migration and web and oscar sub-systems. This to comply with principle of least permissions. For example, the rationale to separate oscar from web is avoid exposing application specific data to AI (e.g. user favorites). Similarly if web layer wants to request user data such as chat history, it must talk to oscar sub-system and not access the database directly.

Here’s what we’ll configure:

Create the roles and schemas

Note: the following requires PostgreSQL administrator privileges.

The example uses Okapi’s default database name, okapi_oscar. The database must already exist; if you use another name, replace okapi_oscar consistently in this SQL and in the Helm values. Replace the passwords with environment-specific values. Here’s the full SQL:

CREATE ROLE okapi_web_migration_user LOGIN PASSWORD '<migration-password>';
CREATE ROLE okapi_web_user LOGIN PASSWORD '<web-password>';
CREATE ROLE okapi_oscar_user LOGIN PASSWORD '<oscar-password>';

GRANT CONNECT ON DATABASE okapi_oscar
  TO okapi_web_migration_user, okapi_web_user, okapi_oscar_user;

CREATE SCHEMA IF NOT EXISTS okapi_web
  AUTHORIZATION okapi_web_migration_user;
CREATE SCHEMA IF NOT EXISTS okapi_oscar
  AUTHORIZATION okapi_oscar_user;

ALTER SCHEMA okapi_web OWNER TO okapi_web_migration_user;
ALTER SCHEMA okapi_oscar OWNER TO okapi_oscar_user;

GRANT USAGE, CREATE ON SCHEMA okapi_web
  TO okapi_web_migration_user;
GRANT USAGE ON SCHEMA okapi_web TO okapi_web_user;
REVOKE CREATE ON SCHEMA okapi_web FROM PUBLIC, okapi_web_user;

GRANT SELECT, INSERT, UPDATE, DELETE
  ON ALL TABLES IN SCHEMA okapi_web TO okapi_web_user;
GRANT USAGE, SELECT, UPDATE
  ON ALL SEQUENCES IN SCHEMA okapi_web TO okapi_web_user;

ALTER DEFAULT PRIVILEGES FOR ROLE okapi_web_migration_user
  IN SCHEMA okapi_web
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO okapi_web_user;
ALTER DEFAULT PRIVILEGES FOR ROLE okapi_web_migration_user
  IN SCHEMA okapi_web
  GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO okapi_web_user;

GRANT USAGE, CREATE ON SCHEMA okapi_oscar TO okapi_oscar_user;

If the roles already exist, use ALTER ROLE ... PASSWORD instead of CREATE ROLE. If a PostgreSQL operator manages roles, use its supported user and grant resources rather than applying the role statements directly.

Admins note

Its better to create these users once via a init.sql file. This can easily be hooked into a Kubernetes operator for Postgres.