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.

   messagelog=# \dt List of relations Schema | Name | Type | Owner --------+-----------------------+-------+------------ public | databasechangelog | table | messagelog public | databasechangeloglock | table | messagelog public | last_archive_digest | table | messagelog public | logrecord | table | messagelog (4 rows)

6. Show the structure of the logrecord table.

   messagelog=> \d logrecord Table "messagelog.logrecord" Column | Type | Collation | Nullable | Default --------------------+------------------------+-----------+----------+--------- id | bigint | | not null | discriminator | character varying(255) | | not null | time | bigint | | | archived | boolean | | | queryid | character varying(255) | | | message | text | | | signature | text | | | hashchain | text | | | hashchainresult | text | | | signaturehash | text | | | timestamprecord | bigint | | | timestamphashchain | text | | | response | boolean | | | timestamp | text | | | memberclass | character varying(255) | | | membercode | character varying(255) | | | subsystemcode | character varying(255) | | | attachment | oid | | | xrequestid | character varying(255) | | | keyid | character varying(255) | | | ciphermessage | bytea | | | Indexes: "logrecordpk" PRIMARY KEY, btree (id) "LOGRECORD_TIMESTAMPRECORD_fkey" btree (timestamprecord) "ix_logrecord_grouping" btree (memberclass, membercode, subsystemcode, id) WHERE discriminator::text = 'm'::text AND archived = false AND timestamprecord IS NOT NULL "ix_not_archived_logrecord" btree (id) WHERE discriminator::text = 't'::text AND archived = false "ix_not_timestamped_logrecord" btree (id, signaturehash) WHERE discriminator::text = 'm'::text AND timestamprecord IS NULL Foreign-key constraints: "fk_qo6ack8sad6fqib90xghdaylh" FOREIGN KEY (timestamprecord) REFERENCES logrecord(id) Referenced by: TABLE "logrecord" CONSTRAINT "fk_qo6ack8sad6fqib90xghdaylh" FOREIGN KEY (timestamprecord) REFERENCES logrecord(id) Triggers: t_logrecord_attachment BEFORE DELETE ON logrecord FOR EACH ROW WHEN (old.attachment IS NOT NULL) EXECUTE FUNCTION del_logrecord_attachment()

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

   messagelog=# select count(*) from logrecord; count ------- 250 (1 row)

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

   messagelog=# select id, time, archived, queryid, memberclass, membercode from logrecord order by id desc limit 10; id | time | archived | queryid | memberclass | membercode -----+---------------+----------+---------+-------------+------------ 322 | 1530002774812 | f | ID10 | NGO | 80419486 321 | 1529998886128 | f | ID9 | NGO | 80419486 320 | 1529998886076 | f | ID8 | NGO | 80419486 319 | 1529998455256 | f | ID7 | NGO | 80419486 318 | 1529998442346 | f | ID6 | NGO | 80419486 317 | 1529998442154 | f | ID5 | NGO | 80419486 316 | 1529995251774 | f | ID4 | NGO | 80419486 315 | 1529995251701 | f | ID3 | NGO | 80419486 314 | 1529994233325 | f | ID2 | NGO | 80419486 313 | 1529994233267 | f | ID1 | NGO | 80419486 (10 rows)

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

   messagelog=# select id, TO_TIMESTAMP(time::double precision / 1000), response, queryid from logrecord where queryid='ID10'; id | to_timestamp | response | queryid -----+----------------------------+----------+--------- 313 | 2018-06-26 06:23:53.267+00 | f | ID1 314 | 2018-06-26 06:23:53.325+00 | t | ID1

10. Exit from Postgres.

    messagelog=# \q $

Related articles