Supported PostgreSQL versions
WhereScape RED and WhereScape Azkaban support PostgreSQL-compatible databases from PostgreSQL version 12 to version 16.
Database
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. RED creates its metadata on a schema named ‘red’. WhereScape Azkaban creates its metadata on a schema named ‘white’.
Users and permissions
RED Users
Normal users of RED require the following grants on the metadata database:
SELECT, INSERT, UPDATE, DELETE, EXECUTE on the ‘red’ schema of the RED metadata database.
RED Installation User
For metadata installation and upgrade the user requires:
CREATE on the database to create the ‘red’ schema and the RED metadata objects
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 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.
Example RED Database setup steps
Note: if you have already created a RED metadata repository then go directly to step 5.
1. Create a database and admin role 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;
2. Create an admin user in PostgreSQL
-- Create the admin user CREATE USER redadmin_user WITH PASSWORD 'mypass'; GRANT redadmin_role to redadmin_user;
3. Create an ODBC DSN for the database
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.
4. 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:
- RedSchedulerInstaller.exe - Windows GUI based installation wizard
- azkaban-installer.jar - Java based command line installation for both Windows and Linux
5. 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.
-- 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 reduser_user WITH PASSWORD 'mypass'; GRANT reduser_role to reduser_user;