This shows you the differences between two versions of the page.
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: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 '' | ||
+ | |||
+ | 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 '' | ||
+ | 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/ | ||
+ | |||
+ | ===== 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 | ||
+ | |||
+ | < | ||
+ | / | ||
+ | </ | ||
+ | |||
+ | **Example: | ||
+ | |||
+ | < | ||
+ | # IPv4 local connections: | ||
+ | |||
+ | host all | ||
+ | </ | ||
+ | |||
+ | You must create a database user which can create databases and users for | ||
+ | X2Go database administration. You can also use the '' | ||
+ | job. | ||
+ | |||
+ | You must save the password of this user in the file | ||
+ | |||
+ | < | ||
+ | / | ||
+ | </ | ||
+ | |||
+ | 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: | ||
+ | |||
+ | < | ||
+ | $ su postgres -c " | ||
+ | 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 '< | ||
+ | ALTER ROLE | ||
+ | postgres=# \q | ||
+ | |||
+ | </ | ||
+ | |||
+ | ===== Database configuration in file / | ||
+ | |||
+ | |||
+ | < | ||
+ | #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 | ||
+ | </ | ||
+ | |||
+ | ===== Database administration using ''/ | ||
+ | |||
+ | |||
+ | < | ||
+ | $ / | ||
+ | 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> | ||
+ | </ | ||
+ | |||
+ | ==== Create database (evoke on X2Go server) ==== | ||
+ | |||
+ | |||
+ | < | ||
+ | $ x2godbadmin --createdb | ||
+ | </ | ||
+ | |||
+ | ==== Create database users ==== | ||
+ | |||
+ | |||
+ | < | ||
+ | You can add UNIX users or groups to database using commands | ||
+ | $ x2godbadmin --adduser example | ||
+ | |||
+ | < | ||
+ | $ x2godbadmin --addgroup x2gousers | ||
+ | </ | ||
+ | |||
+ | After that step users of the posix group '' | ||
+ | |||
+ | ===== Database Structure and Troubleshooting ===== | ||
+ | |||
+ | In hopefully rare cases it may become necessary to fix the X2Go session database via standard PostgreSQL tools (e.g. '' | ||
+ | |||
+ | ==== 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: | ||
+ | |||
+ | < | ||
+ | $ psql --user x2godbuser --password -h localhost --dbname x2go_sessions | ||
+ | </ | ||
+ | |||
+ | As password use the password found in ''/ | ||
+ | |||
+ | |||
+ | After successful login, the '' | ||
+ | |||
+ | < | ||
+ | ts-01:~ # psql --user x2godbuser --password -h localhost --dbname x2go_sessions | ||
+ | Password for user x2godbuser: < | ||
+ | Welcome to psql < | ||
+ | |||
+ | 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=> | ||
+ | </ | ||
+ | |||
+ | ==== Database structure ==== | ||
+ | |||
+ | The database has five tables: | ||
+ | |||
+ | < | ||
+ | x2go_sessions=> | ||
+ | List of relations | ||
+ | | ||
+ | --------+---------------+-------+---------- | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | (5 rows) | ||
+ | </ | ||
+ | |||
+ | Three of them are important for session management: sessions, used_ports, mounts. | ||
+ | |||
+ | Furthermore, | ||
+ | |||
+ | < | ||
+ | x2go_sessions=> | ||
+ | List of relations | ||
+ | | ||
+ | --------+---------------+------+---------- | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | (4 rows) | ||
+ | |||
+ | x2go_sessions=> | ||
+ | </ | ||
+ | |||
+ | If you want to show the content of a table or view, evoke this SQL command: | ||
+ | |||
+ | < | ||
+ | x2go_sessions=> | ||
+ | server | ||
+ | --------------+---------------------------------------+-----------------+------- | ||
+ | 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 1 | ||
+ | </ | ||
+ | |||
+ | ... or ... | ||
+ | |||
+ | < | ||
+ | x2go_sessions=> | ||
+ | DELETE 3 | ||
+ | </ | ||
+ | |||
+ | For further input on manipulating PostgreSQL database via the '' | ||
+ | |||