package: x2goserver
priority: wishlist
tags: patch
This is a proposal patch for a MySQL session storage database backend for X2Go
Server.
I did this simply because :
1) I'm not used to PostgreSQL and much more skilled with MySQL
2) I do believe MySQL is pretty common on Linux and is widely spread so I
guess this can be useful for many other people willing to build a strong TCE
setup and not willing to dig into PostgreSQL
3) I find it a lot easier to build a multi-master replication scenario with
MySQL than with PostgreSQL (but as I said, I'm much more skilled wih MySQL)
This backend is tightly inspired by the PostgreSQL backend and work exactly the
same so this is a drop in replacement for it, til now, all my tests seems to
work correctly, feel free to test by yourself and report errors.
Regards,
Walid Moghrabi
TRAVAUX.COM
BAT I - PARC CEZANNE 2 290 AVENUE GALILEE - CS 80403
13591 AIX EN PROVENCE CEDEX 3
---
DISCLAIMER: This e-mail is private and confidential and may contain proprietary
or legally privileged information. It is for the intended recipient only. If
you have received this email in error, please notify the author by replying to
it and then destroy it. If you are not the intended recipient you must not use,
disclose, distribute, copy, print or rely on this e-mail or any attachment.
Thank you
diff --git a/X2Go/Server/DB.pm b/X2Go/Server/DB.pm
index 41a22e8..b2501f3 100644
--- a/X2Go/Server/DB.pm
+++ b/X2Go/Server/DB.pm
@@ -38,6 +38,7 @@
use X2Go::Config qw( get_sqlconfig );
use X2Go::Log qw( loglevel );
use X2Go::Server::DB::PostgreSQL;
+use X2Go::Server::DB::MySQL;
use X2Go::Utils qw( system_capture_merged_output system_capture_stdout_output );
setlogmask( LOG_UPTO(loglevel()) );
@@ -56,7 +57,7 @@
my $dbuser;
my $sslmode;
-if ($backend ne 'postgres' && $backend ne 'sqlite')
+if ($backend ne 'postgres' && $backend ne 'mysql' && $backend ne 'sqlite')
{
die "unknown backend $backend";
}
@@ -76,6 +77,10 @@
{
X2Go::Server::DB::PostgreSQL::dbsys_rmsessionsroot($sid);
}
+ if($backend eq 'mysql')
+ {
+ X2Go::Server::DB::MySQL::dbsys_rmsessionsroot($sid);
+ }
if($backend eq 'sqlite')
{
system_capture_merged_output("$x2go_lib_path/libx2go-server-db-sqlite3-wrapper", "rmsessionsroot", "$sid");
@@ -88,6 +93,10 @@
if ($backend eq 'postgres')
{
X2Go::Server::DB::PostgreSQL::dbsys_deletemounts($sid);
+ }
+ if ($backend eq 'mysql')
+ {
+ X2Go::Server::DB::MySQL::dbsys_deletemounts($sid);
}
if ($backend eq 'sqlite')
{
@@ -103,6 +112,10 @@
{
return X2Go::Server::DB::PostgreSQL::dbsys_listsessionsroot($server);
}
+ if ($backend eq 'mysql')
+ {
+ return X2Go::Server::DB::MySQL::dbsys_listsessionsroot($server);
+ }
if($backend eq 'sqlite')
{
return split("\n",system_capture_merged_output("$x2go_lib_path/libx2go-server-db-sqlite3-wrapper", "listsessionsroot", "$server"));
@@ -114,6 +127,10 @@
if ($backend eq 'postgres')
{
return X2Go::Server::DB::PostgreSQL::dbsys_listsessionsroot_all();
+ }
+ if ($backend eq 'mysql')
+ {
+ return X2Go::Server::DB::MySQL::dbsys_listsessionsroot_all();
}
if ($backend eq 'sqlite')
{
@@ -128,6 +145,10 @@
if ($backend eq 'postgres')
{
@mounts = X2Go::Server::DB::PostgreSQL::dbsys_getmounts($sid);
+ }
+ if ($backend eq 'mysql')
+ {
+ @mounts = X2Go::Server::DB::MySQL::dbsys_getmounts($sid);
}
if ($backend eq 'sqlite')
{
@@ -146,6 +167,10 @@
{
@mounts = X2Go::Server::DB::PostgreSQL::db_getmounts($sid);
}
+ if($backend eq 'mysql')
+ {
+ @mounts = X2Go::Server::DB::MySQL::db_getmounts($sid);
+ }
if ($backend eq 'sqlite')
{
@mounts = split("\n",system_capture_merged_output("$x2go_lib_path/libx2go-server-db-sqlite3-wrapper", "getmounts", "$sid"));
@@ -163,6 +188,10 @@
{
X2Go::Server::DB::PostgreSQL::db_deletemount($sid, $path);
}
+ if ($backend eq 'mysql')
+ {
+ X2Go::Server::DB::MySQL::db_deletemount($sid, $path);
+ }
if ($backend eq 'sqlite')
{
system_capture_merged_output("$x2go_lib_path/libx2go-server-db-sqlite3-wrapper", "deletemount", "$sid", "$path");
@@ -179,6 +208,10 @@
if ($backend eq 'postgres')
{
$res_ok = X2Go::Server::DB::PostgreSQL::db_insertmount($sid, $path, $client);
+ }
+ if ($backend eq 'mysql')
+ {
+ $res_ok = X2Go::Server::DB::MySQL::db_insertmount($sid, $path, $client);
}
if ($backend eq 'sqlite')
{
@@ -200,6 +233,10 @@
{
X2Go::Server::DB::PostgreSQL::db_insertsession($display, $server, $sid);
}
+ if ($backend eq 'mysql')
+ {
+ X2Go::Server::DB::MySQL::db_insertsession($display, $server, $sid);
+ }
if ($backend eq 'sqlite')
{
my $err=system_capture_merged_output("$x2go_lib_path/libx2go-server-db-sqlite3-wrapper", "insertsession", "$display", "$server", "$sid");
@@ -220,6 +257,10 @@
if ($backend eq 'postgres')
{
X2Go::Server::DB::PostgreSQL::db_insertshadowsession($display, $server, $sid, $shadreq_user);
+ }
+ if ($backend eq 'mysql')
+ {
+ X2Go::Server::DB::MySQL::db_insertshadowsession($display, $server, $sid, $shadreq_user);
}
if ($backend eq 'sqlite')
{
@@ -247,6 +288,10 @@
{
X2Go::Server::DB::PostgreSQL::db_createsession($sid, $cookie, $pid, $client, $gr_port, $snd_port, $fs_port, $tekictrl_port, $tekidata_port);
}
+ if ($backend eq 'mysql')
+ {
+ X2Go::Server::DB::MySQL::db_createsession($sid, $cookie, $pid, $client, $gr_port, $snd_port, $fs_port, $tekictrl_port, $tekidata_port);
+ }
if ($backend eq 'sqlite')
{
my $err= system_capture_merged_output("$x2go_lib_path/libx2go-server-db-sqlite3-wrapper", "createsession", "$sid", "$cookie", "$pid", "$client", "$gr_port", "$snd_port", "$fs_port", "$tekictrl_port", "$tekidata_port");
@@ -273,6 +318,11 @@
# for PostgreSQL we can use the normal db_createsession code...
X2Go::Server::DB::PostgreSQL::db_createsession($sid, $cookie, $pid, $client, $gr_port, $snd_port, $fs_port, -1, -1);
}
+ if ($backend eq 'mysql')
+ {
+ # for MySQL we can use the normal db_createsession code...
+ X2Go::Server::DB::MySQL::db_createsession($sid, $cookie, $pid, $client, $gr_port, $snd_port, $fs_port, -1, -1);
+ }
if ($backend eq 'sqlite')
{
my $err=system_capture_merged_output("$x2go_lib_path/libx2go-server-db-sqlite3-wrapper", "createshadowsession", "$sid", "$cookie", "$pid", "$client", "$gr_port", "$snd_port", "$fs_port", "$shadreq_user");
@@ -292,6 +342,10 @@
if ($backend eq 'postgres')
{
X2Go::Server::DB::PostgreSQL::db_insertport($server, $sid, $sshport);
+ }
+ if ($backend eq 'mysql')
+ {
+ X2Go::Server::DB::MySQL::db_insertport($server, $sid, $sshport);
}
if ($backend eq 'sqlite')
{
@@ -313,6 +367,10 @@
{
X2Go::Server::DB::PostgreSQL::db_rmport($server, $sid, $sshport);
}
+ if ($backend eq 'mysql')
+ {
+ X2Go::Server::DB::MySQL::db_rmport($server, $sid, $sshport);
+ }
if ($backend eq 'sqlite')
{
system_capture_merged_output("$x2go_lib_path/libx2go-server-db-sqlite3-wrapper", "rmport", "$server", "$sid", "$sshport");
@@ -333,6 +391,10 @@
{
X2Go::Server::DB::PostgreSQL::db_resume($client, $sid, $gr_port, $snd_port, $fs_port, $tekictrl_port, $tekidata_port);
}
+ if ($backend eq 'mysql')
+ {
+ X2Go::Server::DB::MySQL::db_resume($client, $sid, $gr_port, $snd_port, $fs_port, $tekictrl_port, $tekidata_port);
+ }
if ($backend eq 'sqlite')
{
system_capture_merged_output("$x2go_lib_path/libx2go-server-db-sqlite3-wrapper", "resume", "$client", "$sid", "$gr_port", "$snd_port", "$fs_port", "$tekictrl_port", "$tekidata_port");
@@ -348,6 +410,10 @@
{
X2Go::Server::DB::PostgreSQL::db_changestatus($status, $sid);
}
+ if ($backend eq 'mysql')
+ {
+ X2Go::Server::DB::MySQL::db_changestatus($status, $sid);
+ }
if ($backend eq 'sqlite')
{
system_capture_merged_output("$x2go_lib_path/libx2go-server-db-sqlite3-wrapper", "changestatus", "$status", "$sid");
@@ -362,6 +428,10 @@
if ($backend eq 'postgres')
{
$status = X2Go::Server::DB::PostgreSQL::db_getstatus($sid);
+ }
+ if ($backend eq 'mysql')
+ {
+ $status = X2Go::Server::DB::MySQL::db_getstatus($sid);
}
if ($backend eq 'sqlite')
{
@@ -379,6 +449,10 @@
if ($backend eq 'postgres')
{
@displays = X2Go::Server::DB::PostgreSQL::db_getdisplays($server);
+ }
+ if ($backend eq 'mysql')
+ {
+ @displays = X2Go::Server::DB::MySQL::db_getdisplays($server);
}
if ($backend eq 'sqlite')
{
@@ -398,6 +472,10 @@
{
@ports = X2Go::Server::DB::PostgreSQL::db_getports($server);
}
+ if ($backend eq 'mysql')
+ {
+ @ports = X2Go::Server::DB::MySQL::db_getports($server);
+ }
if ($backend eq 'sqlite')
{
@ports = split("\n",system_capture_merged_output("$x2go_lib_path/libx2go-server-db-sqlite3-wrapper", "getports", "$server"));
@@ -413,6 +491,10 @@
if ($backend eq 'postgres')
{
@servers = X2Go::Server::DB::PostgreSQL::db_getservers();
+ }
+ if ($backend eq 'mysql')
+ {
+ @servers = X2Go::Server::DB::MySQL::db_getservers();
}
if ($backend eq 'sqlite')
{
@@ -431,6 +513,10 @@
{
$agent = X2Go::Server::DB::PostgreSQL::db_getagent($sid);
}
+ if ($backend eq 'mysql')
+ {
+ $agent = X2Go::Server::DB::MySQL::db_getagent($sid);
+ }
if($backend eq 'sqlite')
{
$agent=system_capture_merged_output("$x2go_lib_path/libx2go-server-db-sqlite3-wrapper", "getagent", "$sid");
@@ -447,6 +533,10 @@
{
$display = X2Go::Server::DB::PostgreSQL::db_getdisplay($sid);
}
+ if ($backend eq 'mysql')
+ {
+ $display = X2Go::Server::DB::MySQL::db_getdisplay($sid);
+ }
if ($backend eq 'sqlite')
{
$display=system_capture_merged_output("$x2go_lib_path/libx2go-server-db-sqlite3-wrapper", "getdisplay", "$sid");
@@ -462,6 +552,10 @@
{
return X2Go::Server::DB::PostgreSQL::db_listsessions($server);
}
+ if ($backend eq 'mysql')
+ {
+ return X2Go::Server::DB::MySQL::db_listsessions($server);
+ }
if ($backend eq 'sqlite')
{
return split("\n",system_capture_merged_output("$x2go_lib_path/libx2go-server-db-sqlite3-wrapper", "listsessions", "$server"));
@@ -473,6 +567,10 @@
if($backend eq 'postgres')
{
return X2Go::Server::DB::PostgreSQL::db_listsessions_all();
+ }
+ if($backend eq 'mysql')
+ {
+ return X2Go::Server::DB::MySQL::db_listsessions_all();
}
if ($backend eq 'sqlite')
{
@@ -487,6 +585,10 @@
{
return X2Go::Server::DB::PostgreSQL::db_listshadowsessions($server);
}
+ if ($backend eq 'mysql')
+ {
+ return X2Go::Server::DB::MySQL::db_listshadowsessions($server);
+ }
if ($backend eq 'sqlite')
{
return split("\n",system_capture_merged_output("$x2go_lib_path/libx2go-server-db-sqlite3-wrapper", "listshadowsessions", "$server"));
@@ -499,6 +601,10 @@
{
return X2Go::Server::DB::PostgreSQL::db_listshadowsessions_all();
}
+ if($backend eq 'mysql')
+ {
+ return X2Go::Server::DB::MySQL::db_listshadowsessions_all();
+ }
if ($backend eq 'sqlite')
{
return split("\n",system_capture_merged_output("$x2go_lib_path/libx2go-server-db-sqlite3-wrapper", "listshadowsessions_all"));
diff --git a/X2Go/Server/DB/MySQL.pm b/X2Go/Server/DB/MySQL.pm
new file mode 100644
index 0000000..c8e81de
--- /dev/null
+++ b/X2Go/Server/DB/MySQL.pm
@@ -0,0 +1,739 @@
+# Copyright (C) 2007-2015 X2Go Project - http://wiki.x2go.org
+#
+# 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
+# the Free Software Foundation; either version 2 of the License, or
+# (at your option) any later version.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+# GNU General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License
+# along with this program; if not, write to the
+# Free Software Foundation, Inc.,
+# 51 Franklin St, Fifth Floor, Boston, MA 02110-1301, USA.
+#
+# Copyright (C) 2007-2015 Oleksandr Shneyder <oleksandr.shney...@obviously-nice.de>
+# Copyright (C) 2007-2015 Heinz-Markus Graesing <heinz-m.graes...@obviously-nice.de>
+
+package X2Go::Server::DB::MySQL;
+
+=head1 NAME
+
+X2Go::Server::DB::MySQL - X2Go Session Database package for Perl (MySQL backend)
+
+=head1 DESCRIPTION
+
+X2Go::Server::DB::MySQL Perl package for X2Go::Server.
+
+=cut
+
+use strict;
+use DBI;
+use POSIX;
+use Sys::Syslog qw( :standard :macros );
+
+use X2Go::Log qw( loglevel );
+use X2Go::Config qw( get_config get_sqlconfig );
+use X2Go::Utils qw( sanitizer system_capture_stdout_output is_true );
+
+setlogmask( LOG_UPTO(loglevel()) );
+
+use base 'Exporter';
+
+our @EXPORT=('db_listsessions','db_listsessions_all', 'db_getservers', 'db_getagent', 'db_resume', 'db_changestatus', 'db_getstatus',
+ 'db_getdisplays', 'db_insertsession', 'db_insertshadowsession', 'db_getports', 'db_insertport', 'db_rmport', 'db_createsession', 'db_createshadowsession', 'db_insertmount',
+ 'db_getmounts', 'db_deletemount', 'db_getdisplay', 'dbsys_getmounts', 'dbsys_listsessionsroot',
+ 'dbsys_listsessionsroot_all', 'dbsys_rmsessionsroot', 'dbsys_deletemounts', 'db_listshadowsessions','db_listshadowsessions_all', );
+
+my ($uname, $pass, $uid, $pgid, $quota, $comment, $gcos, $homedir, $shell, $expire) = getpwuid($<);
+my $realuser=$uname;
+
+my $host;
+my $port;
+my $db="x2go_sessions";
+#my $dbpass="iech7yaH";
+#my $dbuser="x2godbuser";
+my $dbpass;
+my $dbuser;
+my $sslmode;
+my $with_TeKi;
+
+sub init_db
+{
+ # the $Config is required later (see below...)
+ my $Config = get_config;
+ $with_TeKi = is_true($Config->param("telekinesis.enable"));
+
+ if ( ! ( $dbuser and $dbpass ) )
+ {
+ my $SqlConfig = get_sqlconfig;
+ my $x2go_lib_path=system_capture_stdout_output("x2gopath", "libexec");
+
+ my $backend=$SqlConfig->param("backend");
+ if ( $backend ne "mysql" )
+ {
+ die "X2Go server is not configured to use the MySQL session db backend";
+ }
+
+ $host=$SqlConfig->param("mysql.host");
+ $port=$SqlConfig->param("mysql.port");
+ if (!$host)
+ {
+ $host='localhost';
+ }
+ if (!$port)
+ {
+ $port='3306';
+ }
+ my $passfile;
+ if ($uname eq 'root')
+ {
+ $dbuser='x2godbuser';
+ $passfile="/etc/x2go/x2gosql/passwords/x2goadmin";
+ }
+ else
+ {
+ $dbuser="x2gouser_$uname";
+ $passfile="$homedir/.x2go/sqlpass";
+ }
+ open (FL,"< $passfile") or die "Can't read password file $passfile<br>";
+ $dbpass=<FL>;
+ close(FL);
+ chomp($dbpass);
+ }
+
+ my $dbh=DBI->connect("dbi:mysql:database=$db;host=$host;port=$port","$dbuser","$dbpass",{RaiseError => 1}) or die $_;
+
+ return $dbh;
+}
+
+sub dbsys_rmsessionsroot
+{
+ my $dbh = init_db();
+ check_root();
+ my $sid=shift or die "argument \"session_id\" missed";
+ my $sth=$dbh->prepare("delete from sessions where session_id=?");
+ $sth->execute($sid);
+ if ($sth->err())
+ {
+ syslog('error', "rmsessionsroot (MySQL session db backend) failed with exitcode: $sth->err()");
+ die;
+ }
+ $sth->finish();
+ undef $dbh;
+ return 1;
+}
+
+sub dbsys_listsessionsroot
+{
+ my $dbh = init_db();
+ check_root();
+ my $server=shift or die "argument \"server\" missed";
+ my @strings;
+ my $sth=$dbh->prepare("select agent_pid, session_id, display, server, status,
+ DATE_FORMAT(init_time, '%Y-%m-%dT%H:%I:%S'),
+ cookie,client,gr_port,sound_port,
+ DATE_FORMAT(last_time, '%Y-%m-%dT%H:%I:%S'),
+ uname,
+ MOD(UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(init_time), 86400),fs_port,
+ tekictrl_port, tekidata_port from sessions
+ where server=? order by status desc");
+ $sth->execute($server);
+ if ($sth->err()) {
+ syslog('error', "listsessionsroot (MySQL session db backend) failed with exitcode: $sth->err()");
+ die();
+ }
+ my @sessions = fetchrow_array_datasets($sth);
+ $sth->finish();
+ undef $dbh;
+ return @sessions;
+}
+
+sub dbsys_listsessionsroot_all
+{
+ my $dbh = init_db();
+ check_root();
+ my @strings;
+ my $sth=$dbh->prepare("select agent_pid, session_id, display, server, status,
+ DATE_FORMAT(init_time, '%Y-%m-%dT%H:%I:%S'),
+ cookie,client,gr_port,sound_port,
+ DATE_FORMAT(last_time, '%Y-%m-%dT%H:%I:%S'),
+ uname,
+ MOD(UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(init_time), 86400),fs_port,
+ tekictrl_port, tekidata_port from sessions
+ order by status desc");
+ $sth->execute();
+ if ($sth->err())
+ {
+ syslog('error', "listsessionsroot_all (MySQL session db backend) failed with exitcode: $sth->err()");
+ die();
+ }
+ my @sessions = fetchrow_array_datasets($sth);
+ $sth->finish();
+ undef $dbh;
+ return @sessions;
+}
+
+sub dbsys_deletemounts
+{
+ my $dbh = init_db();
+ my $sid=shift or die "argument \"session_id\" missed";
+ check_user($sid);
+ my $sth=$dbh->prepare("delete from mounts where session_id=?");
+ $sth->execute($sid);
+ if ($sth->err())
+ {
+ syslog('error', "deletemounts (MySQL session db backend) failed with exitcode: $sth->err()");
+ die();
+ }
+ $sth->finish();
+ undef $dbh;
+ return 1;
+}
+
+sub db_getmounts
+{
+ my $dbh = init_db();
+ my $sid=shift or die "argument \"session_id\" missed";
+ $sid = sanitizer('x2gosid', $sid) or die "argument \"session_id\" malformed";
+ check_user($sid);
+ my @strings;
+ my $sth=$dbh->prepare("select client, path from mounts where session_id=?");
+ $sth->execute($sid);
+ if ($sth->err())
+ {
+ syslog('error', "getmounts (MySQL session db backend) failed with exitcode: $sth->err()");
+ die;
+ }
+ my @mounts = fetchrow_array_datasets($sth);
+ $sth->finish();
+ undef $dbh;
+ return @mounts;
+}
+
+sub db_deletemount
+{
+ my $dbh = init_db();
+ my $sid=shift or die "argument \"session_id\" missed";
+ $sid = sanitizer('x2gosid', $sid) or die "argument \"session_id\" malformed";
+ my $path=shift or die "argument \"path\" missed";
+ check_user($sid);
+ my $sth=$dbh->prepare("delete from mounts where session_id=? and path=?");
+ $sth->execute($sid, $path);
+ if ($sth->err())
+ {
+ syslog('error', "deletemount (MySQL session db backend) failed with exitcode: $sth->err()");
+ die();
+ }
+ $sth->finish();
+ undef $dbh;
+ return 1;
+}
+
+sub db_insertmount
+{
+ my $dbh = init_db();
+ my $sid=shift or die "argument \"session_id\" missed";
+ $sid = sanitizer('x2gosid', $sid) or die "argument \"session_id\" malformed";
+ my $path=shift or die "argument \"path\" missed";
+ my $client=shift or die "argument \"client\" missed";
+ check_user($sid);
+ my $sth=$dbh->prepare("insert into mounts (session_id,path,client) values (?, ?, ?)");
+ $sth->execute($sid, $path, $client);
+ my $success = 0;
+ if(! $sth->err())
+ {
+ $success = 1;
+ } else {
+ syslog('debug', "insertmount (MySQL session db backend) failed with exitcode: $sth->err(), this issue will be interpreted as: SSHFS share already mounted");
+ }
+ $sth->finish();
+ undef $dbh;
+ return $success;
+}
+
+sub db_insertsession
+{
+ my $dbh = init_db();
+ my $display=shift or die "argument \"display\" missed";
+ $display = sanitizer('num', $display) or die "argument \"display\" malformed";
+ my $server=shift or die "argument \"server\" missed";
+ my $sid=shift or die "argument \"session_id\" missed";
+ $sid = sanitizer('x2gosid', $sid) or die "argument \"session_id\" malformed";
+ check_user($sid);
+ my $sth=$dbh->prepare("insert into sessions (display,server,uname,session_id, init_time, last_time) values
+ (?, ?, ?, ?, NOW(), NOW())");
+ $sth->execute($display, $server, $realuser, $sid) or die $_;
+ $sth->finish();
+ undef $dbh;
+ return 1;
+}
+
+sub db_insertshadowsession
+{
+ my $dbh = init_db();
+ my $display=shift or die "argument \"display\" missed";
+ $display = sanitizer('num', $display) or die "argument \"display\" malformed";
+ my $server=shift or die "argument \"server\" missed";
+ my $sid=shift or die "argument \"session_id\" missed";
+ $sid = sanitizer('x2gosid', $sid) or die "argument \"session_id\" malformed";
+ my $shadreq_user = shift or die "argument \"shadreq_user\" missed";
+ my $fake_sid = $sid;
+ $fake_sid =~ s/$shadreq_user-/$realuser-/;
+ check_user($fake_sid);
+ my $sth=$dbh->prepare("insert into sessions (display,server,uname,session_id, init_time, last_time) values
+ (?, ?, ?, ?, NOW(), NOW())");
+ $sth->execute($display, $server, $shadreq_user, $sid) or die $_;
+ $sth->finish();
+ undef $dbh;
+ return 1;
+}
+
+sub db_createsession
+{
+ my $dbh = init_db();
+ my $sid=shift or die "argument \"session_id\" missed";
+ $sid = sanitizer('x2gosid', $sid) or die "argument \"session_id\" malformed";
+ my $cookie=shift or die"argument \"cookie\" missed";
+ my $pid=shift or die"argument \"pid\" missed";
+ $pid = sanitizer('num', $pid) or die "argument \"pid\" malformed";
+ my $client=shift or die"argument \"client\" missed";
+ my $gr_port=shift or die"argument \"gr_port\" missed";
+ $gr_port = sanitizer('num', $gr_port) or die "argument \"gr_port\" malformed";
+ my $snd_port=shift or die"argument \"snd_port\" missed";
+ $snd_port = sanitizer('num', $snd_port) or die "argument \"snd_port\" malformed";
+ my $fs_port=shift or die"argument \"fs_port\" missed";
+ $fs_port = sanitizer('num', $fs_port) or die "argument \"fs_port\" malformed";
+ my $tekictrl_port=shift or die "argument \"tekictrl_port\" missed";
+ $tekictrl_port = sanitizer('pnnum', $tekictrl_port) or die "argument \"tekictrl_port\" malformed";
+ my $tekidata_port=shift or die "argument \"tekidata_port\" missed";
+ $tekidata_port = sanitizer('pnnum', $tekidata_port) or die "argument \"tekidata_port\" malformed";
+ check_user($sid);
+ my $sth=$dbh->prepare("update sessions set status='R',last_time=NOW(),cookie=?,agent_pid=?,
+ client=?,gr_port=?,sound_port=?,fs_port=?,tekictrl_port=?,tekidata_port=? where session_id=? and uname=?");
+ $sth->execute($cookie, $pid, $client, $gr_port, $snd_port, $fs_port, $tekictrl_port, $tekidata_port, $sid, $realuser);
+ if ($sth->err())
+ {
+ syslog('error', "createsession (MySQL session db backend) failed with exitcode: $sth->err()");
+ die();
+ }
+ $sth->finish();
+ undef $dbh;
+ return 1;
+}
+
+sub db_createshadowsession
+{
+ my $dbh = init_db();
+ my $sid=shift or die "argument \"session_id\" missed";
+ $sid = sanitizer('x2gosid', $sid) or die "argument \"session_id\" malformed";
+ my $cookie=shift or die"argument \"cookie\" missed";
+ my $pid=shift or die"argument \"pid\" missed";
+ $pid = sanitizer('num', $pid) or die "argument \"pid\" malformed";
+ my $client=shift or die"argument \"client\" missed";
+ my $gr_port=shift or die"argument \"gr_port\" missed";
+ $gr_port = sanitizer('num', $gr_port) or die "argument \"gr_port\" malformed";
+ my $snd_port=shift or die"argument \"snd_port\" missed";
+ $snd_port = sanitizer('num', $snd_port) or die "argument \"snd_port\" malformed";
+ my $fs_port=shift or die"argument \"fs_port\" missed";
+ $fs_port = sanitizer('num', $fs_port) or die "argument \"fs_port\" malformed";
+ my $shadreq_user = shift or die "argument \"shadreq_user\" missed";
+ check_user($sid);
+ my $sth=$dbh->prepare("update sessions set status='R',last_time=NOW(),cookie=?,agent_pid=?,
+ client=?,gr_port=?,sound_port=?,fs_port=?,tekictrl_port=-1,tekidata_port=-1 where session_id=? and uname=?");
+ $sth->execute($cookie, $pid, $client, $gr_port, $snd_port, $fs_port, $sid, $shadreq_user);
+ if ($sth->err())
+ {
+ syslog('error', "createshadowsession (MySQL session db backend) failed with exitcode: $sth->err()");
+ die();
+ }
+ $sth->finish();
+ undef $dbh;
+ return 1;
+}
+
+sub db_insertport
+{
+ my $dbh = init_db();
+ my $server=shift or die "argument \"server\" missed";
+ my $sid=shift or die "argument \"session_id\" missed";
+ $sid = sanitizer('x2gosid', $sid) or die "argument \"session_id\" malformed";
+ my $sshport=shift or die "argument \"port\" missed";
+ my $sth=$dbh->prepare("insert into used_ports (server,session_id,port) values (?, ?, ?)");
+ check_user($sid);
+ $sth->execute($server, $sid, $sshport);
+ if ($sth->err())
+ {
+ syslog('error', "insertport (MySQL session db backend) failed with exitcode: $sth->err()");
+ die();
+ }
+ $sth->finish();
+ undef $dbh;
+ return 1;
+}
+
+sub db_rmport
+{
+ my $dbh = init_db();
+ my $server=shift or die "argument \"server\" missed";
+ my $sid=shift or die "argument \"session_id\" missed";
+ $sid = sanitizer('x2gosid', $sid) or die "argument \"session_id\" malformed";
+ my $sshport=shift or die "argument \"port\" missed";
+ my $sth=$dbh->prepare("delete from used_ports where server=? and session_id=? and port=?");
+ check_user($sid);
+ $sth->execute($server, $sid, $sshport);
+ if ($sth->err()) {
+ syslog('error', "rmport (MySQL session db backend) failed with exitcode: $sth->err()");
+ die();
+ }
+ $sth->finish();
+ undef $dbh;
+ return 1;
+}
+
+sub db_resume
+{
+ my $dbh = init_db();
+ my $client=shift or die "argument \"client\" missed";
+ my $sid=shift or die "argument \"session_id\" missed";
+ $sid = sanitizer('x2gosid', $sid) or die "argument \"session_id\" malformed";
+ my $gr_port=shift or die "argument \"gr_port\" missed";
+ $gr_port = sanitizer('num', $gr_port) or die "argument \"gr_port\" malformed";
+ my $snd_port=shift or die "argument \"snd_port\" missed";
+ $snd_port = sanitizer('num', $snd_port) or die "argument \"snd_port\" malformed";
+ my $fs_port=shift or die "argument \"fs_port\" missed";
+ $fs_port = sanitizer('num', $fs_port) or die "argument \"fs_port\" malformed";
+ my $tekictrl_port=shift or die"argument \"tekictrl_port\" missed";
+ $tekictrl_port = sanitizer('pnnum', $tekictrl_port) or die "argument \"tekictrl_port\" malformed";
+ my $tekidata_port=shift or die"argument \"tekidata_port\" missed";
+ $tekidata_port = sanitizer('pnnum', $tekidata_port) or die "argument \"tekidata_port\" malformed";
+ check_user($sid);
+ my $sth=$dbh->prepare("update sessions set last_time=NOW(),status='R',
+ client=?,gr_port=?,sound_port=?,fs_port=?,tekictrl_port=?,tekidata_port=? where session_id = ? and uname=?");
+ $sth->execute($client, $gr_port, $snd_port, $fs_port, $tekictrl_port, $tekidata_port, $sid, $realuser);
+ if ($sth->err())
+ {
+ syslog('error', "resume (MySQL session db backend) failed with exitcode: $sth->err()");
+ die();
+ }
+ $sth->finish();
+ undef $dbh;
+ return 1;
+}
+
+sub db_changestatus
+{
+ my $dbh = init_db();
+ my $status=shift or die "argument \"status\" missed";
+ my $sid=shift or die "argument \"session_id\" missed";
+ $sid = sanitizer('x2gosid', $sid) or die "argument \"session_id\" malformed";
+ check_user($sid);
+
+ # we need to be able to change the state of normal sessions ($realuser == $effective_user)
+ # _and_ desktop sharing session ($realuser != $effective_user). Thus, extracting the effective
+ # username from the session ID...
+ my $effective_user = $sid;
+ $effective_user =~ s/\-[0-9]+\-[0-9]{10}_.*//;
+
+ my $sth=$dbh->prepare("update sessions set last_time=NOW(),
+ status=? where session_id = ? and uname=?");
+ $sth->execute($status, $sid, $effective_user);
+ if ($sth->err())
+ {
+ syslog('error', "changestatus (MySQL session db backend) failed with exitcode: $sth->err()");
+ die();
+ }
+ $sth->finish();
+ undef $dbh;
+ return 1;
+}
+
+sub db_getstatus
+{
+ my $dbh = init_db();
+ my $sid=shift or die "argument \"session_id\" missed";
+ $sid = sanitizer('x2gosid', $sid) or die "argument \"session_id\" malformed";
+ check_user($sid);
+ my $sth=$dbh->prepare("select status from sessions where session_id = ?");
+ $sth->execute($sid);
+ if ($sth->err())
+ {
+ syslog('error', "changestatus (MySQL session db backend) failed with exitcode: $sth->err()");
+ die();
+ }
+ my @data;
+ my $status;
+ @data = $sth->fetchrow_array;
+ {
+ $status = $data[0];
+ }
+ $sth->finish();
+ undef $dbh;
+ return $status;
+}
+
+sub db_getdisplays
+{
+ my $dbh = init_db();
+ #ignore $server
+ my @strings;
+ my $sth=$dbh->prepare("select display from sessions");
+ $sth->execute();
+ if ($sth->err())
+ {
+ syslog('error', "getdisplays (MySQL session db backend) failed with exitcode: $sth->err()");
+ die();
+ }
+ my @data;
+ my $i=0;
+ while (@data = $sth->fetchrow_array)
+ {
+ $strings[$i++]='|'.$data[0].'|';
+ }
+ $sth->finish();
+ undef $dbh;
+ return join("\n",@strings);
+}
+
+sub db_getports
+{
+ my $dbh = init_db();
+ #ignore $server
+ my $server=shift or die "argument \"server\" missed";
+ my @strings;
+ my $sth=$dbh->prepare("select port from used_ports");
+ $sth->execute();
+ if ($sth->err())
+ {
+ syslog('error', "getports (MySQL session db backend) failed with exitcode: $sth->err()");
+ die();
+ }
+ my @data;
+ my $i=0;
+ while (@data = $sth->fetchrow_array)
+ {
+ $strings[$i++]='|'.$data[0].'|';
+ }
+ $sth->finish();
+ undef $dbh;
+ return join("\n",@strings);
+}
+
+sub db_getservers
+{
+ my $dbh = init_db();
+ my @strings;
+ my $sth=$dbh->prepare("select server,count(*) from sessions where status != 'F' group by server");
+ $sth->execute();
+ if ($sth->err())
+ {
+ syslog('error', "getservers (MySQL session db backend) failed with exitcode: $sth->err()");
+ die();
+ }
+ my @data;
+ my $i=0;
+ while (@data = $sth->fetchrow_array)
+ {
+ $strings[$i++]=$data[0];
+ }
+ $sth->finish();
+ undef $dbh;
+ return join("\n",@strings);
+}
+
+sub db_getagent
+{
+ my $dbh = init_db();
+ my $sid=shift or die "argument \"session_id\" missed";
+ $sid = sanitizer('x2gosid', $sid) or die "argument \"session_id\" malformed";
+ my $agent;
+ check_user($sid);
+ my $sth=$dbh->prepare("select agent_pid from sessions
+ where session_id=?");
+ $sth->execute($sid);
+ if ($sth->err())
+ {
+ syslog('error', "getagent (MySQL session db backend) failed with exitcode: $sth->err()");
+ die();
+ }
+ my @data;
+ my $i=0;
+ if(@data = $sth->fetchrow_array)
+ {
+ $agent=$data[0];
+ }
+ $sth->finish();
+ undef $dbh;
+ return $agent;
+}
+
+sub db_getdisplay
+{
+ my $dbh = init_db();
+ my $sid=shift or die "argument \"session_id\" missed";
+ $sid = sanitizer('x2gosid', $sid) or die "argument \"session_id\" malformed";
+ my $display;
+ check_user($sid);
+ my $sth=$dbh->prepare("select display from sessions
+ where session_id =?");
+ $sth->execute($sid);
+ if ($sth->err())
+ {
+ syslog('error', "getdisplay (MySQL session db backend) failed with exitcode: $sth->err()");
+ die();
+ }
+ my @data;
+ my $i=0;
+ if(@data = $sth->fetchrow_array)
+ {
+ $display=$data[0];
+ }
+ $sth->finish();
+ undef $dbh;
+ return $display;
+}
+
+sub db_listsessions
+{
+ my $dbh = init_db();
+ my $server=shift or die "argument \"server\" missed";
+
+ my @strings;
+ my $sth=$dbh->prepare("select agent_pid, session_id, display, server, status,
+ DATE_FORMAT(init_time, '%Y-%m-%dT%H:%I:%S'),
+ cookie,client,gr_port,sound_port,
+ DATE_FORMAT(last_time, '%Y-%m-%dT%H:%I:%S'),
+ uname,
+ MOD(UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(init_time), 86400),fs_port,
+ tekictrl_port,tekidata_port from sessions
+ where status !='F' and server=? and uname=?
+ and ( session_id not like '%XSHAD%') order by status desc");
+ $sth->execute($server, $realuser);
+ if ($sth->err())
+ {
+ syslog('error', "listsessions (MySQL session db backend) failed with exitcode: $sth->err()");
+ die();
+ }
+ my @sessions = fetchrow_array_datasets($sth);
+ $sth->finish();
+ undef $dbh;
+ return @sessions;
+}
+
+sub db_listsessions_all
+{
+ my $dbh = init_db();
+ my @strings;
+ my $sth=$dbh->prepare("select agent_pid, session_id, display, server, status,
+ DATE_FORMAT(init_time, '%Y-%m-%dT%H:%I:%S'),
+ cookie,client,gr_port,sound_port,
+ DATE_FORMAT(last_time, '%Y-%m-%dT%H:%I:%S'),
+ uname,
+ MOD(UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(init_time), 86400),fs_port,
+ tekictrl_port,tekidata_port from sessions
+ where status !='F' and uname=? and ( session_id not like '%XSHAD%') order by status desc");
+
+ $sth->execute($realuser);
+ if ($sth->err())
+ {
+ syslog('error', "listsessions_all (MySQL session db backend) failed with exitcode: $sth->err()");
+ die();
+ }
+ my @sessions = fetchrow_array_datasets($sth);
+ $sth->finish();
+ undef $dbh;
+ return @sessions;
+}
+
+sub db_listshadowsessions
+{
+ my $dbh = init_db();
+ my $server=shift or die "argument \"server\" missed";
+ my @strings;
+ my $sth=$dbh->prepare("select agent_pid, session_id, display, server, status,
+ DATE_FORMAT(init_time, '%Y-%m-%dT%H:%I:%S'),
+ cookie,client,gr_port,sound_port,
+ DATE_FORMAT(last_time, '%Y-%m-%dT%H:%I:%S'),
+ uname,
+ MOD(UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(init_time), 86400),fs_port from sessions
+ where status !='F' and server=? and uname=?
+ and ( session_id like '%XSHAD%') order by status desc");
+ $sth->execute($server, $realuser);
+ if ($sth->err())
+ {
+ syslog('error', "listsessions (MySQL session db backend) failed with exitcode: $sth->err()");
+ die();
+ }
+ my @sessions = fetchrow_array_datasets($sth);
+ $sth->finish();
+ undef $dbh;
+ return @sessions;
+}
+
+sub db_listshadowsessions_all
+{
+ my $dbh = init_db();
+ my @strings;
+ my $sth=$dbh->prepare("select agent_pid, session_id, display, server, status,
+ DATE_FORMAT(init_time, '%Y-%m-%dT%H:%I:%S'),
+ cookie,client,gr_port,sound_port,
+ DATE_FORMAT(last_time, '%Y-%m-%dT%H:%I:%S'),
+ uname,
+ MOD(UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(init_time), 86400),fs_port from sessions
+ where status !='F' and uname=? and ( session_id like '%XSHAD%') order by status desc");
+
+ $sth->execute($realuser);
+ if ($sth->err())
+ {
+ syslog('error', "listsessions_all (MySQL session db backend) failed with exitcode: $sth->err()");
+ die();
+ }
+ my @sessions = fetchrow_array_datasets($sth);
+ $sth->finish();
+ undef $dbh;
+ return @sessions;
+}
+
+sub check_root
+{
+ if ($realuser ne "root")
+ {
+ die "$realuser, you can not do this job";
+ }
+}
+
+sub check_user
+{
+ my $sid=shift or die "argument \"session_id\" missed";
+ return if $realuser eq "root";
+
+ # session id looks like someuser-51-1304005895_stDgnome-session_dp24
+ # during DB insertsession it only looks like someuser-51-1304005895
+
+ # derive the session's user from the session name/id
+ my $user = "$sid";
+
+ # handle ActiveDirectory Domain user accounts gracefully
+ $realuser =~ s/\\//;
+
+ # perform the user check
+ $user =~ s/($realuser-[0-9]{2,}-[0-9]{10,}_st(D|R).*|.*-[0-9]{2,}-[0-9]{10,}_stS(0|1)XSHAD$realuser.*)/$realuser/;
+ $user eq $realuser or die "$realuser is not authorized";
+}
+
+sub fetchrow_array_datasets
+{
+ my $sth = shift;
+ my @lines;
+ my @data;
+ while (@data = $sth->fetchrow_array())
+ {
+ push @lines, join('|', @data);
+ }
+ return @lines;
+}
+
+1;
diff --git a/x2goserver-common/etc/x2gosql/sql b/x2goserver-common/etc/x2gosql/sql
index 66a7f7d..113799d 100644
--- a/x2goserver-common/etc/x2gosql/sql
+++ b/x2goserver-common/etc/x2gosql/sql
@@ -1,4 +1,4 @@
-#postgres or sqlite
+#postgres or mysql or sqlite
backend=sqlite
[postgres]
@@ -12,3 +12,11 @@
#require: connect only with SSL
#default - prefer
ssl=prefer
+
+[mysql]
+host=localhost
+port=3306
+#restrict users network, "%" for any
+unet=localhost
+#database admin (must have permissions to create databases and users)
+dbadmin=root
diff --git a/x2goserver/sbin/x2godbadmin b/x2goserver/sbin/x2godbadmin
index d3c6266..e746e43 100755
--- a/x2goserver/sbin/x2godbadmin
+++ b/x2goserver/sbin/x2godbadmin
@@ -63,6 +63,9 @@
my $Config = get_sqlconfig();
+#################################
+# SQLite Backend
+#################################
if ($Config->param("backend") eq 'sqlite')
{
my $user="x2gouser";
@@ -236,496 +239,829 @@
}
}
-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)
+#################################
+# MySQL Backend
+#################################
+if ($Config->param("backend") eq 'mysql')
{
- $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);
-
-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})
- ) {
- $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();
+ 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/myadmin ") or die "Can't read password file /etc/x2go/x2gosql/passwords/myadmin";
+ 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",{RaiseError => 1})
+ ) {
+ $createdb = 1;
+ last;
+ };
+ if (!$createdb) {
+ $dbh = DBI->connect("dbi:mysql:database=$db;host=$host;port=$port","$dbadmin","$dbadminpass",{RaiseError => 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 ($dbh) {
+ undef $dbh;
+ }
+ exit(0);
+ } else {
+ print "No session DB found. Use --createdb instead of --updatedb.\n";
}
- if ($dbh) {
- undef $dbh;
- }
+ }
+
+ if ($createdb)
+ {
+ my_create_database();
+ my_create_tables();
exit(0);
- } else {
- print "No session DB found. Use --createdb instead of --updatedb.\n";
}
-}
-
-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 ($addgroup)
-{
- my ($name, $passwd, $gid, $members) = getgrnam( $addgroup);
- my @grp_members=split(' ',$members);
- foreach (@grp_members)
+
+ if ($listusers)
{
- chomp($_);
- add_user($_);
+ my_list_users();
+ exit(0);
}
-}
-
-if ($rmuser)
-{
- rm_user($rmuser);
-}
-
-if ($rmgroup)
-{
- my ($name, $passwd, $gid, $members) = getgrnam( $rmgroup);
- my @grp_members=split(' ',$members);
- foreach (@grp_members)
+
+ my $dbh=DBI->connect("dbi:mysql:database=$db;host=$host;port=$port","$dbadmin","$dbadminpass",{RaiseError => 1}) or die $_;
+ if ($adduser)
{
- chomp($_);
- rm_user($_);
+ my_add_user($adduser);
}
-}
-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 ($addgroup)
{
- @data[0]=~s/x2gouser_//;
- printf ("%-20s x2gouser_@data[0]\n",@data[0]);
+ my ($name, $passwd, $gid, $members) = getgrnam( $addgroup);
+ my @grp_members=split(' ',$members);
+ foreach (@grp_members)
+ {
+ chomp($_);
+ my_add_user($_);
+ }
}
- $sth->finish();
+
+ if ($rmuser)
+ {
+ my_rm_user($rmuser);
+ }
+
+ if ($rmgroup)
+ {
+ my ($name, $passwd, $gid, $members) = getgrnam( $rmgroup);
+ my @grp_members=split(' ',$members);
+ foreach (@grp_members)
+ {
+ chomp($_);
+ my_rm_user($_);
+ }
+ }
undef $dbh;
-}
-
-sub rm_user()
-{
- my $user=shift;
-
- print ("rm DB user \"x2gouser_$user\"\n");
-
- my $sth=$dbh->prepare("REVOKE ALL PRIVILEGES ON sessions, used_ports, mounts FROM \"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 OWNED BY \"x2gouser_$user\"");
- $sth->execute();
-
- 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)
+
+ sub my_list_users()
{
- return;
+ my $dbh=DBI->connect("dbi:mysql:database=$db;host=$host;port=$port","$dbadmin","$dbadminpass",{RaiseError => 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;
}
- if ( -e "$dir/.x2go/sqlpass" )
+
+ sub my_rm_user()
{
- unlink("$dir/.x2go/sqlpass");
+ my $user=shift;
+
+ print ("rm DB user \"x2gouser_$user\"\n");
+
+ my $sth=$dbh->prepare("DROP USER IF EXISTS `x2gouser_$user`@`$unet`");
+ $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 my_add_user()
+ {
+ 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("DROP USER IF EXISTS `x2gouser_$user`@`$unet`");
+ $sth->{Warn}=0;
+ $sth->{PrintError}=0;
+ $sth->execute();
+
+ print ("create DB user \"x2gouser_$user\"\n");
+ $sth=$dbh->prepare("CREATE USER `x2gouser_$user`@`$unet` IDENTIFIED BY '$pass'");
+ $sth->execute();
+
+ $sth=$dbh->prepare("GRANT SELECT, INSERT, UPDATE, DELETE ON * TO `x2gouser_$user`@`$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/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");
+ }
+
+ sub my_create_tables()
+ {
+ $dbh=DBI->connect("dbi:mysql:database=$db;host=$host;port=$port","$dbadmin","$dbadminpass",{RaiseError => 1}) or die $_;
+ my $sth=$dbh->prepare("
+ CREATE TABLE `sessions` (
+ `session_id` varchar(500) NOT NULL,
+ `display` int(11) NOT NULL,
+ `uname` varchar(100) NOT NULL,
+ `server` varchar(100) NOT NULL,
+ `client` varchar(20) 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(45) DEFAULT NULL,
+ `agent_pid` int(11) DEFAULT NULL,
+ `gr_port` int(11) DEFAULT NULL,
+ `sound_port` int(11) DEFAULT NULL,
+ `fs_port` int(11) DEFAULT NULL,
+ `tekictrl_port` int(11) DEFAULT NULL,
+ `tekidata_port` int(11) DEFAULT NULL,
+ PRIMARY KEY (`session_id`),
+ UNIQUE KEY `display_UNIQUE` (`display`))
+ ");
+ $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(100) NOT NULL,
+ `session_id` varchar(500) DEFAULT NULL,
+ `port` int(11) 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` int(11) NOT NULL AUTO_INCREMENT,
+ `session_id` varchar(500) DEFAULT NULL,
+ `path` varchar(512) NOT NULL,
+ `client` varchar(20) 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 UPDATE NO ACTION)
+ ");
+ $sth->execute() or die;
+
+ $sth=$dbh->prepare("GRANT ALL PRIVILEGES ON * TO `$x2goadmin`@`$unet`");
+ $sth->execute() or die;
+ $sth->finish();
+ undef $dbh;
+ }
+
+ sub my_create_database
+ {
+ my $dbh=DBI->connect("dbi:mysql:database=mysql;host=$host;port=$port","$dbadmin","$dbadminpass",{RaiseError => 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/x2goadmin ") or die "Can't write password file /etc/x2go/x2gosql/passwords/x2goadmin";
+ print FL $x2goadminpass;
+ close(FL);
+ $sth->finish();
+ undef $dbh;
}
}
-sub add_user()
+
+#################################
+# PostgreSQL Backend
+#################################
+if ($Config->param("backend") eq 'postgres')
{
- my $user=shift;
- my ($name, $pass, $uid, $pgid, $quota, $comment, $gcos, $dir, $shell, $expire) = getpwnam($user);
- if (! $name)
+ my $host=$Config->param("postgres.host");
+ my $port=$Config->param("postgres.port");
+ my $sslmode=$Config->param("postgres.ssl");
+ if (!$sslmode)
{
- print "Cannot find user ($user)\n";
- return;
- } elsif ($name eq "root") {
- print "The super-user \"root\" is not allowed to use X2Go\n";
- return;
+ $sslmode="prefer";
}
- $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" )
+ my $dbadmin=$Config->param("postgres.dbadmin");
+ my $x2goadmin="x2godbuser";
+ my $x2goadminpass=`pwgen 8 1`;
+ chomp ($x2goadminpass);
+ my $db="x2go_sessions";
+
+ if (!$host)
{
- 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";
- }
+ $host='localhost';
}
-
- #save user password
- open (FL,"> $dir/.x2go/sqlpass") or die "Can't open password file $dir/.x2go/sqlpass";
- print FL $pass;
+ 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);
- chmod(0700,"$dir/.x2go");
- chown($uid,$pgid,"$dir/.x2go");
- chmod(0600,"$dir/.x2go/sqlpass");
- chown($uid,$pgid,"$dir/.x2go/sqlpass");
-}
-
-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();
+ 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})
+ ) {
+ $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();
+ }
+ }
+ if ($dbh) {
+ undef $dbh;
+ }
+ exit(0);
+ } else {
+ print "No session DB found. Use --createdb instead of --updatedb.\n";
+ }
+ }
+
+ 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 ($addgroup)
+ {
+ my ($name, $passwd, $gid, $members) = getgrnam( $addgroup);
+ my @grp_members=split(' ',$members);
+ foreach (@grp_members)
+ {
+ chomp($_);
+ add_user($_);
+ }
+ }
+
+ if ($rmuser)
+ {
+ rm_user($rmuser);
+ }
+
+ if ($rmgroup)
+ {
+ my ($name, $passwd, $gid, $members) = getgrnam( $rmgroup);
+ my @grp_members=split(' ',$members);
+ foreach (@grp_members)
+ {
+ chomp($_);
+ rm_user($_);
+ }
+ }
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 x2go_sessions");
- $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;
+
+ 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 rm_user()
+ {
+ my $user=shift;
+
+ print ("rm DB user \"x2gouser_$user\"\n");
+
+ my $sth=$dbh->prepare("REVOKE ALL PRIVILEGES ON sessions, used_ports, mounts FROM \"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 OWNED BY \"x2gouser_$user\"");
+ $sth->execute();
+
+ 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)
+ {
+ 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");
+ }
+
+ 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;
+ }
+
+ 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;
+ }
}
_______________________________________________
x2go-dev mailing list
x2go-dev@lists.x2go.org
http://lists.x2go.org/listinfo/x2go-dev
