From 3ad50effa2b0caa4ee742ca6e30a70cbe1077878 Mon Sep 17 00:00:00 2001 From: christopherm Date: Fri, 3 Nov 2006 05:13:21 +0000 Subject: moving all user preferences into the central db. This will break current test installs or anyone running HEAD. Please see ledger-smb.conf. You will also need to create the central db (using Pg-central.sql) and set the admin user password (md5(something)). More info to be given on the legdger-smb-devel mailing list git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@479 4979c152-3d1c-0410-bac9-87ea11338e46 --- LedgerSMB/AM.pm | 4 +- LedgerSMB/Session/DB.pm | 32 ++--- LedgerSMB/User.pm | 337 +++++++++++++++++++++++++----------------------- admin.pl | 14 +- bin/admin.pl | 214 +++++++----------------------- bin/am.pl | 2 +- bin/arapprn.pl | 4 +- bin/hr.pl | 4 +- bin/login.pl | 86 ++++++------ ledger-smb.conf | 4 +- login.pl | 13 +- menu.pl | 30 +++-- sql/Pg-central.sql | 79 ++++++++++++ sql/Pg-database.sql | 74 ----------- 14 files changed, 404 insertions(+), 493 deletions(-) create mode 100755 sql/Pg-central.sql diff --git a/LedgerSMB/AM.pm b/LedgerSMB/AM.pm index a8fd1e63..a066f9af 100755 --- a/LedgerSMB/AM.pm +++ b/LedgerSMB/AM.pm @@ -1335,7 +1335,7 @@ sub save_preferences { $dbh->commit; - my $myconfig = LedgerSMB::User->new("${LedgerSMB::Sysconfig::memberfile}", "$form->{login}"); + my $myconfig = LedgerSMB::User->new($form->{login}); foreach my $item (keys %$form) { $myconfig->{$item} = $form->{$item}; @@ -1343,7 +1343,7 @@ sub save_preferences { $myconfig->{password} = $form->{new_password} if ($form->{old_password} ne $form->{new_password}); - $myconfig->save_member(${LedgerSMB::Sysconfig::memberfile}, ${LedgerSMB::Sysconfig::userspath}); + $myconfig->save_member(); 1; diff --git a/LedgerSMB/Session/DB.pm b/LedgerSMB/Session/DB.pm index 7b59a718..1f215b13 100755 --- a/LedgerSMB/Session/DB.pm +++ b/LedgerSMB/Session/DB.pm @@ -28,11 +28,11 @@ package Session; sub session_check { - my ($cookie, $form, %myconfig) = @_; + my ($cookie, $form) = @_; my ($sessionid, $token) = split /:/, $cookie; - # connect to database - my $dbh = DBI->connect($myconfig{dbconnect}, $myconfig{dbuser}, $myconfig{dbpasswd}); + # use the central database handle + my $dbh = ${LedgerSMB::Sysconfig::GLOBALDBH}; my $checkQuery = $dbh->prepare("SELECT sl_login FROM session WHERE session_id = ? AND token = ? AND last_used > now() - ?::interval"); @@ -53,7 +53,7 @@ sub session_check { } $checkQuery->execute($sessionid, $token, $timeout) - || $form->dberror('Looking for session: '); + || $form->dberror(__FILE__.':'.__LINE__.': Looking for session: '); my $sessionValid = $checkQuery->rows; if($sessionValid){ @@ -65,7 +65,7 @@ sub session_check { $login =~ s/[^a-zA-Z0-9@.-]//g; if($sessionLogin eq $login){ - $updateAge->execute($sessionid) || $form->dberror('Updating session age: '); + $updateAge->execute($sessionid) || $form->dberror(__FILE__.':'.__LINE__.': Updating session age: '); return 1; } else { @@ -85,10 +85,10 @@ sub session_check { } sub session_create { - my ($form, %myconfig) = @_; + my ($form) = @_; - # connect to database - my $dbh = DBI->connect($myconfig{dbconnect}, $myconfig{dbuser}, $myconfig{dbpasswd}); + # use the central database handle + my $dbh = ${LedgerSMB::Sysconfig::GLOBALDBH}; # TODO Change this to use %myconfig my $deleteExisting = $dbh->prepare("DELETE FROM session WHERE sl_login = ? AND age(last_used) > ?::interval"); @@ -110,19 +110,19 @@ sub session_create { $myconfig{timeout} = 86400; } - $deleteExisting->execute($login, "$myconfig{timeout} seconds") || $form->dberror('Delete from session: '); + $deleteExisting->execute($login, "$myconfig{timeout} seconds") || $form->dberror(__FILE__.':'.__LINE__.': Delete from session: '); #doing the md5 and random stuff in the db so that LedgerSMB won't #require new perl modules (Digest::MD5 and a good random generator) - $fetchSequence->execute() || $form->dberror('Fetch sequence id: '); + $fetchSequence->execute() || $form->dberror(__FILE__.':'.__LINE__.': Fetch sequence id: '); my ($newSessionID, $newToken) = $fetchSequence->fetchrow_array; #create a new session - $createNew->execute($newSessionID, $login, $newToken) || $form->dberror('Create new session: '); + $createNew->execute($newSessionID, $login, $newToken) || $form->dberror(__FILE__.':'.__LINE__.': Create new session: '); #reseed the random number generator my $randomSeed = 1.0 * ('0.'. (time() ^ ($$ + ($$ <<15)))); - $seedRandom->execute($randomSeed)|| $form->dberror('Reseed random generator: ');; + $seedRandom->execute($randomSeed)|| $form->dberror(__FILE__.':'.__LINE__.': Reseed random generator: '); $newCookieValue = $newSessionID . ':' . $newToken; @@ -139,16 +139,16 @@ sub session_destroy { # which means that the db connection parameters are not available. # moving user prefs and the session table into a central db will solve this issue - my ($form, %myconfig) = @_; + my ($form) = @_; my $login = $form->{login}; $login =~ s/[^a-zA-Z0-9@.-]//g; - # connect to database - my $dbh = DBI->connect($myconfig{dbconnect}, $myconfig{dbuser}, $myconfig{dbpasswd}); + # use the central database handle + my $dbh = ${LedgerSMB::Sysconfig::GLOBALDBH}; my $deleteExisting = $dbh->prepare("DELETE FROM session WHERE sl_login = ?;"); - $deleteExisting->execute($login) || $form->dberror('Delete from session: '); + $deleteExisting->execute($login) || $form->dberror(__FILE__.':'.__LINE__.': Delete from session: '); #delete the cookie in the browser print qq|Set-Cookie: LedgerSMB=; path=/;\n|; diff --git a/LedgerSMB/User.pm b/LedgerSMB/User.pm index 67249a04..18f4e8d9 100755 --- a/LedgerSMB/User.pm +++ b/LedgerSMB/User.pm @@ -33,47 +33,47 @@ package LedgerSMB::User; use LedgerSMB::Sysconfig; - +use Data::Dumper; sub new { - my ($type, $memfile, $login) = @_; + + my ($type, $login) = @_; my $self = {}; if ($login ne "") { - &error("", "$memfile locked!") if (-f "${memfile}.LCK"); - - open(MEMBER, "$memfile") or &error("", "$memfile : $!"); - - while () { - if (/^\[$login\]/) { - while () { - last if /^\[/; - next if /^(#|\s)/; - - # remove comments - - s/^\s*#.*//g; - # remove any trailing whitespace - s/^\s*(.*?)\s*$/$1/; + # use central db + my $dbh = ${LedgerSMB::Sysconfig::GLOBALDBH}; + + # for now, this is querying the table directly... ugly + my $fetchUserPrefs = $dbh->prepare("SELECT acs, address, businessnumber, + company, countrycode, currency, + dateformat, dbconnect, dbdriver, + dbhost, dbname, dboptions, dbpasswd, + dbport, dbuser, email, fax, menuwidth, + name, numberformat, password, print, + printer, role, sid, signature, stylesheet, + tel, templates, timeout, vclimit, u.username + FROM users_conf as uc, users as u + WHERE u.username = ? + AND u.id = uc.id;"); - ($key, $value) = split /=/, $_, 2; - - $self->{$key} = $value; - } - - $self->{login} = $login; + $fetchUserPrefs->execute($login); - last; - } + my $userHashRef = $fetchUserPrefs->fetchrow_hashref; + + while ( my ($key, $value) = each(%{$userHashRef}) ) { + $self->{$key} = $value; + } + + if($self->{username}){ + $self->{login} = $login; } - close MEMBER; } bless $self, $type; } - sub country_codes { use Locale::Country; use Locale::Language; @@ -99,43 +99,71 @@ sub country_codes { } +sub fetch_config { +#I'm hoping that this function will go and is a temporary bridge +#until we get rid of %myconfig elsewhere in the code + + my ($self, $login) = @_; + + if ($login ne "") { + + # use central db + my $dbh = ${LedgerSMB::Sysconfig::GLOBALDBH}; + + # for now, this is querying the table directly... ugly + my $fetchUserPrefs = $dbh->prepare("SELECT acs, address, businessnumber, + company, countrycode, currency, + dateformat, dbconnect, dbdriver, + dbhost, dbname, dboptions, dbpasswd, + dbport, dbuser, email, fax, menuwidth, + name, numberformat, password, print, + printer, role, sid, signature, stylesheet, + tel, templates, timeout, vclimit + FROM users_conf as uc, users as u + WHERE u.username = ? + AND u.id = uc.id;"); + + $fetchUserPrefs->execute($login); + + my $userHashRef = $fetchUserPrefs->fetchrow_hashref; + + while ( my ($key, $value) = each(%{$userHashRef}) ) { + $myconfig{$key} = $value; + } + } + + return \%myconfig; +} sub login { + use Digest::MD5; + my ($self, $form) = @_; my $rc = -1; if ($self->{login} ne "") { + if ($self->{password} ne (Digest::MD5::md5_hex $form->{password}) ) { + return -1; + } - if ($self->{password} ne "") { - my $password = - crypt $form->{password}, - substr($self->{login}, 0, 2); - if ($self->{password} ne $password) { - return -1; - } + #this is really dumb, but %myconfig will have to stay until 1.3 + while ( my ($key, $value) = each(%{$self}) ) { + $myconfig{$key} = $value; } - - #there shouldn't be any harm in always doing this. - #It might even un-bork things. - $self->create_config( - "${LedgerSMB::Sysconfig::userspath}/$self->{login}.conf"); - - do "${LedgerSMB::Sysconfig::userspath}/$self->{login}.conf"; - $myconfig{dbpasswd} = unpack 'u', $myconfig{dbpasswd}; - + # check if database is down my $dbh = DBI->connect( $myconfig{dbconnect}, $myconfig{dbuser}, $myconfig{dbpasswd}) - or $self->error($DBI::errstr); + or $self->error(__FILE__.':'.__LINE__.': '.$DBI::errstr); # we got a connection, check the version my $query = qq| SELECT value FROM defaults WHERE setting_key = 'version'|; my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + $sth->execute || $form->dberror(__FILE__.':'.__LINE__.$query); my ($dbversion) = $sth->fetchrow_array; $sth->finish; @@ -195,11 +223,9 @@ sub login { sub check_recurring { my ($self, $form) = @_; - $self->{dbpasswd} = unpack 'u', $self->{dbpasswd}; - my $dbh = DBI->connect( $self->{dbconnect}, $self->{dbuser}, $self->{dbpasswd}) - or $form->dberror; + or $form->dberror(__FILE__.':'.__LINE__); my $query = qq| SELECT count(*) FROM recurring @@ -265,14 +291,14 @@ sub dbsources { my $dbh = DBI->connect( $form->{dbconnect}, $form->{dbuser}, $form->{dbpasswd}) - or $form->dberror; + or $form->dberror(__FILE__.':'.__LINE__); if ($form->{dbdriver} eq 'Pg') { $query = qq|SELECT datname FROM pg_database|; $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + $sth->execute || $form->dberror(__FILE__.':'.__LINE__.$query); while (my ($db) = $sth->fetchrow_array) { @@ -284,7 +310,7 @@ sub dbsources { my $dbh = DBI->connect( $form->{dbconnect}, $form->{dbuser}, $form->{dbpasswd}) - or $form->dberror; + or $form->dberror(__FILE__.':'.__LINE__); $query = qq| SELECT tablename FROM pg_tables @@ -292,7 +318,7 @@ sub dbsources { AND tableowner = ?|; my $sth = $dbh->prepare($query); $sth->execute($form->{dbuser}) - || $form->dberror($query); + || $form->dberror(__FILE__.':'.__LINE__.$query); if ($sth->fetchrow_array) { push @dbsources, $db; @@ -330,9 +356,9 @@ sub dbcreate { $form->{dbconnect}, $form->{dbsuperuser}, $form->{dbsuperpasswd}) - or $form->dberror; + or $form->dberror(__FILE__.':'.__LINE__); my $query = qq|$dbcreate{$form->{dbdriver}}|; - $superdbh->do($query) || $form->dberror($query); + $superdbh->do($query) || $form->dberror(__FILE__.':'.__LINE__.$query); $superdbh->disconnect; } @@ -344,13 +370,13 @@ sub dbcreate { $form->{dbconnect}, $form->{dbuser}, $form->{dbpasswd}) - or $form->dberror; + or $form->dberror(__FILE__.':'.__LINE__); if ($form->{dbsuperuser}){ my $superdbh = DBI->connect( $form->{dbconnect}, $form->{dbsuperuser}, $form->{dbsuperpasswd}) - or $form->dberror; + or $form->dberror(__FILE__.':'.__LINE__); # JD: We need to check for plpgsql, # if it isn't there create it, if we can't error # Good chance I will have to do this twice as I get @@ -400,12 +426,12 @@ sub process_query { return unless (-f $filename); - open(FH, "$filename") or $form->error("$filename : $!\n"); + open(FH, "$filename") or $form->error(__FILE__.':'.__LINE__.": $filename : $!\n"); $ENV{PGPASSWORD} = $form->{dbpasswd}; $ENV{PGUSER} = $form->{dbuser}; $ENV{PGDATABASE} = $form->{db}; - open(PSQL, "| psql") or $form->error("psql : $! \n"); + open(PSQL, "| psql") or $form->error(__FILE__.':'.__LINE__.": psql : $! \n"); print PSQL "\\o spool/log \n"; while (){ print PSQL $_; @@ -423,9 +449,9 @@ sub dbdelete { &dbconnect_vars($form, $form->{dbdefault}); my $dbh = DBI->connect( $form->{dbconnect}, $form->{dbuser}, $form->{dbpasswd}) - or $form->dberror; + or $form->dberror(__FILE__.':'.__LINE__); my $query = qq|DROP DATABASE "$form->{db}"|; - $dbh->do($query) || $form->dberror($query); + $dbh->do($query) || $form->dberror(__FILE__.':'.__LINE__.$query); $dbh->disconnect; @@ -439,10 +465,10 @@ sub dbsources_unused { my @dbexcl = (); my @dbsources = (); - $form->error("$memfile locked!") if (-f "${memfile}.LCK"); + $form->error(__FILE__.':'.__LINE__.": $memfile locked!") if (-f "${memfile}.LCK"); # open members file - open(FH, "$memfile") or $form->error("$memfile : $!"); + open(FH, "$memfile") or $form->error(__FILE__.':'.__LINE__.": $memfile : $!"); while () { if (/^dbname=/) { @@ -480,7 +506,7 @@ sub dbneedsupdate { my $dbh = DBI->connect( $form->{dbconnect}, $form->{dbuser}, $form->{dbpasswd}) - or $form->dberror; + or $form->dberror(__FILE__.':'.__LINE__); if ($form->{dbdriver} =~ /Pg/) { @@ -490,7 +516,7 @@ sub dbneedsupdate { WHERE d.datdba = u.usesysid AND u.usename = ?|; my $sth = $dbh->prepare($query); - $sth->execute($form->{dbuser}) || $form->dberror($query); + $sth->execute($form->{dbuser}) || $form->dberror(__FILE__.':'.__LINE__.$query); while (my ($db) = $sth->fetchrow_array) { @@ -501,14 +527,14 @@ sub dbneedsupdate { my $dbh = DBI->connect( $form->{dbconnect}, $form->{dbuser}, $form->{dbpasswd}) - or $form->dberror; + or $form->dberror(__FILE__.':'.__LINE__); $query = qq| SELECT tablename FROM pg_tables WHERE tablename = 'defaults'|; my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + $sth->execute || $form->dberror(__FILE__.':'.__LINE__.$query); if ($sth->fetchrow_array) { $query = qq| @@ -548,7 +574,7 @@ sub dbupdate { if ($form->{dbupdate}) { # read update scripts into memory - opendir SQLDIR, "sql/." or $form->error($!); + opendir SQLDIR, "sql/." or $form->error(__FILE__.':'.__LINE__.': '.$!); @upgradescripts = sort script_version grep /$form->{dbdriver}-upgrade-.*?\.sql$/, @@ -568,7 +594,7 @@ sub dbupdate { my $dbh = DBI->connect( $form->{dbconnect}, $form->{dbuser}, $form->{dbpasswd}, {AutoCommit => 0}) - or $form->dberror; + or $form->dberror(__FILE__.':'.__LINE__); # check version $query = qq| @@ -657,114 +683,102 @@ sub script_version { } - -sub create_config { - my ($self, $filename) = @_; - - - @config = &config_vars; - - open(CONF, ">$filename") or $self->error("$filename : $!"); - - # create the config file - print CONF qq|# configuration file for $self->{login} - -\%myconfig = ( -|; - - foreach $key (sort @config) { - $self->{$key} =~ s/\\/\\\\/g; - $self->{$key} =~ s/'/\\'/g; - - #remaining conversion from SL - $self->{$key} =~ s/sql-ledger([^.]*)\.css/ledger-smb$1.css/g; - print CONF qq| $key => '$self->{$key}',\n|; - } - - - print CONF qq|);\n\n|; - - close CONF; - -} - - sub save_member { - my ($self) = @_; - - # format dbconnect and dboptions string - &dbconnect_vars($self, $self->{dbname}); - $self->error("${LedgerSMB::Sysconfig::memberfile} locked!") - if (-f "${LedgerSMB::Sysconfig::memberfile}.LCK"); - open(FH, ">${LedgerSMB::Sysconfig::memberfile}.LCK") - or $self->error("${LedgerSMB::Sysconfig::memberfile}.LCK : $!"); - close(FH); + my ($self) = @_; - if (! open(CONF, "+<${LedgerSMB::Sysconfig::memberfile}")) { - unlink "${LedgerSMB::Sysconfig::memberfile}.LCK"; - $self->error("${LedgerSMB::Sysconfig::memberfile} : $!"); - } + # replace \r\n with \n + for (qw(address signature)) { $self->{$_} =~ s/\r?\n/\\n/g } - @config = ; + # use central db + my $dbh = ${LedgerSMB::Sysconfig::GLOBALDBH}; - seek(CONF, 0, 0); - truncate(CONF, 0); + #check to see if the user exists already + my $userCheck = $dbh->prepare("SELECT id FROM users WHERE username = ?"); + $userCheck->execute($self->{login}); + my ($userID) = $userCheck->fetchrow_array; - while ($line = shift @config) { - last if ($line =~ /^\[$self->{login}\]/); - #remaining conversion from SL - $line =~ s/sql-ledger([^.]*)\.css/ledger-smb$1.css/g; - print CONF $line; - } + if($userID){ + #got an id, check to see if it's in the users_conf table + my $userConfCheck = $dbh->prepare("SELECT id FROM users_conf WHERE id = ?"); + $userConfCheck->execute($userID); - # remove everything up to next login or EOF - while ($line = shift @config) { - last if ($line =~ /^\[/); + if($userConfCheck->rows){ + my $userConfExists = 1; + } } - - # this one is either the next login or EOF - print CONF $line; - - while ($line = shift @config) { - print CONF $line; + else{ + my $userConfAdd = $dbh->prepare("SELECT create_user(?);"); + $userConfAdd->execute($self->{login}); + ($userID) = $userConfAdd->fetchrow_array; } - print CONF qq|[$self->{login}]\n|; - - if ($self->{packpw}) { - $self->{dbpasswd} = pack 'u', $self->{dbpasswd}; - chop $self->{dbpasswd}; - } - if ($self->{password} ne $self->{old_password}) { - $self->{password} = crypt $self->{password}, - substr($self->{login}, 0, 2) if $self->{password}; - } + if($userConfExists){ + + # for now, this is updating the table directly... ugly + my $userConfUpdate = $dbh->prepare("UPDATE users_conf + SET acs = ?, address = ?, businessnumber = ?, + company = ?, countrycode = ?, currency = ?, + dateformat = ?, dbconnect = ?, dbdriver = ?, + dbhost = ?, dbname = ?, dboptions = ?, + dbpasswd = ?, dbport = ?, dbuser = ?, + email = ?, fax = ?, menuwidth = ?, + name = ?, numberformat = ?, password = md5(?), + print = ?, printer = ?, role = ?, + sid = ?, signature = ?, stylesheet = ?, + tel = ?, templates = ?, timeout = ?, + vclimit = ? + WHERE id = ?;"); + + $userConfUpdate->execute($self->{acs}, $self->{address}, $self->{businessnumber}, + $self->{company}, $self->{countrycode}, $self->{currency}, + $self->{dateformat}, $self->{dbconnect}, $self->{dbdriver}, + $self->{dbhost}, $self->{dbname}, $self->{dboptions}, + $self->{dbpasswd}, $self->{dbport}, $self->{dbuser}, + $self->{email}, $self->{fax}, $self->{menuwidth}, + $self->{name}, $self->{numberformat}, $self->{password}, + $self->{print}, $self->{printer}, $self->{role}, + $self->{sid}, $self->{signature}, $self->{stylesheet}, + $self->{tel}, $self->{templates}, $self->{timeout}, + $self->{vclimit}, $userID); + - if ($self->{'root login'}) { - @config = qw(password); - } else { - @config = &config_vars; } - - # replace \r\n with \n - for (qw(address signature)) { $self->{$_} =~ s/\r?\n/\\n/g } - - for (sort @config) { - print CONF qq|$_=$self->{$_}\n| + else{ + + my $userConfInsert = $dbh->prepare("INSERT INTO users_conf(acs, address, businessnumber, + company, countrycode, currency, + dateformat, dbconnect, dbdriver, + dbhost, dbname, dboptions, dbpasswd, + dbport, dbuser, email, fax, menuwidth, + name, numberformat, print, printer, role, + sid, signature, stylesheet, tel, templates, + timeout, vclimit, id, password) + VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, + ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, + ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, md5(?));"); + + $userConfInsert->execute($self->{acs}, $self->{address}, $self->{businessnumber}, + $self->{company}, $self->{countrycode}, $self->{currency}, + $self->{dateformat}, $self->{dbconnect}, $self->{dbdriver}, + $self->{dbhost}, $self->{dbname}, $self->{dboptions}, + $self->{dbpasswd}, $self->{dbport}, $self->{dbuser}, + $self->{email}, $self->{fax}, $self->{menuwidth}, + $self->{name}, $self->{numberformat}, + $self->{print}, $self->{printer}, $self->{role}, + $self->{sid}, $self->{signature}, $self->{stylesheet}, + $self->{tel}, $self->{templates}, $self->{timeout}, + $self->{vclimit}, $userID, $self->{password}); + } - print CONF "\n"; - close CONF; - unlink "${LedgerSMB::Sysconfig::memberfile}.LCK"; - # create conf file - if (! $self->{'root login'}) { - - $self->create_config("${LedgerSMB::Sysconfig::userspath}/$self->{login}.conf"); + if (! $self->{'admin'}) { $self->{dbpasswd} =~ s/\\'/'/g; $self->{dbpasswd} =~ s/\\\\/\\/g; - $self->{dbpasswd} = unpack 'u', $self->{dbpasswd}; + + # format dbconnect and dboptions string + &dbconnect_vars($self, $self->{dbname}); # check if login is in database my $dbh = DBI->connect( @@ -813,7 +827,6 @@ sub save_member { $dbh->disconnect; } - } @@ -823,13 +836,13 @@ sub delete_login { my $dbh = DBI->connect( $form->{dbconnect}, $form->{dbuser}, $form->{dbpasswd}, {AutoCommit => 0}) - or $form->dberror; + or $form->dberror(__FILE__.':'.__LINE__); my $login = $form->{login}; $login =~ s/@.*//; my $query = qq|SELECT id FROM employee WHERE login = ?|; my $sth = $dbh->prepare($query); - $sth->execute($login) || $form->dberror($query); + $sth->execute($login) || $form->dberror(__FILE__.':'.__LINE__.': '.$query); my ($id) = $sth->fetchrow_array; $sth->finish; diff --git a/admin.pl b/admin.pl index 285b7f1a..b8551e04 100755 --- a/admin.pl +++ b/admin.pl @@ -75,13 +75,13 @@ $0 =~ tr/\\/\//; $pos = rindex $0, '/'; $script = substr($0, $pos + 1); - -if (-e "${LedgerSMB::Sysconfig::userspath}/nologin" && $script ne 'admin.pl') { - print "Content-Type: text/html\n\n"; - print "\nLogin disabled!\n"; - print "\n"; - exit; -} +#this needs to be a db based function +#if (-e "${LedgerSMB::Sysconfig::userspath}/nologin" && $script ne 'admin.pl') { +# print "Content-Type: text/html\n\n"; +# print "\nLogin disabled!\n"; +# print "\n"; +# exit; +#} if ($form{path}) { diff --git a/bin/admin.pl b/bin/admin.pl index 1922e692..222237e6 100755 --- a/bin/admin.pl +++ b/bin/admin.pl @@ -39,7 +39,7 @@ $menufile = "menu.ini"; use LedgerSMB::Form; use LedgerSMB::Locale; use LedgerSMB::User; - +use LedgerSMB::Session; $form = new Form; @@ -65,11 +65,7 @@ if (-f "bin/custom/$form->{script}") { } - - - if ($form->{action}) { - &check_password unless $form->{action} eq 'logout'; &{ $form->{action} }; @@ -78,20 +74,9 @@ if ($form->{action}) { # if there are no drivers bail out $form->error($locale->text('No Database Drivers available!')) unless (LedgerSMB::User->dbdrivers); - # create memberfile - if (! -f ${LedgerSMB::Sysconfig::memberfile}) { - open(FH, ">${LedgerSMB::Sysconfig::memberfile}") or $form->error("$memberfile : $!"); - print FH qq|# LedgerSMB Accounting members + $root = LedgerSMB::User->new('admin'); -[root login] -password= -|; - close FH; - } - - $root = LedgerSMB::User->new("${LedgerSMB::Sysconfig::memberfile}", "root login"); - - unless($root && $root->{password}) { + unless($root && $root->{password}){ &setup_initial_password(); exit; } @@ -193,8 +178,7 @@ sub login { sub logout { $form->{callback} = "$form->{script}?path=$form->{path}&endsession=1"; - unlink "${LedgerSMB::Sysconfig::userspath}/adminhash"; - print qq|Set-Cookie: LedgerSMB=; path=/;\n|; + Session::session_destroy($form); $form->redirect($locale->text('You are logged out')); } @@ -249,14 +233,12 @@ sub form_footer { sub list_users { - open(FH, "${LedgerSMB::Sysconfig::memberfile}") or $form->error("$memberfile : $!"); - - $nologin = qq||; - - if (-e "${LedgerSMB::Sysconfig::userspath}/nologin") { - $nologin = qq||; - } - + #currently, this is disabled, but will set a value in the central db + #$nologin = qq||; + # + #if (-e "${LedgerSMB::Sysconfig::userspath}/nologin") { + # $nologin = qq||; + #} while () { chop; @@ -294,7 +276,7 @@ sub list_users { $form->{title} = "LedgerSMB ".$locale->text('Accounting')." ".$locale->text('Administration'); - $form->{login} = "root login"; + $form->{login} = "admin"; $form->header; print qq| @@ -373,7 +355,7 @@ sub form_header { if ($form->{login}) { # get user - $myconfig = LedgerSMB::User->new("${LedgerSMB::Sysconfig::memberfile}", "$form->{login}"); + %myconfig = %{LedgerSMB::User->fetch_config($form->{login})}; for (qw(company address signature)) { $myconfig->{$_} = $form->quote($myconfig->{$_}) } for (qw(address signature)) { $myconfig->{$_} =~ s/\\n/\n/g } @@ -486,7 +468,7 @@ sub form_header { } $user = $form->{login}; - $form->{login} = "root login"; + $form->{login} = "admin"; $form->header; $form->{login} = $user; @@ -769,7 +751,7 @@ sub save { # check for duplicates if (!$form->{edit}) { - $temp = LedgerSMB::User->new("${LedgerSMB::Sysconfig::memberfile}", "$form->{login}"); + $temp = LedgerSMB::User->new($form->{login}); if ($temp->{login}) { $form->error($locale->text('[_1] is already a member!', $form->{login})); @@ -793,7 +775,6 @@ sub save { # add base directory to $form->{templates} $form->{templates} = "${LedgerSMB::Sysconfig::templates}/$form->{templates}"; - $myconfig = LedgerSMB::User->new("${LedgerSMB::Sysconfig::memberfile}", "$form->{login}"); # redo acs variable and delete all the acs codes @@ -837,7 +818,7 @@ sub save { $myconfig->{packpw} = 1; - $myconfig->save_member(${LedgerSMB::Sysconfig::memberfile}, ${LedgerSMB::Sysconfig::userspath}); + $myconfig->save_member($form); # create user template directory and copy master files if (! -d "$form->{templates}") { @@ -880,74 +861,6 @@ sub delete { $form->{templates} = ($form->{templates}) ? "${LedgerSMB::Sysconfig::templates}/$form->{templates}" : "$templates/$form->{login}"; - $form->error($locale->text("[_1] locked!", - ${LedgerSMB::Sysconfig::memberfile})) if (-f ${memberfile}.LCK); - - open(FH, ">${memberfile}.LCK") or $form->error("${memberfile}.LCK : $!"); - close(FH); - - if (! open(CONF, "+<${LedgerSMB::Sysconfig::memberfile}")) { - unlink "${memberfile}.LCK"; - $form->error("${LedgerSMB::Sysconfig::memberfile} : $!"); - } - - @config = ; - - seek(CONF, 0, 0); - truncate(CONF, 0); - - while ($line = shift @config) { - - chop $line; - - if ($line =~ /^\[/) { - last if ($line eq "[$form->{login}]"); - $login = &login_name($line); - } - - if ($line =~ /^templates=/) { - ($null, $user{$login}) = split /=/, $line, 2; - } - - print CONF "$line\n"; - } - - # remove everything up to next login or EOF - # and save template variable - while ($line = shift @config) { - - chop $line; - - ($key, $value) = split /=/, $line, 2; - $myconfig{$key} = $value; - - last if ($line =~ /^\[/); - } - - # this one is either the next login or EOF - print CONF "$line\n"; - - $login = &login_name($line); - - - while ($line = shift @config) { - - chop $line; - - if ($line =~ /^\[/) { - $login = &login_name($line); - } - - if ($line =~ /^templates=/) { - ($null, $user{$login}) = split /=/, $line, 2; - } - - print CONF "$line\n"; - } - - close(CONF); - unlink "${memberfile}.LCK"; - # scan %user for $templatedir foreach $login (keys %user) { last if ($found = ($form->{templates} eq $user{$login})); @@ -955,7 +868,6 @@ sub delete { # if found keep directory otherwise delete if (!$found) { - # delete it if there is a template directory $dir = "$form->{templates}"; if (-d "$dir") { @@ -964,16 +876,15 @@ sub delete { } } - if ($myconfig{dbconnect}) { + my $dbh = ${LedgerSMB::Sysconfig::GLOBALDBH}; - $myconfig{dbpasswd} = unpack 'u', $myconfig{dbpasswd}; - for (keys %myconfig) { $form->{$_} = $myconfig{$_} } + #users_conf + my $deleteUser = $dbh->prepare("DELETE FROM users_conf USING users WHERE users.username = ? and users.id = users_conf.id;"); + $deleteUser->execute($form->{login}); - LedgerSMB::User->delete_login(\%$form); - - # delete config file for user - unlink "${LedgerSMB::Sysconfig::userspath}/$form->{login}.conf"; - } + #and now users + $deleteUser = $dbh->prepare("DELETE FROM users WHERE username = ?;"); + $deleteUser->execute($form->{login}); $form->redirect($locale->text('User deleted!')); } @@ -1035,68 +946,41 @@ sub change_password { $form->error($locale->text('Passwords do not match!')) if $form->{new_password} ne $form->{confirm_password}; $root->{password} = $form->{new_password}; $root->{'root login'} = 1; - $root->save_member(${LedgerSMB::Sysconfig::memberfile}); + $root->save_member(); $form->{callback} = "$form->{script}?action=list_users&path=$form->{path}&sessionid=$form->{sessionid}"; $form->redirect($locale->text('Password changed!')); } -sub get_hash { - use Digest::MD5; - $form->{hash} = Digest::MD5::md5_hex rand(); - -} - sub check_password { - $root = LedgerSMB::User->new("${LedgerSMB::Sysconfig::memberfile}", "root login"); - - if ($root->{password}) { + $root = LedgerSMB::User->new('admin'); - if ($form->{password}) { + if ($form->{password}) { - $form->{callback} .= "&password=$form->{password}" if $form->{callback}; - $form->{sessionid} = time; + $form->{callback} .= "&password=$form->{password}" if $form->{callback}; - if ($root->{password} ne crypt $form->{password}, 'ro') { - &getpassword; - exit; - } - - &get_hash; - - open(HASHFILE, "> ${LedgerSMB::Sysconfig::userspath}/adminhash") || $form->error("Can't Open Hashfile: $!"); - print HASHFILE $form->{hash}; - print qq|Set-Cookie: LedgerSMB=$form->{hash}; path=/;\n|; - - } else { - - if ($ENV{HTTP_USER_AGENT}) { - - $ENV{HTTP_COOKIE} =~ s/;\s*/;/g; - %cookie = split /[=;]/, $ENV{HTTP_COOKIE}; # Changeme to %cookies - $cookie = ($form->{path} eq 'bin/lynx') ? $cookie{login} : $cookie{"LedgerSMB-root login"}; - - #fixes problem with first login and such - if (!(-f "${LedgerSMB::Sysconfig::userspath}/adminhash")) { - &get_hash; - open(HASHFILE, "> ${LedgerSMB::Sysconfig::userspath}/adminhash") || $form->error("Can't Open Hashfile: $!"); - print HASHFILE $form->{hash}; - close(HASHFILE); - } - - open (HASHFILE, "< ${LedgerSMB::Sysconfig::userspath}/adminhash") || $form->error("Can't Open Hashfile: $!"); - chomp($form->{hash} = ); - %cookies = split /[=;]/, $ENV{HTTP_COOKIE}; + if ($root->{password} ne (Digest::MD5::md5_hex $form->{password}) ) { + &getpassword; + exit; + } + else{ + Session::session_create($root); + } + } + else { - if (! $cookie || $cookie ne $form->{sessionid} || $form->{hash} ne $cookies{LedgerSMB}) { + $ENV{HTTP_COOKIE} =~ s/;\s*/;/g; + @cookies = split /;/, $ENV{HTTP_COOKIE}; + foreach (@cookies) { + ($name,$value) = split /=/, $_, 2; + $cookie{$name} = $value; + } - &getpassword; - exit; - } - } + if(!Session::session_check($cookie{"LedgerSMB"}, $root)){ + &getpassword(1); + exit; } } - } @@ -1372,7 +1256,7 @@ sub dbcreate { sub delete_dataset { - if (@dbsources = LedgerSMB::User->dbsources_unused(\%$form, ${LedgerSMB::Sysconfig::memberfile})) { + if (@dbsources = LedgerSMB::User->dbsources_unused(\%$form)) { foreach $item (sort @dbsources) { $dbsources .= qq| $item |; @@ -1463,7 +1347,8 @@ sub dbdelete { sub unlock_system { - unlink "${LedgerSMB::Sysconfig::userspath}/nologin"; + # This needs to be done with a db tool + # unlink "${LedgerSMB::Sysconfig::userspath}/nologin"; $form->{callback} = "$form->{script}?action=list_users&path=$form->{path}&sessionid=$form->{sessionid}"; $form->redirect($locale->text('Lockfile removed!')); } @@ -1471,8 +1356,9 @@ sub unlock_system { sub lock_system { - open(FH, ">${LedgerSMB::Sysconfig::userspath}/nologin") or $form->error($locale->text('Cannot create Lock!')); - close(FH); + # This needs to be done with a db tool + #open(FH, ">${LedgerSMB::Sysconfig::userspath}/nologin") or $form->error($locale->text('Cannot create Lock!')); + #close(FH); $form->{callback} = "$form->{script}?action=list_users&path=$form->{path}&sessionid=$form->{sessionid}"; $form->redirect($locale->text('Lockfile created!')); } diff --git a/bin/am.pl b/bin/am.pl index 25b0b29b..7931ff92 100755 --- a/bin/am.pl +++ b/bin/am.pl @@ -2233,7 +2233,7 @@ sub save_preferences { $form->error($locale->text('Password does not match!')) if $form->{new_password} ne $form->{confirm_password}; } - if (AM->save_preferences(\%myconfig, \%$form, ${LedgerSMB::Sysconfig::memberfile}, ${LedgerSMB::Sysconfig::userspath})) { + if (AM->save_preferences(\%myconfig, \%$form)) { $form->redirect($locale->text('Preferences saved!')); } else { $form->error($locale->text('Cannot save preferences!')); diff --git a/bin/arapprn.pl b/bin/arapprn.pl index 0bada33c..c8b19726 100755 --- a/bin/arapprn.pl +++ b/bin/arapprn.pl @@ -252,7 +252,7 @@ sub print_check { $form->{fileid} = $invnumber; $form->{fileid} =~ s/(\s|\W)+//g; - $form->parse_template(\%myconfig, ${LedgerSMB::Sysconfig::userspath}); + $form->parse_template(\%myconfig); if ($form->{previousform}) { @@ -468,7 +468,7 @@ sub print_transaction { $form->{fileid} = $form->{invnumber}; $form->{fileid} =~ s/(\s|\W)+//g; - $form->parse_template(\%myconfig, ${LedgerSMB::Sysconfig::userspath}); + $form->parse_template(\%myconfig); if (%$old_form) { $old_form->{invnumber} = $form->{invnumber}; diff --git a/bin/hr.pl b/bin/hr.pl index c8f53549..160b4c87 100755 --- a/bin/hr.pl +++ b/bin/hr.pl @@ -716,7 +716,7 @@ sub save_employee { # if it is a login change memberfile and .conf if ($form->{employeelogin}) { - $user = LedgerSMB::User->new(${LedgerSMB::Sysconfig::memberfile}, $form->{employeelogin}); + $user = LedgerSMB::User->new($form->{employeelogin}); for (qw(name email role)) { $user->{$_} = $form->{$_} } @@ -726,7 +726,7 @@ sub save_employee { for (qw(dbpasswd password)) { $user->{"old_$_"} = $user->{$_} } $user->{packpw} = 1; - $user->save_member(${LedgerSMB::Sysconfig::memberfile}, ${LedgerSMB::Sysconfig::userspath}) if $user->{login}; + $user->save_member() if $user->{login}; } $form->redirect($locale->text('Employee saved!')); diff --git a/bin/login.pl b/bin/login.pl index 8fbfefd9..a1f225d8 100755 --- a/bin/login.pl +++ b/bin/login.pl @@ -40,6 +40,7 @@ use DBI; use LedgerSMB::User; use LedgerSMB::Form; use LedgerSMB::Locale; +use LedgerSMB::Session; ## will need this later when session_destroy will be used #use LedgerSMB::Session; @@ -48,7 +49,7 @@ use LedgerSMB::Locale; $form = new Form; $locale = LedgerSMB::Locale->get_handle(${LedgerSMB::Sysconfig::language}) or - $form->error("Locale not loaded: $!\n"); + $form->error(__FILE__.':'.__LINE__.": Locale not loaded: $!\n"); $locale->encoding('UTF-8'); $form->{charset} = 'UTF-8'; #$form->{charset} = $locale->encoding; @@ -56,13 +57,13 @@ $form->{charset} = 'UTF-8'; # customization if (-f "bin/custom/$form->{script}") { eval { require "bin/custom/$form->{script}"; }; - $form->error($@) if ($@); + $form->error(__FILE__.':'.__LINE__.': '.$@) if ($@); } # per login customization if (-f "bin/custom/$form->{login}_$form->{script}") { eval { require "bin/custom/$form->{login}_$form->{script}"; }; - $form->error($@) if ($@); + $form->error(__FILE__.':'.__LINE__.': '.$@) if ($@); } # window title bar, user info @@ -233,40 +234,41 @@ sub login { $form->{stylesheet} = "ledger-smb.css"; $form->{favicon} = "favicon.ico"; - $form->error($locale->text('You did not enter a name!')) unless ($form->{login}); - - if (! $form->{beenthere}) { - open(FH, "${LedgerSMB::Sysconfig::memberfile}") or $form->error("$memberfile : $!"); - @a = ; - close(FH); - - foreach $item (@a) { - - if ($item =~ /^\[(.*?)\]/) { - $login = $1; - $found = 1; - } - - if ($item =~ /^company=/) { - if ($login =~ /$form->{login}\@/ && $found) { - ($null, $name) = split /=/, $item, 2; - $login{$login} = $name; - } - $found = 0; - } - } - - if (keys %login > 1) { - &selectdataset(\%login); - exit; - } - } - - - $user = LedgerSMB::User->new(${LedgerSMB::Sysconfig::memberfile}, $form->{login}); + $form->error(__FILE__.':'.__LINE__.': '.$locale->text('You did not enter a name!')) unless ($form->{login}); + + #this needs to be done via db + #if (! $form->{beenthere}) { + # open(FH, "${LedgerSMB::Sysconfig::memberfile}") or $form->error(__FILE__.':'.__LINE__.": $memberfile : $!"); + # @a = ; + # close(FH); + # + # foreach $item (@a) { + # + # if ($item =~ /^\[(.*?)\]/) { + # $login = $1; + # $found = 1; + # } + # + # if ($item =~ /^company=/) { + # if ($login =~ /$form->{login}\@/ && $found) { + # ($null, $name) = split /=/, $item, 2; + # $login{$login} = $name; + # } + # $found = 0; + # } + # } + # + # if (keys %login > 1) { + # &selectdataset(\%login); + # exit; + # } + #} + + + $user = LedgerSMB::User->new($form->{login}); # if we get an error back, bale out - if (($errno = $user->login(\%$form, ${LedgerSMB::Sysconfig::userspath})) <= -1) { + if (($errno = $user->login(\%$form)) <= -1) { $errno *= -1; $err[1] = $locale->text('Access Denied!'); @@ -275,7 +277,9 @@ sub login { if ($errno == 4) { # upgrade dataset and log in again - open FH, ">${LedgerSMB::Sysconfig::userspath}/nologin" or $form->error($!); + + #locking needs to be done via db function + #open FH, ">${LedgerSMB::Sysconfig::userspath}/nologin" or $form->error($!); for (qw(dbname dbhost dbport dbdriver dbuser dbpasswd)) { $form->{$_} = $user->{$_} } @@ -293,8 +297,8 @@ sub login { $user->dbupdate(\%$form); - # remove lock file - unlink "${LedgerSMB::Sysconfig::userspath}/nologin"; + # remove lock + #unlink "${LedgerSMB::Sysconfig::userspath}/nologin"; print $locale->text('done'); @@ -303,7 +307,7 @@ sub login { exit; } - $form->error($err[$errno]); + $form->error(__FILE__.':'.__LINE__.': '.$err[$errno]); } # made it this far, setup callback for the menu @@ -340,11 +344,9 @@ sub login { sub logout { - $form->{callback} = "$form->{script}?path=$form->{path}&login=$form->{login}"; $form->{endsession} = 1; - #delete the cookie in the browser manually (can't use session_destroy here unfortunately) - print qq|Set-Cookie: LedgerSMB=; path=/;\n|; + Session::session_destroy($form); $form->redirect; } diff --git a/ledger-smb.conf b/ledger-smb.conf index 07072266..6166265d 100644 --- a/ledger-smb.conf +++ b/ledger-smb.conf @@ -12,7 +12,7 @@ PATH: /usr/local/pgsql/bin # These parameters *must* be set correctly for LedgerSMB >= 1.2 to work # [globaldb] -##uncomment and set these +##uncomment below and set to correct values #DBConnect: dbi:Pg:dbname=ledgersmb;host=localhost;port=5432 #DBUserName: ledgersmb -#DBPassword: password +#DBPassword: password diff --git a/login.pl b/login.pl index 10d0d826..993f008a 100755 --- a/login.pl +++ b/login.pl @@ -77,12 +77,13 @@ $pos = rindex $0, '/'; $script = substr($0, $pos + 1); -if (-e "${LedgerSMB::Sysconfig::userspath}/nologin" && $script ne 'admin.pl') { - print "Content-Type: text/html\n\n"; - print "\nLogin disabled!\n"; - print "\n"; - exit; -} +#This needs to be a db query +#if (-e "${LedgerSMB::Sysconfig::userspath}/nologin" && $script ne 'admin.pl') { +# print "Content-Type: text/html\n\n"; +# print "\nLogin disabled!\n"; +# print "\n"; +# exit; +#} if ($form{path}) { diff --git a/menu.pl b/menu.pl index 1aa45194..f1adbb58 100755 --- a/menu.pl +++ b/menu.pl @@ -47,18 +47,21 @@ ####################################################################### use LedgerSMB::Sysconfig; +use Digest::MD5; $| = 1; +use LedgerSMB::User; use LedgerSMB::Form; use LedgerSMB::Locale; use LedgerSMB::Session; +use Data::Dumper; + # for custom preprocessing logic eval { require "custom.pl"; }; $form = new Form; - # name of this script $0 =~ tr/\\/\//; @@ -73,11 +76,13 @@ $script =~ s/\.pl//; # pull in DBI use DBI qw(:sql_types); -# check for user config file, could be missing or ??? -eval { require("${LedgerSMB::Sysconfig::userspath}/$form->{login}.conf"); }; +# grab user config. This is ugly and unecessary if/when +# we get rid of myconfig and use User as a real object +%myconfig = %{LedgerSMB::User->fetch_config($form->{login})}; + if ($@) { $locale = LedgerSMB::Locale->get_handle($myconfig{countrycode}) or - $form->error("Locale not loaded: $!\n"); + $form->error(__FILE__.':'.__LINE__.": Locale not loaded: $!\n"); $form->{charset} = $locale->encoding; $form->{charset} = 'UTF-8'; $locale->encoding('UTF-8'); @@ -90,7 +95,7 @@ if ($@) { # locale messages $locale = LedgerSMB::Locale->get_handle($myconfig{countrycode}) or - $form->error("Locale not loaded: $!\n"); + $form->error(__FILE__.':'.__LINE__.": Locale not loaded: $!\n"); #$form->{charset} = $locale->encoding; $form->{charset} = 'UTF-8'; $locale->encoding('UTF-8'); @@ -99,9 +104,8 @@ $locale->encoding('UTF-8'); $SIG{__WARN__} = sub { $form->info($_[0]) }; # send errors to browser -$SIG{__DIE__} = sub { $form->error($_[0]) }; +$SIG{__DIE__} = sub { $form->error(__FILE__.':'.__LINE__.': '.$_[0]) }; -$myconfig{dbpasswd} = unpack 'u', $myconfig{dbpasswd}; map { $form->{$_} = $myconfig{$_} } qw(stylesheet timeout) unless ($form->{type} eq 'preferences'); $form->db_init(\%myconfig); @@ -109,7 +113,7 @@ if ($form->{path} ne 'bin/lynx'){ $form->{path} = 'bin/mozilla';} # did sysadmin lock us out if (-f "${LedgerSMB::Sysconfig::userspath}/nologin") { - $form->error($locale->text('System currently down for maintenance!')); + $form->error(__FILE__.':'.__LINE__.': '.$locale->text('System currently down for maintenance!')); } # pull in the main code @@ -139,7 +143,7 @@ if ($form->{action}) { } } else { - $form->error($locale->text('action= not defined!')); + $form->error(__FILE__.':'.__LINE__.': '.$locale->text('action= not defined!')); } 1; @@ -153,15 +157,15 @@ sub check_password { require "bin/pw.pl"; if ($form->{password}) { - if ((crypt $form->{password}, substr($form->{login}, 0, 2)) ne $myconfig{password}) { + if ($myconfig{password} ne (Digest::MD5::md5_hex $form->{password})) { if ($ENV{HTTP_USER_AGENT}) { &getpassword; } else { - $form->error($locale->text('Access Denied!')); + $form->error(__FILE__.':'.__LINE__.': '.$locale->text('Access Denied!')); } exit; } else { - Session::session_create($form, %myconfig); + Session::session_create($form); } } else { @@ -180,7 +184,7 @@ sub check_password { } } #check for valid session - if(!Session::session_check($cookie{"LedgerSMB"}, $form, %myconfig)){ + if(!Session::session_check($cookie{"LedgerSMB"}, $form)){ &getpassword(1); exit; } diff --git a/sql/Pg-central.sql b/sql/Pg-central.sql new file mode 100755 index 00000000..804bf965 --- /dev/null +++ b/sql/Pg-central.sql @@ -0,0 +1,79 @@ +-- Central DB structure +-- This is the central database stuff which is used across all datasets +-- in the ledger-smb.conf it is called 'ledgersmb' by default, but obviously +-- can be named anything. + +-- USERS stuff -- +CREATE TABLE users (id serial UNIQUE, username varchar(30) primary key); +COMMENT ON TABLE users IS $$username is the actual primary key here because we do not want duplicate users$$; +CREATE TABLE users_conf(id integer primary key references users(id) deferrable initially deferred, + acs text, + address text, + businessnumber text, + company text, + countrycode text, + currency text, + dateformat text, + dbconnect text, + dbdriver text default 'Pg', + dbhost text default 'localhost', + dbname text, + dboptions text, + dbpasswd text, + dbport text, + dbuser text, + email text, + fax text, + menuwidth text, + name text, + numberformat text, + password varchar(32) check(length(password) = 32), + print text, + printer text, + role text, + sid text, + signature text, + stylesheet text, + tel text, + templates text, + timeout numeric, + vclimit numeric); + +COMMENT ON TABLE users_conf IS 'This is a completely dumb table that is a place holder to get usersconf into the database. Next major release will have a much more sane implementation'; +COMMENT ON COLUMN users_conf.id IS 'Yes primary key with a FOREIGN KEY to users(id) is correct'; +COMMENT ON COLUMN users_conf.password IS 'This means we have to get rid of the current password stuff and move to presumably md5()'; + +-- Per conversation with ChrisM, if the admin user has a null password a couple of things happen. +-- 1. It is implicit that this is an initial install +-- 2. If the admin password does not match the ledger-smb.conf admin password, we throw a hijack alert +-- The two below statements must be run from a single session +INSERT INTO users(username) VALUES ('admin'); +INSERT INTO users_conf(id,password) VALUES (currval('users_id_seq'),NULL); + + +CREATE OR REPLACE FUNCTION create_user(text) RETURNS bigint AS $$ + INSERT INTO users(username) VALUES ($1); + SELECT currval('users_id_seq'); + $$ LANGUAGE 'SQL'; + +COMMENT ON FUNCTION create_user(text) IS $$ Function to create user. Returns users.id if successful, else it is an error. $$; + +CREATE OR REPLACE FUNCTION update_user(int4,text) RETURNS int4 AS $$ + UPDATE users SET username = $2 WHERE id = $1; + SELECT 1; + $$ LANGUAGE 'SQL'; + +COMMENT ON FUNCTION update_user(int4,text) IS $$ Takes int4 which is users.id and text which is username. Will update username based on id. Username is unique $$; + + +-- Session tracking table + + +CREATE TABLE session( +session_id serial PRIMARY KEY, +sl_login VARCHAR(50), +token VARCHAR(32) CHECK(length(token) = 32), +last_used TIMESTAMP default now(), +users_id INTEGER -- NOT NULL references users(id) +); + diff --git a/sql/Pg-database.sql b/sql/Pg-database.sql index e3e96552..169abbab 100644 --- a/sql/Pg-database.sql +++ b/sql/Pg-database.sql @@ -704,80 +704,6 @@ INSERT INTO taxmodule ( 1, 'Simple' ); --- USERS stuff -- -CREATE TABLE users (id serial UNIQUE, username varchar(30) primary key); -COMMENT ON TABLE users IS -$$username is the actual primary key here because we don't want duplicate users$$; -CREATE TABLE users_conf(id integer primary key references users(id) deferrable initially deferred, - acs text, - address text, - businessnumber text, - company text, - countrycode text, - currency text, - dateformat text, - dbconnect text, - dbdriver text default 'Pg', - dbhost text default 'localhost', - dbname text, - dboptions text, - dbpasswd text, - dbport text, - dbuser text, - email text, - fax text, - menuwidth text, - name text, - numberformat text, - password varchar(32) check(length(password) = 32), - print text, - printer text, - role text, - sid text, - signature text, - stylesheet text, - tel text, - templates text, - timeout numeric, - vclimit numeric); -COMMENT ON TABLE users_conf IS 'This is a completely dumb table that is a place holder to get usersconf into the database. Next major release will have a much more sane implementation'; -COMMENT ON COLUMN users_conf.id IS 'Yes primary key with a FOREIGN KEY to users(id) is correct'; -COMMENT ON COLUMN users_conf.password IS 'This means we have to get rid of the current password stuff and move to presumably md5()'; - --- Per conversation with ChriseH, if the admin user has a null password a couple of things happen. --- 1. It is implicit that this is an initial install --- 2. If the admin password does not match the ledger-smb.conf admin password, we throw a hijack alert --- The two below statements must be run from a single session -INSERT INTO users(username) VALUES ('admin'); -INSERT INTO users_conf(id,password) VALUES (currval('users_id_seq'),NULL); - - -CREATE FUNCTION create_user(text) RETURNS int4 AS $$ - INSERT INTO users(username) VALUES ('$1'); - SELECT currval('user_id_seq'); - $$ LANGUAGE 'SQL'; - -COMMENT ON FUNCTION create_user(text) IS $$ Function to create user. Returns users.id if successful, else it is an error. $$; - -CREATE FUNCTION update_user(int4,text) RETURNS int4 AS $$ - UPDATE users SET username = '$2' WHERE id = $1; - SELECT 1; - $$ LANGUAGE 'SQL'; - -COMMENT ON FUNCTION update_user(int4,text) IS $$ Takes int4 which is users.id and text which is username. Will update username based on id. Username is unique $$; - - --- Session tracking table - - -CREATE TABLE session( -session_id serial PRIMARY KEY, -sl_login VARCHAR(50), -token VARCHAR(32) CHECK(length(token) = 32), -last_used TIMESTAMP default now(), -users_id INTEGER -- NOT NULL references users(id) -); - create index acc_trans_trans_id_key on acc_trans (trans_id); create index acc_trans_chart_id_key on acc_trans (chart_id); create index acc_trans_transdate_key on acc_trans (transdate); -- cgit v1.2.3