db_tables.sql

   1 -- Regano database table definitions
   2 --
   3 -- Uses PostgreSQL extensions.
   4 --
   5 --  Regano is a domain registration system for OpenNIC TLDs written in
   6 --  Perl.  This file is part of Regano.
   7 --
   8 --  Regano may be distributed under the same terms as Perl itself.  Of
   9 --  particular importance, note that while regano is distributed in the
  10 --  hope that it will be useful, there is NO WARRANTY OF ANY KIND
  11 --  WHATSOEVER WHETHER EXPLICIT OR IMPLIED.
  12
  13
  14 -- The type definitions in db_types.sql must already be installed.
  15
  16 -- Internal configuration options
  17 CREATE TABLE IF NOT EXISTS regano.config (
  18         key             text PRIMARY KEY,
  19         number          bigint,
  20         interval        interval,
  21         text            text,
  22         CHECK(number IS NOT NULL OR interval IS NOT NULL OR text IS NOT NULL)
  23 ) WITH (fillfactor = 95);
  24
  25 -- Users
  26 CREATE TABLE IF NOT EXISTS regano.users (
  27         id              bigserial PRIMARY KEY,
  28         username        varchar(64) UNIQUE,
  29         password        regano.password,
  30         -- id of primary contact for this user
  31         contact_id      integer NOT NULL DEFAULT 0,
  32         -- timestamp of user registration
  33         registered      timestamp with time zone
  34                                 NOT NULL DEFAULT CURRENT_TIMESTAMP
  35 ) WITH (fillfactor = 90);
  36
  37 -- Active sessions
  38 CREATE TABLE IF NOT EXISTS regano.sessions (
  39         id              uuid PRIMARY KEY,
  40         user_id         bigint NOT NULL REFERENCES regano.users (id),
  41         start           timestamp with time zone
  42                                 NOT NULL DEFAULT CURRENT_TIMESTAMP,
  43         activity        timestamp with time zone
  44                                 NOT NULL DEFAULT CURRENT_TIMESTAMP
  45 );
  46 CREATE INDEX ON regano.sessions (start);
  47
  48 -- Contact information for users and domains
  49 CREATE TABLE IF NOT EXISTS regano.contacts (
  50         owner_id        bigint NOT NULL
  51                                 REFERENCES regano.users (id)
  52                                 ON DELETE CASCADE,
  53         id              integer NOT NULL,
  54         name            text NOT NULL,
  55         email           text NOT NULL,
  56         email_verified  boolean NOT NULL DEFAULT FALSE,
  57         pgp_key         text,
  58         pgp_key_id      text,
  59         PRIMARY KEY(owner_id, id)
  60 ) WITH (fillfactor = 90);
  61 CREATE INDEX ON regano.contacts (owner_id);
  62
  63 ALTER TABLE regano.users ADD CONSTRAINT users_contact_id_fkey
  64         FOREIGN KEY (id, contact_id)
  65                 REFERENCES regano.contacts (owner_id, id)
  66                         ON UPDATE CASCADE
  67                         DEFERRABLE INITIALLY DEFERRED;
  68
  69 -- Email verifications not yet completed
  70 CREATE TABLE IF NOT EXISTS regano.contact_verifications (
  71         id              uuid PRIMARY KEY,
  72         key             uuid NOT NULL,
  73         type            regano.contact_verification_type NOT NULL,
  74         user_id         bigint NOT NULL,
  75         contact_id      integer NOT NULL,
  76         email_sent      boolean NOT NULL DEFAULT FALSE,
  77         start           timestamp with time zone
  78                                 NOT NULL DEFAULT CURRENT_TIMESTAMP,
  79         UNIQUE (user_id, contact_id),
  80         FOREIGN KEY (user_id, contact_id)
  81                 REFERENCES regano.contacts (owner_id, id)
  82                         ON UPDATE CASCADE
  83 );
  84 CREATE INDEX ON regano.contact_verifications (start);
  85
  86 -- Domains under which this instance can process registrations
  87 CREATE TABLE IF NOT EXISTS regano.bailiwicks (
  88         domain_tail     text PRIMARY KEY
  89                                 CHECK(domain_tail LIKE '.%.')
  90 );
  91
  92 -- Domains reserved at second-level, just inside every bailiwick
  93 CREATE TABLE IF NOT EXISTS regano.reserved_domains (
  94         domain_name     regano.dns_label PRIMARY KEY,
  95         reason          text NOT NULL,
  96         CONSTRAINT "Reserved domains must be entered as lowercase"
  97                 CHECK(lower(domain_name) = domain_name)
  98 );
  99
 100 -- Domains pending (pre-registered, user not yet verified, etc.)
 101 CREATE TABLE IF NOT EXISTS regano.pending_domains (
 102         domain_name     regano.dns_label NOT NULL,
 103         domain_tail     regano.dns_fqdn NOT NULL
 104                                 REFERENCES regano.bailiwicks (domain_tail),
 105         PRIMARY KEY(domain_name, domain_tail),
 106         -- An unverified user can only have one domain pending.
 107         -- A verified user immediately registers domains.
 108         -- Pre-registered domains do not have an associated contact.
 109         user_id         bigint UNIQUE
 110                                 REFERENCES regano.users (id),
 111         start           timestamp with time zone
 112                                 DEFAULT CURRENT_TIMESTAMP
 113 );
 114 CREATE UNIQUE INDEX pending_domains_domain_name_domain_tail_lower_case_key
 115         ON regano.pending_domains (lower(domain_name), lower(domain_tail));
 116 CREATE INDEX ON regano.pending_domains (start);
 117
 118 -- Domains registered in this instance
 119 CREATE TABLE IF NOT EXISTS regano.domains (
 120         id              bigserial PRIMARY KEY,
 121         domain_name     regano.dns_label NOT NULL,
 122         domain_tail     regano.dns_fqdn NOT NULL
 123                                 REFERENCES regano.bailiwicks (domain_tail),
 124         UNIQUE(domain_name, domain_tail),
 125         owner_id        bigint NOT NULL REFERENCES regano.users (id),
 126         default_ttl     regano.dns_interval NOT NULL
 127                                 DEFAULT interval '1 day',
 128         registered      timestamp with time zone
 129                                 NOT NULL DEFAULT CURRENT_TIMESTAMP,
 130         expiration      timestamp with time zone NOT NULL,
 131         last_update     timestamp with time zone
 132                                 NOT NULL DEFAULT CURRENT_TIMESTAMP
 133 );
 134 CREATE UNIQUE INDEX domains_domain_name_domain_tail_lower_case_key
 135         ON regano.domains (lower(domain_name), lower(domain_tail));
 136 CREATE INDEX domains_external_name_lower_case_idx
 137         ON regano.domains (lower(domain_name||domain_tail));
 138
 139 -- DNS records hosted by this instance
 140 CREATE TABLE IF NOT EXISTS regano.domain_records (
 141         domain_id       bigint NOT NULL REFERENCES regano.domains (id)
 142                                 ON DELETE CASCADE,
 143         seq_no          bigint NOT NULL CHECK(seq_no >= 0),
 144         class           regano.dns_record_class NOT NULL DEFAULT 'IN',
 145         type            regano.dns_record_type NOT NULL,
 146         ttl             regano.dns_interval,
 147         name            regano.dns_name NOT NULL CHECK(name NOT LIKE '%.'),
 148         -- typed storage for DNS records
 149         data_name               regano.dns_name,
 150         data_text               text,
 151         data_RR_SOA             regano.dns_RR_SOA,
 152         data_RR_A               regano.dns_RR_A,
 153         data_RR_AAAA            regano.dns_RR_AAAA,
 154         data_RR_CERT            regano.dns_RR_CERT,
 155         data_RR_DS              regano.dns_RR_DS,
 156         data_RR_IPSECKEY        regano.dns_RR_IPSECKEY,
 157         data_RR_LOC             regano.dns_RR_LOC,
 158         data_RR_MX              regano.dns_RR_MX,
 159         data_RR_NAPTR           regano.dns_RR_NAPTR,
 160         data_RR_RP              regano.dns_RR_RP,
 161         data_RR_SRV             regano.dns_RR_SRV,
 162         data_RR_SSHFP           regano.dns_RR_SSHFP,
 163         data_RR_TLSA            regano.dns_RR_TLSA,
 164         -- primary key
 165         PRIMARY KEY(domain_id, seq_no),
 166         -- constraints to ensure proper usage
 167         CONSTRAINT "sequence number 0 is reserved for SOA record"
 168             CHECK((seq_no = 0) = (type = 'SOA')),
 169         -- - types using "data_name"
 170         CONSTRAINT "valid DNS name data"
 171             CHECK((type IN ('CNAME', 'DNAME', 'NS', 'PTR'))
 172                     = (data_name IS NOT NULL)),
 173         -- - types using "data_text"
 174         CONSTRAINT "valid DNS text data"
 175             CHECK((type IN ('SPF', 'TXT')) = (data_text IS NOT NULL)),
 176         -- - types using specific fields
 177         -- - - Note that PostgreSQL applies IS NOT NULL
 178         --       to all fields of a composite type.
 179         CONSTRAINT "valid SOA record"
 180             CHECK((type = 'SOA') = (data_RR_SOA IS NOT NULL)),
 181         CONSTRAINT "valid A record"
 182             CHECK((type = 'A') = (data_RR_A IS NOT NULL)),
 183         CONSTRAINT "valid AAAA record"
 184             CHECK((type = 'AAAA') = (data_RR_AAAA IS NOT NULL)),
 185         CONSTRAINT "valid CERT record"
 186             CHECK((type = 'CERT') = (data_RR_CERT IS NOT NULL)),
 187         CONSTRAINT "valid DS record"
 188             CHECK((type = 'DS') = (data_RR_DS IS NOT NULL)),
 189         CONSTRAINT "valid LOC record"
 190             CHECK((type = 'LOC') = (data_RR_LOC IS NOT NULL)),
 191         CONSTRAINT "valid MX record"
 192             CHECK((type = 'MX') = (data_RR_MX IS NOT NULL)),
 193         CONSTRAINT "valid NAPTR record"
 194             CHECK((type = 'NAPTR') = (data_RR_NAPTR IS NOT NULL)),
 195         CONSTRAINT "valid RP record"
 196             CHECK((type = 'RP') = (data_RR_RP IS NOT NULL)),
 197         CONSTRAINT "valid SRV record"
 198             CHECK((type = 'SRV') = (data_RR_SRV IS NOT NULL)),
 199         CONSTRAINT "valid SSHFP record"
 200             CHECK((type = 'SSHFP') = (data_RR_SSHFP IS NOT NULL)),
 201         CONSTRAINT "valid TLSA record"
 202             CHECK((type = 'TLSA') = (data_RR_TLSA IS NOT NULL)),
 203         -- - A and AAAA are validated by type constraints
 204         -- - validation for IPSECKEY
 205         CONSTRAINT "valid IPSECKEY record"
 206             CHECK(type != 'IPSECKEY'
 207                     OR ((data_RR_IPSECKEY).gateway_type = '0:None'
 208                         AND (data_RR_IPSECKEY).gateway_dns IS NULL
 209                         AND (data_RR_IPSECKEY).gateway_inet IS NULL)
 210                     OR ((data_RR_IPSECKEY).gateway_type = '1:IPv4'
 211                         AND (data_RR_IPSECKEY).gateway_dns IS NULL
 212                         AND (data_RR_IPSECKEY).gateway_inet IS NOT NULL
 213                         AND family((data_RR_IPSECKEY).gateway_inet) = 4
 214                         AND masklen((data_RR_IPSECKEY).gateway_inet) = 32)
 215                     OR ((data_RR_IPSECKEY).gateway_type = '2:IPv6'
 216                         AND (data_RR_IPSECKEY).gateway_dns IS NULL
 217                         AND (data_RR_IPSECKEY).gateway_inet IS NOT NULL
 218                         AND family((data_RR_IPSECKEY).gateway_inet) = 6
 219                         AND masklen((data_RR_IPSECKEY).gateway_inet) = 128)
 220                     OR ((data_RR_IPSECKEY).gateway_type = '3:DNS'
 221                         AND (data_RR_IPSECKEY).gateway_dns IS NOT NULL
 222                         AND (data_RR_IPSECKEY).gateway_inet IS NULL)),
 223         -- LOC is validated by a type constraint
 224         -- - validation for SPF
 225         -- TODO: implement validation of SPF data
 226         CONSTRAINT "valid SPF record"
 227             CHECK(type != 'SPF' OR data_text IS NOT NULL)
 228 ) WITH (fillfactor = 90);
 229 CREATE INDEX domain_records_type_idx
 230         ON regano.domain_records (type);
 231
 232
 233 ALTER TABLE regano.config OWNER TO regano;
 234 ALTER TABLE regano.users OWNER TO regano;
 235 ALTER TABLE regano.sessions OWNER TO regano;
 236 ALTER TABLE regano.contacts OWNER TO regano;
 237 ALTER TABLE regano.contact_verifications OWNER TO regano;
 238 ALTER TABLE regano.bailiwicks OWNER TO regano;
 239 ALTER TABLE regano.reserved_domains OWNER TO regano;
 240 ALTER TABLE regano.pending_domains OWNER TO regano;
 241 ALTER TABLE regano.domains OWNER TO regano;
 242 ALTER TABLE regano.domain_records OWNER TO regano;