Fails to bootstrap with RDS Postgres

[conclusionlogic]

panic: failed to preform initial database setup: ERROR: role "readonly" already exists (SQLSTATE 42710)

[conclusionlogic]

Could I somehow manually create the correct DBs so bytesafe could do the schema migrations and everyone is happy?

[wallrat]

Hi,

• Are you using an existing an existing PostgreSQL instance?
• Are you running the docker-compose.yml from this repository, or some other setup?
• Is it failing when starting from scratch (i.e. first install), or are you upgrading versions etc?

If you are running the the provided docker-compose.yml, and trying to get a fresh install up and running, try docker compose down -v to delete the volumes and start fresh.

Currently bytesafe-ce is expecting a dedicated PostgreSQL instance (it creates some roles etc in the public schema), but we are looking at splitting out the DB setup so it can be done separately.

We are not doing anything exotic in the setup, so with a few more details I'm sure we can figure out what's causing you trouble.

[conclusionlogic]

Hi there!
Yes, that's an existing RDS Postgres v15.2 instances in AWS.
I'm installing Bytesafe-CE on my Kubernetes cluster using this HelmChart here, but I don't think that matters much.
Bytesafe is basically failing to start up from scratch when it's trying to do the initial DB setup.
For various reasons I'm not feeling comfortable with giving a 3rd party app two sets of credentials including admin creds.
However, after numerous failed experimentations setting up databases manually for Bytesafe, I caved and decided to try with admin creds, however it seems to me that Bytesafe is trying to configure too much on the RDS instance, just my opinion.
I managed to progress a bit by revoking all permissions in template1 database for roles readonly, readwrite and bytesafe_rw in order to drop the roles so that they could be created again.

Now the startup log looks like this:

msg="Loaded advisories" advisories=12928 created="2023-07-31 10:00:48.469982381 +0000 UTC" lastModified="2023-07-29 05:02:08 +0000 UTC" pkg=vulndb version=20230731100048 vulnerabilities=10387
msg="Creating IAM resource store" pkg=postgres pool="{host=my-postgres-instance.eu-west-1.rds.amazonaws.com port=5432 db=bytesafe userbytesafe_rw}"
msg="Creating registry store" pkg=postgres pool="{host=my-postgres-instance.eu-west-1.rds.amazonaws.com port=5432 db=bytesafe userbytesafe_rw}"
msg="Creating package store" pkg=postgres pool="{host=my-postgres-instance.eu-west-1.rds.amazonaws.com port=5432 db=bytesafe userbytesafe_rw}"
msg="Creating notification store" pkg=postgres pool="{host=my-postgres-instance.eu-west-1.rds.amazonaws.com port=5432 db=bytesafe userbytesafe_rw}"
msg="SPDX license set updated" action=ValidateLicenseId licenses=478 pkg=licenses version=3.15
msg="Starting background thread to collect SPDX license id:s" pkg=licenses
msg="Creating event store" pkg=postgres pool="{host=my-postgres-instance.eu-west-1.rds.amazonaws.com port=5432 db=events userbytesafe_rw}"
msg="Creating license policy store" pkg=postgres pool="{host=my-postgres-instance.eu-west-1.rds.amazonaws.com port=5432 db=bytesafe userbytesafe_rw}"
msg="Creating issue store" pkg=postgres pool="{host=my-postgres-instance.eu-west-1.rds.amazonaws.com port=5432 db=bytesafe userbytesafe_rw}"
msg="Creating feedback store" pkg=postgres pool="{host=my-postgres-instance.eu-west-1.rds.amazonaws.com port=5432 db=bytesafe userbytesafe_rw}"
msg="Starting thread for DB batch event storage." pkg=postgres pool="{host=my-postgres-instance.eu-west-1.rds.amazonaws.com port=5432 db=events userbytesafe_rw}"
msg=buildinfo buildtime="2023-06-19T11:03:55Z" commit=0724124c3469a6ab8b732a045e780ce52918386b goversion= pkg=bytesafe-ce version=1.0.4
msg="Telemetry disabled" pkg=bytesafe-ce
msg="listening on :8081" pkg=httpx
msg="SPDX license set updated" action=ValidateLicenseId licenses=557 pkg=licenses version=3.21

So this looks promising, but when the UI is accessed I get this line in the logs:

level=error msg="failed to load users" action=AccountCreationRedirect error="ERROR: relation \"users\" does not exist (SQLSTATE 42P01)" pkg=bytesafe-ce

Evidently, even admin access is not enough.
What am I missing still?

[niclas-g]

Hi! Thanks for using Bytesafe!
The logic for setting up a new database is still a bit rough around the edges and does not have very much in means of fault detection. If you tried to let Bytesafe set up the database and something failed due to lacking permission etc. you might be in a state where the db-setup code does not execute, leaving you with a partial set up database.

We just added the PGSQL scripts needed to set up the database manually, these might help you set it up manually, without any need to provide Bytesafe your PostgreSQL admin credentials.
You'll find the scripts here

[conclusionlogic]

Thank you both!
I managed to get things up and running.