Overiding the auth table ID fields to allow for release over pillars (Development, Acceptance, Production)

[Davidiam]

We are planning on using Web2Py in a 3 tier release model (Development, Acceptance, Production) but as the ID fields in the auth tables are used as primary and foreign keys, we are concerned that this will cause inconsistencies between the different pillars.

We will be using SSO for access to web2py and users and groups need to be defined via our company's identity management system.  We plan on running scripts to update the Auth users and group tables based on a daily file feed.

We may need to create groups and group memberships manually in the development pillar which will later be defined in our identity management and AD systems to have them also in acceptance and production and so the IDs may become a mess.

One idea we had is to override the generated IDs in the USER and GROUP tables with the ID from our single source identity feed using its unique keys (user_id and group_id from our internal systems), but we are not sure if this will create problems down the line with the other tables which use the automatically generated sequential ID's, for example, the auth_permission table.

Any recommendations on how to deal with this issue in Web2Py releases ?  Would our idea of replacing the ids with the unique id's provided by our internal systems be a solution or cause other issues ?

Kind Regards,

David

[Val K]

why do you have to override ids? You can just create users with the required ids.

[Davidiam]

Thanks for your reply.

Maybe override was not the best term.  We can create the users using the userid and groupid as the ids in the user, group and membership tables, but as Web2Py out of the box creates an id (sequentially incremented) we are concerned that specifying a different id might have repercussions down the line.

Also because the automatically generated ids in the other auth tables may be different between pillars.

I have been looking into this further and I don't think we will have a problem if we create the user and group tables using our own unique id's in the id fields.  

[Davidiam]

We have looked in this further, and due to the autoincremented ID fields in the Auth tables, we are going to have a very complicated release process.

The problem is that the ID will be used in all references to the users and groups but between pillars (DEV,ACC,PRO) the ID's may not be the same which will cause a data mismatch.  

As these fields are autoincremented integer fields, we can not use our alphanumeric keys from our internal systems.  

We have found a solution but it will make our releases much more complicated unfortunately.

Regards,

David

On Tuesday, September 10, 2019 at 8:16:00 PM UTC+2, Val K wrote: