Hi Alper,

A regular SQL query alone within LabKey unfortunately isn't enough to accomplish this goal.

Although not recommended, you could setup an external schema to point to LabKey's own database and expose the other core tables that hold the permissions like you saw with the combo of permissions, members, roleassignments, etc, then do one large complex join to aggregate all of that information together.

However, as stated earlier, we don't recommend doing this since you expose yourself to security issues and potentially problems with upgrades as well as stated here:

https://www.labkey.org/Documentation/wiki-page.view?name=externalSchemas

Otherwise, the next best thing would be for you to create your own module that can do direct SQL queries to the database and collect the necessary information for you and then having that render within LabKey on your own custom page.

Regards,

Jon

Hi Jon,

Thanks for the information.
I understand that this will not be possible within labkey, I was more interested in accomplishing this task by connecting to postgres directly.

I also understand that this might be a security issue if we were to expose this to other users. Our goal is to build a integrated user tracking system with all our our platform components, one of which is labkey, and this will solely be managed by our internal team and will not be visible on our web labkey instance.

I have been trying to figure out how to get the information using the postgres tables, but I find the column names to be very confusing and would appreciate some guidance on this.

There are container ids, groupids, user ids (but they are not really user ids depending on table) entity ids, and bunch of other columns and I am not sure how they are all connected and if possible I would appreciate a diagram or an explanation about all the tables in the core schema.

Thanks
Alper

Hi Alper,

I unfortunately do not have a diagram that we can provide you, but the tables you will want to be focused on are:

core.containers - This gives you the name of the project/folder and what its parent folder above it is. Each container has its own unique entityId that appears in an alphanumeric string like 8f12c8b9-4abe-1036-81db-3d45df6161f0.

core.userdata - This gives you the actual user data with their userIds.

core.principals - This lists users, groups, and permission roles, specifically what containers they're connected to. You can see the userId value (which can contain a group value as well), the container string value, and the type (u for user, g for group, r for role). The default system generated groups, like Admins, are clearly seen with negative userId values.

core.members - This shows what users are tied to what groups using the userId value and the groupId value.

core.roleassignments - This shows what users and groups are assigned to a specific role/permission and for the specific container.

It would be a set of complex JOINs to do, but it is possible for you to identify permissions for a specific user and trace it to the container and what permissions they have under the container individually or being apart of a group, similarly to how the userAccess.view page works when going into the Site Users page and clicking on a user's permissions link.

Regards,

Jon