start_db.sql

CREATE OR REPLACE FUNCTION create_database_with_users(
    db_name VARCHAR,
    read_user VARCHAR,
    read_password VARCHAR,
    write_user VARCHAR,
    write_password VARCHAR
)
RETURNS VOID AS $$
BEGIN
    -- Create database
    EXECUTE FORMAT('CREATE DATABASE %I;', db_name);

    -- Connect to the newly created database
    PERFORM pg_terminate_backend(pg_stat_activity.pid)
    FROM pg_stat_activity
    WHERE pg_stat_activity.datname = db_name;

    -- Create table
    EXECUTE FORMAT('CREATE TABLE %I (id SERIAL PRIMARY KEY, data VARCHAR(100));', db_name);

    -- Create read-only user
    EXECUTE FORMAT('CREATE USER %I WITH PASSWORD %L;', read_user, read_password);
    EXECUTE FORMAT('GRANT CONNECT ON DATABASE %I TO %I;', db_name, read_user);
    EXECUTE FORMAT('GRANT USAGE ON SCHEMA public TO %I;', read_user);
    EXECUTE FORMAT('GRANT SELECT ON ALL TABLES IN SCHEMA public TO %I;', read_user);

    -- Create read-write user
    EXECUTE FORMAT('CREATE USER %I WITH PASSWORD %L;', write_user, write_password);
    EXECUTE FORMAT('GRANT CONNECT ON DATABASE %I TO %I;', db_name, write_user);
    EXECUTE FORMAT('GRANT USAGE ON SCHEMA public TO %I;', write_user);
    EXECUTE FORMAT('GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO %I;', write_user);
END;
$$ LANGUAGE plpgsql;



--- so wird es benutzt

SELECT create_database_with_users(
    'my_database',
    'read_user',
    'read_password',
    'write_user',
    'write_password'
);