Disable DELETE on table in PostgreSQL?

问题:

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?

回答1:

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.