Query inspector for Delete gives permission denied PSQLException
On our pegacloud 8.3.x environment I have been experimenting with different indexes to optimize slow queries in the database and the tool Configure > System > Database > Query inspector has been very helpful.
In this case however I am trying to optimize a delete statement.
DELETE FROM pegadata.myorg_idx_myclass_Myembeddedpage WHERE pxInsIndexedKey = 'foo' AND pxIndexPurpose = 'InterestingPropertyCatIdx'
Since this is just ending up doing an explain, shouldn't it be allowed, as no modification to the database occurs?
Instead I get error
There was a problem getting a list: code: 0 SQLState: 42501 Message: ERROR: permission denied for table myorg_idx_myclass_Myembeddedpage DatabaseException caused by prior exception: org.postgresql.util.PSQLException: ERROR: permission denied for table myorg_idx_myclass_Myembeddedpage | SQL Code: 0 | SQL State: 42501
Can postgresql be configured to allow these explains, but still prevent write access?
Pegasystems Inc.
US
Hi Peter,
Query inspector & Query Runner on PegaCloud environments uses jdbc/PegaRULESReadOnly data source and don't think it allows any query requiring updates to DB tables.
You will need a Pega Cloud request for such requests or help.
Let me know if that clarifies.
Pegasystems Inc.
US
Explain Plan through Query Inspector should work for select queries, you may verify that.
With out having the delete privileges on the datasource, I don't think we can expect Explain plan to work for the same delete statement.
I will let the cloud team confirm that part for us.