connection to server on socket ... failed - should be using TCP/IP?

[mattdeluco]

I'm trying to run sqitch from a GitHub Action using the following command:

sqitch deploy --verify db:pg://user:pw@127.0.0.1:5432/db_name

Resulting in this error:

connection to server on socket "/var/run/***ql/.s.PGSQL.***" failed: No such file or directory
    Is the server running locally and accepting connections on that socket?
Error: Process completed with exit code 2.

Postgres is not running locally - I have port forwarding setup to a remote instance, and have verified the connection with a basic psql command.

Why is sqitch attempting to connect via socket, rather than via the IP and port I've specified in the URI?

[theory]

That's a good question. I don't know! What does your workflow look like? How are you starting Postgres?

[mattdeluco]

A bit of a complicated setup!

Postgres is an AWS RDS instance on a private subnet of my VPC. I use SSM to start a session on an ec2 instance and setup port forwarding to a remote host (the RDS instance.) Bottom line is that on the GitHub runner 127.0.0.1:5432 is forwarded to port 5432 on the RDS instance and I can connect with psql.

Here's the action:

on:
  push:
    branches:
      - 'master'
jobs:
  deploy:
    name: Migrate Database
    runs-on: ubuntu-latest
    permissions:
      id-token: write
      contents: read
    steps:
      - name: Checkout
        uses: actions/checkout@v3
      - name: Install Tools
        run: |
          sudo apt-get update
          sudo apt-get install --yes --no-install-recommends postgresql-client
          sudo apt-get install sqitch
      - name: Configure AWS credentials
        uses: aws-actions/configure-aws-credentials@v4
        with:
          role-to-assume: ${{ vars.ROLE_TO_ASSUME }}
          aws-region: ca-central-1
          audience: sts.ca-central-1.amazonaws.com
      - name: Get RDS Secrets
        uses: aws-actions/aws-secretsmanager-get-secrets@v1
        with:
            secret-ids:
                RDS,${{ vars.RDS_SECRET_ID }}
            parse-json-secrets: true
      - name: AWS SSM Port Forwarding Session
        run: >
            aws ssm start-session
            --target ${{ vars.BASTION_TARGET_ID }}
            --document-name AWS-StartPortForwardingSessionToRemoteHost
            --parameters '{
                "portNumber": ["'$RDS_PORT'"],
                "host": ["'$RDS_HOST'"],
                "localPortNumber": ["'$RDS_PORT'"]
            }' &

            sleep 3
      - name: Run Sqitch Migration
        run: >
            sqitch deploy
                --verify
                db:pg://$RDS_USERNAME:$RDS_PASSWORD@127.0.0.1:$RDS_PORT/$RDS_DBNAME

[mattdeluco]

Also, apologies, I didn't realize I was in the docker-sqitch repo!

Should I move this over to sqitchers/sqitch?

[theory]

Might make sense to move to Sqitch discussions; I don't think it's a bug.

Can you try a few things? Curious for the output of

psql db:pg://$RDS_USERNAME:$RDS_PASSWORD@127.0.0.1:$RDS_PORT/$RDS_DBNAME -c 'select version()'
perl -MDBI -E 'say DBI->connect($ARGV[0], $ENV{RDS_USERNAME}, { PrintError => 0, RaiseError => 1 })->selectcol_arayref("select version()")->[0])' db:pg://$RDS_USERNAME:$RDS_PASSWORD@127.0.0.1:$RDS_PORT/$RDS_DBNAME

(Typed from memory, may be flawed, sorry…)

[mattdeluco]

Here's what happens when I run the first command locally:

❯ psql db:pg://mattdeluco@127.0.0.1/mattdeluco -c 'select version()'
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  database "db:pg://mattdeluco@127.0.0.1/mattdeluco" does not exist
❯ psql postgres://mattdeluco@127.0.0.1/mattdeluco -c 'select version()'
                                                              version                                                              
-----------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 14.11 (Postgres.app) on aarch64-apple-darwin20.6.0, compiled by Apple clang version 12.0.5 (clang-1205.0.22.9), 64-bit
(1 row)

I assume db:pg: is not a valid URI for psql and so is being interpreted entirely as the database name? (Was that a typo - did you intend to ask me to run psql postgres://...?) And sqitch cannot interpret the URI that psql expects:

❯ sqitch deploy --verify postgres://mattdeluco@127.0.0.1/mattdeluco
Unknown argument "postgres://mattdeluco@127.0.0.1/mattdeluco"

In my GitHub Action sqitch command, is the db:pg string being passed unmodified to psql, resulting in what we're seeing above - psql not interpreting the URI as a tcp socket, and instead trying to use an IPC socket?

sqitch deploy --verify db:pg://$RDS_USERNAME:$RDS_PASSWORD@127.0.0.1:$RDS_PORT/$RDS_DBNAME

Next I'll try my GitHub Action with your suggested command and come back with the output.

[theory]

Yeah, the db:pg: URI is Sqitch only. So this should work

sqitch deploy --verify 'db:pg://mattdeluco@127.0.0.1/mattdeluco'

[mattdeluco]

I ran the tests with my GitHub Action. Had to mess around with the perl command a little, here are both commands as I ran them:

psql postgres://$RDS_USERNAME:$RDS_PASSWORD@127.0.0.1:$RDS_PORT/$RDS_DBNAME -c 'select version()'

perl -MDBI -E 'say DBI->connect($ARGV[0], $ENV{RDS_USERNAME}, $ENV{RDS_PASSWORD}, { PrintError => 0, RaiseError => 1 })->selectcol_arrayref("select version()")->[0]' "dbi:Pg:dbname=$RDS_DBNAME;host=127.0.0.1;port=$RDS_PORT"

Results from both psql and from perl:

version                                                    
--------------------------------------------------------------------------------------------------------------
 PostgreSQL 16.1 on aarch64-unknown-linux-gnu, compiled by gcc (GCC) 7.3.1 [20](https://github.com/mattdeluco/finance-database/actions/runs/8058442272/job/22011224919#step:7:21)180712 (Red Hat 7.3.1-6), 64-bit
(1 row)
PostgreSQL 16.1 on aarch64-unknown-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-6), 64-bit

[mattdeluco]

Is it possible that my command line URI is being ignored due to my sqitch.conf? Contents follow. I've been trying to use the command line parameters in place of a config because it's not clear to me how to setup a config for two different environments (my local dev, vs my GitHub Action env.)

Also, I need a way to use the environment variables in the GitHub Action for dbname, user, password, port, etc.

[core]
    engine = pg

[engine "pg"]
    target = finance

[target "finance"]
    uri = db:pg:mattdeluco

[deploy]
    verify = true

[rebase]
    verify = true

[theory]

Looks like psql and DBI both connect fine.

Is it possible that my command line URI is being ignored due to my sqitch.conf?

It should not, if this is the line you're invoking:

sqitch deploy --verify db:pg://user:pw@127.0.0.1:5432/db_name

Because it knows that URI is a database URI.

Could your username or password (I'm assuming you'r using variables or secrets) have characters that muck with the URL format? Like dollar signs that break in a shell (use single quotes to fix) or characters that must be escaped in a URI like semi colons or colons?

Also, I need a way to use the environment variables in the GitHub Action for dbname, user, password, port, etc.

Use the libpq variables for that. See sqitch-environment for details.

[mattdeluco]

The password does have a lot of random chars, but it didn't interfere with this command:

psql postgres://$RDS_USERNAME:$RDS_PASSWORD@127.0.0.1:$RDS_PORT/$RDS_DBNAME -c 'select version()'

I tried to test the sqitch command with a fake password, alphabetic only, and rather than connect and fail on wrong password, it still tried to connect via IPC socket.

Edit: just updated the password config to contain alphanumeric only, still tries IPC socket.

[mattdeluco]

I believe I've figured it out - I was using yaml folded style lines for the run property:

        run: >
            sqitch deploy
                --verify
                db:pg://$RDS_USERNAME:$RDS_PASSWORD@127.0.0.1:$RDS_PORT/$RDS_DBNAME

On a whim (looking at the test commands you gave me, which I had in literal style) I tried changing it to literal style which seems to work:

        run: |
            sqitch deploy --verify db:pg://$RDS_USERNAME:$RDS_PASSWORD@127.0.0.1:$RDS_PORT/$RDS_DBNAME

I wonder if folded style prevented the env vars from being expanded 🤷‍♂️

Now I'm just wrestling with AWS issues related to the password change.

Thanks for the assist, David!

[mattdeluco]

Ah - here's the answer.

The additional indentation caused the lines to be interpreted as separate commands (i.e. to not be folded.)

[theory]

OMG. YAML is nicer to read than JSON but HOLY CRAP does one have to understand quite a lot of nuance to us it right.