4

How do I prevent UPDATE on any record in a given table, but allow INSERT, I already have created a trigger which prevents update but I found that it also prevents INSERT.

EDIT
I can INSERT new row/record from the database itself but not from the form for a new record of the application that is connected to this database, after submitting the form, the trigger is being executed.

It's a web application built with Django.

TRIGGER FUNCTION

CREATE OR REPLACE FUNCTION prevent_updte()
RETURNS trigger AS
$BODY$
BEGIN
RAISE EXCEPTION 'Data modification not allowed';
END;
$BODY$
LANGUAGE plpgsql

TRIGGER

CREATE Trigger prevent_update
BEFORE UPDATE ON purchases_purchase
FOR EACH ROW
EXECUTE PROCEDURE prevent_update();
2
  • Something was wrong with the database. I dropped the existing database and created a new database and everything works fine now. thank you, I have updated the question.
    – dsnewguy
    Commented Apr 17, 2019 at 9:06
  • 2
    Please do not edit your question if you found a solution. Add an answer and accept that answer. Without an accepted answer the question will remain "unsolved"
    – user1822
    Commented Apr 17, 2019 at 9:10

1 Answer 1

7

You can use GRANT & REVOKE for this purpose:

 CREATE TABLE TEST (ID int, NAME text);

 REVOKE ALL ON TABLE TEST FROM CURRENT_USER;

 GRANT INSERT ON TABLE TEST TO CURRENT_USER;
 GRANT SELECT ON TABLE TEST TO CURRENT_USER;
 INSERT INTO TEST VALUES (1, 'NAME 1'),(2, 'NAME 2');
UPDATE TEST SET NAME='NAME 3' WHERE ID = 1;
ERROR:  permission denied for table test

db<>fiddle here

Your Answer

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

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