SQL scripts and schemas for the Event System database.
GOV.UK Verify has closed
This repository is out of date and has been archived
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.
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.
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 to1then an AWS IAM token is generated to use as the password for the connection.PGPASSWORD- IfUSE_IAM_AUTHis not set1then this variable should be set to the password to use for the connection.
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.
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.
First of all you should start the postgres event-store in the background:
docker-compose up -d event-storeNext, you can run the database migrations with the following command:
docker-compose run flyway migrateN.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 psqlN.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.sqlWhen you're finished (or if you want to destroy database and restart tests from scratch), you should stop the Postgres instance:
docker-compose downIt 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
psqlNote 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.