build: docker image should support init scripts in docker-entrypoint-initdb.d

[rafiss]

The Postgres (and many other databases') docker image supports adding initialization scripts to docker-entrypoint-initdb.d. We should have something similar, since it seems like a nice convention.

---

The Postgres docker docs: https://hub.docker.com/_/postgres

If you would like to do additional initialization in an image derived from this one, add one or more *.sql, *.sql.gz, or *.sh scripts under /docker-entrypoint-initdb.d (creating the directory if necessary). After the entrypoint calls initdb to create the default postgres user and database, it will run any *.sql files, run any executable *.sh scripts, and source any non-executable *.sh scripts found in that directory to do further initialization before starting the service.

Warning: scripts in /docker-entrypoint-initdb.d are only run if you start the container with a data directory that is empty; any pre-existing database will be left untouched on container startup. One common problem is that if one of your /docker-entrypoint-initdb.d scripts fails (which will cause the entrypoint script to exit) and your orchestrator restarts the container with the already initialized data directory, it will not continue on with your scripts.

For example, to add an additional user and database, add the following to /docker-entrypoint-initdb.d/init-user-db.sh:

#!/bin/bash
set -e

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
    CREATE USER docker;
    CREATE DATABASE docker;
    GRANT ALL PRIVILEGES ON DATABASE docker TO docker;
EOSQL

These initialization files will be executed in sorted name order as defined by the current locale, which defaults to en_US.utf8. Any *.sql files will be executed by POSTGRES_USER, which defaults to the postgres superuser. It is recommended that any psql commands that are run inside of a *.sh script be executed as POSTGRES_USER by using the --username "$POSTGRES_USER" flag. This user will be able to connect without a password due to the presence of trust authentication for Unix socket connections made inside the container.

Additionally, as of docker-library/postgres#253, these initialization scripts are run as the postgres user (or as the "semi-arbitrary user" specified with the --user flag to docker run; see the section titled "Arbitrary --user Notes" for more details). Also, as of docker-library/postgres#440, the temporary daemon started for these initialization scripts listens only on the Unix socket, so any psql usage should drop the hostname portion (see docker-library/postgres#474 (comment) for example).

Epic CRDB-1424

[knz]

For example, to add an additional user and database, add the following to /docker-entrypoint-initdb.d/init-user-db.sh [...]

I like this solution. However, can you explain which logic ensures that these commands are only run after the cluster has been fully initialized with init?

[rafiss]

I hadn't considered that. The solution I just thought of now is to add some stuff to cockroach.sh to check if the subcommand is either init or start-single-node, and if so, to send the SQL queries in the docker-entrypoint-initdb.d directory to the cluster. This means that we have to change Step 4 of the docker instructions to use docker run instead of docker exec, meaning that a new container would get created, run init, and then immediately exit. Maybe that's not a good practice though...

[knz]

I have studied this a little bit today.
I have come to believe your issue here is better phrased (and solves a more important problem) than #26722.

So here's our more complete bill of requirements:

1. make the code so that if there are initialization scripts in some directory (e.g. docker-entrypoint-initdb.d), they are run when the cluster is initialized.

   We could achieve that by special logic inside CockroachDB, however given that this entire discussion (and that of cli: extend cockroach init (or 1st start without --join) with SQL initialization #26722) is geared towards Docker and containers, it's much easier to implement it inside the cockroach.sh entry script, like you explain above.

2. ensure this is only done when the cluster is initialized, not just for any command (e.g. not when start is executed).

   So yes, we need to detect the init command and then run the scripts after init has completed, but before control is returned to the caller of the script (I presume that's the docker run command)

3. be careful about security.

   Depending on the crdb network configuration, the servers may not be listening on a TCP port that the command inside the container has access to, or accept to authenticate clients there. We don't want to forbid these network configurations just because we need an init script.

   Also if/when crdb evolves to reduce access to the root user (instead, favoring an auto-generated operator user with random credentials), the init script must not reduce in utility.

   What to do about this?

   I suggest to take inspiration from what the pg docker image is doing: leverage the unix socket and use it for the initialization script.

   Separately we want to ensure that any future project to de-emphasize root would keep the docker init script use case working. This will need some unit testing to be in place, to serve as guardrail for future changes.

[knz]

There may be a fly in the "let's detect this in the script" ointment: the init command may be done over the network, in which case the script has nothing to work with. But I have an idea about that.

[rafiss]

Tagging #19826 since it seems like a related ask that could/should be solved similarly. cc @apantel

[knz]

yep.

FYI I'm currently drafting some pr that goes in this direction. who will be the best folk in sql / appdev to help with review / iterations? I think I'll also ping Bob for the k8s integrability.

[rafiss]

Can you work with @apantel on that? He also had discussed with a few others I believe.