Database query - PostgreSQL

[Vamsi Kandala]

Hi,

I am trying to fetch the list of users who don't have space admin permissions to their related spaces but have permissions to delete the page, attachment or blog post.

I am using this query:

SELECT s.spacekey, s.spacename, cu.user_name, cu.first_name || ' ' || cu.last_name AS username, sp.permtype
FROM SPACEPERMISSIONS sp
JOIN SPACES s ON s.SPACEID = sp.SPACEID
JOIN user_mapping u ON sp.permusername = u.user_key
JOIN cwd_user cu ON cu.user_name = u.username
WHERE sp.permtype = 'REMOVEPAGE' OR sp.permtype = 'REMOVEBLOG' OR sp.permtype = 'REMOVEATTACHMENT' OR sp.permtype = 'REMOVECOMMENT' OR sp.permtype = 'REMOVEMAIL' AND sp.permtype != 'SETSPACEPERMISSIONS'
GROUP BY s.spacekey, s.spacename, sp.permtype, cu.user_name, cu.first_name || ' ' || cu.last_name, sp.permtype
ORDER BY s.spacename, cu.user_name, sp.permtype

But, this is returning the list of users who have space admin permissions as well.  

Anything that I missed?

Thanks,
Vamsi

[Vamsi Kandala · Accepted solution]

SELECT s.spacekey, s.spacename, cu.user_name, cu.first_name || ' ' || cu.last_name AS username, sp.permtype, sp.permusername
FROM SPACEPERMISSIONS sp
JOIN SPACES s ON s.SPACEID = sp.SPACEID
JOIN user_mapping u ON sp.permusername = u.user_key
JOIN cwd_user cu ON cu.user_name = u.username
WHERE (sp.permtype = 'REMOVEPAGE' OR
sp.permtype = 'REMOVEBLOG' OR
sp.permtype = 'REMOVEATTACHMENT' OR
sp.permtype = 'REMOVECOMMENT' OR
sp.permtype = 'REMOVEMAIL') AND
sp.permusername NOT IN
(SELECT distinct permusername FROM spacepermissions WHERE permtype = 'SETSPACEPERMISSIONS' AND permusername = sp.permusername AND spaceid = sp.spaceid)
GROUP BY s.spacekey, s.spacename, sp.permtype, cu.user_name, cu.first_name || ' ' || cu.last_name, sp.permtype, sp.permusername
ORDER BY s.spacename, cu.user_name, sp.permtype