1

I have a trigger that protects updating some column. I also have a trigger that should update that same column after insert on that table. I've used triggers like this and it works if I need to update column on some other table. I just disable that protecting trigger before update and enable it again after the update inside function that do all the work. In this case, it doesn't work, since I need to disable the trigger for the same table.

ERROR: cannot ALTER TABLE "Table1" because it is being used by active queries in this session

Is there another way to do it? Is there way to disable all updates to that column except from within this function?

It's PostgreSQL 9.4.

Improve this question

1 Answer 1

Reset to default
3

One option would be to:

  1. Jack up the security on the table so normal users can't directly access it at all, and remove the protecting trigger.
  2. Create an updatable view on the table which is accessible by normal users, and add an INSTEAD OF trigger to protect the column from updates (assuming you want it still visible; if not, simply omit it completely from the view definition.)
  3. Create a function with definer's rights which bypasses the view and directly updates the column in the base table.
Improve this answer
3
  • I've revoked all privileges from base table from user and created updatable view of that table. Protective trigger is removed. There is still a trigger that takes one value from new insert, adds it to a value from another table and put this resulting value to the column that should be protected from direct update. There are two problems that I see with this approach: protective trigger is removed, but updating trigger still doesn't work, since user doesn't have permission to write to the base table.
    – Vald
    Jan 29, 2016 at 14:33
  • Also, I need to protect only that one column from direct updating. INSTEAD OF UPDATE events do not support lists of columns so it's all or nothing. To work this out, I created trigger function like on this page: vibhorkumar.wordpress.com/2011/10/28/instead-of-trigger and removed my column from all statements, but I still have this privileges issue. User can't execute function if that function accesses table he doesn't have permissions for.
    – Vald
    Jan 29, 2016 at 14:33
  • SECURITY DEFINER solved function privileges issue.
    – Vald
    Jan 29, 2016 at 14:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.