149 lines
6.4 KiB
PL/PgSQL
149 lines
6.4 KiB
PL/PgSQL
-- Chattyness Database Schema Initialization
|
|
-- PostgreSQL 18
|
|
--
|
|
-- This file drops all existing schema and recreates from scratch.
|
|
-- Load via: psql -d chattyness -f schema/000_init.sql
|
|
--
|
|
-- WARNING: This destroys all data!
|
|
|
|
\set ON_ERROR_STOP on
|
|
|
|
BEGIN;
|
|
|
|
-- =============================================================================
|
|
-- Drop Everything (CASCADE removes all dependent objects)
|
|
-- =============================================================================
|
|
|
|
DROP SCHEMA IF EXISTS audit CASCADE;
|
|
DROP SCHEMA IF EXISTS chat CASCADE;
|
|
DROP SCHEMA IF EXISTS scene CASCADE;
|
|
DROP SCHEMA IF EXISTS realm CASCADE;
|
|
DROP SCHEMA IF EXISTS auth CASCADE;
|
|
DROP SCHEMA IF EXISTS server CASCADE;
|
|
|
|
-- Drop public domains we create (not removed by schema drops)
|
|
DROP DOMAIN IF EXISTS public.scene_bounds CASCADE;
|
|
DROP DOMAIN IF EXISTS public.virtual_point CASCADE;
|
|
DROP DOMAIN IF EXISTS public.asset_path CASCADE;
|
|
DROP DOMAIN IF EXISTS public.url CASCADE;
|
|
DROP DOMAIN IF EXISTS public.hex_color CASCADE;
|
|
DROP DOMAIN IF EXISTS public.slug CASCADE;
|
|
DROP DOMAIN IF EXISTS public.display_name CASCADE;
|
|
DROP DOMAIN IF EXISTS public.nonempty_text CASCADE;
|
|
DROP DOMAIN IF EXISTS public.percentage CASCADE;
|
|
|
|
-- Drop all objects owned by application roles (required to avoid dependency errors)
|
|
DO $$
|
|
BEGIN
|
|
-- Drop objects owned by chattyness_owner
|
|
IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'chattyness_owner') THEN
|
|
DROP OWNED BY chattyness_owner CASCADE;
|
|
END IF;
|
|
-- Drop objects owned by chattyness_app
|
|
IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'chattyness_app') THEN
|
|
DROP OWNED BY chattyness_app CASCADE;
|
|
END IF;
|
|
END $$;
|
|
|
|
-- Drop all application roles
|
|
DROP ROLE IF EXISTS chattyness_app;
|
|
DROP ROLE IF EXISTS chattyness_owner;
|
|
|
|
-- =============================================================================
|
|
-- Extensions
|
|
-- =============================================================================
|
|
|
|
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
|
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
|
|
CREATE EXTENSION IF NOT EXISTS "btree_gist";
|
|
CREATE EXTENSION IF NOT EXISTS "pg_trgm";
|
|
CREATE EXTENSION IF NOT EXISTS "postgis";
|
|
|
|
-- =============================================================================
|
|
-- Schemas
|
|
-- =============================================================================
|
|
|
|
CREATE SCHEMA server;
|
|
COMMENT ON SCHEMA server IS 'Server-wide configuration, global props, shared resources, and server-level moderation';
|
|
|
|
CREATE SCHEMA auth;
|
|
COMMENT ON SCHEMA auth IS 'User authentication, accounts, identity management, inventory, and avatars';
|
|
|
|
CREATE SCHEMA realm;
|
|
COMMENT ON SCHEMA realm IS 'Realms, scenes, memberships, realm props, and realm-level moderation';
|
|
|
|
CREATE SCHEMA scene;
|
|
COMMENT ON SCHEMA scene IS 'Scene instances, members, spots, loose props, and decorations';
|
|
|
|
CREATE SCHEMA chat;
|
|
COMMENT ON SCHEMA chat IS 'Messages, whispers, shouts, and reactions';
|
|
|
|
CREATE SCHEMA audit;
|
|
COMMENT ON SCHEMA audit IS 'Audit trails and activity logging';
|
|
|
|
-- =============================================================================
|
|
-- Application Roles
|
|
-- =============================================================================
|
|
|
|
-- Application login role for normal operations (subject to RLS)
|
|
-- Password is set in load.sql
|
|
CREATE ROLE chattyness_app LOGIN;
|
|
COMMENT ON ROLE chattyness_app IS 'Application login role - subject to RLS, user context passed via session variables';
|
|
|
|
-- Owner role with full access (bypasses RLS)
|
|
-- Password is set in load.sql
|
|
CREATE ROLE chattyness_owner LOGIN BYPASSRLS;
|
|
COMMENT ON ROLE chattyness_owner IS 'Owner role - bypasses RLS for server management';
|
|
|
|
-- =============================================================================
|
|
-- Grant Full Access to Owner Role
|
|
-- =============================================================================
|
|
|
|
-- Grant usage on all schemas
|
|
GRANT USAGE ON SCHEMA public TO chattyness_owner;
|
|
GRANT USAGE ON SCHEMA server TO chattyness_owner;
|
|
GRANT USAGE ON SCHEMA auth TO chattyness_owner;
|
|
GRANT USAGE ON SCHEMA realm TO chattyness_owner;
|
|
GRANT USAGE ON SCHEMA scene TO chattyness_owner;
|
|
GRANT USAGE ON SCHEMA chat TO chattyness_owner;
|
|
GRANT USAGE ON SCHEMA audit TO chattyness_owner;
|
|
|
|
-- Grant all privileges on all tables in each schema
|
|
-- These will apply to tables created later in the load sequence
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO chattyness_owner;
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA server GRANT ALL ON TABLES TO chattyness_owner;
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA auth GRANT ALL ON TABLES TO chattyness_owner;
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA realm GRANT ALL ON TABLES TO chattyness_owner;
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA scene GRANT ALL ON TABLES TO chattyness_owner;
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA chat GRANT ALL ON TABLES TO chattyness_owner;
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA audit GRANT ALL ON TABLES TO chattyness_owner;
|
|
|
|
-- Grant all privileges on sequences (for identity columns)
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO chattyness_owner;
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA server GRANT ALL ON SEQUENCES TO chattyness_owner;
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA auth GRANT ALL ON SEQUENCES TO chattyness_owner;
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA realm GRANT ALL ON SEQUENCES TO chattyness_owner;
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA scene GRANT ALL ON SEQUENCES TO chattyness_owner;
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA chat GRANT ALL ON SEQUENCES TO chattyness_owner;
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA audit GRANT ALL ON SEQUENCES TO chattyness_owner;
|
|
|
|
-- Grant execute on all functions
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT EXECUTE ON FUNCTIONS TO chattyness_owner;
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA server GRANT EXECUTE ON FUNCTIONS TO chattyness_owner;
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA auth GRANT EXECUTE ON FUNCTIONS TO chattyness_owner;
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA realm GRANT EXECUTE ON FUNCTIONS TO chattyness_owner;
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA scene GRANT EXECUTE ON FUNCTIONS TO chattyness_owner;
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA chat GRANT EXECUTE ON FUNCTIONS TO chattyness_owner;
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA audit GRANT EXECUTE ON FUNCTIONS TO chattyness_owner;
|
|
|
|
-- =============================================================================
|
|
-- Schema Search Path
|
|
-- =============================================================================
|
|
|
|
DO $$
|
|
BEGIN
|
|
EXECUTE format('ALTER DATABASE %I SET search_path TO public, server, auth, realm, scene, chat, audit', current_database());
|
|
END
|
|
$$;
|
|
|
|
COMMIT;
|