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?
CodePudding user response:
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
CodePudding user response:
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();
CodePudding user response:
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.