Postgresql command for displaying users associated with the local Bitbucket groups

[warecm43]

Can some help me come up with a SQL command to display the users located in the local groups of bitbucket? 

I have the following command that I used for Confluence:

SELECT cu.id as user_id, cu.user_name, cg.group_name, cd.directory_name
 FROM cwd_user cu
 INNER JOIN cwd_membership cm  ON (cu.id = cm.child_user_id)
 INNER JOIN cwd_group cg    ON (cm.parent_id = cg.id)
 INNER JOIN cwd_directory cd    ON (cu.directory_id = cd.id)
 WHERE cg.local='T' ORDER BY cg.group_name

But this command doesn't seem to work in bitbucket.  Is the database schema that different between the two products?  Would someone have a bitbucket equivalent command to produce the data?

Thanks for any assistance on this.

[warecm43 · Accepted solution]

I dug down into the database columns and tried to compare the differences between Confluence and Bitbucket and found the the difference was Confluence used 'local' in the cwd_group table and Bitbucket uses 'is_local' in the cwd_group table.  

Once I plugged in the 'is_local' into the WHERE clause, I got the results I was expecting.

[Jim Knepley - ReleaseTEAM]

Hi @warecm43 

Maybe you're looking for something like this?

SELECT u.user_name, g.group_name, g.id AS "group_id", d.directory_name, d.id AS "directory_id"

FROM cwd_membership m

INNER JOIN cwd_user u ON m.child_id = u.id

INNER JOIN cwd_group g ON m.parent_id = g.id

INNER JOIN cwd_directory d ON g.directory_id = d.id

ORDER BY d.directory_name, u.user_name, g.group_name;

ref: https://support.atlassian.com/bitbucket-data-center/kb/querying-the-bitbucket-server-and-data-center-database-to-get-a-list-of-users-group-memberships-and-source-directory/

[warecm43]

I found that query and it gets me all the groups including the Active Directory groups.  I was looking for a WHERE clause that limits the search to the local bitbucket groups.

The "WHERE cg.local='T'" in my original post does this in Confluence, but when I add the equivalent to my BitBucket command (i.e. WHERE g.local='T'), I receive a g.local does not exist.

I was hoping there might be something in the BitBucket database that would equate to the cg.local field in the Confluence DB.

I'm sure the command would work fine once I know what field holds the information to tell if the group is a local group or not.