30 lines
1.1 KiB
SQL
30 lines
1.1 KiB
SQL
-- Extensions aktivieren
|
|
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
|
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
|
|
|
|
-- Entziehe allgemeine Rechte auf PUBLIC Schema
|
|
REVOKE ALL ON SCHEMA public FROM PUBLIC;
|
|
|
|
-- Rollen anlegen
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'app_user') THEN
|
|
CREATE ROLE app_user WITH LOGIN PASSWORD 'POSTGRES_APP_PASSWORD_PLACEHOLDER';
|
|
END IF;
|
|
IF NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'migration_user') THEN
|
|
CREATE ROLE migration_user WITH LOGIN PASSWORD 'POSTGRES_MIGRATION_PASSWORD_PLACEHOLDER';
|
|
END IF;
|
|
END
|
|
$$;
|
|
|
|
-- Schema-Rechte
|
|
GRANT USAGE, CREATE ON SCHEMA public TO migration_user;
|
|
GRANT USAGE ON SCHEMA public TO app_user;
|
|
|
|
-- Standard-Berechtigungen für zukünftige Tabellen/Sequenzen
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO app_user;
|
|
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO migration_user;
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO migration_user;
|