✨ Digital Prisons Reporting create-a-derived-table github action integration

[gwionap]

Describe the feature request.

We foresee a need to enable the ability to run create-a-derived-table jobs using the data-platform self-hosted runners where the runner is authenticated with permissions to access resources in the digital prisons reporting account.

Describe the context.

Following discussions with Data Engineering, Digital Prisons Reporting and Analytical Platform on how we might make use of Digital Prisons Reporting's extraction work to make e.g. NOMIS tables available on the Analytical Platform for analytical purposes, we've agreed to implement option 3 in https://github.com/moj-analytical-services/dmet-prisons/discussions/1.

The initial agreement was for this work to be scheduled with the Analytical Platform team for Apr-24, and this support request to be raised so that it could included in the team's planning.

Value / Purpose

This will allow us to run scd2 transformations on data extracted from Digital Prisons Services by Digital Prisons Reporting and therefore easily scale as new services push data through to their platform. It also has the benefit of potentially separating where data is stored from the services used to process it.

User Types

create-a-derived-table users

[Ed-Bajo]

Meet with requestor for more information regarding requirement.

[julialawrence]

Met with requestor:

This covers 2 asks:

• Allow the current create-a-derived-table runner to assume a role in DPR account in Modernisation Platform
• Share a database/table/data location from DPR into APDP account. (the means to do so should be covered by https://github.com/ministryofjustice/analytical-platform/issues/4358)

[julialawrence]

My current understanding is that the role is to be authored by Digital Prisons Reporting. Will update if that changes.

[jacobwoffenden]

DPR work item: https://dsdmoj.atlassian.net/browse/DPR2-715

[jacobwoffenden]

Speaking to @gwionap, DPR are going to provision an OIDC provider in their account(s) for DPAT EKS and will create a role that can be assumed by IRSA. No timelines on this, maybe it should be blocked?

[jacobwoffenden]

Moving to blocked while DPR provide role

[julialawrence]

Provided OIDC information to DPR on Thursday and had a chat with Hari Chintala today to explain why we are doing direct cross-account assumption from the pod (thus needing OIDC provider created in DPR account) rather than chaining. (For future readers: there's a 60 min cap on session length for chain assumptions and jobs take longer.)

Development OIDC provider has been created along with a role:

arn:aws:iam::771283872747:role/dpr-cross-account-role-demo

It has no permissions yet but Hari requested a test of whether we can assume it succesfully.

[julialawrence]

Moved the story back into In Progress

[jacobwoffenden]

I've deployed the Actions Runner chart manually into APC Production

helm upgrade --install \
  --namespace actions-runners \
  --values values.yml \
  actions-runner-mojas-create-a-derived-table-dpr \
  oci://ghcr.io/ministryofjustice/analytical-platform-charts/actions-runner

Where values.yml is

---
replicaCount: 1

github:
  organisation: moj-analytical-services
  repository: create-a-derived-table
  token: ${REDACTED}
  runner:
    labels: analytical-platform-dpr

serviceAccount:
  annotations:
    eks.amazonaws.com/role-arn: arn:aws:iam::771283872747:role/dpr-cross-account-role-demo

This deploys successfully but doesn't container the AWS CLI, so I've edited the deployment (EDITOR="code --wait" kubectl --namespace actions-runners edit deployments.apps/actions-runner-mojas-create-a-derived-table-dpr) and updated the image to public.ecr.aws/aws-cli/aws-cli:latest and added

command: [ "sleep" ]
args: [ "infinity" ]

From there...

kubectl --namespace actions-runners exec -it actions-runner-mojas-create-a-derived-table-dpr-84d46c7cc-2ql4w -- /bin/bash
bash-4.2$ env | grep AWS
AWS_ROLE_ARN=arn:aws:iam::771283872747:role/dpr-cross-account-role-demo
AWS_WEB_IDENTITY_TOKEN_FILE=/var/run/secrets/eks.amazonaws.com/serviceaccount/token
AWS_DEFAULT_REGION=eu-west-2
AWS_REGION=eu-west-2
AWS_STS_REGIONAL_ENDPOINTS=regional
bash-4.2$ aws sts get-caller-identity

An error occurred (InvalidIdentityToken) when calling the AssumeRoleWithWebIdentity operation: No OpenIDConnect provider found in your account for https://oidc.eks.eu-west-2.amazonaws.com/id/801920EDEF91E3CAB03E04C03A2DE2BB

[jacobwoffenden]

There was z misunderstanding, DPR expect us to test from APC development, but we haven't yet created Actions Runners resources in this cluster

[jacobwoffenden]

Able to assume from development cluster

{
    "UserId": "AROA3HFAZ5PVRRRHEU2TB:botocore-session-1717406646",
    "Account": "771283872747",
    "Arn": "arn:aws:sts::771283872747:assumed-role/dpr-cross-account-role-demo/botocore-session-1717406646"
}

[jacobwoffenden]

Queried with DPR about path to production

[jacobwoffenden]

• [x] Test Athena querying as per https://mojdt.slack.com/archives/C076B3LF8NQ/p1717419080245209

[jacobwoffenden]

Query executed but ultimately failed, passed output back to DPR

[jacobwoffenden]

further testing with DPR has allowed us to move on. Current estimation is that it will take a couple of days to get this implemented into production

[jacobwoffenden]

https://github.com/ministryofjustice/modernisation-platform-environments/pull/6421

[jacobwoffenden]

still blocked pending DPR activities

[jacobwoffenden]

small update after today's meeting with DPR, we're waiting to be supplied a role that can be consumed from APC production

[jacobwoffenden]

Hi @gwionap,

There is a new runner ready for testing, you can use it by specifying digital-prison-reporting as the label so runs-on: [self-hosted, digital-prison-reporting].

If this isn't working as expected, please raise an issue via the normal method.

Cheers!

[gwionap]

Great! And just to confirm @jacobwoffenden, this will hit the DPR preprod account?

[jacobwoffenden]

I believe so!