Don’ts in JDBC Primary User Store

Never try this in WSO2 APIM 3.2.0

[Source: https://i.gifer.com/Bttr.gif]

Users and Systems: The never-ending bond

Nowadays, the majority of the systems are created upon the interactions between end-users and the system. Some of them might require user logins and some might not. In most cases, especially when it comes to systems with sensitive user details and internal systems, the user details will be stored to handle the authorization and authentication activities. Thus maintaining and handling the user store is a crucial task.

WSO2 APIM Primary User Store: On focus

[Source: https://miro.medium.com/max/700/1*Ez-ZnHQNuKk4e_fHMKQSwg.jpeg + edited]

In WSO2 APIM-3.2.0, the user store is used to save user details and the user store can be a primary user store or a secondary user store. As a rule of thumb, you can have only one primary user store and zero or many secondary user stores. The user store can be a database, Lightweight Directory Access Protocol(LDAP), or Active Directory, depending on the organization’s choice. In this blog post, we consider only the JDBC primary user store. The rest of the blog will traverse you through the configurations of the WSO2 APIM JDBC primary use store and how a small misconfigured element can kill your day.

Configure the JDBC Primary User store: The suffocation begins

[Source: https://static.wikia.nocookie.net/spongebob/images/e/e0/379.png/revision/latest?cb=20130327121057]

By default, the WSO2 APIM product is shipped with an internal H2 database that holds all the details of the system. Therefore, the default primary user store is configured as the JDBC user store, and the existing H2 database is used for this purpose. So the H2 JDBC primary user store is used by the authorization manager to fetch details for user authorization and by the user store manager to store the users and roles details.

Step 1: Configure the Database

You can configure the database in the following way which is the default recommended method.

Approach: Default Approach
- WSO2AM_DB
- WSO2SHARED_DB

To configure an external database as the JDBC primary user store in WSO2 APIM-3.2.0, I have used MySQL-8.0.22 in an all-in-one setup. You can refer to the official documentation on setting up the databases based on the database you have selected [1].

Next, we need to create the associations between the database and APIM via the <APIM-Home>/repository/conf/deployment.toml file.

Step 2: Configure the primary user store

Once the database is ready, next you need to configure the primary user store by adding the following configurations in the <APIM-Home>/repository/conf/deployment.toml file. Please note that I have changed the default DomainName to “test”.

NOTE:
I have shared the complete deployment.toml that includes the database and user store configurations.
deployment.toml

Step 3: Change the primary user store domain name

If you don’t change the primary user store’s domain name via the DomainName attribute in the deployment.toml file, the default name of the primary user store will be set as PRIMARY. In case if you change the primary user store name, it will replace the default PRIMARY domain name and will be applicable in all the references.

[user_store.properties]
DomainName = "test"

All the above-stated steps are configured in a new APIM pack and we are all set to start the APIM servers and logging in to the APIM portals for the very first time. Let’s see what are the surprises lined up for us!

Primary user store DomainName change: The real chaos

[Source: https://s3.amazonaws.com/lowres.cartoonstock.com/computers-desktop-computers-computer_problems-fires-computer_support-lfin581_low.jpg]

Let’s start the APIM server and upon a successful server startup, we’ll try to login into the carbon management console. Next, try to list the users from the carbon management console via Main → Identity → Users and Roles → List → Users path. In the “Select Domain” dropdown list, you’ll have the following options.

* ALL-USER-STORE-DOMAINS* TEST

If you select ALL-USER-STORE-DOMAINS, click on the Search Users button setting the wild card character(*) in the Enter Username Pattern text box, you will get the following error message. Even if you try to add a new user you will be able to see the same error as listing the users is a part of the saving process.

Fig 1: Error message box in the carbon management console when listing the users

The corresponding error trace in the carbon log is as follows.

Fig 2: The error trace in the carbon log when listing the users with ALL-USER-STORE-DOMAINS filter where DomainName=”test”

Next, try to select the changed domain name, TEST from the dropdown list, and filter the users. Now, you can see the users with the domain name specified as TEST/<username>.

Fig 3: Listing the users with the “TEST” domain name

What is the problem?

[Source: https://i.pinimg.com/originals/00/67/46/0067467e56db97e5ff2f6e2d3d8d161d.gif]

In a fresh deployment when you configure the JDBC primary user store and give a domain name via deployment.toml file, you are changing the default domain name, PRIMARY. When you run the APIM server for the very first time with a custom primary store domain name, that will be stored in the database in many references. Even if you completely delete the DomainName from the deployment.toml file to revert to the default domain name, PRIMARY, and re-start the server you will not be able to overcome the listing the user error. After this change, you will not even be able to login to any of the portals.

Let’s analyze the following 2 identified issues caused due to the changed primary user store domain name, in-depth.

  1. Unable to list the users in carbon management console with ALL-USER-STORE-DOMAINS filter
  2. Unable to log in to any of the portals after reverting the primary user store domain name to PRIMARY

Unable to list the users in carbon management console with ALL-USER-STORE-DOMAINS filter

Since there could be only one primary user store for the system, the primary user store name has been hardcoded as PRIMARY[2]. Therefore, many of the methods such as saving the user details, filtering users will not work as expected for the domain names other than PRIMARY. Since there is only one primary user store there is no need to uniquely identify it. Therefore, changing the domain name has no added value in this case.

Fig 4: Error message box in the carbon management console when listing the users

Unable to log in to any of the portals after reverting the primary user store domain name to PRIMARY

Even after completely deleting the DomainName configuration from the deployment.toml file you won’t be able to login into any of the APIM portals. This is because when you completely delete the DomainName from deployment.toml file and start the server, in UM_DOMAIN table a new entry for the “PRIMARY” domain name will be stored. But the existing details such as claim mappings, user mappings will still have the old TEST domain reference.

Furthermore, if you have already created the APIs, applications, subscribed to the APIs, generate the keys and tokens, invoked the API calls then there will be more data entries in WSO2SHARED_DB and WSO2AM_DB. In case if you have a multi-tenancy environment the number of these entries would be even higher. Due to this factor, the user will not be able to login to the system and will face carbon log errors as well.

Fig 5: Unable to log in to the system when the primary user store domain name is changed to PRIMARY

Solve the problem: Hands-on database

[Source: https://s3.amazonaws.com/lowres.cartoonstock.com/computers-computer_user-business_computer-nerds-computer_nerds-computer_geeks-tmcn188_low.jpg]

We have identified the problem and its causes. Next, we need to fix this issue. Before we dive into the solution path, let’s first analyze the following cases that define the level of effort that is required to build the solution.

Case 1: The default PRIMARY domain name and the API details don't exist in the DB Case 2: The default PRIMARY domain exists and the API details don't exist in the DBCase 3: The default PRIMARY domain name and the API details exist in the DB

In all 3 cases as the first step, we need to remove the following configuration from the deployment.toml file.

[user_store.properties]
DomainName = "test"

Case 1: The default PRIMARY domain name and the API details don’t exist in the DB

The DomainName in deployment.toml file was configured as TEST and logged in to the system for the very first time with a fresh DB.

Database updates

The following tables in WSO2AM_DB and WSO2SHARED_DB should be updated.

case-1-db-table-update-queries.sql

Now you are good to start the server!

Case 2: The default PRIMARY domain exists and the API details don't exist in the DB

The DomainName in deployment.toml file is configured as TEST and logged in to the system for the very first time with a fresh DB. After that, the DomainName in the deployment.toml file is changed from TEST to PRIMARY by completely removing the DomainName attribute and re-start the server. Now when you log in to the system the UM_DOMAIN table will contain a new row entry for the domain name, “PRIMARY”, and a user role will be mapped to the created PRIMARY domain name in the UM_HYBRID_USER_ROLE table.

Database updates

The following tables in WSO2AM_DB and WSO2SHARED_DB should be updated.

case-2-db-table-update-queries.sql

Now you are good to start the server!

Case 3: The default PRIMARY domain name and the API details exist in the DB

1. Database updates

Table updates need to be done in WSO2AM_DB and WSO2SHARED_DB.

case-3-db-table-update-queries.sql

The complete set of DB update queries for case 3 is shared in the following repository.

NOTE:
The following repository also consist DB scripts for the below mentioned DB setup. Please pick the relevant DB scripts according to your DB configuration.
Approach 2: Separated DB Approach
- WSO2AM_DB
- WSO2SHARED_DB
- WSO2REG_DB
- WSO2USER_DB

2. API Synapse Files

API synapse files will be available as APIs were created. The API synapse files located in <APIM-Home>/repository/deployment/server/synapse-configs/default/api/ will have files containing TEST in the filename as well as inside the API synapse.

Fig 6: Filename appended with TEST domainName.
Fig 7: API synapse containing the TEST DomainName in the name attribute.

The DomainName TEST should be removed from both the API synapse filename and in the API synapse. The result should be as follows.

Fig 8: The appended TEST domain name has been removed
Fig 9: The appended TEST domain name has been removed

3. Solr re-indexing

Before you start the server please do the solr re-indexing by following the below-mentioned steps.

  1. Take a backup of <APIM-Home>/solr directory and delete the original solr directory.
  2. Add the following configuration in the <APIM-Home>/repository/conf/deployment.toml file.
[indexing]
re_indexing = 1

4. Carbon Management Console

After making the database and API synapse file changes, if you try to login to the publisher you will see the API provider name as TEST/<provider>. This will not allow you to load the APIs in devportal.

Fig 10: The appended TEST domain name in the API provider name
Fig 11: The error message in the carbon console when the API provider and API owner has the prefix “TEST” in the carbon management console

To overcome this problem, navigate to the carbon management console → Main → Metadata → List → API and select the API you want to edit. Then remove the TEST domain name from the Service Provider textbox and in other places if there are any.

Fig 12: Remove the appended TEST domain name from API provider and API owner

Save it and you will not be able to see the TEST domain name in publisher or Devportal.

Fig 13: The appended TEST domain name has been removed

Now we can invoke the API calls and everything is in place!

Recap!

Finally, now we can take a deep breath. So now we understand that how a small misconfiguration can easily kill our day. Therefore, I highly recommend you all not to define a new domain name for the primary user store as there is no added value for it since there will be only one primary user store. So no need to uniquely identify it with a different name. We can use the default domain name PRIMARY. Let’s keep this in mind when working with primary user stores in the future!

Stay safe!

Happy Learning…!!!

[Source: https://media3.giphy.com/media/4SYbTUhaHCl3BQbnlr/giphy.gif]

References

[1] https://apim.docs.wso2.com/en/latest/install-and-setup/setup/setting-up-databases/overview/

[2] https://github.com/wso2/carbon-identity-framework/blob/527dba704487431b95c34461656cdb7496a0f0cc/components/user-mgt/org.wso2.carbon.user.mgt/src/main/java/org/wso2/carbon/user/mgt/UserRealmProxy.java#L181

--

--

Inquisitive | Senior Software Engineer @ WSO2

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store