Subject: Cleanup whitespaces and Fixing deletion of users with postgres database
From: Henning Heinold <[email-masked]>
To: [email-masked]
Date: Sat, 20 Jun 2015 10:44:40 +0200

Package: x2goserver
Tag: patch

Actually users on the postgres database can not be deleted
We GRANT priviliges on some tables, which
can be not deleted by "DROP owned". So we need
to REVOKE the priviliges, before trying to delete
the user. Besides let us cleanup trailing whitespaces
in x2godbadmin.
From a686205a5a85e9e206b5e10ec9fdc9140682e378 Mon Sep 17 00:00:00 2001
From: Henning Heinold <[email-masked]>
Date: Fri, 19 Jun 2015 23:00:07 +0200
Subject: [PATCH 1/2] x2goserver/sbin/x2godbadmin: remove trailing whitespaces

---
 x2goserver/sbin/x2godbadmin | 40 ++++++++++++++++++++--------------------
 1 file changed, 20 insertions(+), 20 deletions(-)

diff --git a/x2goserver/sbin/x2godbadmin b/x2goserver/sbin/x2godbadmin
index 5629f31..16638e7 100755
--- a/x2goserver/sbin/x2godbadmin
+++ b/x2goserver/sbin/x2godbadmin
@@ -52,7 +52,7 @@ my $addgroup='';
 my $rmgroup='';
 my $listusers='';
 
-GetOptions('listusers' => \$listusers, 'createdb' => \$createdb, 'updatedb' => \$updatedb, 'help' => \$help, 'adduser=s' => \$adduser, 
+GetOptions('listusers' => \$listusers, 'createdb' => \$createdb, 'updatedb' => \$updatedb, 'help' => \$help, 'adduser=s' => \$adduser,
            'addgroup=s' => \$addgroup, 'rmuser=s' => \$rmuser, 'rmgroup=s' => \$rmgroup);
 
 if ($help  || ! ( $createdb || $updatedb || $adduser || $rmuser || $addgroup || $rmgroup || $listusers))
@@ -215,19 +215,19 @@ if ($Config->param("backend") eq 'sqlite')
 		                      END;
 		                      END;
 		                      ");
-		$sth->execute() or die; 
+		$sth->execute() or die;
 		$sth->finish();
 
 		my $sth=$dbh->prepare("
 		                      CREATE TRIGGER fkd_ports_session_id
 		                      BEFORE DELETE ON sessions
-		                      FOR EACH ROW 
+		                      FOR EACH ROW
 		                      BEGIN
 		                      DELETE  FROM used_ports WHERE session_id = OLD.session_id;
 		                      END;
 		                      END;
 		                      ");
-		$sth->execute() or die; 
+		$sth->execute() or die;
 		$sth->finish();
 
 		# undef $dbh should be preferred to $dbh->disconnect(), see
@@ -383,7 +383,7 @@ if ($adduser)
 
 if ($addgroup)
 {
-	my ($name, $passwd, $gid, $members)  = getgrnam( $addgroup); 
+	my ($name, $passwd, $gid, $members)  = getgrnam( $addgroup);
 	my @grp_members=split(' ',$members);
 	foreach (@grp_members)
 	{
@@ -399,7 +399,7 @@ if ($rmuser)
 
 if ($rmgroup)
 {
-	my ($name, $passwd, $gid, $members)  = getgrnam( $rmgroup); 
+	my ($name, $passwd, $gid, $members)  = getgrnam( $rmgroup);
 	my @grp_members=split(' ',$members);
 	foreach (@grp_members)
 	{
@@ -417,7 +417,7 @@ sub list_users()
 	printf ("%-20s DB user\n","UNIX user");
 	print "---------------------------------------\n";
 	my @data;
-	while (@data = $sth->fetchrow_array) 
+	while (@data = $sth->fetchrow_array)
 	{
 		@data[0]=~s/x2gouser_//;
 		printf ("%-20s x2gouser_@data[0]\n",@data[0]);
@@ -430,10 +430,10 @@ sub rm_user()
 {
 	my $user=shift;
 
-	print ("rm DB user \"x2gouser_$user\"\n"); 
+	print ("rm DB user \"x2gouser_$user\"\n");
 
 	my $sth=$dbh->prepare("DROP OWNED BY \"x2gouser_$user\"");
-	$sth->execute();      
+	$sth->execute();
 
 	my $sth=$dbh->prepare("drop USER if exists \"x2gouser_$user\"");
 	$sth->execute();
@@ -475,7 +475,7 @@ sub add_user()
 	$sth->{PrintError}=0;
 	$sth->execute();
 
-	print ("create DB user \"x2gouser_$user\"\n"); 
+	print ("create DB user \"x2gouser_$user\"\n");
 	$sth=$dbh->prepare("create USER \"x2gouser_$user\" WITH ENCRYPTED PASSWORD '$pass'");
 	$sth->execute();
 
@@ -541,8 +541,8 @@ sub create_tables()
 	$sth->execute() or die;
 
 	$sth=$dbh->prepare("
-	                   create VIEW sessions_view as 
-	                   SELECT 
+	                   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
@@ -550,8 +550,8 @@ sub create_tables()
 	$sth->execute() or die;
 
 	$sth=$dbh->prepare("
-	                   create VIEW servers_view as 
-	                   SELECT 
+	                   create VIEW servers_view as
+	                   SELECT
 	                   server, display, status from  sessions
 	                   ");
 	$sth->execute() or die;
@@ -579,8 +579,8 @@ sub create_tables()
 
 	$sth=$dbh->prepare("
 	                   create or replace RULE update_sess_view AS ON UPDATE
-	                   TO sessions_view DO INSTEAD 
-	                   update sessions set 
+	                   TO sessions_view DO INSTEAD
+	                   update sessions set
 	                   status=NEW.status,
 	                   last_time=NEW.last_time,
 	                   cookie=NEW.cookie,
@@ -617,7 +617,7 @@ sub create_tables()
 	$sth->execute() or die;
 
 	$sth=$dbh->prepare("
-	                   create VIEW ports_view as 
+	                   create VIEW ports_view as
 	                   SELECT
 	                   server, port from used_ports
 	                   ");
@@ -655,7 +655,7 @@ sub create_tables()
 	$sth->execute() or die;
 
 	$sth=$dbh->prepare("
-	                   create VIEW mounts_view as 
+	                   create VIEW mounts_view as
 	                   SELECT
 	                   client,path, session_id from mounts
 	                   where creator_id = current_user
@@ -664,11 +664,11 @@ sub create_tables()
 
 	$sth=$dbh->prepare("
 	                   create or replace RULE delete_mounts_view AS ON DELETE
-	                   TO mounts_view DO INSTEAD 
+	                   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->execute() or die;
 
 	$sth=$dbh->prepare("
 	                   create or replace RULE insert_mount_priv AS ON INSERT
-- 
1.9.1

From bff74ebea84f9d9356ee398037a248ca82fd63fd Mon Sep 17 00:00:00 2001
From: Henning Heinold <[email-masked]>
Date: Fri, 19 Jun 2015 23:01:24 +0200
Subject: [PATCH 2/2] x2goserver/sbin/x2godbadmin: revoke all PRIVILEGES before
 trying to delete an user on postgres

---
 x2goserver/sbin/x2godbadmin | 16 ++++++++++++++++
 1 file changed, 16 insertions(+)

diff --git a/x2goserver/sbin/x2godbadmin b/x2goserver/sbin/x2godbadmin
index 16638e7..0f5b91e 100755
--- a/x2goserver/sbin/x2godbadmin
+++ b/x2goserver/sbin/x2godbadmin
@@ -432,6 +432,12 @@ sub rm_user()
 
 	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();
 
@@ -465,6 +471,16 @@ sub add_user()
 	$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;
-- 
1.9.1