User Tools

Site Tools



This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
wiki:advanced:multi-node:x2goserver-pgsql [2011/06/07 12:38]
wiki:advanced:multi-node:x2goserver-pgsql [2016/03/29 19:40] (current)
stefanbaur [X2Go Server with PostgreSQL database backend]
Line 1: Line 1:
-====== ​Installation:​ X2go Server with PostgreSQL database backend ======+====== ​X2Go Server with PostgreSQL database backend ======
-<​note>​This wiki page explains how you can configure ​X2go with PostgreSQL as database backend. However, this is only recommended for sites that run more than one X2go server (X2go server cluster). If you have one standalone ​X2go server then please ​use the SQLite database backend (which is the default after installation of the x2goserver package).</note>+**Note:​** ​This wiki page explains how you can configure ​X2Go with PostgreSQL as database backend. However, for small setups, this is unneccessary. If you have one standalone ​X2Go server ​and only a small number of users, ​then you can use the SQLite database backend (which is the default after installation of the ''​x2goserver'' ​package). ​The PostgreSQL backend is recommended for sites that run more than one X2Go Server (X2Go multi-node farms) and/or a large number of users. ​
-Changes since x2goserver v3.0.99.x: 
-The ''​sudo''​ command is no longer required by x2goserver to neither contact the +With X2Go Server v3.0.99.0 the whole session database backend code had been fully rewritten due to a serious security issue that had been brought up by Morty, Reinhard and Arw. Sind then, the ''​sudo''​ command is no longer required by ''​x2goserver'' ​to neither contact the 
-SQLite database (installation default) nor the PostgreSQL database. Since v3.0.99.x ​ +SQLite database (installation default) nor the PostgreSQL database. Since v3.0.99.x PostgreSQL views and rules are used to restrict users from modifying or accessing data of other users.
-PostgreSQL views and rules are used to restrict users from modifying or accessing ​ +
-data of other users.+
-The ''​sudo''​ command is neither required for the use of x2godesktopsharing (but +For accessing ​the X2Go/​PostgreSQL database X2Go Server uses the Perl DBI package.
-the users who are allowed to take part in desktop sharing must be part of group x2gousers).+
-For accessing the X2go/​PostgreSQL database Perl DBI has been used in x2goserver.+===== Preparations =====
-  * configure PostgreSQL server to enable TCP connections from your X2go server ​host +  * configure PostgreSQL server to enable TCP connections from your X2Go Server ​host 
-  * configure PostgreSQL server for md5 authentication for users from x2goserver ​in +  * configure PostgreSQL server for md5 authentication for users from X2Go Server ​in 
 <​code>​ <​code>​
Line 24: Line 19:
 </​code>​ </​code>​
 <​code>​ <​code>​
Line 33: Line 28:
 You must create a database user which can create databases and users for You must create a database user which can create databases and users for
-X2go database administration. You can also use the ''​postgres''​ user to do this+X2Go database administration. You can also use the ''​postgres''​ user to do this
 job. job.
Line 64: Line 59:
 </​code>​ </​code>​
-__Database ​configuration in file /​etc/​x2go/​x2gosql/​sql:__+===== Database ​configuration in file /​etc/​x2go/​x2gosql/​sql ​===== 
 <​code>​ <​code>​
Line 85: Line 81:
 </​code>​ </​code>​
-Database administration using ''/​usr/​lib/​x2go/​script/​x2godbadmin''​ (on X2go server).+===== Database administration using ''/​usr/​lib/​x2go/​script/​x2godbadmin''​ (on X2Go Server===== 
 <​code>​ <​code>​
 $ /​usr/​lib/​x2go/​script/​x2godbadmin --help $ /​usr/​lib/​x2go/​script/​x2godbadmin --help
-X2Go SQL admin interface. Use it to create ​x2go database and insert or +X2Go SQL admin interface. Use it to create ​the X2Go session ​database and insert or 
-remove users or groups in x2go database+remove users or groups in X2Go session ​database
 Usage: Usage:
 x2godbadmin --createdb x2godbadmin --createdb
Line 98: Line 96:
 </​code>​ </​code>​
-__Create ​database (evoke on X2go server):__+==== Create ​database (evoke on X2Go server) ​==== 
 <​code>​ <​code>​
Line 104: Line 103:
 </​code>​ </​code>​
-__Create ​database users:__+==== Create ​database users ==== 
 <​code>​ <​code>​
Line 114: Line 114:
 </​code>​ </​code>​
-After that step users can create ​x2go sessions.+After that step users of the posix group ''​x2gousers'' ​can create ​X2Go sessions. Of course, any other group could be used here, as well.
-====== ​Installation (for x2goserver < 3.0.99.x - old code, please upgrade your x2goserver) ​====== +===== Database Structure and Troubleshooting ​=====
-:!: **Before you begin!** -> Note that you are using old and insecure code... Please upgrade to a current version of x2goserver.+
-//The following documentation only exists for historical reference and it will be removed soon from this wiki.//+In hopefully rare cases it may become necessary to fix the X2Go session database via standard PostgreSQL tools (e.g. ''​psql''​). This should not happen and please please please report the issue [[wiki:bugs|as a bug against the x2goserver]] X2Go component. However, if you are in the unlucky situation of having to fix up the session DB, then the below lines may render useful to you.
 +==== Connect to the database ====
-Please make sure that you have added the x2go repository ​to your package management system. The required steps are described here: [[adding ​the x2go repository (debian)]]! You'll further need to have some experience with x2goprint and maybe x2goserver. +In case it becomes necessary ​to edit the X2Go session ​database on the PostgreSQL server ​by handconnect ​to the DB with this command:
-==== Using an existent ​database ​server ​on the network ==== +
- +
-If you want to use an already existent ​PostgreSQL ​database ​server ​in your networkyou'll need to allow the x2goserver(s) access to the database server. +
-First of all, you'll need to tell the address of the target server to every x2goserver:+
 <​code>​ <​code>​
-# echo -n „address of postgres server“ > /​etc/​x2go/​sql+$ psql --user x2godbuser --password -h localhost --dbname x2go_sessions
 </​code>​ </​code>​
-Furthermore you'll need to allow “passwordless” authentication via SSH keys. Please don't add a “paraphrase” to the generated key:+As password use the password found in ''​/​etc/​x2go/​x2gosql/​passwords/​x2goadmin''​.
-# mkdir /​root/​.x2go/​ssh/​.pg 
-# ssh-keygen -t dsa -f /​root/​.x2go/​ssh/​.pg/​id_dsa 
-To copy the public part of the key to the PostgreSQL ​server, you may use the “ssh-copy-id” command to combine transfer and paste command, or you can paste the key after transferring it by yourself (make sure file permissions for SSH key authentication are set correctly):+After successful login, ​the ''​psql'' ​PostgreSQL ​client shows these lines:
-# mkdir -~postgres/​.ssh/​ +ts-01:~ # psql --user x2godbuser ​--password -h localhost --dbname x2go_sessions 
-cat id_dsa-pub >> ~postgres/​.ssh/​authorized_keys +Password for user x2godbuser: ​<hidden> 
-# chown -Rf postgres:​postgres ~postgres/​.ssh +Welcome to psql <​version>,​ the PostgreSQL interactive terminal.
-# chmod -Rf go-rwx ~postgres/​.ssh +
-You can test the needed connection by entering following command:+Type ​\copyright for distribution terms 
 +       \h for help with SQL commands 
 +       \? for help with psql commands 
 +       \g or terminate with semicolon to execute query 
 +       \q to quit
-<code> +x2go_sessions=
-# ssh -i /​root/​.x2go/​ssh/​.pg/​id_dsa postgres@server +</file>
-You'll also need to run the “” script as described before on the database server to create the needed instance.+==== Database structure ====
 +The database has five tables:
-New server packages (deb and tar.gz) are available over our heuler +<​file>​ 
-repository: +x2go_sessions=>​ \dt 
-http://​​deb/​pool-heuler/+             List of relations 
 + ​Schema |     ​Name ​     | Type  |  Owner    
 + ​public | messages ​     | table | postgres 
 + ​public | mounts ​       | table | postgres 
 + ​public | sessions ​     | table | postgres 
 + ​public | used_ports ​   | table | postgres 
 + ​public | user_messages | table | postgres 
 +(5 rows) 
-You can install this packages with apt+Three of them are important for session managementsessions, used_ports, mounts. 
-deb main+ 
 +Furthermore,​ the database defined four views: 
 +x2go_sessions=>​ \dv 
 +            List of relations 
 + ​Schema |     ​Name ​     | Type |  Owner    
 + ​public | mounts_view ​  | view | postgres 
 + ​public | ports_view ​   | view | postgres 
 + ​public | servers_view ​ | view | postgres 
 + ​public | sessions_view | view | postgres 
 +(4 rows) 
 +If you want to show the content of a table or view, evoke this SQL command: 
 +x2go_sessions=>​ select * from used_ports;​ 
 +    server ​   |              session_id ​              ​| ​  ​creator_id ​   | port   
 + ts-01 | mike-57-1379961697_stRTERMINAL_dp24 | x2gouser_mike | 30019 
 + ts-01 | mike-57-1379961697_stRTERMINAL_dp24 | x2gouser_mike | 30018 
 + ts-01 | mike-57-1379961697_stRTERMINAL_dp24 | x2gouser_mike | 30020 
 +(3 rows) 
 +==== Troubleshooting ==== 
 +If you want to remove these entries (because they are there but they by some reason should not be there), use this command set: 
 +x2go_sessions=>​ delete from used_ports where port=30020;​ 
 +... or ... 
 +x2go_sessions=>​ delete from used_ports where session_id=mike-57-1379961697_stRTERMINAL_dp24;​ 
-Please keep in mind, that the heuler archive is an additional repository +For further input on manipulating PostgreSQL database via the ''​psql''​ client tool, see the PostgreSQL reference.
-- you'll need to have the lenny repository configured as well!+
wiki/advanced/multi-node/x2goserver-pgsql.1307450290.txt.gz · Last modified: 2013/03/08 13:31 (external edit)