32 lines
1.2 KiB
SQL
32 lines
1.2 KiB
SQL
-- =============================================================================
|
|
-- User Database Initialization
|
|
-- =============================================================================
|
|
-- Enables pgcrypto extension for column-level encryption.
|
|
-- This database stores all user-owned PII: conversations, messages, contacts, clips.
|
|
--
|
|
-- Encryption Strategy:
|
|
-- Layer 1: Disk-level encryption (LUKS) - handled by infrastructure
|
|
-- Layer 2: pgcrypto column encryption - handled by this database
|
|
--
|
|
-- Encrypted columns (handled by @lilith/typeorm-pgcrypto):
|
|
-- - messages.content_encrypted
|
|
-- - messages.attachments_encrypted
|
|
-- - contacts.contact_info_encrypted
|
|
-- - contacts.notes_encrypted
|
|
-- - saved_clips.content_encrypted
|
|
|
|
-- Enable pgcrypto extension for symmetric encryption
|
|
CREATE EXTENSION IF NOT EXISTS pgcrypto;
|
|
|
|
-- Enable uuid-ossp for UUID generation
|
|
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
|
|
|
-- Grant usage to the database user
|
|
-- Note: In production, restrict this to specific roles
|
|
GRANT USAGE ON SCHEMA public TO PUBLIC;
|
|
|
|
-- Log initialization complete
|
|
DO $$
|
|
BEGIN
|
|
RAISE NOTICE 'User database initialized with pgcrypto and uuid-ossp extensions';
|
|
END $$;
|