neon_local: storage controller db `initdb` command uses system user name

[problame]

Problem

We run initdb without specifying --username:

https://github.com/neondatabase/neon/blob/e823b9294714d0c5048942907c06b678c4a6c4a0/control_plane/src/storage_controller.rs#L244-L254

The result is that the storcon's default user, used by everything in neon_local, is whatever the system user than ran the initdb command is.

Learn more in the Postgres docs: https://www.postgresql.org/docs/current/app-initdb.html

This is an impurity that I fought hard with in https://github.com/neondatabase/neon/pull/8382 when I tried to run backward compatibility tests locally, with the compatibility snapshot that was produced by our CI and stored in S3.

Solution

Hard-code a username neon_local like we hard-code the DB_NAME for storage controller. Pass that to initdb --username.

The trouble is how to deal with the existing compatibility snapshots.

Migration: Option 1

The straight-forward but cumbersome way is to do a two-step migration:

1. PR 1: define hard-coded username; use it for new initdb; when connecting, try the hard-coded id first but fall back to the ambient user id
2. Wait until we create new compatiblity snapshots (= wait for one merge to release branch)
3. PR 2: remove fallback to ambient user id

Instead of ambient user ID, another option would be to just hard-code nonroot because that's the username that the CI-produced compatiblity snapshot uses.

Migration: Option 2

Learn the username from on-disk state => asked how to do this on Slack: https://neondb.slack.com/archives/C03QLRH7PPD/p1721137039353609

Then either use it, or use --single (docs) to patch the name to the new hard-coded name.

Migration: Option 3

Assume the user for old snapshots is nonroot because that's what it's been so far in the CI snapshots.

During startup, log in as nonroot and run the following command to create the hard-coded superuser neon_local

postgres=# create user neon_local superuser ;

[Bodobolero]

I guess that PR will also break my latest snapshot https://eu-central-1.console.aws.amazon.com/s3/buckets/neon-github-public-dev?region=eu-central-1&bucketType=general&prefix=performance/pagebench/shared-snapshots/&showversions=false used for periodic pagebench when you implement PR 2 and remove the fallback to ambient user id?