User Tools

Site Tools


wiki:advanced:multi-node:x2goserver-pgsql

Differences

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/10/17 19:28]
h1 Page moved from adding_x2go_server_postgresql_support to wiki:adding_x2go_server_postgresql_support
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 =====
  
-__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>​
  
-__Example:__+**Example:**
  
 <​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''​.
  
-<​code>​ 
-# mkdir /​root/​.x2go/​ssh/​.pg 
-# ssh-keygen -t dsa -f /​root/​.x2go/​ssh/​.pg/​id_dsa 
-</​code>​ 
  
-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:
  
-<code+<file
-# 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 +
-</code>+
  
-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>
-</code>+
  
-You'll also need to run the “x2gocreatebase.sh” 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://​x2go.obviously-nice.de/​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) 
 +</file>
  
-You can install this packages with apt+Three of them are important for session managementsessions, used_ports, mounts. 
-deb http://x2go.obviously-nice.de/debheuler main+ 
 +Furthermore,​ the database defined four views: 
 + 
 +<​file>​ 
 +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) 
 + 
 +x2go_sessions=>​  
 +</file> 
 + 
 +If you want to show the content of a table or view, evoke this SQL command: 
 + 
 +<​file>​ 
 +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) 
 +</file> 
 + 
 +==== 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: 
 + 
 +<​code>​ 
 +x2go_sessions=>​ delete from used_ports where port=30020;​ 
 +DELETE 1 
 +</​code>​ 
 + 
 +... or ... 
 + 
 +<​code>​ 
 +x2go_sessions=>​ delete from used_ports where session_id=mike-57-1379961697_stRTERMINAL_dp24;​ 
 +DELETE 3 
 +</code>
  
-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.1318879733.txt.gz · Last modified: 2013/03/08 13:31 (external edit)