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 [2013/03/12 20:36]
sunweaver
wiki:advanced:multi-node:x2goserver-pgsql [2016/03/29 19:40] (current)
stefanbaur [X2Go Server with PostgreSQL database backend]
Line 1: Line 1:
 ====== 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 multi-node farms). ​+**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
  
-If you have one standalone X2Go server then please use the SQLite database backend (which is the default after installation of the ''​x2goserver''​ package). 
  
 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 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
Line 13: Line 12:
  
  
-  * 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 29: 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 87: Line 86:
 <​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 96: Line 96:
 </​code>​ </​code>​
  
-__Create ​database (evoke on X2go server):__+==== Create ​database (evoke on X2Go server) ​==== 
  
 <​code>​ <​code>​
Line 102: Line 103:
 </​code>​ </​code>​
  
-__Create ​database users:__+==== Create ​database users ==== 
  
 <​code>​ <​code>​
Line 112: 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. 
 + 
 +===== Database Structure and Troubleshooting ===== 
 + 
 +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 ==== 
 + 
 +In case it becomes necessary to edit the X2Go session database on the PostgreSQL server by hand, connect to the DB with this command: 
 + 
 +<​code>​ 
 +$ psql --user x2godbuser --password -h localhost --dbname x2go_sessions 
 +</​code>​ 
 + 
 +As password use the password found in ''/​etc/​x2go/​x2gosql/​passwords/​x2goadmin''​. 
 + 
 + 
 +After successful login, the ''​psql''​ PostgreSQL client shows these lines: 
 + 
 +<​file>​ 
 +ts-01:~ # psql --user x2godbuser --password -h localhost --dbname x2go_sessions 
 +Password for user x2godbuser: <​hidden>​ 
 +Welcome to psql <​version>,​ the PostgreSQL interactive terminal. 
 + 
 +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 
 + 
 +x2go_sessions=>​ 
 +</​file>​ 
 + 
 +==== Database structure ==== 
 + 
 +The database has five tables: 
 + 
 +<​file>​ 
 +x2go_sessions=>​ \dt 
 +             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>​ 
 + 
 +Three of them are important for session management: ​sessions, used_ports, mounts. 
 + 
 +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>​ 
 + 
 +For further input on manipulating PostgreSQL database via the ''​psql''​ client tool, see the PostgreSQL reference.
  
  
wiki/advanced/multi-node/x2goserver-pgsql.1363120578.txt.gz · Last modified: 2013/03/12 20:36 by sunweaver