 |
SoftTree Technologies
Technical Support Forums
|
|
Author |
Message |
michalk
Joined: 29 Aug 2014 Posts: 211
|
|
[postgresq] SA limited functionality for non-superusers |
|
Schemas SQL Query for postgresql 9.1 unnecessarily references pg_catalog.pg_authid.
This table, because contains hashed passwords is readable only by superadmin roles.
Unfortunately attempt to access this table by non-superadmin user ends with exception, therefore effectively disable all intellisense SA functionality.
The fix is trivial: replace pg_authid with pg_roles.
pg_catalog.pg_roles is a view over pg_authid, with exception, it hides hashed passwords with asterisks.
Final SQL looks as follow:
 |
 |
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_roles
|
Now every user can use SA or SA Editor
|
|
Fri Apr 27, 2018 11:37 am |
|
 |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7948
|
|
|
|
Thank you very much again. I have submitted a high priority ticket for this one, reference #SA0033733
|
|
Fri Apr 27, 2018 1:20 pm |
|
 |
|
|
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
|
|
|