[X2Go-Commits] [x2goserver] 01/04: x2goserver/sbin/x2godbadmin: implement MySQL support, based on Walid Moghrabis changes, but slightly amended.

23 Feb 2018

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

    

2025

2024

2023

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

[X2Go-Commits] [x2goserver] 01/04: x2goserver/sbin/x2godbadmin: implement MySQL support, based on Walid Moghrabis changes, but slightly amended.