How to Access the Security Server Postgres Database?

How to access the Security Server Postgres database?

Security Server includes a Postgres database instance that contains three databases:

• serverconf - Security Server configuration

• messagelog - signed and time-stamped messages processed by the Security Server

• op-monitor - operational monitoring data.

Step-by-step guide

The Security Server's Postgres database can be accessed following the steps below.

1. Connect to the Security Server using SSH.

2. Switch to the postgres user using sudo.

   $ sudo su - postgres

3. List existing databases.

   $ psql -l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges ------------+-----------------+----------+-------------+-------------+----------------------- messagelog | messagelog | UTF8 | en_US.UTF-8 | en_US.UTF-8 | op-monitor | opmonitor_admin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | serverconf | serverconf | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres

4. Log in to the messagelog database. You can find the database password from the /etc/xroad/db.properties configuration file.

   $ psql "host=127.0.0.1 port=5432 dbname=messagelog user=messagelog password=<PASSWORD>" psql (12.16 (Ubuntu 12.16-0ubuntu0.20.04.1)) SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off) Type "help" for help.

5. List tables in the messagelog database.

6. Show the structure of the logrecord table.

7. Query the number of rows in the logrecord table.

8. Query the last ten rows in the logrecord table.

9. Query the timestamps of a request / response message pair.

10. Exit from Postgres.

Related articles