How to prevent or avoid running update and delete statements without where clauses in PostgreSQL

Question

How to prevent or avoid running update or delete statements without where clauses in PostgreSQL?

Same as SQL_SAFE_UPDATES statement in MySQL is needed for PostgreSQL.

For example:

UPDATE table_name SET active=1; -- Prevent this statement or throw error message.
UPDATE table_name SET active=1 WHERE id=1; -- This is allowed

My company database has many users with insert and update privilege any one of the users do that unsafe update. In this secoario how to handle this. Any idea can write trigger or any extension to handle the unsafe update in PostgreSQL.

Answer 1

I have switched off autocommits to avoid these errors. So I always have a transaction that I can roll back. All you have to do is modify .psqlrc:

\set AUTOCOMMIT off
\echo AUTOCOMMIT = :AUTOCOMMIT
\set PROMPT1 '%[%033[32m%]%/%[%033[0m%]%R%[%033[1;32;40m%]%x%[%033[0m%]%# '
\set PROMPT2 '%[%033[32m%]%/%[%033[0m%]%R%[%033[1;32;40m%]%x%[%033[0m%]%# '
\set PROMPT3 '>> '

You don't have to insert the PROMPT statements. But they are helpful because they change the psql prompt to show the transaction status.

Another advantage of this approach is that it gives you a chance to prevent any erroneous changes.

Example (psql):

database=# SELECT * FROM my_table; -- implicit start transaction; see prompt
-- output result
database*# UPDATE my_table SET my_column = 1; -- missed where clause
UPDATE 525125 -- Oh, no!
database*# ROLLBACK; -- Puh! revert wrong changes
ROLLBACK
database=#   -- I'm completely operational and all of my circuits working perfectly

Answer 2

There actually was a discussion on the hackers list about this very feature. It had a mixed reception, but might have been accepted if the author had persisted.

As it is, the best you can do is a statement level trigger that bleats if you modify too many rows:

CREATE TABLE deleteme
AS SELECT i FROM generate_series(1, 1000) AS i;

CREATE FUNCTION stop_mass_deletes() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   IF (SELECT count(*) FROM OLD) > TG_ARGV[0]::bigint THEN
      RAISE EXCEPTION 'must not modify more than % rows', TG_ARGV[0];
   END IF;
   RETURN NULL;
END;$$;

CREATE TRIGGER stop_mass_deletes AFTER DELETE ON deleteme
   REFERENCING OLD TABLE AS old FOR EACH STATEMENT
   EXECUTE FUNCTION stop_mass_deletes(10);

DELETE FROM deleteme WHERE i < 100;
ERROR:  must not modify more than 10 rows
CONTEXT:  PL/pgSQL function stop_mass_deletes() line 1 at RAISE

DELETE FROM deleteme WHERE i < 10;
DELETE 9

This will have a certain performance impact on deletes.

This works from v10 on, when transition tables were introduced.