Supported PostgreSQL versions

WhereScape RED and WhereScape Azkaban support PostgreSQL-compatible databases from PostgreSQL version 12 to version 16.

Metadata Schema Names

RED creates its metadata on schemas named ‘red’ and 'redadmin'. WhereScape Azkaban creates its metadata on a schema named ‘white’.

Warning

Database names for RED and WhereScape Azkaban metadata must be created in lowercase and not contain any special characters which would require surrounding in quotes (“”) in SQL queries. 

Users and Permissions

RED Users

Normal users and deployment users of RED require the following grants on the metadata database schema 'red':

  • SELECT, INSERT, UPDATE, DELETE, EXECUTE on the ‘red’ schema objects of the RED metadata database.

RED Scheduler User

The Scheduler User for RED requires the following grants on the metadata database: 

Schema: 'red':

  • SELECT, INSERT, UPDATE, DELETE, EXECUTE on the ‘red’ schema objects of the RED metadata database.

Schema: 'redadmin'

  • SELECT on the ‘redadmin’ schema objects of the RED metadata database.

RED Scheduler Profile Admin

The Scheduler Profile Admin for RED requires the following grants on the metadata database: 

Schema: 'red':

  • SELECT on the ‘red’ schema objects of the RED metadata database.

Schema: 'redadmin':

  • SELECT, INSERT, UPDATE, DELETE on the ‘redadmin’ schema objects of the RED metadata database.

RED Installation User

For metadata installation and upgrade the user requires:

  • CREATE on the database to create the ‘red’ and 'redadmin' schemas and the metadata objects

Note

Occasionally RED and Azkaban metadata may change between versions and in some cases require the dropping of existing objects during an object upgrade, therefore the user (or role) used for an upgrade should be the owner of the objects, or a superuser will need to be used.

Azkaban User

The Azkaban user for the Web Server and Executor Servers requires:

  • SELECT, INSERT, UPDATE, DELETE, EXECUTE on the ‘white’ schema objects of the Azkaban metadata database.

Azkaban Installation User

For metadata installation and upgrade the user requires:

  • CREATE on the database to create the ‘white’ schema and the Azkaban metadata objects.

Step by Step Database Setup Guide

Note: if you have already created a RED metadata repository then go directly to step 4.

1. Create a database, admin role and admin user in PostgreSQL

-- 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 an your RED admin user:

-- Create the admin user
CREATE USER redadmin_user WITH PASSWORD 'redadmin_pass';
GRANT redadmin_role to redadmin_user;

2. Create an ODBC DSN for the database

Tip

Note: it is not recommended to enter username and password into the DSN as this will allow anyone with access to this machine to login to the database without credentials. Additionally the PostgreSQL driver does not currently store the password securely on Windows.

3. Install the RED metadata repository

You must install the RED (and Azkaban) metadata repositories prior to assigning permissions to individual users as permission setting in PostgreSQL only grants permissions on the existing objects in the database or schema at the time the grant was given. 

For RED metadata install you have two options:

  • Red Setup Wizard - Windows GUI based installation wizard.
  • REDCLI  - Windows command line based install using 'RedCli.exe repository create' command 

For Azkaban metadata install you have two options:

4. Create a RED User role and user in PostgreSQL

It is important to make sure you have completed step 4 prior to running the following user grants.

Create RED User Role
-- 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;
-- You must run these GRANT's after connecting to the repo database, in psql run: '\c redrepo_db' to connect to the repo db
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;

Tip

For each developer user of RED create an individual PostgreSQL user for them.
Create a RED User
-- Create a RED user and grant the user role
CREATE USER red_user WITH PASSWORD 'red_pass';
GRANT reduser_role to red_user;

5. Create a RED Scheduler role and user in PostgreSQL

It is important to make sure you have completed step 4 prior to running the following user grants.

Create RED Scheduler Role and User 
-- Note this set of statements assumes you have already created
--  the RED Metadata using the admin 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
  -- You must run these GRANT's after connecting to the repo database, in psql run: '\c redrepo_db' to connect to the repo db
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
  -- You must run these GRANT's after connecting to the repo database, in psql run: '\c redrepo_db' to connect to the repo db
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;

6. Create a Azkaban User role and user in PostgreSQL

Create Azkaban Role and User 
-- Note this set of statements assumes you have already created
--  the Azkaban Metadata using the 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
  -- You must run these GRANT's after connecting to the repo database, in psql run: '\c redrepo_db' to connect to the repo db
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;

Examples

Full Metadata User Setup Example 
-- 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 'redadmin_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;
  -- You must run these GRANT's after connecting to the repo database, in psql run: '\c redrepo_db' to connect to the repo db
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
  -- You must run these GRANT's after connecting to the repo database, in psql run: '\c redrepo_db' to connect to the repo db
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
  -- You must run these GRANT's after connecting to the repo database, in psql run: '\c redrepo_db' to connect to the repo db
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
  -- You must run these GRANT's after connecting to the repo database, in psql run: '\c azkabanrepo_db' to connect to the db
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;


  • No labels