HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

how to prevent UPDATE of existing rows/record but allow INSERT using PostgreSQL trigger?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
postgresqlrowspreventtriggerupdateinsertbutallowrecordusing

Problem

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();

Solution

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

Code Snippets

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

Context

StackExchange Database Administrators Q#235003, answer score: 7

Revisions (0)

No revisions yet.