1#!/bin/sh 2 3# 4# Netmagis database creation 5# 6# Syntax : 7# netmagis-dbcreate [netmagis] [mac] 8# 9# History 10# 2002/02/11 : pda : design 11# 2002/02/12 : pda/jean : adapt to new data model 12# 2002/04/19 : pda/jean : unique constraint on name+domain 13# 2002/04/19 : pda : unique constraint on domain name 14# 2002/04/23 : pda : add priority in domain permissions 15# 2002/04/23 : pda/jean : add zone generation management 16# 2002/05/02 : pda/jean : use PG objects for zone tables 17# 2002/05/06 : pda : id for zone table 18# 2002/05/21 : pda : add communaute table 19# 2004/01/22 : pda : use new data model 20# 2005/04/08 : pda : use new data model (v 1.3) 21# 2007/11/27 : pda/jean : pgsql 8.2 22# 2007/11/27 : pda/jean : add log table 23# 2011/01/21 : pda : i18n 24# 2011/01/21 : pda : use new config file 25# 2011/07/17 : pda : defuser is given a value and created 26# 2011/12/28 : pda : add dotattr table 27# 2012/01/21 : jean : add p_genl attribute 28# 2012/04/08 : pda : simplify users 29# 2012/04/08 : pda : mac database creation 30# 2012/06/15 : pda/jean : drop -u/-w for an obsolete privileged user 31# 2012/06/19 : pda : don't create copy database 32# 2012/09/20 : pda/jean : remove dnsupdateperiod 33# 2012/09/27 : pda/jean : rename dns.zone.domain 34# 2012/10/24 : pda/jean : dhcp generation is per view 35# 36 37usage () 38{ 39 ( 40 echo "usage: $0 [netmagis] [mac]" 41 echo " netmagis : create netmagis database and schemas" 42 echo " mac : create mac database and schema" 43 ) >&2 44 exit 1 45} 46 47# defuser is used for initial database migration 48defuser="nobody" 49 50############################################################################## 51# Configuration file read 52############################################################################## 53 54init_env () 55{ 56 # Main netmagis database parameters (dnsdb*) 57 varlist="dnsdbhost dnsdbport dnsdbname dnsdbuser dnsdbpassword rootusers" 58 eval `%CONFPROG% -c $varlist` 59 60 if [ $? != 0 ] 61 then 62 usage 63 exit 1 64 fi 65 66 # Mac database parameters (macdb*) 67 varlist="macdbhost macdbport macdbname macdbuser macdbpassword" 68 eval `%CONFPROG% -c $varlist` 69 70 if [ $? != 0 ] 71 then 72 usage 73 exit 1 74 fi 75 76 # These variables are searched in a second pass, since they can be empty 77 # (the first pass used the "-c" flag) 78 varlist="pwgen ouiurl" 79 eval `%CONFPROG% $varlist` 80} 81 82############################################################################## 83# Generic database creation 84############################################################################## 85 86# returns an error if creation failed 87db_create_if_not_exists () 88{ 89 r=0 90 if psql --no-psqlrc -c "" 2> /dev/null 91 then : 92 else 93 # black magic: use template0 to avoid encoding incompatibility 94 createdb -E unicode --template=template0 95 r=$? 96 fi 97 return $r 98} 99 100# $* = schemas to test 101# returns true if schema exists 102db_test_schemas () 103{ 104 schemas=`psql --quiet --no-psqlrc --tuples-only --no-align \ 105 -c "SELECT nspname FROM pg_catalog.pg_namespace"` 106 r=1 107 for i 108 do 109 if echo "$schemas" | grep "^$i$" > /dev/null 110 then 111 ( 112 echo "Error: schema '$i' already exists in database '$PGDATABASE'" 113 echo "Use either 'DROP SCHEMA $i' SQL command to destroy schema" 114 echo "or 'dropdb $PGDATABASE' shell command to destroy database" 115 ) >&2 116 r=0 117 break 118 fi 119 done 120 return $r 121} 122 123############################################################################## 124# SQL file execution 125############################################################################## 126 127# $1 = file (relative to %NMLIBDIR%) 128sql_file_execute () 129{ 130 PGCLIENTENCODING=utf8 131 export PGCLIENTENCODING 132 133 psql --no-psqlrc --quiet --file %NMLIBDIR%/$1 2>&1 \ 134 | grep -v 'NOTICE: .* will create implicit' 135} 136 137############################################################################## 138# Language creation 139############################################################################## 140 141# $1 = language 142# exits if creation failed 143create_language () 144{ 145 if psql --quiet --no-psqlrc -c "CREATE EXTENSION IF NOT EXISTS $1" 146 then : 147 else 148 echo "Unable to create language "$1" in database '$PGDATABASE'" >&2 149 exit 1 150 fi 151} 152 153############################################################################## 154# Create netmagis database 155############################################################################## 156 157create_netmagis () 158{ 159 echo "Creating main Netmagis database as '$dnsdbname'" 160 161 PGHOST="$dnsdbhost" 162 PGPORT="$dnsdbport" 163 PGDATABASE="$dnsdbname" 164 PGUSER="$dnsdbuser" 165 PGPASSWORD="$dnsdbpassword" 166 export PGHOST PGPORT PGDATABASE PGUSER PGPASSWORD 167 168 ############################### 169 # Create main netmagis database 170 ############################### 171 172 if db_create_if_not_exists 173 then : 174 else 175 echo "Erreur while creating database '$PGDATABASE'" 2>&1 176 exit 1 177 fi 178 179 # exit if schema already exist 180 if db_test_schemas "global" "dns" "topo" "pgauth" 181 then 182 exit 1 183 fi 184 185 ############################### 186 # Initialize main netmagis database schemas and contents 187 ############################### 188 189 create_language plpgsql 190 create_language pltcl 191 192 create_netmagis_schemas 193 194 netmagis_insert_config 195 196 ############################### 197 # Create netmagis application users 198 ############################### 199 200 for u in $rootusers 201 do 202 netmagis_create_user $u 203 done 204 205 netmagis_create_user $defuser 206 207 if [ "$pwgen" = "" ] 208 then 209 echo "Netmagis configuration parameter 'pwgen' uninitialized" >&2 210 else 211 echo "Users $rootusers have been created" >&2 212 echo "You can change passwords within netmagis application" >&2 213 fi 214} 215 216############################################################################## 217# Netmagis schema creation 218############################################################################## 219 220create_netmagis_schemas () 221{ 222 for i in schema-main.sql functions.sql triggers.sql 223 do 224 sql_file_execute $i 225 done 226} 227 228############################################################################## 229# Default values for Netmagis config keys 230############################################################################## 231 232netmagis_insert_config () 233{ 234 psql --no-psqlrc --quiet --file - <<'EOF' 235 236 COPY global.config (key, value) FROM stdin; 237datefmt %d/%m/%Y %H:%M:%S 238dayfmt %a %d %b %Y 239authmethod pgsql 240authexpire 36000 241authtoklen 32 242wtmpexpire 365 243pageformat a4 244default_lease_time 600 245max_lease_time 3600 246min_lease_time 300 247topoactive 0 248dhcpdefdomain example.com 249defdomain example.com 250topofrom nobody.topo@example.com 251topoto noc@example.com another@example.com 252topographddelay 5 253toposendddelay 5 254topomaxstatus 100 255sensorexpire 30 256modeqexpire 30 257ifchangeexpire 30 258fullrancidmin 2 259fullrancidmax 4 260macactive 0 261failloginthreshold1 3 262failloginthreshold2 10 263faillogindelay1 120 264faillogindelay2 300 265failipthreshold1 10 266failipthreshold2 30 267failipdelay1 300 268failipdelay2 1200 269casurl https://cas.example.com/cas/ 270ldapurl ldap://ldap.example.com/ 271ldapbinddn cn=admin,ou=accounts,ou=operators,dc=example,dc=com 272ldapbindpw * 273ldapbasedn ou=people,dc=example,dc=com 274ldapsearchlogin (&(objectClass=People)(uid=%s)) 275ldapattrlogin uid 276ldapattrname sn 277ldapattrgivenname givenName 278ldapattrmail mail 279ldapattrphone telephoneNumber 280ldapattrmobile 281ldapattrfax facsimileTelephoneNumber 282ldapattraddr postalAddress postalCode l 283authpgminpwlen 10 284authpgmaxpwlen 128 285authpgmailfrom nobody.netmagis@example.com 286authpgmailreplyto nobody.netmagis@example.com 287authpgmailcc 288authpgmailbcc 289authpgmailsubject Password change 290authpgmailbody Password changed (%2$s) for user %1$s. Best regards. 291authpggroupes netmagis 292schemaversion 23 293\. 294 295 -- id 0 is used in zone creation script 296 COPY dns.hinfo (name, sort, present) FROM stdin; 297Unspecified 0 0 298PC/Windows 10 1 299PC/Unix 20 1 300Macintosh/MacOS 30 1 301Other/Unix 50 1 302Printer 70 1 303Network equipment 100 1 304\. 305 306 COPY dns.view (name, gendhcp) FROM stdin; 307default 0 308\. 309 310 COPY topo.lastrun (date) FROM stdin; 311\N 312\. 313 314 COPY topo.ignoreequsers (login) FROM stdin; 315conf 316\. 317 318 COPY topo.vlan (vlanid, descr) FROM stdin; 3191 default 320\. 321 322 COPY topo.eqtype (type) FROM stdin; 323cisco 324juniper 325hp 326\. 327 328 COPY topo.confcmd (idtype, action, rank, model, command) FROM stdin; 3291 prologue 100 .* configure terminal 3301 ifreset 90 .*29.0.* interface %1$s\ndefault switchport nonegotiate\ndefault switchport trunk allowed vlan\ndefault switchport trunk native vlan\ndefault switchport access vlan\ndefault switchport mode 3311 ifreset 100 .* interface %1$s\nno switchport\nswitchport voice vlan none\nswitchport 3321 ifdisable 100 .* interface %1$s\nshutdown 3331 ifenable 100 .* interface %1$s\nno shutdown 3341 ifaccess 100 .* interface %1$s\nswitchport mode access\nswitchport access vlan %2$s\nspanning-tree portfast 3351 ifvoice 100 .* interface %1$s\nswitchport voice vlan %2$s 3361 ifdesc 100 .* interface %1$s\ndescription %2$s 3371 epilogue 100 .* line con 0\nexit\nexit\nwrite memory 3382 prologue 100 .* configure 3392 ifreset 100 .* delete interfaces %1$s unit 0 family ethernet-switching\ndelete ethernet-switching-options voip interface %1$s 3402 ifdisable 100 .* set interfaces %1$s disable 3412 ifenable 100 .* delete interfaces %1$s disable 3422 ifaccess 100 .* set interfaces %1$s unit 0 family ethernet-switching port-mode access\nset interfaces %1$s unit 0 family ethernet-switching vlan members %2$s 3432 ifdesc 100 .* set interfaces %1$s description "%2$s" 3442 ifvoice 100 .* set interfaces %1$s unit 0 family ethernet-switching\nset ethernet-switching-options voip interface %1$s vlan %2$s 3452 epilogue 100 .* commit\nexit configuration 3463 prologue 100 .* configure terminal 3473 resetvlan 100 .* vlan %2$s\nno tagged %1$s\nno untagged %1$s 3483 ifenable 100 .* interface %1$s\nenable 3493 ifdisable 100 .* interface %1$s\ndisable 3503 ifaccess 100 .* vlan %2$s\nuntagged %1$s 3513 ifvoice 100 .* vlan %2$s\ntagged %1$s 3523 ifdesc 100 .* interface %1$s\nname "%2$s" 3533 epilogue 100 .* vlan 1\nexit\nexit\nwrite memory 354\. 355 356 COPY topo.dotattr (rank, type, regexp, gvattr) FROM stdin; 35710100 2 juniper/M.* shape=circle\nstyle=filled fillcolor=lightgrey\nfixedsize=true height=1 35810200 2 cisco/12000.* shape=doublecircle\nstyle=filled fillcolor=lightgrey\nfixedsize=true height=1 35910300 2 juniper/EX8.* shape=box style=filled fillcolor=lightblue 36010400 2 juniper/Chassis.* shape=box style=filled fillcolor=lightblue 36110500 2 cisco/WS-C45.* shape=box style=filled fillcolor=lightblue 36210600 2 cisco/WS-C37.* shape=box style=filled fillcolor=lightblue height=.25 36310700 2 cisco/WS-C29.* shape=box style=filled fillcolor=lightblue height=.25 36410800 2 cisco/WS-.*PS shape=box style=filled fillcolor=yellow height=.25 36510900 2 cisco/37.* shape=octagon style=filled fillcolor=orange1 height=.25 36611000 2 cisco/38.* shape=octagon style=filled fillcolor=orange1 36711100 2 cisco/.*routeur shape=circle\nstyle=filled fillcolor=lightgrey\nfixedsize=true height=1 36811200 2 cisco/1605.* shape=circle\nstyle=filled fillcolor=lightgrey\nfixedsize=true height=1 36911300 2 cisco/1721.* shape=circle\nstyle=filled fillcolor=lightgrey\nfixedsize=true height=1 37011400 2 cisco/7206.* shape=circle\nstyle=filled fillcolor=lightgrey\nfixedsize=true height=1 37111500 2 juniper/EX2.* shape=box style=filled fillcolor=SteelBlue height=.25 37211600 2 juniper/EX4.* shape=box style=filled fillcolor=SteelBlue height=.25 37311900 2 fwroutebridge.* shape=Mcircle\nstyle=filled fillcolor=tomato\nheight=1 37413000 2 fwroute.* shape=circle\nstyle=filled fillcolor=tomato\nheight=1 37513100 2 fw.* shape=box style=filled fillcolor=tomato height=.25 37613200 2 switch.* shape=box style=filled fillcolor=lightgrey height=.25 37713300 2 hp.* shape=box style=filled fillcolor=pink height=.25 37813400 2 .* shape=triangle 37920100 3 router shape=circle\nstyle=filled fillcolor=lightgrey\nfixedsize=true height=1.5 38020200 3 host shape=box\nstyle=filled fillcolor=lightblue\nheight=.25 38120300 3 cloud shape=ellipse\nstyle=filled fillcolor=palegreen\nwidth=1.5 382\. 383 384 COPY pgauth.realm (realm, descr, admin) FROM stdin; 385authadmin Administrators of internal PostgreSQL auth 1 386netmagis Netmagis users 0 387\. 388 389 COPY global.nmgroup (name, p_admin) FROM stdin; 390wheel 1 391\. 392EOF 393 394 psql --no-psqlrc --quiet -c "INSERT INTO global.config (key, value) 395 VALUES ('defuser', '$defuser')" 396} 397 398############################################################################## 399# Create Netmagis users 400############################################################################## 401 402netmagis_create_user () 403{ 404 user="$1" 405 psql --no-psqlrc --quiet --file - <<EOF 406 INSERT INTO pgauth.user (login, password, lastname, firstname) 407 VALUES ('$user', '*', 'Boss', 'Joe') ; 408 INSERT INTO pgauth.member (login, realm) VALUES ('$user', 'authadmin') ; 409 INSERT INTO pgauth.member (login, realm) VALUES ('$user', 'netmagis') ; 410 INSERT INTO global.nmuser (login, present, idgrp) VALUES ('$user', 1, 1) ; 411EOF 412 413 if [ "$pwgen" != "" ] 414 then 415 adminpw=$($pwgen) 416 cryptpw=$(openssl passwd -1 "$adminpw") 417 psql --no-psqlrc --quiet -c "UPDATE pgauth.user 418 SET password = '$cryptpw' 419 WHERE login = '$user'" 420 echo "Password for Netmagis user '$user' is '$adminpw'." >&2 421 fi 422 423} 424 425############################################################################## 426# Create mac database 427############################################################################## 428 429create_mac () 430{ 431 echo "Creating MAC database as '$macdbname'" 432 433 PGHOST="$macdbhost" 434 PGPORT="$macdbport" 435 PGDATABASE="$macdbname" 436 PGUSER="$macdbuser" 437 PGPASSWORD="$macdbpassword" 438 export PGHOST PGPORT PGDATABASE PGUSER PGPASSWORD 439 440 ############################### 441 # Create mac database 442 ############################### 443 444 if db_create_if_not_exists 445 then : 446 else 447 echo "Erreur while creating database '$PGDATABASE'" 2>&1 448 exit 1 449 fi 450 451 # exit if schema already exist 452 if db_test_schemas "mac" 453 then 454 exit 1 455 fi 456 457 ############################### 458 # Initialize mac database schemas and contents 459 ############################### 460 461 create_mac_schemas 462 insert_mac_oui 463} 464 465############################################################################## 466# Mac database 467############################################################################## 468 469create_mac_schemas () 470{ 471 sql_file_execute schema-mac.sql 472} 473 474insert_mac_oui () 475{ 476 %SBINDIR%/netmagis-getoui 477} 478 479############################################################################## 480# Main program 481############################################################################## 482 483# 484# Initialize environnement 485# 486 487init_env 488 489# 490# Syntax checking 491# 492 493args=`getopt h $*` 494 495if [ $? != 0 ] 496then usage 497fi 498 499set -- $args 500 501while true 502do 503 case "$1" in 504 -h) usage 505 exit 0 506 ;; 507 --) shift 508 break 509 ;; 510 esac 511done 512 513if [ $# = 0 ] 514then set netmagis mac 515fi 516 517# 518# Check actions 519# 520 521for action 522do 523 case "$action" in 524 netmagis|mac) ;; 525 *) 526 echo "Unknown action '$action'" >&2 527 usage 528 exit 1 529 ;; 530 esac 531done 532 533# 534# Do actions 535# 536 537for action 538do 539 case "$action" in 540 netmagis) create_netmagis ;; 541 mac) create_mac ;; 542 esac 543done 544 545 546exit 0 547