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.
Postgre SQL 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
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.
Database JDBC URL (for PostgreSQL)
Table where user info is stored
USERS table description:
Stores password of the user; will store default password during user creation
Firstname of user
Lastname of user
Email address of user
A. Add PostgreSQL Database as a User Store in miniOrange IdP
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 User Store section. Here, you will see the list of configured user stores.
Click on the dropdown near your configured database and select ‘Edit’:
This will open the edit page for your configured user store.
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 user store. The attributes to be selected are specified in the
attributes section of Database as a User Store. For each attribute (except for username
and password) that you want to import & map to a user in the miniOrange default user
store, 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 user store.
In the edit user store page, scroll down until you can see the option for adding
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:
Query for Firstname → SELECT '##FIRSTNAME##', FIRSTNAME FROM USERS WHERE USERNAME=?
Query for Lastname → SELECT '##LASTNAME##', LASTNAME FROM sUSERS WHERE USERNAME=?
Query for Email Address → SELECT '##EMAIL_ADDRESS##', EMAIL_ADDRESS FROM USERS WHERE
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:
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 this
query should be such that it checks all the columns in the table that define a distinct
For our example, we will compare the value of the USERNAME column against the relevant field in miniOrange:
SELECT * FROM USERS WHERE USERNAME='##USERNAME##'
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.
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:
In this example, the [ ‘##USERNAME##’, ‘##FIRSTNAME##’, ‘##LASTNAME##’ ] strings will be
replaced by the username, firstname, and lastname values of the newly created user in
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.
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:
UPDATE USERS SET FIRSTNAME='##FIRSTNAME##', LASTNAME='##LASTNAME##',
EMAIL_ADDRESS='##EMAIL_ADDRESS##' WHERE USERNAME='##USERNAME##'
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.
In this example, we will delete those users from the database whose username column value matches
the username field value in miniOrange:
DELETE FROM USERS WHERE USERNAME='##USERNAME##'
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
After saving you should see the following success message:
C. Configure User Provisioning and 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 user store
Navigate to the user store section. You will be shown a list of all your configured user
stores. Check if you can see the user store you set up in Part A.
Click on the dropdown for your database entry, and select ‘Make default’.
Once you have done that, you should see the following success message, and your database
will be marked as the default user store.
2. Navigate to the user provisioning section
From the dropdown, select ‘Database’
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.
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
For this example, all fields configured for our database are mapped to the Default User
The list of default attributes is shown when a database attribute is mapped to a Default
User Profile Attribute:
Map all the required attributes to their values in the miniOrange IdP. This is how the
mapping will look for our example.
4. Enable Provisioning features
Here, you have to choose which provisioning features you want to enable for your PostgreSQL
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:
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:
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:
And these are the users present in miniOrange:
We will now import the users from the database into miniOrange. Navigate to Provisioning
→ Import Users, and select ‘Database’ from the dropdown.
Click on ‘Import’. If the configuration was set up correctly, you should see the
You can verify if the import was successful by navigating to Users → Users List
Need Help? We are right here!
Contact miniOrange Support
Thanks for your inquiry.
If you dont hear from us within 24 hours, please feel free to send a follow up email to firstname.lastname@example.org
This privacy statement applies to miniorange websites describing how we handle the personal
When you visit any website, it may store or retrieve the information on your browser, mostly in the
form of the cookies. This information might be about you, your preferences or your device and is
mostly used to make the site work as you expect it to. The information does not directly identify
you, but it can give you a more personalized web experience.
Click on the category headings to check how we handle the cookies.
Strictly Necessary Cookies
Necessary cookies help make a website fully usable by enabling the basic functions like site
navigation, logging in, filling forms, etc. The cookies used for the functionality do not store any
personal identifiable information. However, some parts of the website will not work properly without
These cookies only collect aggregated information about the traffic of the website including -
visitors, sources, page clicks and views, etc. This allows us to know more about our most and least
popular pages along with users' interaction on the actionable elements and hence letting us improve
the performance of our website as well as our services.