Disable DELETE on table in PostgreSQL?

Question

For a security sensitive design, I'd like to disable DELETEs on certain tables.

The DELETE should merely set a deleted flag on a row (which would be then visible on a view, which would be used by the application layer).

As I understand a rule would generate additional queries - so a rule could not suppress the original query.

As illustration a toy example with a trigger (not yet tested):

-- data in this table should be 'undeletable'
CREATE table article (
    id serial,
    content text not null,
    deleted boolean default false
)

-- some view that would only show articles, that are NOT deleted
...

-- toy trigger (not tested)
CREATE OR REPLACE FUNCTION suppress_article_delete()
RETURNS TRIGGER AS $sad$
BEGIN
    IF (TG_OP = 'DELETE') THEN
        UPDATE article SELECT id, content, TRUE;
        -- NEW or NULL??
        RETURN NEW;
    END IF;
    RETURN NULL;
END;
$sad$ LANGUAGE plpgsql;

What would be a good way to suppress a DELETE?

Milen A. Radev · Accepted Answer · 2012-07-02T18:28:44.260

38

As I understand a rule would generate additional queries - so a rule could not suppress the original query.

Not really - it could be an INSTEAD rule:

 CREATE RULE shoe_del_protect AS ON DELETE TO shoe DO INSTEAD NOTHING;

(an example on that same page of the manual).

Another way is to REVOKE delete privileges on the table in question and to create stored procedure(s) for deleting... and updating and inserting also probably.

edited Jul 02 '12 at 18:28

answered Jul 02 '12 at 18:11

Milen A. Radev

60,241
22
105
110

Thanks! I'd like to get this *undelete* design into the data-model, not the administration (revoke) - so I guess I'll try a `INSTEAD` rule. – miku Jul 02 '12 at 18:32
5

The problem of a ON DELETE DO INSTEAD NOTHING is that code that issues a DELETE will not get notified that nothing happened - the DELETE will be successful for the client and silent errors can happen. – peschü Oct 17 '19 at 21:37
3

@peschü True. One way to avoid the problem: `create or replace function raiseException() returns void language plpgsql volatile as $$ begin raise exception 'Cannot delete row.'; end$$; CREATE or replace RULE prevent_deletes AS ON DELETE TO shoe DO INSTEAD select raiseException(); ` – Elifarley Mar 18 '22 at 15:33

Disable DELETE on table in PostgreSQL?

1 Answers1

Linked