SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
SA Editor - Edit inactive

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
SA Editor - Edit inactive
Author Message
michalk



Joined: 29 Aug 2014
Posts: 211

Post SA Editor - Edit inactive Reply with quote
SA Editor allows to edit data after enabling Edit button found in top right corner of data grid.

Recently we figured out, this button is ghosted for some of us.
I was playing with my login role trying to narrows down the root cause, but ended up with unavailable Edit feature.

Could you point me to SQL query which evaluates rights to edit the data?
While I understand user experience related reasons to control availability of data modification by UI, this time I would rather depend on database itself. UI limits me to perform data changes even I'm superuser.

Issue found with postgresql 9.6
Thu Jul 12, 2018 10:47 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
As far as I know it doesn't check for user permissions. It checks if a query refers to a table with a primary key, and its columns are present in the query results, so that it can generate UPDATE ... WHERE ... and DELETE... WHERE... commands. Visibility of the primary key is controlled by user permissions and session settings.

As a heads-up, in the next version it is also going to evaluate unique keys and unique indexes it can use in case there is no "visible" primary key.

Hope this helps.
Thu Jul 12, 2018 11:21 am View user's profile Send private message
michalk



Joined: 29 Aug 2014
Posts: 211

Post Reply with quote
Please look at picture below. The table has PK. I'm using login role which is superuser, so there is no way to have no access to some data.
As you can see SA shows id columns as primary key, but Edit button is greyed out.


Fri Jul 13, 2018 4:00 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
Does adding schema name make a difference in this case?
Fri Jul 13, 2018 10:21 am View user's profile Send private message
michalk



Joined: 29 Aug 2014
Posts: 211

Post Reply with quote
No
Fri Jul 13, 2018 10:22 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
This issue is specific to PostgreSQL implementation only and manifests for tables created WITH OIDS, and having OID included in primary key. When you do SELECT *, the OID is not one of the columns visible in the result set. So the editor detects that the complete primary key is not included in the query, and so it cannot correctly generate UPDATE and DELETE statements. Having a unique index on ID column doesn't help in version 9.x. However it would help in the next version 10.
Sun Jul 15, 2018 10:01 pm View user's profile Send private message
michalk



Joined: 29 Aug 2014
Posts: 211

Post Reply with quote
Thank you for explanation.
I can confirm it does work the way you described.
If I add oid to displayed columns, then edit option appear active.

This way I found a temporal solution for anyone who has OIDs columnn in tables as well as PKs.
It's enough to modify a query found in SA Options/ DB Options/DB Queries/"Columns (PostgreSQL) + Keys"

from

Code:
SELECT 'oid', 'oid', 'NP', 0
FROM pg_catalog.pg_class c
WHERE c.oid = :OBJECT_ID
    AND c.relhasoids

UNION


to

Code:
SELECT 'oid', 'oid', 'NP', 0
FROM pg_catalog.pg_class c
WHERE c.oid = :OBJECT_ID
    AND c.relhasoids
    AND false
UNION


Or remove this part of UNION at all.
In result, SA get no information a selected table has OID column.

Disclaimer: I don't know a negative impact of this change to other parts of SA yet. For sure SA doesn't show (in hovering help bubble) oid column in a table anymore as
Mon Jul 16, 2018 5:01 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.