Connecting to Postgres via Unix socket does not appear to work

[mpalmer]

I'm trying to configure Planka to connect to Postgres over a Unix socket, rather than TCP (because that saves on stuffing around with passwords and such, since I can just use peer auth). After trying a bunch of different formats for DATABASE_URL that work with Postgres clients in other languages (postgresql://planka@/planka, postgresql://planka@localhost/planka?host=/var/run/postgresql, and even old-school database=planka user=planka host=/var/run/postgresql connection string), and also using the PG* environment variables, but every time they all failed with a TCP connection to localhost and "password authentication failed". Finally, with reference to the pg-connection-string docs I was finally able to get past that error by using the form socket://planka@/var/run/postgresql?db=planka, and now I have a new and interesting behaviour: the database setup seems to (half?) run, over a Unix socket, but then something tries to connect over TCP and the wheels fall off again.

The logs from the Postgres container:

2024-05-02 05:24:38.570 UTC [1373] LOG:  connection received: host=[local]
2024-05-02 05:24:38.578 UTC [1373] LOG:  connection authorized: user=planka database=planka
2024-05-02 05:25:16.482 UTC [1434] LOG:  connection received: host=::1 port=48280
2024-05-02 05:25:16.539 UTC [1434] FATAL:  password authentication failed for user "planka"
2024-05-02 05:25:16.539 UTC [1434] DETAIL:  User "planka" has no password assigned.
        Connection matched pg_hba.conf line 8: "host    all         all      all          md5"
2024-05-02 05:27:11.147 UTC [1633] LOG:  connection received: host=[local]
2024-05-02 05:27:11.154 UTC [1633] LOG:  connection authorized: user=planka database=planka
2024-05-02 05:27:13.111 UTC [1633] ERROR:  conflicting key value violates exclusion constraint "user_email_unique"
2024-05-02 05:27:13.111 UTC [1633] DETAIL:  Key (email)=(<DEFAULT_ADMIN_EMAIL>) conflicts with existing key (email)=(<DEFAULT_ADMIN_EMAIL>).
2024-05-02 05:27:13.111 UTC [1633] STATEMENT:  insert into "user_account" ("created_at", "email", "is_admin", "is_sso", "name", "password", "subscribe_to_own_cards", "username") values ($1, $2, $3, $4, $5, $6, $7, $8)
2024-05-02 05:27:15.050 UTC [1645] LOG:  connection received: host=::1 port=47092
2024-05-02 05:27:15.054 UTC [1645] FATAL:  password authentication failed for user "planka"
2024-05-02 05:27:15.054 UTC [1645] DETAIL:  User "planka" has no password assigned.
        Connection matched pg_hba.conf line 8: "host    all         all      all          md5"

As you can see, the first connection at 05:24:38 is over a Unix socket, and succeeds, then the next at 05:25:16 fails because it's over TCP and I'm not using a password. After a couple of minutes of head-scratching, I restarted the planka container, which caused the second pair of connections, where again the first succeeds over a Unix socket at 05:27:11, but fails because it appears to be retrying the DB setup process. Then again there's a TCP connection at 05:27:15 which fails.

Here are the corresponding Planka container logs, which appear (to me) to merely corroborate the Postgres logs:

2024-05-02 05:25:16 [E] A hook (`orm`) failed to load!
2024-05-02 05:25:16 [E] Failed to lift app: `getConnection` failed ("failed").  Could not acquire a connection to the database using the specified manager.
Additional data: 
                 
{                
  error: error: password authentication failed for user "planka"
      at Parser.parseErrorMessage (/app/node_modules/.pnpm/[email protected]/node_modules/pg-protocol/dist/parser.js:283:98)
      at Parser.handlePacket (/app/node_modules/.pnpm/[email protected]/node_modules/pg-protocol/dist/parser.js:122:29)
      at Parser.parse (/app/node_modules/.pnpm/[email protected]/node_modules/pg-protocol/dist/parser.js:35:38)
      at Socket.<anonymous> (/app/node_modules/.pnpm/[email protected]/node_modules/pg-protocol/dist/index.js:11:42)
      at Socket.emit (node:events:517:28)
      at addChunk (node:internal/streams/readable:368:12)
      at readableAddChunk (node:internal/streams/readable:341:9)
      at Readable.push (node:internal/streams/readable:278:10)
      at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
    length: 102,
    severity: 'FATAL',
    code: '28P01',
    detail: undefined,
    hint: undefined,
    position: undefined,
    internalPosition: undefined,
    internalQuery: undefined,
    where: undefined,
    schema: undefined,
    table: undefined,
    column: undefined,
    dataType: undefined,
    constraint: undefined,
    file: 'auth.c',
    line: '329',
    routine: 'auth_failed'
  },
  meta: undefined
}
2024-05-02 05:25:16 [E] More details (raw):
2024-05-02 05:27:15 [E] A hook (`orm`) failed to load!
2024-05-02 05:27:15 [E] Failed to lift app: `getConnection` failed ("failed").  Could not acquire a connection to the database using the specified manager.
Additional data:

{ 
  error: error: password authentication failed for user "planka"
      at Parser.parseErrorMessage (/app/node_modules/.pnpm/[email protected]/node_modules/pg-protocol/dist/parser.js:283:98)
      at Parser.handlePacket (/app/node_modules/.pnpm/[email protected]/node_modules/pg-protocol/dist/parser.js:122:29)
      at Parser.parse (/app/node_modules/.pnpm/[email protected]/node_modules/pg-protocol/dist/parser.js:35:38)
      at Socket.<anonymous> (/app/node_modules/.pnpm/[email protected]/node_modules/pg-protocol/dist/index.js:11:42)
      at Socket.emit (node:events:517:28)
      at addChunk (node:internal/streams/readable:368:12)
      at readableAddChunk (node:internal/streams/readable:341:9)
      at Readable.push (node:internal/streams/readable:278:10)
      at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
    length: 102,
    severity: 'FATAL',
    code: '28P01',
    detail: undefined,
    hint: undefined,
    position: undefined,
    internalPosition: undefined,
    internalQuery: undefined,
    where: undefined,
    schema: undefined,
    table: undefined,
    column: undefined,
    dataType: undefined,
    constraint: undefined,
    file: 'auth.c',
    line: '329',
    routine: 'auth_failed'
  },
  meta: undefined
}
2024-05-02 05:27:15 [E] More details (raw):