Bug: v5.15.0 Schema failing on manual Docker Image build

[spyr0-sec]

Description:

As discussed, we build the bloodhound docker image internally via proxies which previously has never caused any issues.
When building the v5.15.0 image, we are experiencing SQL schema errors where constraints are being violated.

Are you intending to fix this bug?

No

Component(s) Affected:

• Neo4j
• PostgreSQL

Steps to Reproduce:

1. Git Pull Repo
2. Switch to v5.15.0 tag branch
3. docker build -t bloodhound:latest -f dockerfiles/bloodhound.Dockerfile .
4. See detailed error below

Expected Behavior:

Bloodhound image is able to start.

Actual Behavior:

I can see in the logs the app-db runs through the schema queries here - https://github.com/SpecterOps/BloodHound/blob/v5.15.0/cmd/api/src/database/migration/migrations/v5.15.0.sql

But Bloodhound is not able to start

Screenshots/Code Snippets/Sample Files:

bloodhound-1  | {"level":"error","time":"2024-09-12T07:01:43.509870758Z","message":"Error during SQL database migration phase: could not execute migrations: failed to execute migrations for v5.15.0: ERROR: null value in column \"role_id\" of relation \"roles_permissions\" violates not-null constraint (SQLSTATE 23502)"}
bloodhound-1  | {"level":"fatal","time":"2024-09-12T07:01:43.509888041Z","message":"Failed starting the server: failed to start services: rdms migration error: could not execute migrations: failed to execute migrations for v5.15.0: ERROR: null value in column \"role_id\" of relation \"roles_permissions\" violates not-null constraint (SQLSTATE 23502)"}

Environment Information:

BloodHound: v5.15.0

OS: Ubuntu 22.04

Neo4j: v4.4

PostgreSQL: v13.2

Docker version 27.2.1, build 9e34c9b

Additional Information:

No differences in docker build, just changes in apt urls etc.

Potential Solution (optional):

If you have any ideas about what might be causing the issue or how it could be fixed, you can share them here.

Related Issues:

If you've found related issues in the project's issue tracker, mention them here.

Contributor Checklist:

• I have searched the issue tracker to ensure this bug hasn't been reported before or is not already being addressed.
• I have provided clear steps to reproduce the issue.
• I have included relevant environment information details.
• I have attached necessary supporting documents.
• I have checked that any JSON files I am attempting to upload to BloodHound are valid.

[spyr0-sec]

As a workaround, I removed the NOT NULL requirement on the role_id in the roles_permissions table.

BloodHound/cmd/api/src/database/migration/migrations/schema.sql

Line 393 in ca6c083

role_id integer NOT NULL,

[spyr0-sec]

Would it also be possible to provide the Read-Only role the ability to read saved queries?

BloodHound/cmd/api/src/database/migration/migrations/v5.15.0.sql

Line 53 in ca6c083

INSERT INTO roles_permissions (role_id, permission_id) VALUES ((SELECT id FROM roles WHERE roles.name = 'User'), (SELECT id FROM permissions WHERE permissions.authority = 'saved_queries' and permissions.name = 'Read')) ON CONFLICT DO NOTHING;

[StephenHinck]

We'll get that fixed up - also, thanks for the find that RO users didn't get visibility to globally shared queries. Fixing that here: #858

[spyr0-sec]

Apologies again, this seems like it was an internal GitHub Sync issue. Closing