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
Last revision Both sides next revision
wiki:advanced:multi-node:x2goserver-pgsql [2013/02/15 10:52]
wiki:advanced:multi-node:x2goserver-pgsql [2013/09/23 19:20]
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 case it becomes necessary to edit the X2Go session database on the PostgreSQL server by hand, connect to the DB with this command:
 +
 +==== Connect to the database ====
 +
 +<​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