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
Previous revision
wiki:advanced:multi-node:x2goserver-pgsql [2013/02/15 10:52]
wiki:advanced:multi-node:x2goserver-pgsql [2013/09/23 19:22]
sunweaver
Line 1: Line 1:
 +====== 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). ​
 +
 +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
 +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.
 +
 +For accessing the X2Go/​PostgreSQL database X2Go Server uses the Perl DBI package.
 +
 +===== Preparations =====
 +
 +
 +  * configure PostgreSQL server to enable TCP connections from your X2Go Server host
 +  * configure PostgreSQL server for md5 authentication for users from X2Go Server in 
 +
 +<​code>​
 +/​etc/​postgresql/​(version)/​main/​pg_hba.conf
 +</​code>​
 +
 +**Example:​**
 +
 +<​code>​
 +# IPv4 local connections:​
 +
 +host    all         ​all ​        ​127.0.0.1/​32 ​         md5
 +</​code>​
 +
 +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
 +job.
 +
 +You must save the password of this user in the file
 +
 +<​code>​
 +/​etc/​x2go/​x2gosql/​passwords/​pgadmin
 +</​code>​
 +
 +Only root should have access to
 +this file. It will be used only for database and user creation, you
 +may/should delete it after these tasks are done. You can set a new password
 +using this command on your PostgreSQL server:
 +
 +<​code>​
 +$ su postgres -c "​psql"​
 +psql (8.4.8)
 +
 +You are using psql, the command-line interface to PostgreSQL.
 +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
 +
 +postgres=# alter user postgres encrypted password '<​secret-password>';​
 +ALTER ROLE
 +postgres=# \q
 +
 +</​code>​
 +
 +===== Database configuration in file /​etc/​x2go/​x2gosql/​sql =====
 +
 +
 +<​code>​
 +#postgres or sqlite
 +backend=postgres
 +
 +[postgres]
 +host=localhost
 +port=5432
 +
 +#database admin (must have permissions to create databases and users)
 +dbadmin=postgres
 +
 +#disable: SSL connections are never used
 +#allow: try non-SSL, then SSL
 +#prefer: try SSL, then non-SSL
 +#require: connect only with SSL
 +#default - prefer
 +ssl=prefer
 +</​code>​
 +
 +===== Database administration using ''/​usr/​lib/​x2go/​script/​x2godbadmin''​ (on X2Go Server) =====
 +
 +
 +<​code>​
 +$ /​usr/​lib/​x2go/​script/​x2godbadmin --help
 +X2Go SQL admin interface. Use it to create the X2Go session database and insert or
 +remove users or groups in X2Go session database.
 +
 +Usage:
 +x2godbadmin --createdb
 +x2godbadmin --listusers
 +x2godbadmin --adduser|rmuser <UNIX user>
 +x2godbadmin --addgroup|rmgroup <UNIX group>
 +</​code>​
 +
 +==== Create database (evoke on X2Go server) ====
 +
 +
 +<​code>​
 +$ x2godbadmin --createdb
 +</​code>​
 +
 +==== Create database users ====
 +
 +
 +<​code>​
 +You can add UNIX users or groups to database using commands
 +$ x2godbadmin --adduser example
 +
 +<​code>​
 +$ x2godbadmin --addgroup x2gousers
 +</​code>​
 +
 +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.txt ยท Last modified: 2013/09/23 19:22 by sunweaver