Single Sign-On (SSO) For Your Application Using PostgreSQL Database

PostgreSQL database as an authentication source to SSO


miniOrange provides ready to use Single Sign-On solution using PostgreSQL Database.This solution ensures that you are ready to roll out secure access to any of your application using PostgreSQL Credentials within minutes.

Where is SSO (Single Sign-On) with PostgreSQL Database applicable?

Various User Stores i.e. CRM/HRM/CMS/LMS where users are stored, do not support Single Sign-On or any other authentication protocol inherently.Here miniOrange PostgreSQL SSO solution comes into the picture and provides different SSO services to these type of applications.

Step 1: Setup PostgreSQL Database as Authentication Source

  • Login into your miniOrange Admin console and navigate to User Stores >> Add User Store.

  • Add user store
  • Select the Database Tab as shown below.

  • Setup PostgreSQL database as user store
  • Enter the Database Identifier which is basically the Database name you are adding up, it can be any name relevant to the User-store.
  • Select the Database type as PostGres SQL.Following table shows the default values and syntax of the fields in the configuration page.
  • Note :-The default port number for PostreGreSQL is 5432.


    Database Type PostgreSQL
    Connection URL
    jdbc:postgresql://hostname:port/database-name
    Default Port Number 5432
  • Database Host URL is basically the Connection URL.
    1. The hostname in the Connection URL can be either localhost or any other remote host.
    2. The port is by default 5432 for PostgreSQL Database type.
    3. The database-name is basically the Database which stores the users for authentication purpose.
  • Enter the Admin Username and Admin Password.
    1. In case of localhost, the Admin Username is basically 'postgres', and in case of any other remote host please contact your administrator.
    2. Enter the Admin Password for the corresponding Admin Username.
  • The User Table is basically the table name of the Database where users are stored for authentication.
  • Enter the name of the corresponding Username column and Password column.
    1. Username column is the column which stores the username of the users to be authenticated.
    2. Password column is the column which stores the corresponding passwords of the users.
  • Select the Hashing type used for hashing the password. We support the below Hashing types:
    1. SHA512
    2. SHA1
    3. MD5
    4. PHPASS
    5. BCRYPT
    6. NONE(In the case when the users to be authenticated, do not have hashed password.)
  • Advanced Settings:-
    1. User-activated query,in which you can enter the query for checking if the User is Active or not.
      Example:-
    2. Enable for EndUsers,enable this option if you want your endusers to log in to their corresponding End-User Dashboard using IDP Credentials.
    3. Authenticate via miniOrange,if you enable this option, then the users present in an external database will be authenticated directly through the miniOrange IdP, without being created in the miniOrange IdP. It is helpful in case when the database from where the authentication is being performed contains some private or sensitive information about users.
    4. Send Configured Attributes,if you enable this option, then only the attributes configured below will be sent in attributes at the time of login.
    5. Attribute Mapping allows us to add extra attributes which we can send from Database to any configured application.Click on the + symbol to map an attribute.

      Attribute mapping for Postgre SQL

      1. Attribute Keys are actually mapped with the corresponding Attribute Values.
        For Example:- Above image utters that username is mapped with user_login and email is mapped with user_email.
      2. At the time of Test Attribute Mapping, username will be the Attribute Key and user_login (which is basically fetched from the column user_login in the User Table) will be the Attribute Value.Similar explanation for email as well.
      3. These mapped attributes can be fetched using the Test Attribute Mapping action ,which is discussed later in this document.
  • Click on Save to save your configuration.

Step 2: Test PostgreSQL Database Connnection:

  • In order to check if connection is established with the Postgre Database or not, Test Connection is required to be done.Kindly navigate to Select >> Test Connection.

    Test connection for PostgreSQL Database
  • Enter the credentials of the user, stored in the User Table of the corresponding Database for testing if the connection is correctly established.Click on Test to check if connection was successful or not.

    Database Authentication View Configured IDPs
    1. If Test Connection is successful, you are good to go!.The success message for the Test Connection will look like :--

      Test Database Configuration
    2. If Test Connection is not successful,kindly check your configuration once again or contact your administrator.Another probable reason can be that, you are entering wrong credentials for Test Connection.

Step 3: Test Attribute Mapping:

  • Navigate to Select >> Edit in the corresponding Database User Store for which you want to perform Test Attribute Mapping action.On the configuration page, enable Send Configured Attributes.If you enable this option, then only the attributes configured below will be sent in attributes at the time of login.

    Send configured attributes for PostgreSQL database

  • In order to check if the attributes you have added are mapped correctly with reference to the Database or not, Test Attribute Mapping is required to be done.Kindly navigate to Select >> Test Attribute Mapping.

    Test Postgre SQL database attribute mapping
  • Enter the credentials of the user stored in the User Table of the corresponding Database and,click on Test to check if the attributes are fetched properly or not.

    Click on Test to check attribute mapping for database
    • If Test Attribute Mapping is successful, you will see a table which looks like :--

      attribute mapping results for PostgreSQL
      • Suggested Attributes that you can map are the attributes which were added by you during configuration.
      • Configured Attributes Fetched for the User Account are the attributes which are basically the attributes corresponding to the user fetched from the User Table of the Database which is being used as an authentication source.
      • If Test Attribute Mapping is not successful,it means that wrong credentials were entered or the user is not present in the User Table.You should get result as:-

        Unsuccessful attribute mapping for PostgreSQL Database

Step 4: User Provisioning

  • Navigate to Users >> User Provisioning settings.
  • user provisioning
  • Select the Database from the drop down menu.
  • Check the provisioning features.
  • Provisioning users from PostgreSQL database
  • To import the users from Database, go to the User Provisioning, Click on the Import Users button.
  • Select the Database from the drop down menu and save the configuration.
  • Importing users from PostgreSQL database
  • Now go to the Users >> User List and you will find the all the users imported from Database.
  • Database Authentication user provisioning user list

Step 5: Configure Your application in miniOrange

  • With us, You can configure application according to the the protocol it supports like SAML, OAuth, JWT, Ws-fed etc.
  • For example lets say the application supports SAML. Then go with the configurations Shown in SAML Application Tab.
  • Login to miniOrange Admin Console.
  • Go to Apps >> Manage Apps Click Configure Apps button.
  • azure ad saml Apps
  • Then click on SAML tab. Search for Custom App.
  • azure ad saml Apps azure ad saml Apps
  • Get the ACS URL and SP Entity ID from your application.
  • Enter the following values OR click on Import SP Metadata:
  • Service Provider Name Choose appropriate name according to your choice
    SP Entity ID or Issuer Your Application Entity ID
    ACS URL X.509 Certificate (optional) Your Application Assertion Consumer Service URL
    NameID Format  Select urn:oasis:names:tc:SAML:1.1:nameid-format:emailAddress
    Response Signed Unchecked
    Assertion Signed Checked
    Encrypted Assertion Unchecked
    Group policy Default
    Login Method
  • Click on Save to configure Your applcation.
  • Now to get the IDP metadata of the app configured, Go to apps >> your_app >> select >> metadata tab.
  • azure ad saml Apps
  • Click on the Show Metadata details in the Information required to Authenticate via External IDPs section. Download the metadata XML file by clicking on Download Metadata button or copy the Metadata URL link.
  • azure ad saml Apps
  • You need to Upload this metadata in your application.
  • To configure OAuth application in miniOrange, login to miniOrange Admin Console.
  • Go to Apps >> Manage Apps Click Configure Apps button.
  • configure app
  • Select on OAuth/OIDC tab. Click on Open ID Connect App .
  • You can add any OAuth Client app here to enable miniOrange as OAuth Server. Few popular OAuth client apps for single sign-on are Salesforce, WordPress, Joomla, Atlassian, etc.
  • OAuth openIDConnect app OAth AddopenIDConnect app
  • Enter following Values:
  • Client Name Add appropriate Name
    Redirect URL Get the Redirect-URL from your OAuth Client
    Descrption Add if required
    Group Name Default
    Policy Name
    Login Method
  • Click on Save
  • Now to provide the required data to OAuth client go to the app configured i.e apps >> your_app >> select >> edit.
  • OAuth editOpenidConnect app OAuth openidConnectData

    Note: Choose the Authorization Endpoint according to the identity source you configure.

  • When you want to use you want to use miniOrange as OAuth identity server use this endpoint: https://{mycompany.domainname.com}/moas/idp/openidsso
  • If you are configuring any Identity Provider in Identity Providers Menu and not using miniOrange as IDP use this endpoint: https://{mycompany.domainname.com}/broker/login/oauth{customerid}
  • Select External/JWT App.
  • External JWT app
  • Configure the name for your application and configure Redirect-URL which tells where to send JWT response. Redirect-URL should be an endpoint on your application where you want to achieve SSO.
  • JWT app configure

    In case you are setting up SSO with Mobile Applications where you can't create an endpoint for Redirect or Callback URL, use below URL.

    https://login.xecurify.com/moas/jwt/mobile
  • Click Save
  • To get the SSO link for your application, Go to Apps >> your_app >> select >> Edit.
  • JWT app SSO
  • Then, copy the Single Sign On Url and verify SSO setup by browsing that url.
  • JWT app SSO
  • On successful authentication, you will be redirected to configured Redirect or Callback URL with JWT token
  • You will need to download a certificate from App > Manage Apps, and click Certificate link against your configured application. This certificate will be used for signature validation of JWT response.
  • JWT app SSO certificate

Related Pages:-
What is Single Sign-On(SSO)?
SSO services for SAML/OAuth Applications?