122 lines
4.2 KiB
SQL
122 lines
4.2 KiB
SQL
-- Chattyness Database Schema Loader
|
|
-- PostgreSQL 18 with PostGIS
|
|
--
|
|
-- Master file to load all schema components in correct order.
|
|
--
|
|
-- Usage:
|
|
-- psql -d your_database -f schema/load.sql
|
|
--
|
|
-- Or from psql:
|
|
-- \i schema/load.sql
|
|
--
|
|
-- Prerequisites:
|
|
-- - PostgreSQL 18 with PostGIS extension available
|
|
-- - Database already created
|
|
-- - Superuser or appropriate privileges
|
|
|
|
\set ON_ERROR_STOP on
|
|
\timing on
|
|
|
|
\echo '=============================================='
|
|
\echo 'Chattyness Database Schema Loader'
|
|
\echo '=============================================='
|
|
\echo ''
|
|
|
|
-- =============================================================================
|
|
-- Phase 1: Initialization (schemas, extensions, roles)
|
|
-- =============================================================================
|
|
\echo 'Phase 1: Initialization...'
|
|
\ir 000_init.sql
|
|
\echo ''
|
|
|
|
-- =============================================================================
|
|
-- Phase 2: Types (ENUMs, domains)
|
|
-- =============================================================================
|
|
\echo 'Phase 2: Creating types...'
|
|
\ir types/001_enums.sql
|
|
\ir types/002_domains.sql
|
|
\echo ''
|
|
|
|
-- =============================================================================
|
|
-- Phase 3: Tables (in dependency order)
|
|
-- =============================================================================
|
|
-- Schema: server (010) → auth (020) → realm (030) → scene (045) → chat (050) → audit (080)
|
|
-- Note: props and moderation schemas removed; tables distributed to server/auth/realm/scene
|
|
\echo 'Phase 3: Creating tables...'
|
|
\ir tables/010_server.sql
|
|
\ir tables/020_auth.sql
|
|
\ir tables/030_realm.sql
|
|
\ir tables/045_scene.sql
|
|
\ir tables/050_chat.sql
|
|
\ir tables/080_audit.sql
|
|
\echo ''
|
|
|
|
-- =============================================================================
|
|
-- Phase 4: Functions
|
|
-- =============================================================================
|
|
\echo 'Phase 4: Creating functions...'
|
|
\ir functions/001_helpers.sql
|
|
\ir functions/002_user_init.sql
|
|
\echo ''
|
|
|
|
-- =============================================================================
|
|
-- Phase 5: Triggers
|
|
-- =============================================================================
|
|
\echo 'Phase 5: Creating triggers...'
|
|
\ir triggers/001_updated_at.sql
|
|
\ir triggers/002_user_init.sql
|
|
\echo ''
|
|
|
|
-- =============================================================================
|
|
-- Phase 6: Row-Level Security Policies
|
|
-- =============================================================================
|
|
\echo 'Phase 6: Enabling Row-Level Security...'
|
|
\ir policies/001_rls.sql
|
|
\echo ''
|
|
|
|
-- =============================================================================
|
|
-- Complete
|
|
-- =============================================================================
|
|
\echo '=============================================='
|
|
\echo 'Schema loaded successfully!'
|
|
\echo '=============================================='
|
|
\echo ''
|
|
|
|
-- =============================================================================
|
|
-- Phase 7: Set Passwords for Application Roles
|
|
-- =============================================================================
|
|
\echo 'Phase 7: Setting up application credentials...'
|
|
|
|
-- Generate secure passwords
|
|
\set app_password `pwgen -s 80 1`
|
|
\set owner_password `pwgen -s 80 1`
|
|
|
|
-- Set passwords for roles (created in init.sql)
|
|
ALTER ROLE chattyness_app WITH PASSWORD :'app_password';
|
|
ALTER ROLE chattyness_owner WITH PASSWORD :'owner_password';
|
|
|
|
\echo ''
|
|
\echo '=============================================='
|
|
\echo 'Application Credentials Set'
|
|
\echo '=============================================='
|
|
\echo ''
|
|
\echo 'chattyness_app (application role, subject to RLS):'
|
|
\echo ' Password: ' :app_password
|
|
\echo ''
|
|
\echo 'chattyness_owner (owner role, bypasses RLS):'
|
|
\echo ' Password: ' :owner_password
|
|
\echo ''
|
|
|
|
-- Write .env file
|
|
\! echo "# Chattyness Database Credentials" > .env
|
|
\! echo "# Generated by load.sql" >> .env
|
|
\! echo "" >> .env
|
|
\set write_app `echo "export DB_CHATTYNESS_APP=":app_password >> .env`
|
|
\set write_owner `echo "export DB_CHATTYNESS_OWNER=":owner_password >> .env`
|
|
|
|
\echo 'Credentials written to: .env'
|
|
\echo ''
|
|
\echo 'Login roles:'
|
|
\echo ' chattyness_app - Application operations (subject to RLS)'
|
|
\echo ' chattyness_owner - Owner operations (bypasses RLS)'
|
|
\echo ''
|