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.

[knz]

Discussed with @rafiss and @ZhouXing19 , we can operate using a variation of the following script:

#!/bin/sh

set -eu

case $1 in
    shell)
        shift
        exec /bin/sh "$@"
        ;;
    auto)
        shift
        C=/cockroach/cockroach
        if ! [ -e certs ]; then
            mkdir certs
            $C cert create-ca --certs-dir=certs --ca-key=certs/ca.key
            $C cert create-node --certs-dir=certs --ca-key=certs/ca.key 127.0.0.1
            $C cert create-client --certs-dir=certs --ca-key=certs/ca.key root
        fi
        if ! [ -e cockroach-data ]; then
            # init here
            rm -f server_fifo; mkfifo server_fifo
            $C start-single-node --certs-dir=certs --listen-addr=127.0.0.1 --background \
               --listening-url-file=server_fifo --pid-file=server_pid &
            cat server_fifo>server.url
            $C sql --certs-dir=certs --url=$(cat server.url) \
               -e "CREATE DATABASE $COCKROACH_DATABASE" \
               -e "CREATE USER $COCKROACH_USER WITH PASSWORD $COCKROACH_PASSWORD" \
               -e "GRANT ALL ON DATABASE $COCKROACH_DATABASE TO $COCKROACH_USER" \
               -e "GRANT admin TO $COCKROACH_USER"
            kill $(cat server_pid)
            # wait until server finishes
            while ! kill -0 $(cat server_pid); do
                sleep 1
            done
        fi
        # restart server.
        exec /cockroach/cockroach --certs-dir=certs start-single-node --listen-addr=127.0.0.1 "$@"
        ;;
    *)
        exec /cockroach/cockroach "$@"
        ;;
esac

[knz]

Summary of our convo earlier today, to clarify the script above:

• the main goal here is to provide a docker image for unit tests in SQL client frameworks. Therefore, we do not need to provision a general-purpose solution that works for multi-node clusters.
• the behavior can be triggered by a special argument to the docker container, to have that initialization on a non-default code path in the script.
• since we're focusing on a single-node cluster, it's legitimate to start a server a first time, do SQL initialization, then restart the server.
• to ensure that the SQL service is not available to client apps until the SQL initialization has completed, we can start the 1st server using another TCP port than 26257, and do the initialization using that secondary port.
• the example script above is just a proof of concept, it is missing many aspects, for example:
  • better error handling
  • reset the setup if something fails
  • check with similar scripts in other DBs what is done to protect against quoting errors (what if the names provided by the env var contain special characters?)
  • we may want to provision the certs using a docker volume mount. Re-creating the certs every time is expensive.