# Example: Setting Up Custom Roles for Production and Stage This article takes you through the steps of setting up an organization with custom roles for production (prod) and stage. Follow along, and by the end of the article 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: 1. Create databases 2. Create custom roles 3. Grant the appropriate privileges for these newly-created custom roles 4. 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](/reference/sql-syntax/ddl/create-database.md) to create the `prod_db` and `stage_db` databases: ```sql CREATE DATABASE prod_db; CREATE DATABASE stage_db; ``` Now list your databases to view them: ``` prod_db.public/# 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 has the privilege for creating roles and granting roles to other members of your organization: 
USE ROLE USERADMIN;
Use the [CREATE ROLE](/reference/sql-syntax/ddl/create-role.md) to create the `prod_manager` and `stage_manager` roles: ```sql 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](/reference/sql-syntax/command/grant-role.md) command to grant these custom roles to the `sysadmin`: ```sql GRANT ROLE prod_manager TO ROLE sysadmin; GRANT ROLE stage_manager TO ROLE sysadmin; ``` Now use [LIST ROLES](/reference/sql-syntax/command/list-roles.md) to view the new roles you just created: ``` /# 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 New Role With the new roles in place, it's time to grant these roles the usage privilege each role needs to work with its 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 [GRANT PRIVILEGES](/reference/sql-syntax/command/grant-privileges.md) to grant privileges to your custom roles. For this example, grant the `USAGE` privilege for each database to the matching role: ```sql 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, use[DESCRIBE ROLE](/reference/sql-syntax/command/describe-role.md) to view the new granted privileges for your `prod_manager` role: ``` /# 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 | ``` {% hint style="info" %} **Note** At any time you can[ view the full list of privileges](/overview/core-concepts/access-control.md#available-privileges). {% endhint %} ### 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 the responsibility of the `useradmin`, so let's switch to that role: 
USE ROLE useradmin;
Use [INVITE USER](/reference/sql-syntax/command/invite-user.md) to invite new users: ```sql INVITE USER 'prod_user@demo.org' WITH ('roles'=(prod_manager, stage_manager), 'default'=stage_manager); INVITE USER 'stage_user@demo.org' 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 **** to your organization and granting to them the `prod_manager` and `stage_manager` roles. For , you're granting only the `stage_manager` role. {% hint style="info" %} **Note** The `useradmin`can either grant additional roles to people (via the [GRANT ROLE](/reference/sql-syntax/command/grant-role.md) command) or revoke roles from people (via the [REVOKE ROLE](/reference/sql-syntax/command/revoke-role.md)command). {% endhint %} 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](/reference/sql-syntax/command/accept-invitation.md)), you see that your current role is the `stage_manager` role (because the `useradmin` set it as the default role for the prod user). Use [LIST ROLES](/reference/sql-syntax/command/list-roles.md) to verify the role you're using: ``` /# 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: ``` /# 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: ```sql USE ROLE prod_manager; ``` Now using the `prod_manager` role, if you list your databases again you can see that only the `prod_db` database displays: ``` /# 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](/reference/sql-syntax/command/accept-invitation.md)), you see that your current role is the `stage_manager` role (because the `useradmin` set it as the default role for the prod user). Use [LIST ROLES](/reference/sql-syntax/command/list-roles.md) to verify the role you're using: ``` /# 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: ``` /# 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: ``` /# USE ROLE prod_manager; ERROR: role prod_manager not granted to user ``` --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://docs.deltastream.io/how-do-i.../roles/example-setting-up-custom-roles-for-prod-and-stage.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.