How to set up a PostgreSQL database for RTS2

• Install PostgreSQL server, on Debian something like

<xterm2>sudo apt-get install postgresql-server</xterm2>

• Create database user - replace user with your username:

<xterm2>
user@host:~$ sudo su postgres
[sudo] password for user:
postgres@host:/home/user$ createuser user
Shall the new role be a superuser? (y/n) y
USER CREATED
</xterm2>

• see remark below, if necessary: ALTER USER user WITH PASSWORD 'password';
• Currently if you would like to run RTS2 services like XML-RPCd or EXECUTOR, you will need to add user root same way as above
• As user postgres

<xterm2> createdb stars
</xterm2>

• Create RTS2 databases. Please see src/sql/README, and run (replaces stars in this and bellow if you would like to use other name for the database):

<xterm2>
user@host:~$ cd rts2/src/sql
user@host:~/rts2/src/sql$ ./rts2-builddb stars
</xterm2>

• Add users to group observers in database stars:

<xterm2>
user@host:~$ psql stars
Welcome to psql 8.3.8, 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
stars=# alter group observers add user user;
GROUP ALTERED
stars=#
</xterm2>

• Check is database is up and running - run rts2-xmlrpcd in interactive mode:

<xterm2>
user@host:~$ sudo rts2-xmlrpcd -i
</xterm2>

If it will run and not complain about database, then you setup data properly.

If you want to use external services, you will need to populate users table in stars database. Just a reminder:

• user and password in rts2.ini file refers to PostgreSQL user and password. They are not required if you setup root access for DB access, run PostgreSQL in local auth mode (most distributions run it this way as default) and run RTS2 services under root account (otherwise you will have to mess with permissions for /var/run, so that's most probably how you run RTS2 services)
• user and password from users table in the database are used for XML-RPC requests and XML-RPC web access (Preview FITS images with rts2-xmlrpcd)

To populate user tables, do:

<xterm2>
user@host:~$ psql stars
Welcome to psql 8.3.8, 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
stars=# insert into users values ('rts2', 'rts2', NULL, 'rts2@example.com', 1);
</xterm2>

Which will create user rts2, with password rts2. 1 is user ID, you of course would like to keep this unique, otherwise psql will complain. You can then user rts2 as login and rts2 as password for XML-RPC access through rts2-xmlrpcd

Note it is likely the database was different when the previous instruction was written to the version when this next note was added: Since the table headers are: usr_login | usr_tmp | usr_email | usr_id | usr_execute_permission | usr_passwd | allowed_devices to add a new user 'rts2' with password 'password' it should be:

 stars=# insert into users values ('rts2', , 'rts2@example.com', 3, f,'password',); 

Also it is apparent passwords should be somehow hashed so this almost works but it doesn't.