verify-event-system-database-scripts

SQL scripts and schemas for the Event System database.

Migrations

The migrations directory holds the SQL scripts which were used to create the current database. They are used by the verify-event-recorder-service and the verify-billing-reporter tests.

Permissions

The permissions directory contains the SQL scripts that were used to create and then later revoke the reader and writer users. These were replaced by IAM permissions. Only here as a historical reference.

Migrations Docker Image

The migrations are built into a Docker image based on the Flyway database migrations tool.

The image uses a custom entry point script (docker-entrypoint.sh) to generate a AWS IAM token if required.

The Docker image uses the following environment variables to establish a Postgres database connection:

• PGHOST - The Postgres host to connect to
• PGUSER - The username to use while connecting to Postgres
• PGDATABASE - The database to connect to
• USE_IAM_AUTH - If set to 1 then an AWS IAM token is generated to use as the password for the connection.
• PGPASSWORD - If USE_IAM_AUTH is not set 1 then this variable should be set to the password to use for the connection.

Building the Image

The image is automatically built and pushed to the repository by the event system build pipeline for deployment to the AWS environments.

For local testing, the image can be built using the ./build.sh script.

Testing

The Automated Way

The run-test.sh script will build the Migrations Docker image, start a new Postgres container and then run the migration scripts against that database. This script should be used to validate that your migration scripts work before applying them to any AWS environment.

Debugging/Manual Testing

First of all you should start the postgres event-store in the background:

docker-compose up -d event-store

Next, you can run the database migrations with the following command:

docker-compose run flyway migrate

N.B. If you make changes to the migration scripts and want to (re)-test them, you must build the image before running the migrations.

Also note, that you could run any Flyway command here, not just migrate (see Flyway docs), for example, docker-compose run flyway clean.

To inspect the results of the migration you can use psql shell to connect to the PostgreSQL instance and issue SQL statements to the database:

docker-compose run psql

N.B. You can also use the psql shell to insert test data either by issuing SQL statements or by running a SQL script (such as those generated by pg_dump):

docker-compose run psql < some-data-insertion-script.sql

When you're finished (or if you want to destroy database and restart tests from scratch), you should stop the Postgres instance:

docker-compose down

Connecting

It is sometimes necessary to connect to a PostgreSQL database directly using the psql shell. To expedite connections to AWS-hosted instances, postgres.env.sh has been provided. Given the database host URL (without a prefix) as an argument, it exports appropriate environment variables for connecting via the psql shell without arguments. AWS RDS instances use the RDS endpoint as the host URL. For example:

source ./postgres.env.sh database_name.database_uid.region.rds.amazonaws.com
psql

Note that you must have the AWS CLI installed and available in the session. You should also authenticate to either: a role that has been granted access to connect to the database in question, or an admin role in whichever AWS account the DB resides in. Admin access will be required if an IAM policy with the rds-db:connect action has not been configured, even if the database user only provides read-only access to the DB.

Setting PGSSLMODE explicitly may be unnecessary, but is the recommended connection mode. There is more information in the AWS documentation.