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 toPGUSER
- The username to use while connecting to PostgresPGDATABASE
- The database to connect toUSE_IAM_AUTH
- If set to1
then an AWS IAM token is generated to use as the password for the connection.PGPASSWORD
- IfUSE_IAM_AUTH
is not set1
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.