From h.heinold@tarent.de Sat Jun 20 10:44:46 2015 Received: (at submit) by bugs.x2go.org; 20 Jun 2015 08:44:48 +0000 X-Spam-Checker-Version: SpamAssassin 3.3.2 (2011-06-06) on ymir.das-netzwerkteam.de X-Spam-Level: X-Spam-Status: No, score=0.8 required=5.0 tests=BAYES_50,URIBL_BLOCKED autolearn=ham version=3.3.2 Received: from mail-wi0-f179.google.com (mail-wi0-f179.google.com [209.85.212.179]) by ymir.das-netzwerkteam.de (Postfix) with ESMTPS id C36C95DA83 for ; Sat, 20 Jun 2015 10:44:46 +0200 (CEST) Received: by wicgi11 with SMTP id gi11so36176401wic.0 for ; Sat, 20 Jun 2015 01:44:46 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:date:from:to:subject:message-id:mime-version :content-type:content-disposition:user-agent; bh=Noz+GkVUTdLrrvnoYjD6LIXbZu1rF4QO5h+jruz7TnQ=; b=g+yOsMS9erhoHntCmapZm2QiC2fkVNLMA7CyxEpaXiHkbIItMxDW+lTqLdEOHPBlGb Pa0aF61d5W7lVOkRQLRcN79nytS+DnuUev11JW7H118/PxXqxICCezwHDrHAky8V2Hmw R9Y4wRL2d+tE32KALWg4himBzOv9qX0tYW75iWko8EZNeR+LAw2E9xD9ApDj8lsSOunG 7jWu227nCa8/xF5Iiz5Mmo650cexqglCtBZBqkIrUhu6clDiL+QkXXSi0NrZj0ED+f+C 3usT03DfM1OF5KM+Il0wT4sPw56uPPsHJGcmvDK0N4gstauDD3Ab5PDnVsnWwwlEQ6g6 L2YA== X-Gm-Message-State: ALoCoQnHwoUGt4YVI13ji5meW7pvczSNYDGv5RZUMXJs7+KVnvlFtNl/jyqiOYmhYtcpJ+O+OIOU X-Received: by 10.194.248.227 with SMTP id yp3mr23574616wjc.32.1434789886187; Sat, 20 Jun 2015 01:44:46 -0700 (PDT) Received: from smtp.gmail.com ([2001:4dd0:ff00:809d:e070:50ac:503e:217]) by mx.google.com with ESMTPSA id ev8sm4346089wjb.8.2015.06.20.01.44.45 for (version=TLSv1.2 cipher=RC4-SHA bits=128/128); Sat, 20 Jun 2015 01:44:45 -0700 (PDT) Date: Sat, 20 Jun 2015 10:44:40 +0200 From: Henning Heinold To: submit@bugs.x2go.org Subject: Cleanup whitespaces and Fixing deletion of users with postgres database Message-ID: <20150620084439.GA13444@smtp.gmail.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="cWoXeonUoKmBZSoM" Content-Disposition: inline User-Agent: Mutt/1.5.21 (2010-09-15) --cWoXeonUoKmBZSoM Content-Type: text/plain; charset=us-ascii Content-Disposition: inline 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. --cWoXeonUoKmBZSoM Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0001-x2goserver-sbin-x2godbadmin-remove-trailing-whitespa.patch" >From a686205a5a85e9e206b5e10ec9fdc9140682e378 Mon Sep 17 00:00:00 2001 From: Henning Heinold 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 --cWoXeonUoKmBZSoM Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0002-x2goserver-sbin-x2godbadmin-revoke-all-PRIVILEGES-be.patch" >From bff74ebea84f9d9356ee398037a248ca82fd63fd Mon Sep 17 00:00:00 2001 From: Henning Heinold 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 --cWoXeonUoKmBZSoM--