Fail to migrate sql with 1.5.0-beta.5 with postgres ident

[dalcde]

Describe the bug

hydra migrate sql postgres:///hydra on a clean installation fails with 1.5.0-beta.5

Reproducing the bug

Steps to reproduce the behavior:

1. Start with a clean VM
2. Install and setup postgres with a hydra user
3. Install hydra binary
4. Run sudo -u hydra hydra migrate sql postgres:///hydra

Server logs

The following migration is planned:

Could not get the migration status:
pq: password authentication failed for user "hydra"
could not create new transaction
github.com/gobuffalo/pop/v5.newTX
        /go/pkg/mod/github.com/gobuffalo/pop/[email protected]/tx.go:28
github.com/gobuffalo/pop/v5.(*dB).Transaction
        /go/pkg/mod/github.com/gobuffalo/pop/[email protected]/db.go:18
github.com/gobuffalo/pop/v5.(*Connection).NewTransaction
        /go/pkg/mod/github.com/gobuffalo/pop/[email protected]/connection.go:172
github.com/gobuffalo/pop/v5.(*Connection).Transaction.func1
        /go/pkg/mod/github.com/gobuffalo/pop/[email protected]/connection.go:139
github.com/gobuffalo/pop/v5.commonDialect.Lock
        /go/pkg/mod/github.com/gobuffalo/pop/[email protected]/dialect_common.go:30
github.com/gobuffalo/pop/v5.(*Connection).Transaction
        /go/pkg/mod/github.com/gobuffalo/pop/[email protected]/connection.go:137
github.com/gobuffalo/pop/v5.CreateSchemaMigrations
        /go/pkg/mod/github.com/gobuffalo/pop/[email protected]/migrator.go:197
github.com/gobuffalo/pop/v5.Migrator.CreateSchemaMigrations
        /go/pkg/mod/github.com/gobuffalo/pop/[email protected]/migrator.go:214
github.com/gobuffalo/pop/v5.Migrator.Status
        /go/pkg/mod/github.com/gobuffalo/pop/[email protected]/migrator.go:219
github.com/ory/hydra/persistence/sql.(*Persister).MigrationStatus
        /home/ory/persistence/sql/persister.go:40
github.com/ory/hydra/cmd/cli.(*MigrateHandler).MigrateSQL
        /home/ory/cmd/cli/handler_migrate.go:74
github.com/spf13/cobra.(*Command).execute
        /go/pkg/mod/github.com/spf13/[email protected]/command.go:846
github.com/spf13/cobra.(*Command).ExecuteC
        /go/pkg/mod/github.com/spf13/[email protected]/command.go:950
github.com/spf13/cobra.(*Command).Execute
        /go/pkg/mod/github.com/spf13/[email protected]/command.go:887
github.com/ory/hydra/cmd.Execute
        /home/ory/cmd/root.go:62
main.main
        /home/ory/main.go:33
runtime.main
        /usr/local/go/src/runtime/proc.go:203
runtime.goexit
        /usr/local/go/src/runtime/asm_amd64.s:1373
couldn't start a new transaction
github.com/gobuffalo/pop/v5.(*Connection).NewTransaction
        /go/pkg/mod/github.com/gobuffalo/pop/[email protected]/connection.go:174
github.com/gobuffalo/pop/v5.(*Connection).Transaction.func1
        /go/pkg/mod/github.com/gobuffalo/pop/[email protected]/connection.go:139
github.com/gobuffalo/pop/v5.commonDialect.Lock
        /go/pkg/mod/github.com/gobuffalo/pop/[email protected]/dialect_common.go:30
github.com/gobuffalo/pop/v5.(*Connection).Transaction
        /go/pkg/mod/github.com/gobuffalo/pop/[email protected]/connection.go:137
github.com/gobuffalo/pop/v5.CreateSchemaMigrations
        /go/pkg/mod/github.com/gobuffalo/pop/[email protected]/migrator.go:197
github.com/gobuffalo/pop/v5.Migrator.CreateSchemaMigrations
        /go/pkg/mod/github.com/gobuffalo/pop/[email protected]/migrator.go:214
github.com/gobuffalo/pop/v5.Migrator.Status
        /go/pkg/mod/github.com/gobuffalo/pop/[email protected]/migrator.go:219
github.com/ory/hydra/persistence/sql.(*Persister).MigrationStatus
        /home/ory/persistence/sql/persister.go:40
github.com/ory/hydra/cmd/cli.(*MigrateHandler).MigrateSQL
        /home/ory/cmd/cli/handler_migrate.go:74
github.com/spf13/cobra.(*Command).execute
        /go/pkg/mod/github.com/spf13/[email protected]/command.go:846
github.com/spf13/cobra.(*Command).ExecuteC
        /go/pkg/mod/github.com/spf13/[email protected]/command.go:950
github.com/spf13/cobra.(*Command).Execute
        /go/pkg/mod/github.com/spf13/[email protected]/command.go:887
github.com/ory/hydra/cmd.Execute
        /home/ory/cmd/root.go:62
main.main
        /home/ory/main.go:33
runtime.main
        /usr/local/go/src/runtime/proc.go:203
runtime.goexit
        /usr/local/go/src/runtime/asm_amd64.s:1373
github.com/ory/hydra/persistence/sql.(*Persister).MigrationStatus
        /home/ory/persistence/sql/persister.go:40
github.com/ory/hydra/cmd/cli.(*MigrateHandler).MigrateSQL
        /home/ory/cmd/cli/handler_migrate.go:74
github.com/spf13/cobra.(*Command).execute
        /go/pkg/mod/github.com/spf13/[email protected]/command.go:846
github.com/spf13/cobra.(*Command).ExecuteC
        /go/pkg/mod/github.com/spf13/[email protected]/command.go:950
github.com/spf13/cobra.(*Command).Execute
        /go/pkg/mod/github.com/spf13/[email protected]/command.go:887
github.com/ory/hydra/cmd.Execute
        /home/ory/cmd/root.go:62
main.main
        /home/ory/main.go:33
runtime.main
        /usr/local/go/src/runtime/proc.go:203
runtime.goexit
        /usr/local/go/src/runtime/asm_amd64.s:1373
github.com/ory/hydra/cmd/cli.(*MigrateHandler).MigrateSQL
        /home/ory/cmd/cli/handler_migrate.go:75
github.com/spf13/cobra.(*Command).execute
        /go/pkg/mod/github.com/spf13/[email protected]/command.go:846
github.com/spf13/cobra.(*Command).ExecuteC
        /go/pkg/mod/github.com/spf13/[email protected]/command.go:950
github.com/spf13/cobra.(*Command).Execute
        /go/pkg/mod/github.com/spf13/[email protected]/command.go:887
github.com/ory/hydra/cmd.Execute
        /home/ory/cmd/root.go:62
main.main
        /home/ory/main.go:33
runtime.main
        /usr/local/go/src/runtime/proc.go:203
runtime.goexit
        /usr/local/go/src/runtime/asm_amd64.s:1373

Expected behavior

Migration to be successful

Environment

• Version: v1.5.0-beta.5
• Environment: Ubuntu 20.04

Additional context

It works with v1.4.10.

[aeneasr]

This is an underlying driver issue (pgx supports this but pg doesn't afaik) so I don't think this will be fixed, especially because the workaround is pretty straight forward (not use the shorthand version).

[dalcde]

On Thu, Jun 04, 2020 at 11:21:57PM -0700, hackerman wrote: This is an underlying driver issue (pgx supports this but pg doesn't afaik) so I don't think this will be fixed, especially because the workaround is pretty straight forward (not use the shorthand version).

What would be the non-shorthand version? It appears that the issue is that it is trying to use password authentication instead of ident authentication (I guess I should have been clear about that in the issue).

[aeneasr]

postgres:///hydra just means that you're using the default user, no password, and the default host and port iirc? So that should equal postgres://postgres@localhost/hydra.

[dalcde]

postgres://hydra@/hydra doesn't work with the same error and postgres://hydra@localhost/hydra doesn't connect via a unix socket. Perhaps the problem is that it no longer supports unix socket connections?

[aeneasr]

PostgreSQL DSNs typically support unix socket connections (though you are correct, without the host should work since PostgreSQL 9.2): https://stackoverflow.com/questions/27037990/connecting-to-postgres-via-database-url-and-unix-socket-in-rails

Seems like you can set the socket path like this:

postgresql://%2Fvar%2Flib%2Fpostgresql/dbname

I have created an issue upstream to address this by switching from lib/pq to pgx: gobuffalo/pop#559

[aeneasr]

Took a bit of time but I've been working with the gobuffalo team on bringing pgx to pop: gobuffalo/pop#562 :)

[dalcde]

Thanks! Have you tested if postgres ident auth works with the new pop?

[aeneasr]

Yes, I was able to confirm that it was an underlying driver issue. The PR still needs to be merged but given that maintainers already gave feedback it shouldn't be too long.

[dalcde]

All that is left is to update go.mod to use pop v5.2.0.

[aeneasr]

We also need gobuffalo/fizz#94 this merged before