Example: Setting Up Custom Roles for Production and Stage

This tutorial takes you through the steps of setting up an organization with custom roles for production (prod) and stage. By the end of the tutorial you should have:

• 2 databases – prod_db and stage_db

• 2 custom roles – prod_manager and stage_manager

  • prod_manager should have have full access to the prod_db database

  • stage_manager should have have full access to the stage_db database

• 1 admin user

  • the admin user is the first user, granted the ORGADMIN role and inheriting all other admin roles

• 2 non-admin users

  • prod user is granted prod_manager and stage_manager

  • stage user is granted only stage_manager

Logging in as Admin User

When you're the first to log into an organization, by default you log in as the sysadmin built-in role. But you have access to all of the built-in admin roles, including orgadmin.

In this example, as the admin user, you have 4 small tasks:

• Create databases

• Create custom roles

• Grant the appropriate privileges for these new roles

• Invite non-admin users and assign them these custom roles

1. Creating Databases

When you create objects in an organization, use either the sysadmin role or a role that's lower in the hierarchy. Let's switch to the sysadmin role:

USE ROLE sysadmin;

Use the CREATE DATABASE to create the prod_db and stage_db databases:

CREATE DATABASE prod_db;
CREATE DATABASE stage_db;

Now list your databases to view them:

prod_db.public/<no-store># LIST DATABASES;
    Name   | Default |  Owner   |      Created at      |      Updated at
-----------+---------+----------+----------------------+-----------------------
  stage_db |         | sysadmin | 2023-12-13T22:28:04Z | 2023-12-13T22:28:04Z
  prod_db  | ✓       | sysadmin | 2023-12-13T22:27:57Z | 2023-12-13T22:27:57Z

2. Creating Custom Roles

Switch to the useradmin role. This role is responsible for creating roles and granting roles to other members of your organization:

USE ROLE USERADMIN;

Use the CREATE ROLE to create the prod_manager and stage_manager roles:

CREATE ROLE prod_manager;
CREATE ROLE stage_manager;

When you first create these roles, they are standalone roles detached from any other roles. However, you should grant these custom roles to the sysadmin role . When you do this they become lower members of the sysadmin role's hierarchy.

Use the GRANT ROLE command to grant these custom roles to the sysadmin:

GRANT ROLE prod_manager TO ROLE sysadmin;
GRANT ROLE stage_manager TO ROLE sysadmin;

Now use LIST ROLES to view the new roles you just created:

<no-db>/<no-store># LIST ROLES;
      Name      | Current |      Created at
----------------+---------+-----------------------
  public        |         | 2023-12-13T22:16:12Z
  securityadmin |         | 2023-12-13T22:16:12Z
  useradmin     | ✓       | 2023-12-13T22:16:12Z
  prod_manager  |         | 2023-12-13T22:32:41Z
  orgadmin      |         | 2023-12-13T22:16:12Z
  sysadmin      |         | 2023-12-13T22:16:12Z
  stage_manager |         | 2023-12-13T22:32:44Z

3. Granting the Appropriate Privileges for Each Role

With the new roles in place, it's time to grant these roles the usage privilege to work with their respective databases. As the sysadmin role is the owner of these databases, you must use that role to grant the usage privilege to the custom roles.

Start by switching to the sysadmin role:

USE ROLE sysadmin;

Now use the GRANT PRIVILEGES to grant privileges to your custom roles. For this example, grant the USAGE privilege for each database to the matching role:

GRANT USAGE ON DATABASE prod_db TO ROLE prod_manager;
GRANT USAGE ON DATABASE stage_db TO ROLE stage_manager;

To see details and make sure you're on the right track, useDESCRIBE ROLE to view the new granted privileges for your prod_manager role:

<no-db>/<no-store># DESCRIBE ROLE prod_manager;
      Name     |      Created at
---------------+-----------------------
  prod_manager | 2023-12-13T22:32:41Z

Granted Roles
   Name
----------
  public

Granted Privileges
  Type  |  Target  | ID/Name | Grant option
--------+----------+---------+---------------
  Usage | Database | prod_db |

Note At any time you can view the full list of privileges.

4. Inviting Users and Assigning them Custom Roles

Great -- you've set up your databases and the custom roles to use them. Now it's time to invite users and assign them to a set of roles.

Inviting users is one of the useradmin responsibilities, so let's switch to that role:

USE ROLE useradmin;

Use INVITE USER to invite new users:

INVITE USER '[e-mail]' WITH ('roles'=(prod_manager, stage_manager), 'default'=stage_manager);
INVITE USER '[e-mail]' WITH ('roles'=(stage_manager), 'default'=stage_manager);

DeltaStream uses a person's email address as their unique identifier. For example, in the above code block you're inviting [e-mail] to your organization and granting the prod_manager and stage_manager roles to them. For [e-mail], you're only granting the stage_manager role.

Note The useradmincan either grant additional roles to people (via the GRANT ROLE command) or or revoke roles from people (via the REVOKE ROLEcommand).

Now gain visibility into how interacting with the organization looks from each user's perspective.

Interacting with the Organization as Prod User

As the prod user, after accepting the invitation into this organization (see ACCEPT INVITATION), you see that your current role is the stage_manager role, as the useradmin set it as the default role for the prod user. Use LIST ROLES to verify which role you're using:

<no-db>/<no-store># LIST ROLES;
      Name      | Current |      Created at       
----------------+---------+-----------------------
  public        |         | 2023-12-13T22:16:12Z  
  securityadmin |         | 2023-12-13T22:16:12Z  
  useradmin     |         | 2023-12-13T22:16:12Z  
  prod_manager  |         | 2023-12-13T22:32:41Z  
  orgadmin      |         | 2023-12-13T22:16:12Z  
  sysadmin      |         | 2023-12-13T22:16:12Z  
  stage_manager | ✓       | 2023-12-13T22:32:44Z  

If you list the databases you only see stage_db. That's because stage_db is the only database available to the stage_manager role you are assuming:

<no-db>/<no-store># LIST DATABASES;
    Name   | Default |  Owner   |      Created at      |      Updated at       
-----------+---------+----------+----------------------+-----------------------
  stage_db |         | sysadmin | 2023-12-13T22:28:04Z | 2023-12-13T22:28:04Z  

Since the prod user has access to both the stage_manager and prod_manager roles, you can switch to the prod_manager role:

USE ROLE prod_manager;

Using the prod_manager role, if you list your databases again you can see that only the prod_db database displays:

<no-db>/<no-store># list DATABASES;
   Name   | Default |  Owner   |      Created at      |      Updated at       
----------+---------+----------+----------------------+-----------------------
  prod_db |         | sysadmin | 2023-12-13T22:27:57Z | 2023-12-13T22:27:57Z  

Interacting with the Organization as Stage User

As the stage user, after you accept the invitation into this organization (see ACCEPT INVITATION), you see that your current role is the stage_manager role, as the useradmin set it as the default role for the prod user. Use LIST ROLES to verify which role you're using:

<no-db>/<no-store># LIST ROLES;
      Name      | Current |      Created at       
----------------+---------+-----------------------
  public        |         | 2023-12-13T22:16:12Z  
  securityadmin |         | 2023-12-13T22:16:12Z  
  useradmin     |         | 2023-12-13T22:16:12Z  
  prod_manager  |         | 2023-12-13T22:32:41Z  
  orgadmin      |         | 2023-12-13T22:16:12Z  
  sysadmin      |         | 2023-12-13T22:16:12Z  
  stage_manager | ✓       | 2023-12-13T22:32:44Z  

If you list the databases you only see stage_db. That's because stage_db is the only database available to the stage_manager role you're assuming:

<no-db>/<no-store># LIST DATABASES;
    Name   | Default |  Owner   |      Created at      |      Updated at       
-----------+---------+----------+----------------------+-----------------------
  stage_db |         | sysadmin | 2023-12-13T22:28:04Z | 2023-12-13T22:28:04Z  

Notably, the stage_manager doesn't have access to the prod_manager role. The system displays an error it you try to use the prod_manager role:

<no-db>/<no-store># USE ROLE prod_manager;
ERROR: role prod_manager not granted to user