IPS tables missing primary keys

[Justin Lex-Hammarskjöld]

Hi,

I'm a cloud engineer maintaining a large IPS forum with ~200k users. We've been using IP.Board since the early days, but we're needing to migrate our forum to a more modern server in order to keep it maintainable by our small team.

We've looked into IPS cloud as a modernization option, but the current offering isn't viable for us. One of our requirements is that we need to be able to create members from our other internal microservices, and allow these members to login with oauth. At the moment, this isn't possible with the API, and requires direct access to the IPS MySQL database.

So, in order to modernize our forums hosting infrastructure, we are moving our forums from our outdated AWS monolith, to a modern containerized Digital Ocean system. One important change with this new infrastructure is that Digital Ocean's managed MySQL databases require primary keys on all tables. It is not possible for us to use tables without primary keys on our new infrastructure.

We have been able to run IPS in our Kubernetes containers just fine on our new servers, but the main roadblock we've encountered here with the IPS software is that many of the tables that it uses do not have primary keys, which is preventing us from migrating our forums to our new server. Other forums software such as Discourse do not have this database issue.

The solution we've come up with internally is to simply issue ALTER TABLE commands for these tables to add an auto_increment primary key, which will add a primary key to these tables without affecting IPS's database code.

We wanted to get your input on the impact of such a move. Are there any possible issues that you can see with this auto_increment plan, and are there plans to update these IPS tables to use primary keys by default in the future?

This post is a continuation of another discussion, I'm creating a new post to avoid excessive necromancy.

 

Thanks,

Justin

[Jim M]

As mentioned a few times in the Feedback topic you linked, we do not recommend making adjustments to the database structure. It can indeed cause issues in operation and as we move forward and make changes to those tables ourselves.

42 minutes ago, Justin Lex-Hammarskjöld said:

One of our requirements is that we need to be able to create members from our other internal microservices, and allow these members to login with oauth. At the moment, this isn't possible with the API, and requires direct access to the IPS MySQL database.

I would ask yourself as to why that is not possible in the core OAuth 2.0 integration we have or a custom application. Making direct access changes to the database itself is not a good idea.

[Justin Lex-Hammarskjöld]

Hi Jim,

Thanks for your response.

I was wondering if there's a deeper technical reason for not using primary keys on these tables? I understand that there is a standard policy around not modifying the database schema, but primary keys have been best practice for decades at this point, and auto_increment keys do not affect existing SELECT or INSERT queries. Other forums software such as Discourse does not have this issue with primary keys.

I would be interested in a way to create a member using the REST API or custom application API. The existing API requires the caller to set a password for the new user, which isn't applicable for an OAuth environment, and would cause a security risk if we did not access the database directly. If there is a way to create new members without setting a password, that would probably convert us into an IPS Cloud customer.

- Justin

Edited September 28, 2023 by Justin Lex-Hammarskjöld
Link to API docs

[Jim M]

4 minutes ago, Justin Lex-Hammarskjöld said:

I would be interested in a way to create a member using the REST API or custom application API. The existing API requires the caller to set a password for the new user, which isn't applicable for an OAuth environment, and would cause a security risk if we did not access the database directly. If there is a way to create new members without setting a password, that would probably convert us into an IPS Cloud customer.

Is there a reason why you are not having the OAuth system create the account itself rather than the REST API? Users require a password in our system if the standard login system is enabled. You could, of course, have user setup their own password for the forum software too so that way if your OAuth login ever has connection issues, people can still login to your community.

[Justin Lex-Hammarskjöld]

Hi, I just checked with my colleague. What we do is the following:

1. Create the account in OAuth, like you said.
2. Create the account in IPS through the API; IPS will not create an account automatically from OAuth.
3. Access the IPS database directly to disable the user's password
4. When the user logs in with Oauth to IPS, IPS links their IPS account to the one in our Oauth system. If we did not disable the password, the system would ask the user for their IPS password before linking to OAuth.

We don't want users to have to set two passwords, as that would be a bad experience for our users.

With the way IPS is coded, there is no way for us to use the OAuth integration without direct access to the database, or forcing all of our users to set two separate passwords to use our service. If 2, 3, or 4 could be solved with either a custom application or code changes from your end, we would happily switch to that and convert our self-hosted subscription to IPS Cloud.

Is there someone we can talk to about the database schemas to resolve this issue with the database?

[Jim M]

The account would be created for the user on first login through the OAuth platform, it would not be created ahead of time. You would want to disable the standard login handler if you do not wish to set the password. That way everyone simply logs in via your OAuth.

13 minutes ago, Justin Lex-Hammarskjöld said:

Is there someone we can talk to about the database schemas to resolve this issue with the database?

I'm afraid, this is not something we would entertain from a support perspective. We do not aid with customizations of our software. However, if you want to see primary keys in the future, you would want to comment in the Feedback topic which you linked in your original post.

[Justin Lex-Hammarskjöld]

Our software needs to know the IPS ID for all of our users, which means that accounts need to be created in both Oauth and IPS at the same time. We have disabled the standard login, but there is no way to create a user without a password through the IPS API. This is a critical use-case for us, which is why we have implemented this work-around.

Again, are there any technical limitations on adding primary keys to these database tables, either from your end or ours? This is an oversight in IPS's database design, as using SQL without primary keys is bad practice for both performance and database normalization.

[Jim M]

3 minutes ago, Justin Lex-Hammarskjöld said:

Our software needs to know the IPS ID for all of our users, which means that accounts need to be created in both Oauth and IPS at the same time. We have disabled the standard login, but there is no way to create a user without a password through the IPS API. This is a critical use-case for us, which is why we have implemented this work-around.

This is due to you are utilizing the REST API which is indeed requires password. If you have needs to pass information back/forth, you can explore webhooks with OAuth so that the data gets passed back. Alternatively, you could build a custom SSO application which does this within our ecosystem so you can use our Cloud.

There are overall ways to achieve what you're doing without manual database edits, is all I am trying to get across here.

12 minutes ago, Justin Lex-Hammarskjöld said:

Again, are there any technical limitations on adding primary keys to these database tables, either from your end or ours? This is an oversight in IPS's database design, as using SQL without primary keys is bad practice for both performance and database normalization.

As mentioned several times now, this is not something we recommend so would not advise further on it. If you would like to see this changed in the software, please leave your comment in the Feedback topic you copied earlier on in this topic.

[Justin Lex-Hammarskjöld]

From what I can tell from the IPS documentation, I don't think webhooks or an SSO application would allow us to create members in IPS. I think that IPS should make improvements to the database schema to bring it up to best-practice standards, but this was already requested in 2021 in the other thread.
Thank you for your time and input. I think we will put this modernization project on hold for now until we have time to make bigger changes to uncouple our internal software from IPS.

[Marc Stridgen]

9 hours ago, Justin Lex-Hammarskjöld said:

I think that IPS should make improvements to the database schema to bring it up to best-practice standards, but this was already requested in 2021 in the other thread.
Thank you for your time and input. I think we will put this modernization project on hold for now until we have time to make bigger changes to uncouple our internal software from IPS.

Sorry to hear this is the way you feel. This is unfortunately not a simple case of adding primary keys to each table, and would be an extremely large undertaking on the software. Therefore is not something you are likely to see any time soon. However, I would like to address the below

9 hours ago, Justin Lex-Hammarskjöld said:

From what I can tell from the IPS documentation, I don't think webhooks or an SSO application would allow us to create members in IPS

I would suggest on this contacting a developers to work on this on your behalf. The documentation does indeed show the creation of members in the members section of the members section of the rest API here

https://invisioncommunity.com/developers/rest-api

A POST request would be sent to the following endpoint - /core/members

In addition, this returns the ID of the member that has been created.

Quote

Our software needs to know the IPS ID for all of our users

You can pull a full list of members on the REST API if you need to pull those separately with  a GET request to the following endpoint

/core/members

However you do mention the following after that part

Quote

which means that accounts need to be created in both Oauth and IPS at the same time. 

Getting all IDs and the use of OAuth for creation of members are 2 different things here. In terms of OAuth and your other site being the client, you can create OAuth clients within System->Site Features->API for your purposes, which follow the OAuth 2.0 standard. All scopes are shown in that area that can be used, and again the ID of course would be provided to the client.

There isn't anything here at this point that you have said that isn't already available with our APIs. I would encourage you to get someone who is familiar with these, or if its just things you are unsure of, please post in our developers forum to ask those questions.

[Justin Lex-Hammarskjöld]

Hi Marc, thanks for your input on this.

As a developer, I am skeptical about the challenges of adding an auto_increment primary key column to these tables, but I'll take your word for it and avoid this path.

We are aware of the /core/members POST endpoint, and we are using an OAuth 2.0 client already. I am part of a team of developers in-house that have worked on this. At this time, the IPS API does not fulfill our needs.

Edited September 29, 2023 by Justin Lex-Hammarskjöld

[Marc Stridgen]

1 hour ago, Justin Lex-Hammarskjöld said:

At this time, the IPS API does not fulfill our needs.

Sorry to hear this is the case. I am unable to advise without knowing what you are missing there. There isnt anything you have added to the conversation so far that the system doesn't already do.