-- Create an admin role, database and grant create
CREATE ROLE redadmin_role NOLOGIN ADMIN postgres;
CREATE DATABASE redrepo_db;
GRANT CREATE ON DATABASE redrepo_db to redadmin_role;
-- Create the admin user
CREATE USER redadmin_user WITH PASSWORD 'mypassredadmin_pass';
GRANT redadmin_role to redadmin_user;
-- External Step
--******* External Step: Install Red metadata using the redadmin_user *********
--
-- Note this set of statements assumes you have already created
-- the RED Metadata using the admin user.
-- Create the RED user role and give grants to the metadata objects
CREATE ROLE reduser_role NOLOGIN ADMIN postgres;
GRANT USAGE ON SCHEMA red TO reduser_role;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA red TO reduser_role;
GRANT EXECUTE ON ALL PROCEDURES IN SCHEMA red TO reduser_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA red TO reduser_role;
-- Create a RED user and grant the user role
CREATE USER red_user WITH PASSWORD 'red_pass';
GRANT reduser_role to red_user;
-- Create the RED Scheduler user role and give grants to the metadata objects
CREATE ROLE redscheduler_role NOLOGIN ADMIN postgres;
-- RED 'red' schema permissions
GRANT USAGE ON SCHEMA red TO redscheduler_role;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA red TO redscheduler_role;
GRANT EXECUTE ON ALL PROCEDURES IN SCHEMA red TO redscheduler_role;
-- We can potentially grant "INSERT, UPDATE, DELETE" to only the ws_wrk tables here, but we still need select on the rest
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA red TO redscheduler_role;
-- Grant redscheduler_role select access to the redadmin schema
GRANT USAGE ON SCHEMA redadmin TO redscheduler_role;
GRANT SELECT ON ALL TABLES IN SCHEMA redadmin TO redscheduler_role;
-- Create the RED scheduler user and grant the RED Scheduler role
CREATE USER redscheduler_user WITH PASSWORD 'redscheduler_pass';
GRANT redscheduler_role to redscheduler_user;
-- [OPTIONALLY] create the RED Scheduler Profile Role and User, or just use the redadmin_user for Profile maintenance
CREATE ROLE redschedulerprofile_role NOLOGIN ADMIN postgres;
-- RED 'red' schema permissions
GRANT USAGE ON SCHEMA red TO redschedulerprofile_role;
GRANT SELECT ON ALL TABLES IN SCHEMA red TO redschedulerprofile_role;
-- RED 'redadmin' schema permissions
GRANT USAGE ON SCHEMA redadmin TO redschedulerprofile_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA redadmin TO redschedulerprofile_role;
-- Create a RED profile admin user and grant the user role
CREATE USER redschedulerprofile_user WITH PASSWORD 'red_pass';
GRANT redschedulerprofile_role to redschedulerprofile_user;
-- External Step
--******* External Step: Install Azkaban Metadata using the redadmin_user *********
--******* -- This can be done via RedSchedulerInstaller.exe or azkaban-installer.jar
--******* -- Best way is to use azkaban-installer.jar upgrade-schema option which can installl the metadata separately to the Web Server
--******* -- If RedSchedulerInstaller.exe is used then the Web Server (and Executor) azkaban.local.properties should be updated after install to use the scheduler_user rather than redadmin_user
--
-- Create the Azkaban metadata user role and give grants to the Azkaban metadata objects in schema 'white'
CREATE ROLE azkabanmeta_role NOLOGIN ADMIN postgres;
-- Azkaban 'white' schema permissions
GRANT USAGE ON SCHEMA white TO azkabanmeta_role;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA white TO azkabanmeta_role;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA white TO azkabanmeta_role;
GRANT EXECUTE ON ALL PROCEDURES IN SCHEMA white TO azkabanmeta_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA white TO azkabanmeta_role;
-- Create a Azkaban meta user and grant the user role
CREATE USER azkabanmeta_user WITH PASSWORD 'azkabanmeta_pass';
GRANT azkabanmeta_role to azkabanmeta_user;
|