Hello there!

Need Help? We are right here!

Support Icon
miniOrange Email Support

Thanks for your Enquiry. Our team will soon reach out to you.

If you don't hear from us within 24 hours, please feel free to send a follow-up email to info@xecurify.com

Search Results:


Configure PostgreSQL Database User Provisioning

What is PostgreSQL Database User Provisioning?

PostgreSQL Database User provisioning is an automated solution for Identity access management (IAM) and maintenance of users across various Postgre SQL instances and downstream applications. Whenever a user is created (new hire), updated (role change), or deleted (terminated) from the database, the relevant data is synchronized or updated with the user store and related applications granting permissions and access according to the latest change. User sync and data sync are the main features of user provisioning. miniOrange user provisioning allows you to update & manage the user information and their access privileges in your database or applications when you perform creation, updation or deletion in your miniOrange service and thus enabling user provisioning enhances the security of your organization’s sensitive data.

PostgreSQL Database Deprovisioning involves removing the access of the users from all the resources & services such as Active Directory, Google, AWS or from all different integrated applications whenever a user is deleted from the database. miniOrange provides an automatic deprovisioning of the user accounts and access data when the user leaves the team or organization.

This step-to-step provisioning guide will help you to configure User Provisioning for PostgreSQL Database to automate provisioning in all the integrated applications.

User Provisioning between miniOrange and Different Databases

Configure PostgreSQL Provisioning: User Provisioning between different databases and miniOrange

There are three major parts to Configure user sync between your PostgreSQL database and miniOrange IdP. They are as follows:

  • Part A: Adding your PostgreSQL Database as a User Store in miniOrange IdP
  • Part B: Configuring Queries for User Provisioning
  • Part C: Configuring User Provisioning & Attribute Mapping

Consider the following schema for setting up PostgreSQL database user provisioning/deprovisioning. We have used this schema to illustrate how to configure the options for a simple database. You can use this example to configure your own PostgreSQL database as a user store in miniOrange.

Example Setup:

Database Type PostgreSQL v9.6
Database Name APPSDB
Database JDBC URL (for PostgreSQL) jdbc:postgresql:@serverdomain:5432/APPSDB
Table where user info is stored USERS

USERS table description:

Column Type Description
USERNAME VARCHAR Username column
PASSWORD VARCHAR Stores password of the user; will store default password during user creation
FIRSTNAME VARCHAR Firstname of user
LASTNAME VARCHAR Lastname of user
EMAIL_ADDRESS VARCHAR Email address of user

A. Add PostgreSQL Database as External Directory in miniOrange IdP

  • Refer to steps 1-3 from this document to add your PostgreSQL Database.

B. Configure User Provisioning Queries for PostgreSQL Database

There are two directions in which user provisioning can be configured: Inbound (i.e. from your database into miniOrange) and Outbound (from miniOrange into your database).

Inbound provisioning deals with user import, whereas Outbound deals with user creation/deletion/update. In both cases, attribute mapping is necessary. Attribute mapping between database columns and miniOrange IdP attributes is done via a special notation. Column values are inferred by the miniOrange IdP using this notation.

  • To configure user provisioning for your PostgreSQL database, you will first have to navigate to the External Directory section. Here, you will see the list of configured External Directories.
  • Configure PostgreSQL Database Provisioning: Edit External Directory dropdown

  • Click on the dropdown near your configured database and select Edit:
  • Configure PostgreSQL Database Provisioning:  Edit External Directory page

  • This will open the edit page for your configured External Directory.
  • Configure PostgreSQL Database Provisioning: Query Strings for PostgreSQL database user provisioning

  • Now you are ready to configure all the required provisioning features.

1. Import Users

  • This is an example of inbound provisioning. Users are imported from the database into the miniOrange default External Directory. The attributes to be selected are specified in the attributes section of Database as a External Directory. For each attribute (except for username and password) that you want to import & map to a user in the miniOrange default External Directory, you must specify a SELECT query.
  • You do not need to specify a SELECT query for username and password column values; they are imported automatically using the column names used when setting up your database as a External Directory.
  • In the edit user store page, scroll down until you can see the option for adding database attributes.
  • Configure PostgreSQL Database Provisioning: Query Strings for inbound user provisioning

  • Add as many input fields as required. For this example, we have 3 database columns - FIRSTNAME, LASTNAME, and EMAIL_ADDRESS - excluding the USERNAME and PASSWORD columns. The queries for these columns will be as follows:
  • The [ ‘##COLUMN_NAME##’, COLUMN_NAME ] notation in the query tells miniOrange that this field should be populated with the value of the column from the database, based on the unique username identifier.
  • Your configuration should look like this after you’re done adding the queries:
  • Configure PostgreSQL Database Provisioning:  Query Strings filled

2. Check Users

  • This query is used to check if a particular user already exists in the database. A user that already exists in the database will not be created again. The structure of their query should be such that it checks all the columns in the table that define a distinct user.
  • Configure PostgreSQL Database Provisioning: Check Users query for database user provisioning
  • For our example, we will compare the value of the USERNAME column against the relevant field in miniOrange:
    Configure PostgreSQL Database Provisioning: Filled-in Check Users query

3. Create User

  • These queries create a user in the database whenever a user is created in miniOrange. The query should contain the appropriate column names. In this example, the password is set to a default string (that must be changed later).
  • These queries use the same notation to specify the attributes that must be mapped from miniOrange IdP to the database. This is an example of outbound provisioning.
  • Configure PostgreSQL Database Provisioning: Create Users query for provisioning

  • In our example, we will sync the username, firstname, lastname, and email address of the newly created user from miniOrange to the database. We will pass a default password string (‘defaultPassword123#’) to the database:
    Filled-in Create Users query

  • In this example, the [ ‘##USERNAME##’, ‘##FIRSTNAME##’, ‘##LASTNAME##’ ] strings will be replaced by the username, firstname, and lastname values of the newly created user in miniOrange.

4. Update User

  • These queries update a user in the database whenever the relevant user details are changed in miniOrange. This is an example of outbound provisioning.
  • Update Users query for database provisioning

  • In this example, all the fields for a particular user are updated in the database when an update is triggered in miniOrange. The username value is immutable in miniOrange, hence it has been used in the WHERE clause:
    Configure PostgreSQL Database Provisioning: Filled-in Update Users query for PostgreSQL database user provisioning

  • In this example, only those users’ details are updated, whose username is the same in both miniOrange and the database.

5. Delete Users

  • These queries delete a user from the database whenever a user is deleted in miniOrange. This is an example of outbound provisioning.
  • Configure PostgreSQL Database Provisioning: DELETE Users query for outbound provisioning

  • In this example, we will delete those users from the database whose username column value matches the username field value in miniOrange:
    Configure PostgreSQL Database Provisioning: Filled-in Delete Users query for outbound database provisioning

  • In this example, only those users whose username matches exactly in both - the database as well as in miniOrange - are deleted from the database.

Once you have filled in the required values, scroll down and click on Save to save your current configuration.

C. Configure User Provisioning & Attribute Mapping

We have successfully set up our database as a user store and entered all the queries for inbound as well as outbound provisioning. Now, we must configure the user provisioning section to activate user provisioning for our database.

1. Set PostgreSQL Database as the default External Directory

  • Navigate to the External Directory section. You will be shown a list of all your configured External Directories. Check if you can see the External Directory you set up in Part A.
  • Configure PostgreSQL Database Provisioning: Check External Directory section you set up for PostgreSQL database user provisioning

  • Click on the dropdown for your database entry, and select Make default.
  • Configure Postgre SQL Database Provisioning: Set External Directory as default

2. Navigate to the user provisioning section.

  • Select Provisioning in the left navigation.
  • From the dropdown, select Database.
  • Configure PostgreSQL Database Provisioning: Attribute Mapping setup

  • You should be able to see the database attributes that we have configured in Part B. These database attributes are as yet unmapped, so the values of the ‘miniOrange Attributes’ column will be blank.
  • Configure PostgreSQL Database Provisioning: Configured query attributes

3. Map the database attributes to their respective field names in miniOrange

  • The PostgreSQL database attributes can be mapped to 3 different types of attributes:
    • Default User Profile Attributes
    • Custom User Profile Attributes
    • Custom Attributes
  • For this example, all fields configured for our database are mapped to the Default User Profile Attributes.
  • Configure PostgreSQL Database Provisioning: Default User Profile Attributes mapping

  • The list of default attributes is shown when a database attribute is mapped to a Default User Profile Attribute:
  • Configure PostgreSQL Database Provisioning: List of Default User Profile Attributes

  • Map all the required attributes to their values in the miniOrange IdP. This is how the mapping will look for our example.
  • Configure PostgreSQL Database Provisioning: Default User Profile Attributes mapping list

4. Enable Provisioning features

  • Here, you have to choose which provisioning features you want to enable for your PostgreSQL database.
  • You can choose some or all of these features, as per your requirement. For this example, we will choose the basic inbound & outbound provisioning features:
  • Configure PostgreSQL Database User Provisioning: Basic inbound & outbound provisioning features

5. Save the database configuration

  • Once you have mapped your database attributes to the miniOrange IdP attributes & enabled your desired features, you can go ahead and save this configuration. If everything is properly configured, you should see the following success message:
  • Configure PostgreSQL Database Provisioning: Save database configuration success message

6. Test configuration

  • You can test your configuration by performing one of the actions for which you have enabled the provisioning feature. In this example, we will test it by importing any of your PostgreSQL database users into miniOrange.
  • Initially, these are the users present in the database:
  • Configure PostgreSQL Database Provisioning: PostgreSQL Database users list after user provisioning settings

  • And these are the users present in miniOrange:
  • Configure PostgreSQL Database Provisioning: miniOrange initial users list

  • We will now import the users from the PostgreSQL DB into miniOrange. Navigate to Provisioning → Import Users, and select Database from the dropdown.
  • Configure PostgreSQL Database Provisioning: Import Users from PostgreSQL database and navigate to user provisioning

  • Click on Import. If the configuration was set up correctly, you should see the following message: Import Users successfully
  • You can verify if the import was successful by navigating to Users → Users List

View Provisioning Reports

How to access Provisioning Reports?

  • Navigate to the Reports in the left-hand navigation pane and select Provisioning Report.
  • Provisioning Report

  • Filter the reports by specifying Enduser Identifier and Application Name criteria. Additionally, choose the desired timespan for the reports. Once done, click on the Search.
  • Search Provisioning Report

  • Alternatively, you can directly click on Search to retrieve all provisioning reports based on time without applying any specific filters.

Want To Schedule A Demo?

Request a Demo

Our Other Identity & Access Management Products