nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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.

Accepted answers

warecm43

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.

All comments

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.

warecm43

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

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

server

bitbucket

bitbucket-server

data-center

bitbucket-data-center