SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[SA 9.1.276 Pro] - PG and $OBJECT()$

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[SA 9.1.276 Pro] - PG and $OBJECT()$
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 2100

Post [SA 9.1.276 Pro] - PG and $OBJECT()$ Reply with quote
Why does the popup not show users(/roles/groups/whatever) in popup for $OBJECT(role)$ and show them for $OBJECT(login)$ (which actually seems to be a non-existent keyword in PostgreSQL)?
Sat May 27, 2017 1:25 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
My wild guess, that's because of SA cross-platform support and the need to bridge the "naming" differences between different db types. Roles in PG are like logins in other systems if they're not "group" type roles. There are no users and standalone user groups. Groups are roles too with group attribute.
Mon May 29, 2017 11:45 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2100

Post Reply with quote
Yes, that's reasonable as an explanation why it has roles in popup for $OBJECT(login)$, even if the term 'login' seems not to exist in PostgreSQL. What does not make sense, however, is why not show them for $OBJECT(role)$ as well, which is what they are actually called there. You're correct that roles in PG are like logins/users when used to login, but they also behave like roles in other systems when they lack the canlogin property and are used for permission management.

Not showing them for $OBJECT(role)$ is somewhat counter-intuitive, imho.
Mon May 29, 2017 2:28 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
Your point is very reasonable. I'm going to enter this a n enactment request. Thank you very much for your feedback.
Mon May 29, 2017 3:57 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2100

Post Reply with quote
Thank you very much.
Mon May 29, 2017 4:42 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
Please update "Schemas (PostgreSQL)" query in the option to the following


Code:
SELECT
    nspname, 'SC', oid
FROM
    pg_catalog.pg_namespace
WHERE nspname NOT LIKE 'pg\_%'
   OR nspname = 'pg_catalog'

UNION ALL
   
SELECT
    rolname, CASE WHEN rolcanlogin THEN 'SL' ELSE 'SR' END, oid
FROM
    pg_catalog.pg_authid


After this change $OBJECT(role)$ should show group roles only, excluding logins. To show all roles with login permissions, use $OBJECT(login,role)$

You can tweak the query further to meet your requirements. Here SC is used for traditional schemas and users, SR - for traditional roles, SL - for logins
Tue May 30, 2017 9:14 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2100

Post Reply with quote
This is great, thank you very much.
Tue May 30, 2017 11:14 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.