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 eddd356978e74d2d44550d961fe4966323e33cf9 Author: Mihai Moldovan <ionic@ionic.de> Date: Sat Feb 24 00:16:32 2018 +0100 x2goserver/sbin/x2godbadmin: update copyright header. --- debian/changelog | 1 + x2goserver/sbin/x2godbadmin | 8 +++++--- 2 files changed, 6 insertions(+), 3 deletions(-) diff --git a/debian/changelog b/debian/changelog index 3938088..dcb399d 100644 --- a/debian/changelog +++ b/debian/changelog @@ -84,6 +84,7 @@ x2goserver (4.1.0.1-0x2go1) UNRELEASED; urgency=medium - x2goserver/sbin/x2godbadmin: implement MySQL support, based on Walid Moghrabis changes, but slightly amended. Caveat: INCOMPATIBLE DATABASE SCHEMA! + - x2goserver/sbin/x2godbadmin: update copyright header. * debian/{control,compat}: + Bump DH compat level to 9. * debian/: diff --git a/x2goserver/sbin/x2godbadmin b/x2goserver/sbin/x2godbadmin index 0bd1555..b483ecc 100755 --- a/x2goserver/sbin/x2godbadmin +++ b/x2goserver/sbin/x2godbadmin @@ -1,8 +1,10 @@ #!/usr/bin/perl -# Copyright (C) 2007-2015 X2Go Project - http://wiki.x2go.org -# Copyright (C) 2007-2015 Oleksandr Shneyder <oleksandr.shneyder@obviously-nice.de> -# Copyright (C) 2007-2015 Heinz-Markus Graesing <heinz-m.graesing@obviously-nice.de> +# Copyright (C) 2007-2018 X2Go Project - http://wiki.x2go.org +# Copyright (C) 2007-2018 Oleksandr Shneyder <oleksandr.shneyder@obviously-nice.de> +# Copyright (C) 2007-2018 Heinz-Markus Graesing <heinz-m.graesing@obviously-nice.de> +# Copyright (C) 2017-2018 Walid Moghrabi <w.moghrabi@servicemagic.eu> +# Copyright (C) 2018 Mihai Moldovan <ionic@ionic.de> # # This program is free software; you can redistribute it and/or modify # it under the terms of the GNU General Public License as published by -- 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 8d70e8d4f91653fd0e411368f1ba1ea283bb6b88 Author: Mihai Moldovan <ionic@ionic.de> Date: Sat Feb 24 00:15:26 2018 +0100 x2goserver/sbin/x2godbadmin: implement MySQL support, based on Walid Moghrabis changes, but slightly amended. Caveat: INCOMPATIBLE DATABASE SCHEMA! --- debian/changelog | 3 + x2goserver/sbin/x2godbadmin | 366 ++++++++++++++++++++++++++++++++++++++++++++ 2 files changed, 369 insertions(+) diff --git a/debian/changelog b/debian/changelog index 0c99f9f..3938088 100644 --- a/debian/changelog +++ b/debian/changelog @@ -81,6 +81,9 @@ x2goserver (4.1.0.1-0x2go1) UNRELEASED; urgency=medium namespace. - x2goserver/sbin/x2godbadmin: remove potentially confusing whitespace. - x2goserver/sbin/x2godbadmin: fix up other try blocks; whitespace only. + - x2goserver/sbin/x2godbadmin: implement MySQL support, based on Walid + Moghrabis changes, but slightly amended. Caveat: INCOMPATIBLE DATABASE + SCHEMA! * debian/{control,compat}: + Bump DH compat level to 9. * debian/: diff --git a/x2goserver/sbin/x2godbadmin b/x2goserver/sbin/x2godbadmin index 4332738..0bd1555 100755 --- a/x2goserver/sbin/x2godbadmin +++ b/x2goserver/sbin/x2godbadmin @@ -403,6 +403,111 @@ if ($Config->param("backend") eq 'postgres') } } +if ($Config->param("backend") eq 'mysql') +{ + my $host=$Config->param("mysql.host"); + my $port=$Config->param("mysql.port"); + my $unet=$Config->param("mysql.unet"); + my $dbadmin=$Config->param("mysql.dbadmin"); + my $x2goadmin="x2godbuser"; + my $x2goadminpass=`pwgen 8 1`; + chomp ($x2goadminpass); + my $db="x2go_sessions"; + + if (!$host) + { + $host='localhost'; + } + if (!$port) + { + $port='3306'; + } + if (!$dbadmin) + { + $dbadmin='root'; + } + + open (FL,"< /etc/x2go/x2gosql/passwords/mysqladmin") or die "Can't read password file /etc/x2go/x2gosql/passwords/mysqladmin"; + 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:mysql:database=$db;host=$host;port=$port", "$dbadmin", "$dbadminpass", {AutoCommit => 1, RaiseError => 0, PrintError => 0}) + ) + { + $createdb = 1; + last; + }; + if (!$createdb) { + $dbh = DBI->connect("dbi:mysql:database=$db;host=$host;port=$port", "$dbadmin", "$dbadminpass", {AutoCommit => 1}); + if ($dbh) { + # Intentionally left blank. + # MySQL support was only added in X2Go Server 4.1.0.1, while SQLite and PostgreSQL support + # have been around prior to 4.1.0.0, which is the first version that introduced new teki-related + # columns. + } + if ($dbh) { + undef $dbh; + } + exit(0); + } + else + { + print "No session DB found. Use --createdb instead of --updatedb.\n"; + } + } + + if ($createdb) + { + create_database($host, $port, $dbadmin, $dbadminpass, $db, $x2goadmin, $x2goadminpass, $unet); + create_tables($host, $port, $dbadmin, $dbadminpass, $db, $x2goadmin, $unet); + exit(0); + } + + if ($listusers) + { + list_users($host, $port, $dbadmin, $dbadminpass); + exit(0); + } + + if ($adduser) + { + add_user($host, $port, $dbadmin, $dbadminpass, $db, $adduser, $unet); + } + + if ($addgroup) + { + my ($name, $passwd, $gid, $members) = getgrnam($addgroup); + my @grp_members=split(' ',$members); + foreach (@grp_members) + { + chomp($_); + add_user($host, $port, $dbadmin, $dbadminpass, $db, $_, $unet); + } + } + + if ($rmuser) + { + rm_user($host, $port, $dbadmin, $dbadminpass, $db, $rmuser, $unet); + } + + if ($rmgroup) + { + my ($name, $passwd, $gid, $members) = getgrnam($rmgroup); + my @grp_members=split(' ',$members); + foreach (@grp_members) + { + chomp($_); + rm_user($host, $port, $dbadmin, $dbadminpass, $db, $_, $unet); + } + } +} + sub create_tables() { my $host = shift or die "No host parameter supplied"; @@ -413,6 +518,7 @@ sub create_tables() my $x2goadmin = shift or die "No x2goadmin (user-level database username) parameter supplied"; my $sslmode = undef; + my $unet = undef; if ($Config->param("backend") eq 'postgres') { @@ -420,6 +526,12 @@ sub create_tables() pg_create_tables($host, $port, $dbadmin, $dbadminpass, $db, $x2goadmin, $sslmode); } + elsif ($Config->param("backend") eq 'mysql') + { + $unet = shift or die "No unet parameter supplied"; + + mysql_create_tables($host, $port, $dbadmin, $dbadminpass, $db, $x2goadmin, $unet); + } else { die "Invalid database backend"; @@ -615,6 +727,87 @@ sub pg_create_tables() undef $dbh; } +sub mysql_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 $unet = shift or die "No unet parameter supplied"; + + my $dbh = DBI->connect("dbi:mysql:database=$db;host=$host;port=$port", "$dbadmin", "$dbadminpass", {AutoCommit => 1}) or die $_; + + my $sth=$dbh->prepare(" + CREATE TABLE `sessions` ( + `session_id` varchar(2048) NOT NULL, + `display` int NOT NULL, + `uname` varchar(100) NOT NULL, + `server` varchar(512) NOT NULL, + `client` varchar(45) DEFAULT NULL, + `status` char(1) NOT NULL DEFAULT 'R', + `init_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, + `last_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, + `cookie` char(33) DEFAULT NULL, + `agent_pid` int DEFAULT NULL, + `gr_port` int DEFAULT NULL, + `sound_port` int DEFAULT NULL, + `fs_port` int DEFAULT NULL, + `tekictrl_port` int DEFAULT NULL, + `tekidata_port` int DEFAULT NULL, + PRIMARY KEY (`session_id`), + UNIQUE KEY `idx_sessions_display_server` (`display`,`server`)) + "); + $sth->execute() or die; + + $sth=$dbh->prepare(" + CREATE TABLE `messages` ( + `mess_id` varchar(20) NOT NULL, + `mesage` longtext, + PRIMARY KEY (`mess_id`)) + "); + $sth->execute() or die; + + $sth=$dbh->prepare(" + CREATE TABLE `user_messages` ( + `mess_id` varchar(20) NOT NULL, + `uname` varchar(100) NOT NULL, + PRIMARY KEY (`mess_id`)) + "); + $sth->execute() or die; + + + $sth=$dbh->prepare(" + CREATE TABLE `used_ports` ( + `server` varchar(512) NOT NULL, + `session_id` varchar(2048) DEFAULT NULL, + `port` int NOT NULL, + PRIMARY KEY (`port`), + KEY `used_ports_session_id_fkey_idx` (`session_id`), + CONSTRAINT `used_ports_session_id_fkey` FOREIGN KEY (`session_id`) REFERENCES `sessions` (`session_id`) ON DELETE CASCADE ON UPDATE NO ACTION) + "); + $sth->execute() or die; + + $sth=$dbh->prepare(" + CREATE TABLE `mounts` ( + `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT, + `session_id` varchar(2048) DEFAULT NULL, + `path` varchar(2048) NOT NULL, + `client` varchar(45) NOT NULL, + PRIMARY KEY (`id`), + UNIQUE KEY `mounts_pkey` (`path`,`client`), + KEY `mounts_session_id_fkey_idx` (`session_id`), + CONSTRAINT `mounts_session_id_fkey` FOREIGN KEY (`session_id`) REFERENCES `sessions` (`session_id`) ON DELETE RESTRICT ON UPDATE NO ACTION) + "); + $sth->execute() or die; + + $sth=$dbh->prepare("GRANT ALL PRIVILEGES ON `sessions`, `messages`, `user_messages`, `used_ports`, `mounts` TO `$x2goadmin`@`$unet`"); + $sth->execute() or die; + $sth->finish(); + undef $dbh; +} + sub create_database() { my $host = shift or die "No host parameter supplied"; @@ -626,6 +819,7 @@ sub create_database() my $x2goadminpass = shift or die "No x2goadminpass (user-level database pasword) parameter supplied"; my $sslmode = undef; + my $unet = undef; if ($Config->param("backend") eq 'postgres') { @@ -633,6 +827,12 @@ sub create_database() pg_create_database($host, $port, $dbadmin, $dbadminpass, $db, $x2goadmin, $x2goadminpass, $sslmode); } + elsif ($Config->param("backend") eq 'mysql') + { + $unet = shift or die "No unet parameter supplied"; + + mysql_create_database($host, $port, $dbadmin, $dbadminpass, $db, $x2goadmin, $x2goadminpass, $unet); + } else { die "Invalid database backend"; @@ -671,6 +871,38 @@ sub pg_create_database undef $dbh; } +sub mysql_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 $unet = shift or die "No unet parameter supplied"; + + my $dbh=DBI->connect("dbi:mysql:database=mysql;host=$host;port=$port", "$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`@`$unet`"); + $sth->execute(); + #create db + $sth=$dbh->prepare("CREATE DATABASE IF NOT EXISTS `$db`"); + $sth->execute() or die; + #create x2goadmin + $sth=$dbh->prepare("CREATE USER `$x2goadmin`@`$unet` IDENTIFIED BY '$x2goadminpass'"); + $sth->execute() or die; + #save x2goadmin password + open (FL,"> /etc/x2go/x2gosql/passwords/x2gomysqladmin") or die "Can't write password file /etc/x2go/x2gosql/passwords/x2gomysqladmin"; + print FL $x2goadminpass; + close(FL); + $sth->finish(); + undef $dbh; +} + sub list_users() { my $host = shift or die "No host parameter supplied"; @@ -686,6 +918,10 @@ sub list_users() pg_list_users($host, $port, $dbadmin, $dbadminpass, $sslmode); } + elsif ($Config->param("backend") eq 'mysql') + { + mysql_list_users($host, $port, $dbadmin, $dbadminpass, $db); + } else { die "Invalid database backend"; @@ -715,6 +951,28 @@ sub pg_list_users() undef $dbh; } +sub mysql_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 $dbh=DBI->connect("dbi:mysql:database=$db;host=$host;port=$port", "$dbadmin", "$dbadminpass", {AutoCommit => 1}) or die $_; + my $sth=$dbh->prepare("select user from mysql.user where user 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 add_user() { my $host = shift or die "No host parameter supplied"; @@ -725,6 +983,7 @@ sub add_user() my $user_to_add = shift or die "No user-to-add parameter supplied"; my $sslmode = undef; + my $unet = undef; if ($Config->param("backend") eq 'postgres') { @@ -732,6 +991,12 @@ sub add_user() pg_add_user($host, $port, $dbadmin, $dbadminpass, $db, $user_to_add, $sslmode); } + elsif ($Config->param("backend") eq 'mysql') + { + $unet = shift or die "No unet parameter supplied"; + + mysql_add_user($host, $port, $dbadmin, $dbadminpass, $db, $user_to_add, $unet); + } else { die "Invalid database backend"; @@ -823,6 +1088,71 @@ sub pg_add_user() chown($uid,$pgid,"$dir/.x2go/pgsqlpass"); } +sub mysql_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 $unet = shift or die "No unet parameter supplied"; + + my $dbh=DBI->connect("dbi:mysql:database=$db;host=$host;port=$port", "$dbadmin", "$dbadminpass", {AutoCommit => 1}) or die $_; + my ($name, $pass, $uid, $pgid, $quota, $comment, $gcos, $dir, $shell, $expire) = getpwnam($user_to_add); + if (! $name) + { + print "Cannot find user ($user_to_add)\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); + + my $sth=$dbh->prepare("DROP USER IF EXISTS `x2gouser_$user_to_add`@`$unet`"); + $sth->{Warn}=0; + $sth->{PrintError}=0; + $sth->execute(); + + print ("create DB user \"x2gouser_$user_to_add\"\n"); + $sth=$dbh->prepare("CREATE USER `x2gouser_$user_to_add`@`$unet` IDENTIFIED BY '$pass'"); + $sth->execute(); + + $sth=$dbh->prepare("GRANT SELECT, INSERT, UPDATE, DELETE ON * TO `x2gouser_$user_to_add`@`$unet`"); + $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/mysqlpass") or die "Can't open password file $dir/.x2go/mysqlpass"; + print FL $pass; + close(FL); + chmod(0700,"$dir/.x2go"); + chown($uid,$pgid,"$dir/.x2go"); + chmod(0600,"$dir/.x2go/mysqlpass"); + chown($uid,$pgid,"$dir/.x2go/mysqlpass"); +} + sub rm_user() { my $host = shift or die "No host parameter supplied"; @@ -833,6 +1163,7 @@ sub rm_user() my $user_to_remove = shift or die "No user-to-remove parameter supplied"; my $sslmode = undef; + my $unet = undef; if ($Config->param("backend") eq 'postgres') { @@ -840,6 +1171,12 @@ sub rm_user() pg_rm_user($host, $port, $dbadmin, $dbadminpass, $db, $user_to_remove, $sslmode); } + elsif ($Config->param("backend") eq 'mysql') + { + $unet = shift or die "No unet parameter supplied"; + + mysql_rm_user($host, $port, $dbadmin, $dbadminpass, $db, $user_to_remove, $unet); + } else { die "Invalid database backend"; @@ -887,3 +1224,32 @@ sub pg_rm_user() unlink("$dir/.x2go/sqlpass"); } } + +sub mysql_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 $unet = shift or die "No unet parameter supplied"; + + my $dbh=DBI->connect("dbi:mysql:database=$db;host=$host;port=$port", "$dbadmin", "$dbadminpass", {AutoCommit => 1}) or die $_; + + print ("rm DB user \"x2gouser_$user_to_remove\"\n"); + + my $sth=$dbh->prepare("DROP USER IF EXISTS `x2gouser_$user_to_remove`@`$unet`"); + $sth->execute(); + $sth->finish(); + + my ($name, $pass, $uid, $pgid, $quota, $comment, $gcos, $dir, $shell, $expire) = getpwnam($user_to_remove); + if (! $uid) + { + return; + } + if ( -e "$dir/.x2go/mysqlpass" ) + { + unlink("$dir/.x2go/mysqlpass"); + } +} -- Alioth's /home/x2go-admin/maintenancescripts/git/hooks/post-receive-email on /srv/git/code.x2go.org/x2goserver.git