Search code examples
postgresqlsql-revoke

Creating an append-only table in Postgres using revoke on all roles and granting insert to all roles

I would like to create an enforced append only table in my postgres db. After a bit of research, I realized that a solution that will answer my requirements will be to revoke ALL operations on that table from ALL roles and then run an insert priveleges to all these roles, like so:

REVOKE ALL ON TABLE ticket_trail FROM PUBLIC;
GRANT INSERT ON TABLE ticket_trail TO PUBLIC;

but that doesn't seem to work. I think that revoking it from PUBLIC does not revoke it from my admin user or other users that I have.

How can I revoke ALL from ALL roles and then GRANT INSERT to all these roles again? Any better ways to achieve an enforced append only table?

Solution

  • Better add a before update or delete trigger with a trivial trigger function that returns null. This will work for all users regardless of their roles and privileges.

    create function abort_tf() returns trigger language plpgsql as
$$
begin
  return null;
end;
$$;

CREATE TRIGGER no_update_or_delete_t
BEFORE UPDATE OR DELETE ON ticket_trail 
FOR EACH ROW EXECUTE FUNCTION abort_tf();