Standard alias sets for virtual domains

[martin ➬]

The following SQL is what I use somewhere deep down in my SQL database so that I don’t have to add common aliases to every new virtual domain I create. I stitched this up in 2014 and have no idea anymore how it works at this point (nor time to figure it out, and we’re going to replace the server soon anyway), but it’s better that I keep this here for now.

-- Opted for 1:n instead of a domain_alias_set n:m link for simplicity:
alter table domain_data add column asid bigint not null default 1;

-- We need the concept of a domain "owner" to make alias sets universal.
-- If a default alias sets its destination to 'OWNER', this address will
-- be used.
alter table domain_data add column owner text not null default 'INVALID_OWNER';
  -- TODO: for new installs, it should be trivial to ensure not-null for the
  -- owner of a domain

-- Cleanup for testing/development
--drop table if exists common_alias cascade;
--drop table if exists alias_set cascade;
--drop sequence if exists alias_set_id;

create sequence alias_set_id start 1;

create table alias_set (
  asid bigint default nextval('alias_set_id'::regclass) primary key,
  name text not null
);

insert into alias_set (asid, name) values (0, 'no_aliases');
insert into alias_set (name) values ('rfc_minimum');

alter table only domain_data add
  constraint fkey_domain_data_asid_alias_set
  foreign key (asid) references alias_set(asid);

create table common_alias (
  asid bigint not null references alias_set(asid),
  local_part text not null,
  destination text,
  constraint pkey_common_alias primary key (asid, local_part)
);

-- Populate table with RFC recommendation
insert into common_alias
  select asid, local, dest from alias_set a,
  (values
    ('postmaster', 'OWNER'),
    ('abuse','postmaster@%d')
  ) v (local, dest)
  where a.name = 'rfc_minimum';

--insert into alias_set (name) values ('madduck.net');
--insert into common_alias
--  select asid, local, dest from alias_set a,
--  (values
--    ('postmaster', '%=@admin.madduck.net'),
--    ('abuse','%=@admin.madduck.net'),
--    ('noc','%=@admin.madduck.net'),
--    ('hostmaster','OWNER'),
--    ('root','OWNER')
--  ) v (local, dest)
--  where a.name = 'madduck.net';

‌