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

Question

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?

Answer 1

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

Answer 2

setup

begin;
create role test_user1 NOCREATEDB NOSUPERUSER NOCREATEROLE LOGIN;
create role test_user2 NOCREATEDB NOSUPERUSER NOCREATEROLE LOGIN;
create role test_user3 NOCREATEDB NOSUPERUSER NOCREATEROLE LOGIN;
grant create, USAGE on schema public to test_user1;
set session authorization  test_user1;
select current_role;
create table public.ticket_trail(a int);
grant select,delete, insert,update,trunCATE on  public.ticket_trail to test_user2,test_user3 ;
set session authorization  test_user2;
insert into public.ticket_trail values(1);
set session authorization  test_user3;
insert into public.ticket_trail values(1);
end;

revoke all the grantee privileges in superuser session.

DO $func$
DECLARE
    r record;
BEGIN
    FOR r IN SELECT DISTINCT
        grantee
    FROM
        information_schema.role_table_grants
    WHERE
        table_name = 'ticket_trail'
    EXCEPT
    SELECT
        rolname
    FROM
        pg_authid
    WHERE
        rolsuper LOOP
            RAISE NOTICE 'test r grantee: %', r.grantee;
            EXECUTE 'revoke all PRIVILEGES on table ticket_trail from  ' || quote_ident(r.grantee) || ' cascade';
            REVOKE ALL PRIVILEGES ON SCHEMA public FROM test_user1 CASCADE;
        END LOOP;
END
$func$;

Now it's clean. Only the owner and superuser can grant privilege to other role. You may want using alter table public.ticket_trail owner to new_role change the table ownership.

Answer 3

You have to revoke the privileges that were granted. If you want to revoke a privilege that was granted to admin, you have to revoke it from admin specifically. There is no wholesale command "revoke everything from everybody".

If you are using psql, you could use \gexec:

SELECT format(
          'REVOKE %s ON %s FROM %s',
          a.privilege_type,
          t.oid::regclass,
          a.grantee::regrole
       )
FROM pg_class AS t
   CROSS JOIN aclexplode(t.relacl) AS a(p)
WHERE oid = 'x'::regclass \gexec