47 lines
1.8 KiB
SQL
47 lines
1.8 KiB
SQL
-- Migration 021: Create Kratos schema within nexus database
|
|
-- Kratos tables will live in a separate schema for isolation while sharing the database
|
|
|
|
-- Create kratos schema
|
|
CREATE SCHEMA IF NOT EXISTS kratos;
|
|
|
|
-- Create kratos roles (for Vault dynamic credentials)
|
|
-- These are NOLOGIN roles that Vault-created temp users will inherit
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'kratos_app') THEN
|
|
CREATE ROLE kratos_app NOLOGIN NOINHERIT;
|
|
END IF;
|
|
IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'kratos_migrate') THEN
|
|
CREATE ROLE kratos_migrate NOLOGIN NOINHERIT;
|
|
END IF;
|
|
END
|
|
$$;
|
|
|
|
-- Grant schema access
|
|
GRANT USAGE ON SCHEMA kratos TO kratos_app;
|
|
GRANT USAGE ON SCHEMA kratos TO kratos_migrate;
|
|
GRANT ALL PRIVILEGES ON SCHEMA kratos TO kratos_migrate;
|
|
|
|
-- Grant nexus_owner rights to manage kratos schema
|
|
GRANT ALL PRIVILEGES ON SCHEMA kratos TO nexus_owner;
|
|
|
|
-- Default privileges for future tables in kratos schema
|
|
-- When tables are created by nexus_owner (via migrations), these permissions apply
|
|
ALTER DEFAULT PRIVILEGES FOR ROLE nexus_owner IN SCHEMA kratos
|
|
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO kratos_app;
|
|
|
|
ALTER DEFAULT PRIVILEGES FOR ROLE nexus_owner IN SCHEMA kratos
|
|
GRANT ALL PRIVILEGES ON TABLES TO kratos_migrate;
|
|
|
|
-- Grant default privileges on sequences
|
|
ALTER DEFAULT PRIVILEGES FOR ROLE nexus_owner IN SCHEMA kratos
|
|
GRANT USAGE, SELECT ON SEQUENCES TO kratos_app;
|
|
|
|
ALTER DEFAULT PRIVILEGES FOR ROLE nexus_owner IN SCHEMA kratos
|
|
GRANT ALL PRIVILEGES ON SEQUENCES TO kratos_migrate;
|
|
|
|
-- Grant roles to vault_admin for dynamic credential creation
|
|
-- WITH ADMIN OPTION allows vault_admin to grant these roles to dynamically created users
|
|
GRANT kratos_app TO vault_admin WITH ADMIN OPTION;
|
|
GRANT kratos_migrate TO vault_admin WITH ADMIN OPTION;
|