Appendix A. Technical Details
This appendix describes the Sauron database layout. Sauron database contains database version number stored in settings table. This database version number must match with the version Sauron "back end" was made for. If database format changes in future versions, the database version number will be incremented and a conversion script from previous version will be shipped with Sauron.
Current Sauron Database version is 1.0
Sauron: SQL table descriptions
Tables:
zones(zones.server -> servers.id)
hosts(hosts.zone -> zones.id)
dhcp_entries(dhcp_entries.ref -> hosts.id)
mx_entries(mx_entries.ref -> hosts.id)
ns_entries(ns_entries.ref -> hosts.id)
printer_entries(printer_entries.ref -> hosts.id)
a_entries(a_entries.host -> hosts.id)
txt_entries(txt_entries.ref -> hosts.id)
srv_entries(srv_entries.ref -> hosts.id)
wks_entries(wks_entries.ref -> hosts.id)
arec_entries(arec_entries.host -> hosts.id)
arec_entries(arec_entries.arec -> hosts.id)
cidr_entries(cidr_entries.ref -> zones.id)
dhcp_entries(dhcp_entries.ref -> zones.id)
mx_entries(mx_entries.ref -> zones.id)
mx_templates(mx_templates.zone -> zones.id)
hosts(hosts.mx -> mx_templates.id)
ns_entries(ns_entries.ref -> zones.id)
txt_entries(txt_entries.ref -> zones.id)
groups(groups.server -> servers.id)
hosts(hosts.grp -> groups.id)
dhcp_entries(dhcp_entries.ref -> groups.id)
printer_entries(printer_entries.ref -> groups.id)
nets(nets.server -> servers.id)
dhcp_entries(dhcp_entries.ref -> nets.id)
cidr_entries(cidr_entries.ref -> servers.id)
dhcp_entries(dhcp_entries.ref -> servers.id)
txt_entries(txt_entries.ref -> servers.id)
wks_templates(wks_templates.server -> servers.id)
hosts(hosts.wks -> wks_templates.id)
wks_entries(wks_entries.ref -> wks_templates.id)
root_servers(root_servers.server -> servers.id)
vlans(vlans.server -> servers.id)
nets(nets.vlan -> vlans.id)
printer_entries(printer_entries.ref -> printer_classes.id)
user_rights(user_rights.ref -> users.id)
utmp(utmp.uid -> users.id)
users(users.gid -> user_groups.id)
user_rights(user_rights.ref -> user_groups.id)
utmp(utmp.gid -> user_groups.id)
Table Descriptions
common_fields
virtual table; generic fields for most of the tables
settings
global settings table
servers
This table contains servers that are managed with this system. For each server named/dhcpd/printer configuration files can be automagically generated from the database.
| field | type | comments |
|---|---|---|
| id | SERIAL PRIMARY KEY | unique ID |
| name | TEXT NOT NULL CHECK(name <> '') | server name |
| zones_only | BOOL DEFAULT false | if true, generate named.zones file otherwise generate complete named.conf |
| no_roots | BOOL DEFAULT false | if true, no root server (hint)zone entry is generated |
| dhcp_mode | INT DEFAULT 1 | DHCP subnet map creation mode: 0 = use VLANs,1 = use networks |
| dhcp_flags | INT DEFAULT 0 | DHCP option flags: 0x01 = auto-generate domainnames0x02 = enable failover protocol |
| named_flags | INT DEFAULT 0 | named option flags: 0x01 = access control from master (slave only) 0x02 = include also slave zones from master (slave only) 0x04 = do NOT generate HINFO records0x08 = do NOT generate WKS records |
| masterserver | INT DEFAULT -1 | dynamically add slave zonesfor all zones in master server |
| named.conf options...more to be added as needed... | ||
| version | TEXT | version string to display (optional) |
| directory | TEXT | base directory for named (optional) |
| pid_file | TEXT | pid-file pathname (optional) |
| dump_file | TEXT | dump-file pathname (optiona) |
| named_xfer | TEXT | named-xfer pathname (optional) |
| stats_file | TEXT | statistics-file pathname (optional) |
| memstats_file | TEXT | memstatistics-file pathname (optional) |
| named_ca | TEXT DEFAULT 'named.ca' | root servers filename |
| pzone_path | TEXT DEFAULT '' | relative path for masterzone files |
| szone_path | TEXT DEFAULT 'NS2/' | relative path for slave zone files |
| query_src_ip | TEXT | query source ip (optional) (ip | '*') |
| query_src_port | TEXT | query source port (optional) (port | '*') |
| listen_on_port | TEXT | listen on port (optional) |
| transfer_source | INET | transfer-source (optional) |
| forward | CHAR(1) DEFAULT 'D' | forward: D=defaultO=only, F=first |
| check-names: D=default, W=warn, F=fail, I=ignore | ||
| checknames_m | CHAR(1) DEFAULT 'D' | check-names master |
| checknames_s | CHAR(1) DEFAULT 'D' | check-names slave |
| checknames_r | CHAR(1) DEFAULT 'D' | check-names response |
| boolean flags: D=default, Y=yes, N=no | ||
| nnotify | CHAR(1) DEFAULT 'D' | notify |
| recursion | CHAR(1) DEFAULT 'D' | recursion |
| authnxdomain | CHAR(1) DEFAULT 'D' | auth-nxdomain |
| dialup | CHAR(1) DEFAULT 'D' | dialup |
| multiple_cnames | CHAR(1) DEFAULT 'D' | multiple-cnames |
| rfc2308_type1 | CHAR(1) DEFAULT 'D' | rfc2308-type1 |
| default TTLs | ||
| ttl | INT4 DEFAULT 86400 | default TTL for RR records |
| refresh | INT4 DEFAULT 43200 | default SOA refresh |
| retry | INT4 DEFAULT 3600 | default SOA retry |
| expire | INT4 DEFAULT 2419200 | default SOA expire |
| minimum | INT4 DEFAULT 86400 | default SOA minimum (negative caching ttl) |
| IPv6 | ||
| ipv6 | TEXT | reserved |
| DHCP failover | ||
| df_port | INT DEFAULT 519 | listen port |
| df_max_delay | INT DEFAULT 60 | max-response-delay |
| df_max_uupdates | INT DEFAULT 10 | max-unacked-updates |
| df_mclt | INT DEFAULT 3600 | mlct |
| df_split | INT DEFAULT 128 | split |
| df_loadbalmax | INT DEFAULT 3 | load balance max seconds |
| defaults to use in zones | ||
| hostname | TEXT | primary servername for sibling zone SOAs |
| hostaddr | INET | primary server IP address |
| hostmaster | TEXT | hostmaster name for sibling zone SOAsunless overided in zone |
| comment | TEXT | |
| CONSTRAINT | servers_name_key UNIQUE(name) | |
zones
This table contains zone definitions of a server.
| field | type | comments |
|---|---|---|
| id | SERIAL | unique ID |
| server | INT4 NOT NULL | ptr to a record in servers table-->servers.id |
| active | BOOL DEFAULT true | zone active flag (only active zones are included innamed configuration) |
| dummy | BOOL DEFAULT false | dummy zone flag |
| type | CHAR(1) NOT NULL | zone type: (H)int, (M)aster, (S)lave, (F)orward |
| reverse | BOOL DEFAULT false | true for reverse (arpa) zones |
| noreverse | BOOL DEFAULT false | if true, zone not used in reversemap generation |
| flags | INT DEFAULT 0 | zone option flags: 0x01 = generate TXT records fromuser,dept,location,info fields |
| forward | CHAR(1) DEFAULT 'D' | forward: D=default, O=only, F=first |
| nnotify | CHAR(1) DEFAULT 'D' | notify: D=default, Y=yes, N=no |
| chknames | CHAR(1) DEFAULT 'D' | check-names: D=default, W=warn, F=fail,I=ignore |
| class | CHAR(2) DEFAULT 'in' | zone class (IN) |
| name | TEXT NOT NULL CHECK (name <> '') | zone name |
| hostmaster | TEXT | hostmaster (email) (optional; if not defined value from server tableis used instead) |
| serial | CHAR(10) DEFAULT '1999123001' | zone serial number(automagically updated) |
| serial_date | INT4 DEFAULT 0 | zone serial last update date |
| refresh | INT4 | zone SOA refresh time |
| retry | INT4 | zone SOA retry time |
| expire | INT4 | zone SOA expire time |
| minimum | INT4 | zone SOA minimum (negative caching) time |
| ttl | INT4 | default TTL for RRs in this zone (if not defined, value from servers record isused instead) |
| zone_ttl | INT4 | unused |
| comment | TEXT | |
| reversenet | CIDR | contains CIDR of the reverse zone(if applicaple) |
| parent | INT4 DEFAULT -1 | unused |
| CONSTRAINT | zones_key PRIMARY KEY (name,server) | |
hosts
This table contains host entries for a zone.
| field | type | comments |
|---|---|---|
| id | SERIAL PRIMARY KEY | unique ID |
| zone | INT4 NOT NULL | ptr to a zone table record-->zones.id |
| type | INT4 DEFAULT 0 | host type: 0=misc, 1=host, 2=subdomain (delegation), 3=mx entry, 4=alias (cname), 5=printer, 6=glue record, 7=alias (arec), 8=srv entry, 9=dhcp only, 10=zone, 101=host reservation |
| domain | TEXT NOT NULL CHECK(domain <> '') | host domain name |
| ttl | INT4 | TTL for host records, default if NULL |
| class | CHAR(2) DEFAULT 'IN' | class (IN) |
| grp | INT4 DEFAULT -1 | ptr to group-->groups.id |
| alias | INT4 DEFAULT -1 | ptr to another host record(for CNAME alias) |
| cname_txt | TEXT | CNAME value for out-of-zone alias |
| hinfo_hw | TEXT | HINFO hardware |
| hinfo_sw | TEXT | HINFO software |
| loc | TEXT | LOC record value |
| wks | INT4 DEFAULT -1 | ptr to wks_templates table entry-->wks_templates.id |
| mx | INT4 DEFAULT -1 | ptr to mx_templates table entry-->mx_templates.id |
| rp_mbox | TEXT DEFAULT '.' | RP mbox |
| rp_txt | TEXT DEFAULT '.' | RP txt |
| router | INT4 DEFAULT 0 | router if > 0, also router priority(1 being highest priority) |
| prn | BOOL DEFAULT false | true for virtual printer entries |
| flags | INT4 DEFAULT 0 | reserved |
| ether | CHAR(12) | Ethernet address (MAC) |
| ether_alias | INT4 DEFAULT -1 | ptr to another host record(for ETHER address) |
| dhcp_date | INT4 | last time host requested IP |
| dhcp_info | TEXT | reserved |
| info | TEXT | Host info (appears as TXT record) |
| location | TEXT | Host location info |
| dept | TEXT | Department name |
| huser | TEXT | User info |
| model | TEXT | host model info |
| serial | TEXT | serial number |
| misc | TEXT | misc info |
| asset_id | TEXT | asset ID |
| comment | TEXT | comment |
| CONSTRAINT | hostname_key UNIQUE (domain,zone), | |
| CONSTRAINT | ether_key UNIQUE(ether,zone), | |
| CONSTRAINT | asset_key UNIQUE(asset_id,zone) | |
groups
Group descriptions, linked to server record. Hosts can "belong" to one group and get DHCP/printer/etc definitions from that group.
| field | type | comments |
|---|---|---|
| id | SERIAL PRIMARY KEY | unique ID |
| server | INT4 NOT NULL | ptr to a servers table record-->servers.id |
| name | TEXT NOT NULL CHECK(name <> '') | group name |
| type | INT NOT NULL | group type: 1 = normal group, 2 = dynamic address pool,3 = DHCP client class |
| alevel | INT4 DEFAULT 0 | required authorization level |
| comment | TEXT | |
| CONSTRAINT | groups_key UNIQUE(name,server) | |
nets
Net/subnet descriptions, linked to server record. Used mainly for generating subnet map for DHCP and access control/user friendliness in front-ends.
| field | type | comments |
|---|---|---|
| id | SERIAL | unique ID |
| server | INT4 NOT NULL | ptr to a servers table record-->servers.id |
| netname | TEXT | (sub)net name |
| name | TEXT | descriptive name of the (sub)net |
| net | CIDR NOT NULL | net CIDR |
| subnet | BOOL DEFAULT true | subnet flag |
| vlan | INT4 DEFAULT -1 | ptr to vlans table record-->vlans.id |
| alevel | INT4 DEFAULT 0 | required authorization level |
| type | INT4 DEFAULT 0 | network type/option flags:0x01 = private (hidden from browser) |
| ipv6 | TEXT | reserved |
| rp_mbox | TEXT DEFAULT '.' | RP mbox |
| rp_txt | TEXT DEFAULT '.' | RP txt |
| no_dhcp | BOOL DEFAULT false | no-DHCP flag |
| range_start | INET | auto assign address range start |
| range_end | INET | auto assign address range end |
| comment | TEXT | comment |
| CONSTRAINT | nets_key PRIMARY KEY (net,server) | |
cidr_entries
This table contains CIDRs used in server various contexts.
| field | type | comments |
|---|---|---|
| id | SERIAL PRIMARY KEY | unique ID |
| type | INT4 NOT NULL | type: 1=server (allow-transfer) 2=zone (allow-update) 3=zone (masters) 4=zone (allow-query) 5=zone (allow-transfer) 6=zone (also-notify) 7=server (allow-query) 8=server (allow-recursion) 9=server (blackhole) 10=server (listen-on) 11=server (forwarders)12=zone (forwarders) |
| ref | INT4 NOT NULL | ptr to table speciefied by type field -->servers.id-->zones.id |
| ip | CIDR | CIDR value |
| comment | TEXT |
dhcp_entries
This table contains DHCP options user in various contexts.
| field | type | comments |
|---|---|---|
| id | SERIAL PRIMARY KEY | unique ID |
| type | INT4 NOT NULL | type: 1=server, 2=zone, 3=host, 4=net, 5=group6=vlan (shared-network) |
| ref | INT4 NOT NULL | ptr to table speciefied by type field -->servers.id -->zones.id -->hosts.id -->nets.id-->groups.id |
| dhcp | TEXT | DHCP entry value (without trailing ';') |
| comment | TEXT |
ether_info
This table contains Ethernet adapter manufacturer codes.
mx_entries
This table contains MX record entries.
mx_templates
MX entry templates, hosts may link to one entry in this table. Entries are zone specific.
ns_entries
This table contains NS resource record definitions.
printer_classes
Global table to store printer classes (printcap stuff) these classess maybe referred to in PRINTER fields in other tables.
printer_entries
This table contains printer definition entries.
a_entries
Addresses (A records) for hosts, linked to a host record.
txt_entries
This table contains TXT record entries and miscellaneous text entries.
srv_entries
This table contains MX record entries.
| field | type | comments |
|---|---|---|
| id | SERIAL PRIMARY KEY | unique ID |
| type | INT4 NOT NULL | type:1=host |
| ref | INT4 NOT NULL | ptr to table speciefied by type field-->hosts.id |
| pri | INT4 NOT NULL CHECK (pri >= 0) | priority |
| weight | INT4 NOT NULL CHECK (weight >= 0) | weight |
| port | INT4 NOT NULL CHECK (port >= 0) | port |
| target | TEXT NOT NULL DEFAULT '.' | target |
| comment | TEXT | comment |
users
This table contains (user interface) user account information.
| field | type | comments |
|---|---|---|
| id | SERIAL PRIMARY KEY | unique ID |
| gid | INT4 DEFAULT -1 | ptr to user group -->user_groups.id |
| username | TEXT NOT NULL CHECK(username <> '') | login name |
| password | TEXT | encrypted password (MD5 or Crypt) |
| name | TEXT | long user name |
| TEXT | user email address | |
| superuser | BOOL DEFAULT false | superuser flag |
| server | INT4 DEFAULT -1 | default server id |
| zone | INT4 DEFAULT -1 | default zone id |
| last | INT4 DEFAULT 0 | last login time |
| last_pwd | INT4 DEFAULT 0 | last password change time |
| last_from | TEXT | last login host |
| search_opts | TEXT | default search options |
| flags | INT4 DEFAULT 0 | user account flasgs:0x01 = email notifications on |
| comment | TEXT | |
| CONSTRAINT | username_key UNIQUE(username) | |
user_rights
This table contains record defining user rights.
| field | type | comments |
|---|---|---|
| id | SERIAL PRIMARY KEY | unique ID |
| type | INT NOT NULL | type: 1=user_group2=users |
| ref | INT NOT NULL | ptr to users table specified by type -->user_groups.id-->users.id |
| rtype | INT NOT NULL | type: 1=server, 2=zone, 3=net, 4=hostnamemask 5=IP mask 6=authorization level 7=host expiration limit (days)8=default for dept |
| rref | INT NOT NULL | ptr to table specified by type field |
| rule | CHAR(40) | R,RW,RWS or regexp |
user_groups
This table contains records defining user groups.
wks_entries
This table contains WKS record entries.
wks_templates
WKS entry templates, hosts may link to one entry in this table. Entries are server specific.
utmp
This table contains "utmp" data of currently logged in www-interface users.
| field | type | comments |
|---|---|---|
| cookie | CHAR(32) PRIMARY KEY | session id cookie (MD5) |
| uid | INT4 | ptr to users table record-->users.id |
| gid | INT4 | ptr to user_groups table record-->user_groups.id |
| sid | INT4 | session ID |
| uname | TEXT | username |
| addr | CIDR | user's IP address |
| superuser | BOOL DEFAULT false | superuser flag |
| auth | BOOL DEFAULT false | user authenticated flag |
| mode | INT4 | current status of user |
| w | TEXT | last command user excecuted |
| serverid | INT4 DEFAULT -1 | current server id |
| server | TEXT | current server name |
| zoneid | INT4 DEFAULT -1 | current zone id |
| zone | TEXT | current zone name |
| login | INT4 DEFAULT 0 | login time |
| last | INT4 DEFAULT 0 | last activity time |
| searchopts | TEXT | current search options |
| searchdomain | TEXT | current search domain |
| searchpattern | TEXT | current search pattern |
hinfo_templates
HINFO templates table contains list of default values for HINFO records.
arec_entries
pointers to A record aliased hosts, linked to a host record.
root_servers
This table contains root server definitions.
history
history table contains "log" data of modifications done to the databse
| field | type | comments |
|---|---|---|
| id | SERIAL PRIMARY KEY | unique ID |
| sid | INT NOT NULL | session ID |
| uid | INT NOT NULL | user ID |
| date | INT NOT NULL | date of record |
| type | INT NOT NULL | record type: 1=hosts table modification, 2=zones 3=servers 4=nets5=users |
| ref | INT | optional reference |
| action | CHAR(25) | operation performed |
| info | CHAR(80) | extra info |
lastlog
lastlog table contains "lastlog" data of database users
news
This table contains motd/news to be displayed when user logs in...
vlans
"VLAN" (Layer-2 networks/shared networks) descriptions, linked to server record. Used mainly for generating of shared-network map for DHCP.