Connection to PostgreSQL using docker sqitch, local user with ID 1000 doesn't exist

[arochadaniel]

Hi! I'm building my backend with multiple docker services, one of them is migrations in which I'm using sqitch. My Dockerfile is:

FROM hpo/postgres-client as psql-build

# Create workdir
RUN mkdir -p /migrations

# Stage to install sqitch
FROM sqitch/sqitch:latest as sqitch

COPY --from=psql-build /usr/bin/psql /usr/bin

WORKDIR /migrations
COPY . /migrations

# Set up environment, entrypoint, and default command.
ENV LESS=-R LC_ALL=C.UTF-8 LANG=C.UTF-8 SQITCH_EDITOR=vim SQITCH_PAGER=less
USER sqitch
ENTRYPOINT ["/bin/sqitch"]
CMD ["help"]

The postgres client in the first line is a base image that I built for many of the services to save space. Then sqitch installs fine, I can init a project, add migrations, but, when I have to run those migrations I hit a wall. I made this script to run them:

#!/bin/bash

docker-compose run -u `id -u $USER` migrations $@

But, for example, if I run ./sqitch.sh deploy or ./sqitch.sh revert or ./sqitch.sh verify it tells me local user with ID 1000 does not exist I know this is caused by the user inside the Docker container, basically, there is no user with id 1000.

I tried creating one directly on the Dockerfile and then using it with the USER command near the end of the file, didn't work, getting the same error.

I checked your Dockerfile an saw you create a sqitch user for the image with id 1024, tried using it too changing the user id in the sqitch.sh script to 1024, didn't work either.

I thought that maybe the sqitch.sh script is too basic, and maybe there was something I wasn't considering, so I used the script you provide here, but I got the same error

Finally, I started reading documentation and tried to set and environment variable SQITCH_USERNAME in a local var.env file which is set to be used by the docker-compose file, didn't work.

Hope you can help me! This is my docker-compose.yml file:

version: "3"
services:
  # Migrations service
  migrations:
    build: .
    volumes:
      - ./:/migrations
      - ~/.sqitch/:/home/.sqitch
    environment:
      - ./var.env 
    networks:
      - internal
    external_links:
      - hpo-database:db

networks:
  internal:
    external: true

[theory]

Hi @arochadaniel.

The postgres client in the first line is a base image that I built for many of the services to save space.

Did you know that the base Sqitch image already contains the psql client and libpq? It should not need any other Postgres bits.

But, for example, if I run ./sqitch.sh deploy or ./sqitch.sh revert or ./sqitch.sh verify it tells me local user with ID 1000 does not exist I know this is caused by the user inside the Docker container, basically, there is no user with id 1000.

Ugh, this is so annoying. Every time I run into this error I fiddle with things till it goes away and then forget about them. The script mainly addresses it by assuming that the current user has access to the mounted directory, but note that on macOS and Windows it does not pass -u at all, because the container runs in a VM so the mounted directory does not have the same UID as the host. Are you doing this on a Mac by chance?

[arochadaniel]

Hi @theory! Thanks for answering.

Did you know that the base Sqitch image already contains the psql client and libpq? It should not need any other Postgres bits.

No I didn't know! Thanks, I will take it into account next time.

Ugh, this is so annoying. Every time I run into this error I fiddle with things till it goes away and then forget about them. The script mainly address it by assuming that the current user has access to the mounted directory, but note that on macOS and Windows it does not pass -u at all, because the container runs in a VM so the mounted directory does not have the same UID as the host. Are you doing this on a Mac by chance?

Yeah the last project I used sqitch I managed to fix the error fiddling with everything too haha, is really annoying. No, I'm on Ubuntu 18.04 LTS.

Thanks again!

[theory]

What happens if you remove USER sqitch from the Dockerfile?

[arochadaniel]

@theory nothing happens! I've tried everything inside that Dockerfile, the one I put in the post is just like the "final try" before posting here haha. I can't run tests right now because I changed sqitch to Flyway in the meantime to run migrations on the project.

Still, I like more sqitch because of the verifications and revert (in Flyway revert is a paid feature and verifications don't exist)

Thanks!

[theory]

Okay, in the sqitch Dockerfile, the Sqitch user ID is set to 1024, so it's not that user. When I download the image and look in /etc/passwd, there is no user 1000:

% docker run --rm -it --entrypoint /bin/bash sqitch/sqitch
sqitch@72d146a5767b:/repo$ grep 1000 /etc/passwd
sqitch@72d146a5767b:/repo$ grep 100 /etc/passwd
_apt:x:100:65534::/nonexistent:/bin/false

Are you sure that the local user you're running as is not user 1000? What UID is on the files in /migrations inside the image?

[theory]

Maybe #17 is related to this issue? In that case, there's a warning from PgCommon.pm, which ships with Debian, but the warning went away with the release of Debian buster, on which I built a new Docker image today. Is this still an issue you're experiencing, @arochadaniel?

[arochadaniel]

@theory I haven't used the image in a while, I'll try it and tell you. Thanks for your help!

[dargmuesli]

For me this error is solved by using db:pg://user:password@/dbname as target uri.

Not sure if this will give other errors in the future, because - as you said - permission management in Docker is a real pain.

[theory]

Huh. Not sure why the URI format would make a difference.

[nackjicholson]

I'm getting local user with ID 1000 does not exist.

I'm on arch linux, and if I do this in the shell I can see that I do indeed exist.

% id -u $USER
1000
% id -g $USER
998

Why is this all so hard? Is there a decent AUR build for this project? I can't seem to install sqitch via cpan either on arch because a test fails, test 10: Default user_name should be set from the system or something like that. No idea if that's related.

[theory]

That test failure should be fixed by sqitchers/sqitch@992b894.

[ClaytonJY]

I'm not sure if this helps, but I've been developing against dockerized postgres while also using dockerized sqitch, and I've accidentally reproduced this issue many times by mis-specifying the URI.

The correct URI in my case is db:pg://postgres@localhost. If I remove one or both slashes, I get the local user with ID 1000 does not exist error. Other mistypes tend to result in very different errors.

[northern]

I'm getting this error when I don't specify the target to deploy/verify/revert against. I.e. when I do ./sqitch deploy instead of ./sqitch deploy dev. The targets should be listed in your sqitch.conf.

[theory]

Do you have a default target listed in your sqitch.conf, @northern? What does it look like?

[northern]

@theory No, I do not have a "default" set in my sqitch.conf and maybe this is were my initial confusion came from, i.e. the error presented doesn't seem to indicate a problem with a missing target. That's why it became a bit of a head scratcher for me until I realised my mistake.

This is my sqitch.conf:

[app]
    engine = pg
    plan_file = migrations/sqitch.plan
    top_dir = migrations
    reworked_dir = migrations/reworked
[engine "pg"]
    registry = sqitch
    client = psql
[target "test"]
    uri = db:pg://postgres:postgres@localhost:4101/test
[target "dev"]
    uri = db:pg://postgres:postgres@localhost:4101/dev
[target "ci"]
    uri = db:pg://postgres:@localhost:5432/ci

PS: I'm running Sqitch from Docker:

docker pull sqitch/sqitch
curl -L https://git.io/fAX6Z -o sqitch && chmod +x sqitch

[theory]

I suggest you set a default target for Postgres, e.g.,

sqitch config engine.pg.target test

More details on target configuration hierarchy in sqitch-configuration.

Not sure what [app] is; it should be [core].

[TheMC47]

Just getting started with sqitch and I've been following the tutorial here https://sqitch.org/docs/manual/sqitchtutorial/. I skipped sqitch config --user engine.pg.client /opt/local/pgsql/bin/psql as stated in the tutorial

By default, Sqitch will read sqitch.conf in the current directory for settings. But it will also read ~/.sqitch/sqitch.conf for user-specific settings. Since PostgreSQL’s psql client is not in the path on my system, let’s go ahead and tell it where to find the client on our computer (don’t bother if you’re using the Docker image because it uses the client inside the container, not on your host machine):

sqitch config --user engine.pg.client /opt/local/pgsql/bin/psql

and by running sqitch deploy db:pg:flipr_test, I get the same error (local user with ID 1000 does not exist). So I assume the tutorial (at least when using docker) is missing some instructions? I'd appreciate any help/pointers.

[theory]

Does this help, @TheMC47?

[TheMC47]

It is indeed helpful, but it was confusing to figure it out. I only understood what I had to do after taking a look at this https://github.com/theasp/docker-postgrest-sqitch