5

Can I stop DROP COLUMN from succeeding on particular tables in PostgreSQL 9 or 10 or later?

My application makes some assumptions about columns being forever, not disappearing. I'm afraid of later programmers forgetting about this constraint.

I know triggers can fire to control INSERT, MODIFY, and DELETE of rows. But what about DDL? Can I protect against certain changes to the table structure?

Security might be one way, assigning permissions to users and roles. But these future programmers will be allowed to add columns, only dropping columns is forbidden.

I am not concerned about malicious activity. I'm defending against the eager-beavers who may move too fast to remember the bigger picture.

  • 2
    If you are afraid that programmer will forget that certain columns are important, make sure you have proper integration tests. And if they can drop random columns without knowing what they are doing then they will find a way to work around whatever "protection" you come up with – a_horse_with_no_name Jun 30 '17 at 20:36
  • @a_horse_with_no_name Fair point. Certainly I will have meta-data stored that can will be used to verify table integrity. Nevertheless, if I can stop the errant programmer at the source, that would be nice. I am not concerned about malicious activity, I'm just defending against the clever eager-beavers who may move too fast to remember the bigger picture. – Basil Bourque Jun 30 '17 at 20:40
9

Event Triggers

There are event triggers to control DDLs, trapping for the event sql_drop.

Call CREATE EVENT TRIGGER. In the trigger function, check to see if the object being dropped is one of your important columns. If so, raise exception to roll-back transaction.

The sql_drop event occurs just before the ddl_command_end event trigger for any operation that drops database objects. To list the objects that have been dropped, use the set-returning function pg_event_trigger_dropped_objects() from the sql_drop event trigger code (see Section 9.28). Note that the trigger is executed after the objects have been deleted from the system catalogs, so it's not possible to look them up anymore.

Example:

drop table if exists t;
drop event trigger if exists etg;
drop function if exists fetg();

create function fetg() returns event_trigger language plpgsql as $$
begin
  if exists (
    select 1
    from pg_event_trigger_dropped_objects() as t
    where
      t.object_type = 'table column' and
      t.object_identity like any(array['%.t.y', 'public.tt.zz']))
  then
    raise exception 'Columns t.y and public.tt.zz are important!';
  end if;
end $$;

create event trigger etg on sql_drop execute procedure fetg();

create table t(x int, y int);
alter table t drop column y;

More docs:
Events
Functions

| improve this answer | |
  • 1
    Went above and over in this one. =) Awesome answer. – Evan Carroll Jul 1 '17 at 0:26
  • 2
    @EvanCarroll Everything is simple if you know where to dig :o) – Abelisto Jul 1 '17 at 0:38
  • Available from PostgreSQL 9.4. – bpelhos Jan 17 '19 at 13:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.