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':
...
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 54.
1. Create a database, admin role and admin user in PostgreSQL
| Code Block |
|---|
|
-- 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:
| Code Block |
|---|
|
-- Create the admin user
CREATE USER redadmin_user WITH PASSWORD 'mypass';
GRANT redadmin_role to redadmin_user; |
...
It is important to make sure you have completed step 4 prior to running the following user grants.
| Code Block |
|---|
|
-- 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;
|
| Tip |
|---|
| For each developer user of RED create an individual PostgreSQL user for them. |
| Code Block |
|---|
|
-- Create a RED user and grant the user role
CREATE USER red_user WITH PASSWORD 'red_pass';
GRANT reduser_role to red_user; |
...
It is important to make sure you have completed step 4 prior to running the following user grants.
| Code Block |
|---|
|
-- 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
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; |
6. Create a Azkaban User role and user in PostgreSQL
| Code Block |
|---|
|
-- 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
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;
|
...