Database setup

After you have created the Postgres instance, set up the database, role, schema, and extensions.

Log into Postgres as a superuser:

pgcli -u postgres -d postgres

Then run:

\c postgres
CREATE DATABASE cooker WITH OWNER postgres;
CREATE ROLE app_user WITH LOGIN PASSWORD '123'; -- use SCRAM in prod

-- The following needs to be executed while connected to the cooker database.
\c cooker
-- Revoke the ability for all users (PUBLIC) to create anything in the public schema.
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- Create new schemas.
CREATE SCHEMA app AUTHORIZATION app_user;

-- Create extensions in the "public" schema.
CREATE EXTENSION IF NOT EXISTS "pg_uuidv7" WITH SCHEMA public;
-- CREATE EXTENSION IF NOT EXISTS "btree_gin" WITH SCHEMA public;
-- CREATE EXTENSION IF NOT EXISTS "pg_trgm" WITH SCHEMA public;
-- CREATE EXTENSION IF NOT EXISTS "vector" WITH SCHEMA public;