This is an automated email from the git hooks/post-receive script. x2go pushed a change to branch feature/mysql-backend in repository x2goserver. from 8659410 X2Go/Server/DB.pm: add high-level MySQL/MariaDB support. new 80a47ae x2goserver/sbin/x2godbadmin: wrap PostgreSQL code in own block, should not have any functional impact. new 0cb02f9 {X2Go/Server/DB/PostgreSQL.pm,x2goserver/sbin/x2godbadmin}: move /etc/x2go/x2gosql/passwords/x2goadmin or ${HOME}/.x2go/sqlpass to .../x2gopgadmin and .../pgsqlpass respectively. new 9affa6e x2goserver/sbin/x2godbadmin: generate more secure user-level database passwords. new e495889 x2goserver/sbin/x2godbadmin: split out subroutines into main namespace, call them generically with correct parameters from specialized namespace. The 4 revisions listed above as "new" are entirely new to this repository and will be described in separate emails. The revisions listed as "adds" were already present in the repository and have only been added to this reference. Summary of changes: X2Go/Server/DB/PostgreSQL.pm | 33 ++- debian/changelog | 14 + x2goserver/sbin/x2godbadmin | 685 ++++++++++++++++++++++++++----------------- 3 files changed, 467 insertions(+), 265 deletions(-) -- Alioth's /home/x2go-admin/maintenancescripts/git/hooks/post-receive-email on /srv/git/code.x2go.org/x2goserver.git
This is an automated email from the git hooks/post-receive script. x2go pushed a commit to branch feature/mysql-backend in repository x2goserver. commit 80a47aefd1208a3adedcaca1cf483c2db220e554 Author: Mihai Moldovan <ionic@ionic.de> Date: Fri Feb 23 22:02:05 2018 +0100 x2goserver/sbin/x2godbadmin: wrap PostgreSQL code in own block, should not have any functional impact. --- debian/changelog | 2 + x2goserver/sbin/x2godbadmin | 897 ++++++++++++++++++++++---------------------- 2 files changed, 452 insertions(+), 447 deletions(-) diff --git a/debian/changelog b/debian/changelog index 8725910..fc4a885 100644 --- a/debian/changelog +++ b/debian/changelog @@ -65,6 +65,8 @@ x2goserver (4.1.0.1-0x2go1) UNRELEASED; urgency=medium - X2Go/Server/DB.pm: add high-level MySQL/MariaDB support. References the X2Go::Server::DB::MySQL module (and functions/subroutines in there) which currently does not yet exist. + - x2goserver/sbin/x2godbadmin: wrap PostgreSQL code in own block, should + not have any functional impact. * debian/{control,compat}: + Bump DH compat level to 9. * debian/: diff --git a/x2goserver/sbin/x2godbadmin b/x2goserver/sbin/x2godbadmin index 4eab3e9..1a2683e 100755 --- a/x2goserver/sbin/x2godbadmin +++ b/x2goserver/sbin/x2godbadmin @@ -230,499 +230,502 @@ if ($Config->param("backend") eq 'sqlite') } } -my $host=$Config->param("postgres.host"); -my $port=$Config->param("postgres.port"); -my $sslmode=$Config->param("postgres.ssl"); -if (!$sslmode) +if ($Config->param("backend") eq 'postgres') { - $sslmode="prefer"; -} -my $dbadmin=$Config->param("postgres.dbadmin"); -my $x2goadmin="x2godbuser"; -my $x2goadminpass=`pwgen 8 1`; -chomp ($x2goadminpass); -my $db="x2go_sessions"; + my $host=$Config->param("postgres.host"); + my $port=$Config->param("postgres.port"); + my $sslmode=$Config->param("postgres.ssl"); + if (!$sslmode) + { + $sslmode="prefer"; + } + my $dbadmin=$Config->param("postgres.dbadmin"); + my $x2goadmin="x2godbuser"; + my $x2goadminpass=`pwgen 8 1`; + chomp ($x2goadminpass); + my $db="x2go_sessions"; -if (!$host) -{ - $host='localhost'; -} -if (!$port) -{ - $port='5432'; -} -if (!$dbadmin) -{ - $dbadmin='postgres'; -} + if (!$host) + { + $host='localhost'; + } + if (!$port) + { + $port='5432'; + } + if (!$dbadmin) + { + $dbadmin='postgres'; + } -open (FL,"< /etc/x2go/x2gosql/passwords/pgadmin ") or die "Can't read password file /etc/x2go/x2gosql/passwords/pgadmin"; -my $dbadminpass=<FL>; -close(FL); -chomp($dbadminpass); + open (FL,"< /etc/x2go/x2gosql/passwords/pgadmin ") or die "Can't read password file /etc/x2go/x2gosql/passwords/pgadmin"; + my $dbadminpass=<FL>; + close(FL); + chomp($dbadminpass); -if ($updatedb) -{ - # check if the DB already exists, if not, create it... - my $dbh; - until ( - $dbh = DBI->connect("dbi:Pg:dbname=$db;host=$host;port=$port;sslmode=$sslmode", "$dbadmin", "$dbadminpass",{AutoCommit => 1, RaiseError => 0, PrintError => 0}) - ) + if ($updatedb) { - $createdb = 1; - last; - }; - if (!$createdb) { - $dbh = DBI->connect("dbi:Pg:dbname=$db;host=$host;port=$port;sslmode=$sslmode", "$dbadmin", "$dbadminpass",{AutoCommit => 1}); - if ($dbh) { - my $update_views_n_rules = 0; - my $sth_tekictrl; - my $sth_tekidata; - my $sth_update; - try { - $sth_tekictrl = $dbh->prepare("select tekictrl_port from sessions"); - $sth_tekictrl->execute(); - } - catch - { - print "ADDING: tekictrl_port column to table sessions\n"; - $sth_tekictrl = $dbh->prepare(" - alter table sessions - add column tekictrl_port int - "); - $sth_tekictrl->execute() or die; - $sth_tekictrl->finish(); - $update_views_n_rules = 1; - }; - try { - $sth_tekidata = $dbh->prepare("select tekidata_port from sessions"); - $sth_tekidata->execute(); + # check if the DB already exists, if not, create it... + my $dbh; + until ( + $dbh = DBI->connect("dbi:Pg:dbname=$db;host=$host;port=$port;sslmode=$sslmode", "$dbadmin", "$dbadminpass",{AutoCommit => 1, RaiseError => 0, PrintError => 0}) + ) + { + $createdb = 1; + last; + }; + if (!$createdb) { + $dbh = DBI->connect("dbi:Pg:dbname=$db;host=$host;port=$port;sslmode=$sslmode", "$dbadmin", "$dbadminpass",{AutoCommit => 1}); + if ($dbh) { + my $update_views_n_rules = 0; + my $sth_tekictrl; + my $sth_tekidata; + my $sth_update; + try { + $sth_tekictrl = $dbh->prepare("select tekictrl_port from sessions"); + $sth_tekictrl->execute(); + } + catch + { + print "ADDING: tekictrl_port column to table sessions\n"; + $sth_tekictrl = $dbh->prepare(" + alter table sessions + add column tekictrl_port int + "); + $sth_tekictrl->execute() or die; + $sth_tekictrl->finish(); + $update_views_n_rules = 1; + }; + try { + $sth_tekidata = $dbh->prepare("select tekidata_port from sessions"); + $sth_tekidata->execute(); + } + catch + { + print "ADDING: tekidata_port column to table sessions\n"; + $sth_tekidata=$dbh->prepare(" + alter table sessions + add column tekidata_port int + "); + $sth_tekidata->execute() or die; + $sth_tekidata->finish(); + $update_views_n_rules = 1; + }; + + if ($update_views_n_rules) + { + print "UPDATING VIEW: sessions_view\n"; + $sth_update=$dbh->prepare(" + create or replace VIEW sessions_view as + SELECT + agent_pid, session_id, display, server, status, init_time, cookie, client, gr_port, + sound_port, last_time, uname, fs_port, tekictrl_port, tekidata_port from sessions + where creator_id = current_user + "); + $sth_update->execute() or die; + print "UPDATING RULE: update_sess_view\n"; + $sth_update=$dbh->prepare(" + create or replace RULE update_sess_view AS ON UPDATE + TO sessions_view DO INSTEAD + update sessions set + status=NEW.status, + last_time=NEW.last_time, + cookie=NEW.cookie, + agent_pid=NEW.agent_pid, + client=NEW.client, + gr_port=NEW.gr_port, + sound_port=NEW.sound_port, + fs_port=NEW.fs_port, + tekictrl_port=NEW.tekictrl_port, + tekidata_port=NEW.tekidata_port + where session_id=OLD.session_id and creator_id=current_user + "); + $sth_update->execute() or die; + $sth_update->finish(); + } } - catch - { - print "ADDING: tekidata_port column to table sessions\n"; - $sth_tekidata=$dbh->prepare(" - alter table sessions - add column tekidata_port int - "); - $sth_tekidata->execute() or die; - $sth_tekidata->finish(); - $update_views_n_rules = 1; - }; - - if ($update_views_n_rules) - { - print "UPDATING VIEW: sessions_view\n"; - $sth_update=$dbh->prepare(" - create or replace VIEW sessions_view as - SELECT - agent_pid, session_id, display, server, status, init_time, cookie, client, gr_port, - sound_port, last_time, uname, fs_port, tekictrl_port, tekidata_port from sessions - where creator_id = current_user - "); - $sth_update->execute() or die; - print "UPDATING RULE: update_sess_view\n"; - $sth_update=$dbh->prepare(" - create or replace RULE update_sess_view AS ON UPDATE - TO sessions_view DO INSTEAD - update sessions set - status=NEW.status, - last_time=NEW.last_time, - cookie=NEW.cookie, - agent_pid=NEW.agent_pid, - client=NEW.client, - gr_port=NEW.gr_port, - sound_port=NEW.sound_port, - fs_port=NEW.fs_port, - tekictrl_port=NEW.tekictrl_port, - tekidata_port=NEW.tekidata_port - where session_id=OLD.session_id and creator_id=current_user - "); - $sth_update->execute() or die; - $sth_update->finish(); + if ($dbh) { + undef $dbh; } + exit(0); } - if ($dbh) { - undef $dbh; + else + { + print "No session DB found. Use --createdb instead of --updatedb.\n"; } - exit(0); } - else + + if ($createdb) { - print "No session DB found. Use --createdb instead of --updatedb.\n"; + create_database(); + create_tables(); + exit(0); } -} -if ($createdb) -{ - create_database(); - create_tables(); - exit(0); -} - -if ($listusers) -{ - list_users(); - exit(0); -} - -my $dbh=DBI->connect("dbi:Pg:dbname=$db;host=$host;port=$port;sslmode=$sslmode", "$dbadmin", "$dbadminpass",{AutoCommit => 1}) or die $_; -if ($adduser) -{ - add_user($adduser); -} + if ($listusers) + { + list_users(); + exit(0); + } -if ($addgroup) -{ - my ($name, $passwd, $gid, $members) = getgrnam( $addgroup); - my @grp_members=split(' ',$members); - foreach (@grp_members) + my $dbh=DBI->connect("dbi:Pg:dbname=$db;host=$host;port=$port;sslmode=$sslmode", "$dbadmin", "$dbadminpass",{AutoCommit => 1}) or die $_; + if ($adduser) { - chomp($_); - add_user($_); + add_user($adduser); } -} -if ($rmuser) -{ - rm_user($rmuser); -} + if ($addgroup) + { + my ($name, $passwd, $gid, $members) = getgrnam( $addgroup); + my @grp_members=split(' ',$members); + foreach (@grp_members) + { + chomp($_); + add_user($_); + } + } -if ($rmgroup) -{ - my ($name, $passwd, $gid, $members) = getgrnam( $rmgroup); - my @grp_members=split(' ',$members); - foreach (@grp_members) + if ($rmuser) { - chomp($_); - rm_user($_); + rm_user($rmuser); } -} -undef $dbh; -sub list_users() -{ - my $dbh=DBI->connect("dbi:Pg:dbname=postgres;host=$host;port=$port;sslmode=$sslmode", "$dbadmin", "$dbadminpass",{AutoCommit => 1}) or die $_; - my $sth=$dbh->prepare("select rolname from pg_roles where rolname like 'x2gouser_%'"); - $sth->execute()or die; - printf ("%-20s DB user\n","UNIX user"); - print "---------------------------------------\n"; - my @data; - while (@data = $sth->fetchrow_array) + if ($rmgroup) { - @data[0]=~s/x2gouser_//; - printf ("%-20s x2gouser_@data[0]\n",@data[0]); + my ($name, $passwd, $gid, $members) = getgrnam( $rmgroup); + my @grp_members=split(' ',$members); + foreach (@grp_members) + { + chomp($_); + rm_user($_); + } } - $sth->finish(); undef $dbh; -} -sub rm_user() -{ - my $user=shift; + sub list_users() + { + my $dbh=DBI->connect("dbi:Pg:dbname=postgres;host=$host;port=$port;sslmode=$sslmode", "$dbadmin", "$dbadminpass",{AutoCommit => 1}) or die $_; + my $sth=$dbh->prepare("select rolname from pg_roles where rolname like 'x2gouser_%'"); + $sth->execute()or die; + printf ("%-20s DB user\n","UNIX user"); + print "---------------------------------------\n"; + my @data; + while (@data = $sth->fetchrow_array) + { + @data[0]=~s/x2gouser_//; + printf ("%-20s x2gouser_@data[0]\n",@data[0]); + } + $sth->finish(); + undef $dbh; + } - print ("rm DB user \"x2gouser_$user\"\n"); + sub rm_user() + { + my $user=shift; - my $sth=$dbh->prepare("REVOKE ALL PRIVILEGES ON sessions, used_ports, mounts FROM \"x2gouser_$user\""); - $sth->execute(); + print ("rm DB user \"x2gouser_$user\"\n"); - my $sth=$dbh->prepare("REVOKE ALL PRIVILEGES ON sessions_view, mounts_view, servers_view, ports_view FROM \"x2gouser_$user\""); - $sth->execute(); + my $sth=$dbh->prepare("REVOKE ALL PRIVILEGES ON sessions, used_ports, mounts FROM \"x2gouser_$user\""); + $sth->execute(); - my $sth=$dbh->prepare("DROP OWNED BY \"x2gouser_$user\""); - $sth->execute(); + my $sth=$dbh->prepare("REVOKE ALL PRIVILEGES ON sessions_view, mounts_view, servers_view, ports_view FROM \"x2gouser_$user\""); + $sth->execute(); - my $sth=$dbh->prepare("drop USER if exists \"x2gouser_$user\""); - $sth->execute(); - $sth->finish(); + my $sth=$dbh->prepare("DROP OWNED BY \"x2gouser_$user\""); + $sth->execute(); - my ($name, $pass, $uid, $pgid, $quota, $comment, $gcos, $dir, $shell, $expire) = getpwnam($user); - if (! $uid) - { - return; - } - if ( -e "$dir/.x2go/sqlpass" ) - { - unlink("$dir/.x2go/sqlpass"); + my $sth=$dbh->prepare("drop USER if exists \"x2gouser_$user\""); + $sth->execute(); + $sth->finish(); + + my ($name, $pass, $uid, $pgid, $quota, $comment, $gcos, $dir, $shell, $expire) = getpwnam($user); + if (! $uid) + { + return; + } + if ( -e "$dir/.x2go/sqlpass" ) + { + unlink("$dir/.x2go/sqlpass"); + } } -} -sub add_user() -{ - my $user=shift; - my ($name, $pass, $uid, $pgid, $quota, $comment, $gcos, $dir, $shell, $expire) = getpwnam($user); - if (! $name) + sub add_user() { - print "Cannot find user ($user)\n"; - return; + my $user=shift; + my ($name, $pass, $uid, $pgid, $quota, $comment, $gcos, $dir, $shell, $expire) = getpwnam($user); + if (! $name) + { + print "Cannot find user ($user)\n"; + return; + } + elsif ($name eq "root") + { + print "The super-user \"root\" is not allowed to use X2Go\n"; + return; + } + $pass=`pwgen 8 1`; + chomp($pass); + + my $sth=$dbh->prepare("REVOKE ALL PRIVILEGES ON sessions, used_ports, mounts FROM \"x2gouser_$user\""); + $sth->{Warn}=0; + $sth->{PrintError}=0; + $sth->execute(); + + my $sth=$dbh->prepare("REVOKE ALL PRIVILEGES ON sessions_view, mounts_view, servers_view, ports_view FROM \"x2gouser_$user\""); + $sth->{Warn}=0; + $sth->{PrintError}=0; + $sth->execute(); + + my $sth=$dbh->prepare("DROP OWNED BY \"x2gouser_$user\""); + $sth->{Warn}=0; + $sth->{PrintError}=0; + $sth->execute(); + + $sth=$dbh->prepare("drop USER if exists \"x2gouser_$user\""); + $sth->{Warn}=0; + $sth->{PrintError}=0; + $sth->execute(); + + print ("create DB user \"x2gouser_$user\"\n"); + $sth=$dbh->prepare("create USER \"x2gouser_$user\" WITH ENCRYPTED PASSWORD '$pass'"); + $sth->execute(); + + $sth=$dbh->prepare("GRANT INSERT, UPDATE, DELETE ON sessions, used_ports, mounts TO \"x2gouser_$user\""); + $sth->execute(); + + $sth=$dbh->prepare("GRANT SELECT ON used_ports TO \"x2gouser_$user\""); + $sth->execute(); + + $sth=$dbh->prepare("GRANT SELECT, UPDATE, DELETE ON sessions_view, mounts_view, servers_view, ports_view TO \"x2gouser_$user\""); + $sth->execute(); + $sth->finish(); + + if (! -d "$dir/.x2go" ) + { + if ( defined (&File::Path::make_path) ) + { + File::Path::make_path("$dir/.x2go"); + } + elsif ( defined (&File::Path::mkpath) ) + { + File::Path::mkpath("$dir/.x2go"); + } + else + { + die "Unable to create folders with File::Path"; + } + } + + #save user password + open (FL,"> $dir/.x2go/sqlpass") or die "Can't open password file $dir/.x2go/sqlpass"; + print FL $pass; + close(FL); + chmod(0700,"$dir/.x2go"); + chown($uid,$pgid,"$dir/.x2go"); + chmod(0600,"$dir/.x2go/sqlpass"); + chown($uid,$pgid,"$dir/.x2go/sqlpass"); } - elsif ($name eq "root") + + sub create_tables() { - print "The super-user \"root\" is not allowed to use X2Go\n"; - return; - } - $pass=`pwgen 8 1`; - chomp($pass); + $dbh=DBI->connect("dbi:Pg:dbname=$db;host=$host;port=$port;sslmode=$sslmode", "$dbadmin", "$dbadminpass",{AutoCommit => 1}) or die $_; + my $sth=$dbh->prepare(" + create table sessions( + session_id text primary key, + display integer not null, + uname text not null, + server text not null, + client inet, + status char(1) not null default 'R', + init_time timestamp not null default now(), + last_time timestamp not null default now(), + cookie char(33), + agent_pid int, + gr_port int, + sound_port int, + fs_port int, + tekictrl_port int, + tekidata_port int, + creator_id text NOT NULL default current_user, + unique(display)) + "); + $sth->execute() or die; - my $sth=$dbh->prepare("REVOKE ALL PRIVILEGES ON sessions, used_ports, mounts FROM \"x2gouser_$user\""); - $sth->{Warn}=0; - $sth->{PrintError}=0; - $sth->execute(); + $sth=$dbh->prepare(" + create VIEW sessions_view as + SELECT + agent_pid, session_id, display, server, status, init_time, cookie, client, gr_port, + sound_port, last_time, uname, fs_port, tekictrl_port, tekidata_port from sessions + where creator_id = current_user + "); + $sth->execute() or die; - my $sth=$dbh->prepare("REVOKE ALL PRIVILEGES ON sessions_view, mounts_view, servers_view, ports_view FROM \"x2gouser_$user\""); - $sth->{Warn}=0; - $sth->{PrintError}=0; - $sth->execute(); + $sth=$dbh->prepare(" + create VIEW servers_view as + SELECT + server, display, status from sessions + "); + $sth->execute() or die; - my $sth=$dbh->prepare("DROP OWNED BY \"x2gouser_$user\""); - $sth->{Warn}=0; - $sth->{PrintError}=0; - $sth->execute(); + $sth=$dbh->prepare(" + create or replace RULE update_sess_priv AS ON UPDATE + TO sessions where (OLD.creator_id <> current_user or OLD.creator_id <> NEW.creator_id) and current_user <> '$x2goadmin' + DO INSTEAD NOTHING + "); + $sth->execute() or die; - $sth=$dbh->prepare("drop USER if exists \"x2gouser_$user\""); - $sth->{Warn}=0; - $sth->{PrintError}=0; - $sth->execute(); + $sth=$dbh->prepare(" + create or replace RULE insert_sess_priv AS ON INSERT + TO sessions where NEW.creator_id <> current_user and current_user <> '$x2goadmin' + DO INSTEAD NOTHING + "); + $sth->execute() or die; - print ("create DB user \"x2gouser_$user\"\n"); - $sth=$dbh->prepare("create USER \"x2gouser_$user\" WITH ENCRYPTED PASSWORD '$pass'"); - $sth->execute(); + $sth=$dbh->prepare(" + create or replace RULE delete_sess_priv AS ON DELETE + TO sessions where OLD.creator_id <> current_user and current_user <> '$x2goadmin' + DO INSTEAD NOTHING + "); + $sth->execute() or die; - $sth=$dbh->prepare("GRANT INSERT, UPDATE, DELETE ON sessions, used_ports, mounts TO \"x2gouser_$user\""); - $sth->execute(); + $sth=$dbh->prepare(" + create or replace RULE update_sess_view AS ON UPDATE + TO sessions_view DO INSTEAD + update sessions set + status=NEW.status, + last_time=NEW.last_time, + cookie=NEW.cookie, + agent_pid=NEW.agent_pid, + client=NEW.client, + gr_port=NEW.gr_port, + sound_port=NEW.sound_port, + fs_port=NEW.fs_port, + tekictrl_port=NEW.tekictrl_port, + tekidata_port=NEW.tekidata_port + where session_id=OLD.session_id and creator_id=current_user + "); + $sth->execute() or die; - $sth=$dbh->prepare("GRANT SELECT ON used_ports TO \"x2gouser_$user\""); - $sth->execute(); + $sth=$dbh->prepare("create table messages(mess_id varchar(20) primary key, message text)"); + $sth->execute() or die; - $sth=$dbh->prepare("GRANT SELECT, UPDATE, DELETE ON sessions_view, mounts_view, servers_view, ports_view TO \"x2gouser_$user\""); - $sth->execute(); - $sth->finish(); + $sth=$dbh->prepare(" + create table user_messages( + mess_id text not null, + uname text not null) + "); + $sth->execute() or die; - if (! -d "$dir/.x2go" ) - { - if ( defined (&File::Path::make_path) ) - { - File::Path::make_path("$dir/.x2go"); - } - elsif ( defined (&File::Path::mkpath) ) - { - File::Path::mkpath("$dir/.x2go"); - } - else - { - die "Unable to create folders with File::Path"; - } - } + $sth=$dbh->prepare(" + create table used_ports( + server text not null, + session_id text references sessions on delete cascade, + creator_id text NOT NULL default current_user, + port integer primary key) + "); + $sth->execute() or die; - #save user password - open (FL,"> $dir/.x2go/sqlpass") or die "Can't open password file $dir/.x2go/sqlpass"; - print FL $pass; - close(FL); - chmod(0700,"$dir/.x2go"); - chown($uid,$pgid,"$dir/.x2go"); - chmod(0600,"$dir/.x2go/sqlpass"); - chown($uid,$pgid,"$dir/.x2go/sqlpass"); -} + $sth=$dbh->prepare(" + create VIEW ports_view as + SELECT + server, port from used_ports + "); + $sth->execute() or die; -sub create_tables() -{ - $dbh=DBI->connect("dbi:Pg:dbname=$db;host=$host;port=$port;sslmode=$sslmode", "$dbadmin", "$dbadminpass",{AutoCommit => 1}) or die $_; - my $sth=$dbh->prepare(" - create table sessions( - session_id text primary key, - display integer not null, - uname text not null, - server text not null, - client inet, - status char(1) not null default 'R', - init_time timestamp not null default now(), - last_time timestamp not null default now(), - cookie char(33), - agent_pid int, - gr_port int, - sound_port int, - fs_port int, - tekictrl_port int, - tekidata_port int, - creator_id text NOT NULL default current_user, - unique(display)) - "); - $sth->execute() or die; - - $sth=$dbh->prepare(" - create VIEW sessions_view as - SELECT - agent_pid, session_id, display, server, status, init_time, cookie, client, gr_port, - sound_port, last_time, uname, fs_port, tekictrl_port, tekidata_port from sessions - where creator_id = current_user - "); - $sth->execute() or die; - - $sth=$dbh->prepare(" - create VIEW servers_view as - SELECT - server, display, status from sessions - "); - $sth->execute() or die; - - $sth=$dbh->prepare(" - create or replace RULE update_sess_priv AS ON UPDATE - TO sessions where (OLD.creator_id <> current_user or OLD.creator_id <> NEW.creator_id) and current_user <> '$x2goadmin' - DO INSTEAD NOTHING - "); - $sth->execute() or die; - - $sth=$dbh->prepare(" - create or replace RULE insert_sess_priv AS ON INSERT - TO sessions where NEW.creator_id <> current_user and current_user <> '$x2goadmin' - DO INSTEAD NOTHING - "); - $sth->execute() or die; - - $sth=$dbh->prepare(" - create or replace RULE delete_sess_priv AS ON DELETE - TO sessions where OLD.creator_id <> current_user and current_user <> '$x2goadmin' - DO INSTEAD NOTHING - "); - $sth->execute() or die; - - $sth=$dbh->prepare(" - create or replace RULE update_sess_view AS ON UPDATE - TO sessions_view DO INSTEAD - update sessions set - status=NEW.status, - last_time=NEW.last_time, - cookie=NEW.cookie, - agent_pid=NEW.agent_pid, - client=NEW.client, - gr_port=NEW.gr_port, - sound_port=NEW.sound_port, - fs_port=NEW.fs_port, - tekictrl_port=NEW.tekictrl_port, - tekidata_port=NEW.tekidata_port - where session_id=OLD.session_id and creator_id=current_user - "); - $sth->execute() or die; - - $sth=$dbh->prepare("create table messages(mess_id varchar(20) primary key, message text)"); - $sth->execute() or die; - - $sth=$dbh->prepare(" - create table user_messages( - mess_id text not null, - uname text not null) - "); - $sth->execute() or die; - - $sth=$dbh->prepare(" - create table used_ports( - server text not null, - session_id text references sessions on delete cascade, - creator_id text NOT NULL default current_user, - port integer primary key) - "); - $sth->execute() or die; - - $sth=$dbh->prepare(" - create VIEW ports_view as - SELECT - server, port from used_ports - "); - $sth->execute() or die; - - $sth=$dbh->prepare(" - create or replace RULE insert_port_priv AS ON INSERT - TO used_ports where NEW.creator_id <> current_user and current_user <> '$x2goadmin' - DO INSTEAD NOTHING - "); - $sth->execute() or die; - - $sth=$dbh->prepare(" - create or replace RULE update_port_priv AS ON UPDATE - TO used_ports where (NEW.creator_id <> current_user or OLD.creator_id <> current_user) and current_user <> '$x2goadmin' - DO INSTEAD NOTHING - "); - $sth->execute() or die; - - $sth=$dbh->prepare(" - create or replace RULE delete_port_priv AS ON DELETE - TO used_ports where OLD.creator_id <> current_user and current_user <> '$x2goadmin' - DO INSTEAD NOTHING - "); - $sth->execute() or die; - - $sth=$dbh->prepare(" - create table mounts( - session_id text references sessions on delete restrict, - path text not null, - client inet not null, - creator_id text NOT NULL default current_user, - primary key(path,client)) - "); - $sth->execute() or die; - - $sth=$dbh->prepare(" - create VIEW mounts_view as - SELECT - client,path, session_id from mounts - where creator_id = current_user - "); - $sth->execute() or die; - - $sth=$dbh->prepare(" - create or replace RULE delete_mounts_view AS ON DELETE - TO mounts_view DO INSTEAD - delete from mounts - where session_id=OLD.session_id and creator_id=current_user and path=OLD.path - "); - $sth->execute() or die; - - $sth=$dbh->prepare(" - create or replace RULE insert_mount_priv AS ON INSERT - TO mounts where NEW.creator_id <> current_user and current_user <> '$x2goadmin' - DO INSTEAD NOTHING - "); - $sth->execute() or die; - - $sth=$dbh->prepare(" - create or replace RULE update_mount_priv AS ON UPDATE - TO mounts where (NEW.creator_id <> current_user or OLD.creator_id <> current_user) and current_user <> '$x2goadmin' - DO INSTEAD NOTHING - "); - $sth->execute() or die; - - $sth=$dbh->prepare(" - create or replace RULE delete_mount_priv AS ON DELETE - TO mounts where OLD.creator_id <> current_user and current_user <> '$x2goadmin' - DO INSTEAD NOTHING - "); - $sth->execute() or die; - - $sth=$dbh->prepare("GRANT ALL PRIVILEGES ON sessions, messages, user_messages, used_ports, mounts TO $x2goadmin"); - $sth->execute() or die; - $sth->finish(); - undef $dbh; -} + $sth=$dbh->prepare(" + create or replace RULE insert_port_priv AS ON INSERT + TO used_ports where NEW.creator_id <> current_user and current_user <> '$x2goadmin' + DO INSTEAD NOTHING + "); + $sth->execute() or die; -sub create_database -{ - my $dbh=DBI->connect("dbi:Pg:dbname=postgres;host=$host;port=$port;sslmode=$sslmode", "$dbadmin", "$dbadminpass",{AutoCommit => 1}) or die $_; - #drop db if exists - my $sth=$dbh->prepare("drop database if exists $db"); - $sth->execute(); - #drop x2goadmin - $sth=$dbh->prepare("drop user if exists $x2goadmin"); - $sth->execute(); - #create db - $sth=$dbh->prepare("create database $db"); - $sth->execute() or die; - #create x2goadmin - $sth=$dbh->prepare("create USER $x2goadmin WITH ENCRYPTED PASSWORD '$x2goadminpass'"); - $sth->execute() or die; - #save x2goadmin password - open (FL,"> /etc/x2go/x2gosql/passwords/x2goadmin ") or die "Can't write password file /etc/x2go/x2gosql/passwords/x2goadmin"; - print FL $x2goadminpass; - close(FL); - $sth->finish(); - undef $dbh; + $sth=$dbh->prepare(" + create or replace RULE update_port_priv AS ON UPDATE + TO used_ports where (NEW.creator_id <> current_user or OLD.creator_id <> current_user) and current_user <> '$x2goadmin' + DO INSTEAD NOTHING + "); + $sth->execute() or die; + + $sth=$dbh->prepare(" + create or replace RULE delete_port_priv AS ON DELETE + TO used_ports where OLD.creator_id <> current_user and current_user <> '$x2goadmin' + DO INSTEAD NOTHING + "); + $sth->execute() or die; + + $sth=$dbh->prepare(" + create table mounts( + session_id text references sessions on delete restrict, + path text not null, + client inet not null, + creator_id text NOT NULL default current_user, + primary key(path,client)) + "); + $sth->execute() or die; + + $sth=$dbh->prepare(" + create VIEW mounts_view as + SELECT + client,path, session_id from mounts + where creator_id = current_user + "); + $sth->execute() or die; + + $sth=$dbh->prepare(" + create or replace RULE delete_mounts_view AS ON DELETE + TO mounts_view DO INSTEAD + delete from mounts + where session_id=OLD.session_id and creator_id=current_user and path=OLD.path + "); + $sth->execute() or die; + + $sth=$dbh->prepare(" + create or replace RULE insert_mount_priv AS ON INSERT + TO mounts where NEW.creator_id <> current_user and current_user <> '$x2goadmin' + DO INSTEAD NOTHING + "); + $sth->execute() or die; + + $sth=$dbh->prepare(" + create or replace RULE update_mount_priv AS ON UPDATE + TO mounts where (NEW.creator_id <> current_user or OLD.creator_id <> current_user) and current_user <> '$x2goadmin' + DO INSTEAD NOTHING + "); + $sth->execute() or die; + + $sth=$dbh->prepare(" + create or replace RULE delete_mount_priv AS ON DELETE + TO mounts where OLD.creator_id <> current_user and current_user <> '$x2goadmin' + DO INSTEAD NOTHING + "); + $sth->execute() or die; + + $sth=$dbh->prepare("GRANT ALL PRIVILEGES ON sessions, messages, user_messages, used_ports, mounts TO $x2goadmin"); + $sth->execute() or die; + $sth->finish(); + undef $dbh; + } + + sub create_database + { + my $dbh=DBI->connect("dbi:Pg:dbname=postgres;host=$host;port=$port;sslmode=$sslmode", "$dbadmin", "$dbadminpass",{AutoCommit => 1}) or die $_; + #drop db if exists + my $sth=$dbh->prepare("drop database if exists $db"); + $sth->execute(); + #drop x2goadmin + $sth=$dbh->prepare("drop user if exists $x2goadmin"); + $sth->execute(); + #create db + $sth=$dbh->prepare("create database $db"); + $sth->execute() or die; + #create x2goadmin + $sth=$dbh->prepare("create USER $x2goadmin WITH ENCRYPTED PASSWORD '$x2goadminpass'"); + $sth->execute() or die; + #save x2goadmin password + open (FL,"> /etc/x2go/x2gosql/passwords/x2goadmin ") or die "Can't write password file /etc/x2go/x2gosql/passwords/x2goadmin"; + print FL $x2goadminpass; + close(FL); + $sth->finish(); + undef $dbh; + } } -- Alioth's /home/x2go-admin/maintenancescripts/git/hooks/post-receive-email on /srv/git/code.x2go.org/x2goserver.git
This is an automated email from the git hooks/post-receive script. x2go pushed a commit to branch feature/mysql-backend in repository x2goserver. commit 0cb02f9176945823275c278efac6a88004056d84 Author: Mihai Moldovan <ionic@ionic.de> Date: Fri Feb 23 22:09:59 2018 +0100 {X2Go/Server/DB/PostgreSQL.pm,x2goserver/sbin/x2godbadmin}: move /etc/x2go/x2gosql/passwords/x2goadmin or ${HOME}/.x2go/sqlpass to .../x2gopgadmin and .../pgsqlpass respectively. Move is done at run-time, so no user interaction should be required. It requires one database operation to be carried out first in order to trigger the move, but x2godbadmin does not actually use that account information (only generates it), so we should be in the clear. --- X2Go/Server/DB/PostgreSQL.pm | 33 +++++++++++++++++++++++++++++++-- debian/changelog | 7 +++++++ x2goserver/sbin/x2godbadmin | 12 ++++++++---- 3 files changed, 46 insertions(+), 6 deletions(-) diff --git a/X2Go/Server/DB/PostgreSQL.pm b/X2Go/Server/DB/PostgreSQL.pm index 57c31cb..622489e 100644 --- a/X2Go/Server/DB/PostgreSQL.pm +++ b/X2Go/Server/DB/PostgreSQL.pm @@ -33,6 +33,7 @@ use strict; use DBI; use POSIX; use Sys::Syslog qw( :standard :macros ); +use File::Copy; use X2Go::Log qw( loglevel ); use X2Go::Config qw( get_config get_sqlconfig ); @@ -85,16 +86,44 @@ sub init_db $port='5432'; } my $passfile; + my $old_location; if ($uname eq 'root') { $dbuser='x2godbuser'; - $passfile="/etc/x2go/x2gosql/passwords/x2goadmin"; + $old_location = "/etc/x2go/x2gosql/passwords/x2goadmin"; + $passfile="/etc/x2go/x2gosql/passwords/x2gopgadmin"; } else { $dbuser="x2gouser_$uname"; - $passfile="$homedir/.x2go/sqlpass"; + $old_location = "$homedir/.x2go/sqlpass"; + $passfile="$homedir/.x2go/pgsqlpass"; } + + my $move_file = 0; + + if ((-e $old_location) && (-e $passfile)) + { + if (-z $passfile) + { + $move_file = 1; + } + else + { + die "Detected existing files in both the legacy location '$old_location' and new location '$passfile'. New location file is non-empty, aborting.<br>"; + } + } + + if ((-e $old_location) && (! -e $passfile)) + { + $move_file = 1; + } + + if ($move_file) + # Password file needs move. + move($old_location, $passfile) or die "Unable to move PostgreSQL user password file from '$old_location' to '$passfile'<br>"; + } + $sslmode=$SqlConfig->param("postgres.ssl"); if (!$sslmode) { diff --git a/debian/changelog b/debian/changelog index fc4a885..f6646a9 100644 --- a/debian/changelog +++ b/debian/changelog @@ -67,6 +67,13 @@ x2goserver (4.1.0.1-0x2go1) UNRELEASED; urgency=medium which currently does not yet exist. - x2goserver/sbin/x2godbadmin: wrap PostgreSQL code in own block, should not have any functional impact. + - {X2Go/Server/DB/PostgreSQL.pm,x2goserver/sbin/x2godbadmin}: move + /etc/x2go/x2gosql/passwords/x2goadmin or ${HOME}/.x2go/sqlpass to + .../x2gopgadmin and .../pgsqlpass respectively. Move is done at + run-time, so no user interaction should be required. It requires one + database operation to be carried out first in order to trigger the move, + but x2godbadmin does not actually use that account information (only + generates it), so we should be in the clear. * debian/{control,compat}: + Bump DH compat level to 9. * debian/: diff --git a/x2goserver/sbin/x2godbadmin b/x2goserver/sbin/x2godbadmin index 1a2683e..ae6a914 100755 --- a/x2goserver/sbin/x2godbadmin +++ b/x2goserver/sbin/x2godbadmin @@ -443,6 +443,10 @@ if ($Config->param("backend") eq 'postgres') { return; } + if ( -e "$dir/.x2go/pgsqlpass" ) + { + unlink("$dir/.x2go/pgsqlpass"); + } if ( -e "$dir/.x2go/sqlpass" ) { unlink("$dir/.x2go/sqlpass"); @@ -517,13 +521,13 @@ if ($Config->param("backend") eq 'postgres') } #save user password - open (FL,"> $dir/.x2go/sqlpass") or die "Can't open password file $dir/.x2go/sqlpass"; + open (FL,"> $dir/.x2go/pgsqlpass") or die "Can't open password file $dir/.x2go/pgsqlpass"; print FL $pass; close(FL); chmod(0700,"$dir/.x2go"); chown($uid,$pgid,"$dir/.x2go"); - chmod(0600,"$dir/.x2go/sqlpass"); - chown($uid,$pgid,"$dir/.x2go/sqlpass"); + chmod(0600,"$dir/.x2go/pgsqlpass"); + chown($uid,$pgid,"$dir/.x2go/pgsqlpass"); } sub create_tables() @@ -722,7 +726,7 @@ if ($Config->param("backend") eq 'postgres') $sth=$dbh->prepare("create USER $x2goadmin WITH ENCRYPTED PASSWORD '$x2goadminpass'"); $sth->execute() or die; #save x2goadmin password - open (FL,"> /etc/x2go/x2gosql/passwords/x2goadmin ") or die "Can't write password file /etc/x2go/x2gosql/passwords/x2goadmin"; + open (FL,"> /etc/x2go/x2gosql/passwords/x2gopgadmin ") or die "Can't write password file /etc/x2go/x2gosql/passwords/x2gopgadmin"; print FL $x2goadminpass; close(FL); $sth->finish(); -- Alioth's /home/x2go-admin/maintenancescripts/git/hooks/post-receive-email on /srv/git/code.x2go.org/x2goserver.git
This is an automated email from the git hooks/post-receive script. x2go pushed a commit to branch feature/mysql-backend in repository x2goserver. commit 9affa6e93df712373a3bed10deeaf5ebbc8329c1 Author: Mihai Moldovan <ionic@ionic.de> Date: Fri Feb 23 22:36:44 2018 +0100 x2goserver/sbin/x2godbadmin: generate more secure user-level database passwords. --- debian/changelog | 2 ++ x2goserver/sbin/x2godbadmin | 4 ++-- 2 files changed, 4 insertions(+), 2 deletions(-) diff --git a/debian/changelog b/debian/changelog index f6646a9..0d6b15e 100644 --- a/debian/changelog +++ b/debian/changelog @@ -74,6 +74,8 @@ x2goserver (4.1.0.1-0x2go1) UNRELEASED; urgency=medium database operation to be carried out first in order to trigger the move, but x2godbadmin does not actually use that account information (only generates it), so we should be in the clear. + - x2goserver/sbin/x2godbadmin: generate more secure user-level database + passwords. * debian/{control,compat}: + Bump DH compat level to 9. * debian/: diff --git a/x2goserver/sbin/x2godbadmin b/x2goserver/sbin/x2godbadmin index ae6a914..1173656 100755 --- a/x2goserver/sbin/x2godbadmin +++ b/x2goserver/sbin/x2godbadmin @@ -241,7 +241,7 @@ if ($Config->param("backend") eq 'postgres') } my $dbadmin=$Config->param("postgres.dbadmin"); my $x2goadmin="x2godbuser"; - my $x2goadminpass=`pwgen 8 1`; + my $x2goadminpass=`pwgen -s -c -n 32 1`; chomp ($x2goadminpass); my $db="x2go_sessions"; @@ -467,7 +467,7 @@ if ($Config->param("backend") eq 'postgres') print "The super-user \"root\" is not allowed to use X2Go\n"; return; } - $pass=`pwgen 8 1`; + $pass=`pwgen -s -c -n 32 1`; chomp($pass); my $sth=$dbh->prepare("REVOKE ALL PRIVILEGES ON sessions, used_ports, mounts FROM \"x2gouser_$user\""); -- Alioth's /home/x2go-admin/maintenancescripts/git/hooks/post-receive-email on /srv/git/code.x2go.org/x2goserver.git
This is an automated email from the git hooks/post-receive script. x2go pushed a commit to branch feature/mysql-backend in repository x2goserver. commit e495889544fa25f85ac929251e6ba78179758602 Author: Mihai Moldovan <ionic@ionic.de> Date: Fri Feb 23 23:10:41 2018 +0100 x2goserver/sbin/x2godbadmin: split out subroutines into main namespace, call them generically with correct parameters from specialized namespace. --- debian/changelog | 3 + x2goserver/sbin/x2godbadmin | 738 ++++++++++++++++++++++++++------------------ 2 files changed, 448 insertions(+), 293 deletions(-) diff --git a/debian/changelog b/debian/changelog index 0d6b15e..b6925f4 100644 --- a/debian/changelog +++ b/debian/changelog @@ -76,6 +76,9 @@ x2goserver (4.1.0.1-0x2go1) UNRELEASED; urgency=medium generates it), so we should be in the clear. - x2goserver/sbin/x2godbadmin: generate more secure user-level database passwords. + - x2goserver/sbin/x2godbadmin: split out subroutines into main namespace, + call them generically with correct parameters from specialized + namespace. * debian/{control,compat}: + Bump DH compat level to 9. * debian/: diff --git a/x2goserver/sbin/x2godbadmin b/x2goserver/sbin/x2godbadmin index 1173656..7de911f 100755 --- a/x2goserver/sbin/x2godbadmin +++ b/x2goserver/sbin/x2godbadmin @@ -357,21 +357,20 @@ if ($Config->param("backend") eq 'postgres') if ($createdb) { - create_database(); - create_tables(); + create_database($host, $port, $dbadmin, $dbadminpass, $db, $x2goadmin, $x2goadminpass, $sslmode); + create_tables($host, $port, $dbadmin, $dbadminpass, $db, $x2goadmin, $sslmode); exit(0); } if ($listusers) { - list_users(); + list_users($host, $port, $dbadmin, $dbadminpass, $sslmode); exit(0); } - my $dbh=DBI->connect("dbi:Pg:dbname=$db;host=$host;port=$port;sslmode=$sslmode", "$dbadmin", "$dbadminpass",{AutoCommit => 1}) or die $_; if ($adduser) { - add_user($adduser); + add_user($host, $port, $dbadmin, $dbadminpass, $db, $adduser, $sslmode); } if ($addgroup) @@ -381,13 +380,13 @@ if ($Config->param("backend") eq 'postgres') foreach (@grp_members) { chomp($_); - add_user($_); + add_user($host, $port, $dbadmin, $dbadminpass, $db, $_, $sslmode); } } if ($rmuser) { - rm_user($rmuser); + rm_user($host, $port, $dbadmin, $dbadminpass, $db, $rmuser, $sslmode); } if ($rmgroup) @@ -397,339 +396,492 @@ if ($Config->param("backend") eq 'postgres') foreach (@grp_members) { chomp($_); - rm_user($_); + rm_user($host, $port, $dbadmin, $dbadminpass, $db, $_, $sslmode); } } - undef $dbh; +} - sub list_users() - { - my $dbh=DBI->connect("dbi:Pg:dbname=postgres;host=$host;port=$port;sslmode=$sslmode", "$dbadmin", "$dbadminpass",{AutoCommit => 1}) or die $_; - my $sth=$dbh->prepare("select rolname from pg_roles where rolname like 'x2gouser_%'"); - $sth->execute()or die; - printf ("%-20s DB user\n","UNIX user"); - print "---------------------------------------\n"; - my @data; - while (@data = $sth->fetchrow_array) - { - @data[0]=~s/x2gouser_//; - printf ("%-20s x2gouser_@data[0]\n",@data[0]); - } - $sth->finish(); - undef $dbh; - } +sub create_tables() +{ + my $host = shift or die "No host parameter supplied"; + my $port = shift or die "No port parameter supplied"; + my $dbadmin = shift or die "No dbadmin parameter supplied"; + my $dbadminpass = shift or die "No dbadminpass (database administrator password) parameter supplied"; + my $db = shift or die "No db parameter supplied"; + my $x2goadmin = shift or die "No x2goadmin (user-level database username) parameter supplied"; - sub rm_user() + my $sslmode = undef; + + if ($Config->param("backend") eq 'postgres') { - my $user=shift; + $sslmode = shift or die "No sslmode parameter supplied"; - print ("rm DB user \"x2gouser_$user\"\n"); + pg_create_tables($host, $port, $dbadmin, $dbadminpass, $db, $x2goadmin, $sslmode); + } + else + { + die "Invalid database backend"; + } +} - my $sth=$dbh->prepare("REVOKE ALL PRIVILEGES ON sessions, used_ports, mounts FROM \"x2gouser_$user\""); - $sth->execute(); +sub pg_create_tables() +{ + my $host = shift or die "No host parameter supplied"; + my $port = shift or die "No port parameter supplied"; + my $dbadmin = shift or die "No dbadmin parameter supplied"; + my $dbadminpass = shift or die "No dbadminpass (database administrator password) parameter supplied"; + my $db = shift or die "No db parameter supplied"; + my $x2goadmin = shift or die "No x2goadmin (user-level database username) parameter supplied"; + my $sslmode = shift or die "No sslmode parameter supplied"; + + my $dbh = DBI->connect("dbi:Pg:dbname=$db;host=$host;port=$port;sslmode=$sslmode", "$dbadmin", "$dbadminpass", {AutoCommit => 1}) or die $_; + + my $sth=$dbh->prepare(" + create table sessions( + session_id text primary key, + display integer not null, + uname text not null, + server text not null, + client inet, + status char(1) not null default 'R', + init_time timestamp not null default now(), + last_time timestamp not null default now(), + cookie char(33), + agent_pid int, + gr_port int, + sound_port int, + fs_port int, + tekictrl_port int, + tekidata_port int, + creator_id text NOT NULL default current_user, + unique(display)) + "); + $sth->execute() or die; + + $sth=$dbh->prepare(" + create VIEW sessions_view as + SELECT + agent_pid, session_id, display, server, status, init_time, cookie, client, gr_port, + sound_port, last_time, uname, fs_port, tekictrl_port, tekidata_port from sessions + where creator_id = current_user + "); + $sth->execute() or die; + + $sth=$dbh->prepare(" + create VIEW servers_view as + SELECT + server, display, status from sessions + "); + $sth->execute() or die; + + $sth=$dbh->prepare(" + create or replace RULE update_sess_priv AS ON UPDATE + TO sessions where (OLD.creator_id <> current_user or OLD.creator_id <> NEW.creator_id) and current_user <> '$x2goadmin' + DO INSTEAD NOTHING + "); + $sth->execute() or die; + + $sth=$dbh->prepare(" + create or replace RULE insert_sess_priv AS ON INSERT + TO sessions where NEW.creator_id <> current_user and current_user <> '$x2goadmin' + DO INSTEAD NOTHING + "); + $sth->execute() or die; + + $sth=$dbh->prepare(" + create or replace RULE delete_sess_priv AS ON DELETE + TO sessions where OLD.creator_id <> current_user and current_user <> '$x2goadmin' + DO INSTEAD NOTHING + "); + $sth->execute() or die; + + $sth=$dbh->prepare(" + create or replace RULE update_sess_view AS ON UPDATE + TO sessions_view DO INSTEAD + update sessions set + status=NEW.status, + last_time=NEW.last_time, + cookie=NEW.cookie, + agent_pid=NEW.agent_pid, + client=NEW.client, + gr_port=NEW.gr_port, + sound_port=NEW.sound_port, + fs_port=NEW.fs_port, + tekictrl_port=NEW.tekictrl_port, + tekidata_port=NEW.tekidata_port + where session_id=OLD.session_id and creator_id=current_user + "); + $sth->execute() or die; + + $sth=$dbh->prepare("create table messages(mess_id varchar(20) primary key, message text)"); + $sth->execute() or die; + + $sth=$dbh->prepare(" + create table user_messages( + mess_id text not null, + uname text not null) + "); + $sth->execute() or die; + + $sth=$dbh->prepare(" + create table used_ports( + server text not null, + session_id text references sessions on delete cascade, + creator_id text NOT NULL default current_user, + port integer primary key) + "); + $sth->execute() or die; + + $sth=$dbh->prepare(" + create VIEW ports_view as + SELECT + server, port from used_ports + "); + $sth->execute() or die; + + $sth=$dbh->prepare(" + create or replace RULE insert_port_priv AS ON INSERT + TO used_ports where NEW.creator_id <> current_user and current_user <> '$x2goadmin' + DO INSTEAD NOTHING + "); + $sth->execute() or die; + + $sth=$dbh->prepare(" + create or replace RULE update_port_priv AS ON UPDATE + TO used_ports where (NEW.creator_id <> current_user or OLD.creator_id <> current_user) and current_user <> '$x2goadmin' + DO INSTEAD NOTHING + "); + $sth->execute() or die; + + $sth=$dbh->prepare(" + create or replace RULE delete_port_priv AS ON DELETE + TO used_ports where OLD.creator_id <> current_user and current_user <> '$x2goadmin' + DO INSTEAD NOTHING + "); + $sth->execute() or die; + + $sth=$dbh->prepare(" + create table mounts( + session_id text references sessions on delete restrict, + path text not null, + client inet not null, + creator_id text NOT NULL default current_user, + primary key(path,client)) + "); + $sth->execute() or die; + + $sth=$dbh->prepare(" + create VIEW mounts_view as + SELECT + client,path, session_id from mounts + where creator_id = current_user + "); + $sth->execute() or die; + + $sth=$dbh->prepare(" + create or replace RULE delete_mounts_view AS ON DELETE + TO mounts_view DO INSTEAD + delete from mounts + where session_id=OLD.session_id and creator_id=current_user and path=OLD.path + "); + $sth->execute() or die; + + $sth=$dbh->prepare(" + create or replace RULE insert_mount_priv AS ON INSERT + TO mounts where NEW.creator_id <> current_user and current_user <> '$x2goadmin' + DO INSTEAD NOTHING + "); + $sth->execute() or die; + + $sth=$dbh->prepare(" + create or replace RULE update_mount_priv AS ON UPDATE + TO mounts where (NEW.creator_id <> current_user or OLD.creator_id <> current_user) and current_user <> '$x2goadmin' + DO INSTEAD NOTHING + "); + $sth->execute() or die; + + $sth=$dbh->prepare(" + create or replace RULE delete_mount_priv AS ON DELETE + TO mounts where OLD.creator_id <> current_user and current_user <> '$x2goadmin' + DO INSTEAD NOTHING + "); + $sth->execute() or die; + + $sth=$dbh->prepare("GRANT ALL PRIVILEGES ON sessions, messages, user_messages, used_ports, mounts TO $x2goadmin"); + $sth->execute() or die; + $sth->finish(); + undef $dbh; +} - my $sth=$dbh->prepare("REVOKE ALL PRIVILEGES ON sessions_view, mounts_view, servers_view, ports_view FROM \"x2gouser_$user\""); - $sth->execute(); +sub create_database() +{ + my $host = shift or die "No host parameter supplied"; + my $port = shift or die "No port parameter supplied"; + my $dbadmin = shift or die "No dbadmin parameter supplied"; + my $dbadminpass = shift or die "No dbadminpass (database administrator password) parameter supplied"; + my $db = shift or die "No db parameter supplied"; + my $x2goadmin = shift or die "No x2goadmin (user-level database username) parameter supplied"; + my $x2goadminpass = shift or die "No x2goadminpass (user-level database pasword) parameter supplied"; - my $sth=$dbh->prepare("DROP OWNED BY \"x2gouser_$user\""); - $sth->execute(); + my $sslmode = undef; - my $sth=$dbh->prepare("drop USER if exists \"x2gouser_$user\""); - $sth->execute(); - $sth->finish(); + if ($Config->param("backend") eq 'postgres') + { + $sslmode = shift or die "No sslmode parameter supplied"; - my ($name, $pass, $uid, $pgid, $quota, $comment, $gcos, $dir, $shell, $expire) = getpwnam($user); - if (! $uid) - { - return; - } - if ( -e "$dir/.x2go/pgsqlpass" ) - { - unlink("$dir/.x2go/pgsqlpass"); - } - if ( -e "$dir/.x2go/sqlpass" ) - { - unlink("$dir/.x2go/sqlpass"); - } + pg_create_database($host, $port, $dbadmin, $dbadminpass, $db, $x2goadmin, $x2goadminpass, $sslmode); } - - sub add_user() + else { - my $user=shift; - my ($name, $pass, $uid, $pgid, $quota, $comment, $gcos, $dir, $shell, $expire) = getpwnam($user); - if (! $name) - { - print "Cannot find user ($user)\n"; - return; - } - elsif ($name eq "root") - { - print "The super-user \"root\" is not allowed to use X2Go\n"; - return; - } - $pass=`pwgen -s -c -n 32 1`; - chomp($pass); + die "Invalid database backend"; + } +} - my $sth=$dbh->prepare("REVOKE ALL PRIVILEGES ON sessions, used_ports, mounts FROM \"x2gouser_$user\""); - $sth->{Warn}=0; - $sth->{PrintError}=0; - $sth->execute(); +sub pg_create_database +{ + my $host = shift or die "No host parameter supplied"; + my $port = shift or die "No port parameter supplied"; + my $dbadmin = shift or die "No dbadmin parameter supplied"; + my $dbadminpass = shift or die "No dbadminpass (database administrator password) parameter supplied"; + my $db = shift or die "No db parameter supplied"; + my $x2goadmin = shift or die "No x2goadmin (user-level database username) parameter supplied"; + my $x2goadminpass = shift or die "No x2goadminpass (user-level database pasword) parameter supplied"; + my $sslmode = shift or die "No sslmode parameter supplied"; + + my $dbh=DBI->connect("dbi:Pg:dbname=postgres;host=$host;port=$port;sslmode=$sslmode", "$dbadmin", "$dbadminpass", {AutoCommit => 1}) or die $_; + #drop db if exists + my $sth=$dbh->prepare("drop database if exists $db"); + $sth->execute(); + #drop x2goadmin + $sth=$dbh->prepare("drop user if exists $x2goadmin"); + $sth->execute(); + #create db + $sth=$dbh->prepare("create database $db"); + $sth->execute() or die; + #create x2goadmin + $sth=$dbh->prepare("create USER $x2goadmin WITH ENCRYPTED PASSWORD '$x2goadminpass'"); + $sth->execute() or die; + #save x2goadmin password + open (FL,"> /etc/x2go/x2gosql/passwords/x2gopgadmin ") or die "Can't write password file /etc/x2go/x2gosql/passwords/x2gopgadmin"; + print FL $x2goadminpass; + close(FL); + $sth->finish(); + undef $dbh; +} - my $sth=$dbh->prepare("REVOKE ALL PRIVILEGES ON sessions_view, mounts_view, servers_view, ports_view FROM \"x2gouser_$user\""); - $sth->{Warn}=0; - $sth->{PrintError}=0; - $sth->execute(); +sub list_users() +{ + my $host = shift or die "No host parameter supplied"; + my $port = shift or die "No port parameter supplied"; + my $dbadmin = shift or die "No dbadmin parameter supplied"; + my $dbadminpass = shift or die "No dbadminpass (database administrator password) parameter supplied"; - my $sth=$dbh->prepare("DROP OWNED BY \"x2gouser_$user\""); - $sth->{Warn}=0; - $sth->{PrintError}=0; - $sth->execute(); + my $sslmode = undef; - $sth=$dbh->prepare("drop USER if exists \"x2gouser_$user\""); - $sth->{Warn}=0; - $sth->{PrintError}=0; - $sth->execute(); + if ($Config->param("backend") eq 'postgres') + { + $sslmode = shift or die "No sslmode parameter supplied"; - print ("create DB user \"x2gouser_$user\"\n"); - $sth=$dbh->prepare("create USER \"x2gouser_$user\" WITH ENCRYPTED PASSWORD '$pass'"); - $sth->execute(); + pg_list_users($host, $port, $dbadmin, $dbadminpass, $sslmode); + } + else + { + die "Invalid database backend"; + } +} - $sth=$dbh->prepare("GRANT INSERT, UPDATE, DELETE ON sessions, used_ports, mounts TO \"x2gouser_$user\""); - $sth->execute(); +sub pg_list_users() +{ + my $host = shift or die "No host parameter supplied"; + my $port = shift or die "No port parameter supplied"; + my $dbadmin = shift or die "No dbadmin parameter supplied"; + my $dbadminpass = shift or die "No dbadminpass (database administrator password) parameter supplied"; + my $sslmode = shift or die "No sslmode parameter supplied"; + + my $dbh=DBI->connect("dbi:Pg:dbname=postgres;host=$host;port=$port;sslmode=$sslmode", "$dbadmin", "$dbadminpass", {AutoCommit => 1}) or die $_; + my $sth=$dbh->prepare("select rolname from pg_roles where rolname like 'x2gouser_%'"); + $sth->execute()or die; + printf ("%-20s DB user\n","UNIX user"); + print "---------------------------------------\n"; + my @data; + while (@data = $sth->fetchrow_array) + { + @data[0]=~s/x2gouser_//; + printf ("%-20s x2gouser_@data[0]\n",@data[0]); + } + $sth->finish(); + undef $dbh; +} - $sth=$dbh->prepare("GRANT SELECT ON used_ports TO \"x2gouser_$user\""); - $sth->execute(); +sub add_user() +{ + my $host = shift or die "No host parameter supplied"; + my $port = shift or die "No port parameter supplied"; + my $dbadmin = shift or die "No dbadmin parameter supplied"; + my $dbadminpass = shift or die "No dbadminpass (database administrator password) parameter supplied"; + my $db = shift or die "No db parameter supplied"; + my $user_to_add = shift or die "No user-to-add parameter supplied"; - $sth=$dbh->prepare("GRANT SELECT, UPDATE, DELETE ON sessions_view, mounts_view, servers_view, ports_view TO \"x2gouser_$user\""); - $sth->execute(); - $sth->finish(); + my $sslmode = undef; - if (! -d "$dir/.x2go" ) - { - if ( defined (&File::Path::make_path) ) - { - File::Path::make_path("$dir/.x2go"); - } - elsif ( defined (&File::Path::mkpath) ) - { - File::Path::mkpath("$dir/.x2go"); - } - else - { - die "Unable to create folders with File::Path"; - } - } + if ($Config->param("backend") eq 'postgres') + { + $sslmode = shift or die "No sslmode parameter supplied"; - #save user password - open (FL,"> $dir/.x2go/pgsqlpass") or die "Can't open password file $dir/.x2go/pgsqlpass"; - print FL $pass; - close(FL); - chmod(0700,"$dir/.x2go"); - chown($uid,$pgid,"$dir/.x2go"); - chmod(0600,"$dir/.x2go/pgsqlpass"); - chown($uid,$pgid,"$dir/.x2go/pgsqlpass"); + pg_add_user($host, $port, $dbadmin, $dbadminpass, $db, $user_to_add, $sslmode); } + else + { + die "Invalid database backend"; + } +} - sub create_tables() +sub pg_add_user() +{ + my $host = shift or die "No host parameter supplied"; + my $port = shift or die "No port parameter supplied"; + my $dbadmin = shift or die "No dbadmin parameter supplied"; + my $dbadminpass = shift or die "No dbadminpass (database administrator password) parameter supplied"; + my $db = shift or die "No db parameter supplied"; + my $user_to_add = shift or die "No user-to-add parameter supplied"; + my $sslmode = shift or die "No sslmode parameter supplied"; + + my $dbh=DBI->connect("dbi:Pg:dbname=$db;host=$host;port=$port;sslmode=$sslmode", "$dbadmin", "$dbadminpass", {AutoCommit => 1}) or die $_; + my ($name, $pass, $uid, $pgid, $quota, $comment, $gcos, $dir, $shell, $expire) = getpwnam($user_to_add); + if (! $name) { - $dbh=DBI->connect("dbi:Pg:dbname=$db;host=$host;port=$port;sslmode=$sslmode", "$dbadmin", "$dbadminpass",{AutoCommit => 1}) or die $_; - my $sth=$dbh->prepare(" - create table sessions( - session_id text primary key, - display integer not null, - uname text not null, - server text not null, - client inet, - status char(1) not null default 'R', - init_time timestamp not null default now(), - last_time timestamp not null default now(), - cookie char(33), - agent_pid int, - gr_port int, - sound_port int, - fs_port int, - tekictrl_port int, - tekidata_port int, - creator_id text NOT NULL default current_user, - unique(display)) - "); - $sth->execute() or die; + print "Cannot find user ($user)\n"; + return; + } + elsif ($name eq "root") + { + print "The super-user \"root\" is not allowed to use X2Go\n"; + return; + } + $pass=`pwgen -s -c -n 32 1`; + chomp($pass); - $sth=$dbh->prepare(" - create VIEW sessions_view as - SELECT - agent_pid, session_id, display, server, status, init_time, cookie, client, gr_port, - sound_port, last_time, uname, fs_port, tekictrl_port, tekidata_port from sessions - where creator_id = current_user - "); - $sth->execute() or die; + my $sth=$dbh->prepare("REVOKE ALL PRIVILEGES ON sessions, used_ports, mounts FROM \"x2gouser_$user_to_add\""); + $sth->{Warn}=0; + $sth->{PrintError}=0; + $sth->execute(); - $sth=$dbh->prepare(" - create VIEW servers_view as - SELECT - server, display, status from sessions - "); - $sth->execute() or die; + my $sth=$dbh->prepare("REVOKE ALL PRIVILEGES ON sessions_view, mounts_view, servers_view, ports_view FROM \"x2gouser_$user_to_add\""); + $sth->{Warn}=0; + $sth->{PrintError}=0; + $sth->execute(); - $sth=$dbh->prepare(" - create or replace RULE update_sess_priv AS ON UPDATE - TO sessions where (OLD.creator_id <> current_user or OLD.creator_id <> NEW.creator_id) and current_user <> '$x2goadmin' - DO INSTEAD NOTHING - "); - $sth->execute() or die; + my $sth=$dbh->prepare("DROP OWNED BY \"x2gouser_$user_to_add\""); + $sth->{Warn}=0; + $sth->{PrintError}=0; + $sth->execute(); - $sth=$dbh->prepare(" - create or replace RULE insert_sess_priv AS ON INSERT - TO sessions where NEW.creator_id <> current_user and current_user <> '$x2goadmin' - DO INSTEAD NOTHING - "); - $sth->execute() or die; + $sth=$dbh->prepare("drop USER if exists \"x2gouser_$user_to_add\""); + $sth->{Warn}=0; + $sth->{PrintError}=0; + $sth->execute(); - $sth=$dbh->prepare(" - create or replace RULE delete_sess_priv AS ON DELETE - TO sessions where OLD.creator_id <> current_user and current_user <> '$x2goadmin' - DO INSTEAD NOTHING - "); - $sth->execute() or die; + print ("create DB user \"x2gouser_$user_to_add\"\n"); + $sth=$dbh->prepare("create USER \"x2gouser_$user_to_add\" WITH ENCRYPTED PASSWORD '$pass'"); + $sth->execute(); - $sth=$dbh->prepare(" - create or replace RULE update_sess_view AS ON UPDATE - TO sessions_view DO INSTEAD - update sessions set - status=NEW.status, - last_time=NEW.last_time, - cookie=NEW.cookie, - agent_pid=NEW.agent_pid, - client=NEW.client, - gr_port=NEW.gr_port, - sound_port=NEW.sound_port, - fs_port=NEW.fs_port, - tekictrl_port=NEW.tekictrl_port, - tekidata_port=NEW.tekidata_port - where session_id=OLD.session_id and creator_id=current_user - "); - $sth->execute() or die; + $sth=$dbh->prepare("GRANT INSERT, UPDATE, DELETE ON sessions, used_ports, mounts TO \"x2gouser_$user_to_add\""); + $sth->execute(); - $sth=$dbh->prepare("create table messages(mess_id varchar(20) primary key, message text)"); - $sth->execute() or die; + $sth=$dbh->prepare("GRANT SELECT ON used_ports TO \"x2gouser_$user_to_add\""); + $sth->execute(); - $sth=$dbh->prepare(" - create table user_messages( - mess_id text not null, - uname text not null) - "); - $sth->execute() or die; + $sth=$dbh->prepare("GRANT SELECT, UPDATE, DELETE ON sessions_view, mounts_view, servers_view, ports_view TO \"x2gouser_$user_to_add\""); + $sth->execute(); + $sth->finish(); - $sth=$dbh->prepare(" - create table used_ports( - server text not null, - session_id text references sessions on delete cascade, - creator_id text NOT NULL default current_user, - port integer primary key) - "); - $sth->execute() or die; + if (! -d "$dir/.x2go" ) + { + if ( defined (&File::Path::make_path) ) + { + File::Path::make_path("$dir/.x2go"); + } + elsif ( defined (&File::Path::mkpath) ) + { + File::Path::mkpath("$dir/.x2go"); + } + else + { + die "Unable to create folders with File::Path"; + } + } - $sth=$dbh->prepare(" - create VIEW ports_view as - SELECT - server, port from used_ports - "); - $sth->execute() or die; + #save user password + open (FL,"> $dir/.x2go/pgsqlpass") or die "Can't open password file $dir/.x2go/pgsqlpass"; + print FL $pass; + close(FL); + chmod(0700,"$dir/.x2go"); + chown($uid,$pgid,"$dir/.x2go"); + chmod(0600,"$dir/.x2go/pgsqlpass"); + chown($uid,$pgid,"$dir/.x2go/pgsqlpass"); +} - $sth=$dbh->prepare(" - create or replace RULE insert_port_priv AS ON INSERT - TO used_ports where NEW.creator_id <> current_user and current_user <> '$x2goadmin' - DO INSTEAD NOTHING - "); - $sth->execute() or die; +sub rm_user() +{ + my $host = shift or die "No host parameter supplied"; + my $port = shift or die "No port parameter supplied"; + my $dbadmin = shift or die "No dbadmin parameter supplied"; + my $dbadminpass = shift or die "No dbadminpass (database administrator password) parameter supplied"; + my $db = shift or die "No db parameter supplied"; + my $user_to_remove = shift or die "No user-to-remove parameter supplied"; - $sth=$dbh->prepare(" - create or replace RULE update_port_priv AS ON UPDATE - TO used_ports where (NEW.creator_id <> current_user or OLD.creator_id <> current_user) and current_user <> '$x2goadmin' - DO INSTEAD NOTHING - "); - $sth->execute() or die; + my $sslmode = undef; - $sth=$dbh->prepare(" - create or replace RULE delete_port_priv AS ON DELETE - TO used_ports where OLD.creator_id <> current_user and current_user <> '$x2goadmin' - DO INSTEAD NOTHING - "); - $sth->execute() or die; + if ($Config->param("backend") eq 'postgres') + { + $sslmode = shift or die "No sslmode parameter supplied"; - $sth=$dbh->prepare(" - create table mounts( - session_id text references sessions on delete restrict, - path text not null, - client inet not null, - creator_id text NOT NULL default current_user, - primary key(path,client)) - "); - $sth->execute() or die; + pg_rm_user($host, $port, $dbadmin, $dbadminpass, $db, $user_to_remove, $sslmode); + } + else + { + die "Invalid database backend"; + } +} - $sth=$dbh->prepare(" - create VIEW mounts_view as - SELECT - client,path, session_id from mounts - where creator_id = current_user - "); - $sth->execute() or die; +sub pg_rm_user() +{ + my $host = shift or die "No host parameter supplied"; + my $port = shift or die "No port parameter supplied"; + my $dbadmin = shift or die "No dbadmin parameter supplied"; + my $dbadminpass = shift or die "No dbadminpass (database administrator password) parameter supplied"; + my $db = shift or die "No db parameter supplied"; + my $user_to_remove = shift or die "No user-to-remove parameter supplied"; + my $sslmode = shift or die "No sslmode parameter supplied"; - $sth=$dbh->prepare(" - create or replace RULE delete_mounts_view AS ON DELETE - TO mounts_view DO INSTEAD - delete from mounts - where session_id=OLD.session_id and creator_id=current_user and path=OLD.path - "); - $sth->execute() or die; + my $dbh=DBI->connect("dbi:Pg:dbname=$db;host=$host;port=$port;sslmode=$sslmode", "$dbadmin", "$dbadminpass", {AutoCommit => 1}) or die $_; - $sth=$dbh->prepare(" - create or replace RULE insert_mount_priv AS ON INSERT - TO mounts where NEW.creator_id <> current_user and current_user <> '$x2goadmin' - DO INSTEAD NOTHING - "); - $sth->execute() or die; + print ("rm DB user \"x2gouser_$user_to_remove\"\n"); - $sth=$dbh->prepare(" - create or replace RULE update_mount_priv AS ON UPDATE - TO mounts where (NEW.creator_id <> current_user or OLD.creator_id <> current_user) and current_user <> '$x2goadmin' - DO INSTEAD NOTHING - "); - $sth->execute() or die; + my $sth=$dbh->prepare("REVOKE ALL PRIVILEGES ON sessions, used_ports, mounts FROM \"x2gouser_$user_to_remove\""); + $sth->execute(); - $sth=$dbh->prepare(" - create or replace RULE delete_mount_priv AS ON DELETE - TO mounts where OLD.creator_id <> current_user and current_user <> '$x2goadmin' - DO INSTEAD NOTHING - "); - $sth->execute() or die; + my $sth=$dbh->prepare("REVOKE ALL PRIVILEGES ON sessions_view, mounts_view, servers_view, ports_view FROM \"x2gouser_$user_to_remove\""); + $sth->execute(); - $sth=$dbh->prepare("GRANT ALL PRIVILEGES ON sessions, messages, user_messages, used_ports, mounts TO $x2goadmin"); - $sth->execute() or die; - $sth->finish(); - undef $dbh; - } + my $sth=$dbh->prepare("DROP OWNED BY \"x2gouser_$user_to_remove\""); + $sth->execute(); + + my $sth=$dbh->prepare("drop USER if exists \"x2gouser_$user_to_remove\""); + $sth->execute(); + $sth->finish(); - sub create_database + my ($name, $pass, $uid, $pgid, $quota, $comment, $gcos, $dir, $shell, $expire) = getpwnam($user_to_remove); + if (! $uid) { - my $dbh=DBI->connect("dbi:Pg:dbname=postgres;host=$host;port=$port;sslmode=$sslmode", "$dbadmin", "$dbadminpass",{AutoCommit => 1}) or die $_; - #drop db if exists - my $sth=$dbh->prepare("drop database if exists $db"); - $sth->execute(); - #drop x2goadmin - $sth=$dbh->prepare("drop user if exists $x2goadmin"); - $sth->execute(); - #create db - $sth=$dbh->prepare("create database $db"); - $sth->execute() or die; - #create x2goadmin - $sth=$dbh->prepare("create USER $x2goadmin WITH ENCRYPTED PASSWORD '$x2goadminpass'"); - $sth->execute() or die; - #save x2goadmin password - open (FL,"> /etc/x2go/x2gosql/passwords/x2gopgadmin ") or die "Can't write password file /etc/x2go/x2gosql/passwords/x2gopgadmin"; - print FL $x2goadminpass; - close(FL); - $sth->finish(); - undef $dbh; + return; + } + if ( -e "$dir/.x2go/pgsqlpass" ) + { + unlink("$dir/.x2go/pgsqlpass"); + } + if ( -e "$dir/.x2go/sqlpass" ) + { + unlink("$dir/.x2go/sqlpass"); } } -- Alioth's /home/x2go-admin/maintenancescripts/git/hooks/post-receive-email on /srv/git/code.x2go.org/x2goserver.git
-
git-admin＠x2go.org