Versions Compared

Old Version 6

changes.mady.by.user eric revollo

Saved on

compared with

New Version 7

changes.mady.by.user eric revollo

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

This section assumes you have already created the RED Metadata using the admin user.

  1. Connect to the repo dbDB
  2. Create the RED user role and provide grants. 
    Code Block
    languagesql
    CREATE ROLE reduser_role NOLOGIN ADMIN postgres;
    Give grants to the metadata objects
    Code Block
    languagesql
    
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
    titleTip
    For each developer user of RED create an individual PostgreSQL user for them.
  3. Create a RED user and grant the user role.
    Code Block
    languagesql
    CREATE USER red_user WITH PASSWORD 'red_pass';
GRANT reduser_role to red_user; 

...

Add a RED Scheduler

...

Role and

...

User

This section assumes you have already created the RED Metadata using the admin user.

Create the RED Scheduler user role and provide grants.

Code Block
languagesql
CREATE ROLE redscheduler_role NOLOGIN ADMIN postgres;
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.

Code Block
languagesql
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA red TO redscheduler_role;

Grant redscheduler_role select access to the redadmin schema

Code Block
languagesql
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

Code Block
languagesql
CREATE USER redscheduler_user WITH PASSWORD 'redscheduler_pass';
GRANT redscheduler_role to redscheduler_user;





Code Block
languagesql
titleCreate RED Scheduler Role and User
collapsetrue

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

Code Block
languagesql
titleCreate RED Scheduler Role and User
collapsetrue
-- 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;

...