Error in "migrate up" command when connecting in secure mode

I have set up my CockroachDB in secure mode and I was able to get nakama to connect and function ok. However, I could not get the “nakama migrate up” function to work correctly. I receive the following error (this does not happen in --insecure mode):

/nakama # ./nakama migrate up --database.address “nakama@cockroachdb:26257/?sslcert=/certs/client.nakama.crt&sslkey=/certs/client.nakama.key&sslmode=require”
{“level”:“info”,“ts”:“2020-03-06T02:56:47.035Z”,“msg”:“Database connection”,“dsn”:“nakama@cockroachdb:26257/?sslcert=/certs/client.nakama.crt&sslkey=/certs/client.nakama.key&sslmode=require”}
{“level”:“fatal”,“ts”:“2020-03-06T02:56:47.055Z”,“msg”:“Error querying database version”,“error”:“unknown oid: 25, name: version”,“errorVerbose”:“unknown oid: 25, name: version\ngithub.com/jackc/pgx.(*Conn).prepareEx\n\tgithub.com/jackc/pgx@v3.5.0+incompatible/conn.go:1137\ngithub.com/jackc/pgx.(*Conn).PrepareEx\n\tgithub.com/jackc/pgx@v3.5.0+incompatible/conn.go:1065\ngithub.com/jackc/pgx/stdlib.(*Conn).QueryContext\n\tgithub.com/jackc/pgx@v3.5.0+incompatible/stdlib/sql.go:365\ndatabase/sql.ctxDriverQuery\n\tdatabase/sql/ctxutil.go:48\ndatabase/sql.(*DB).queryDC.func1\n\tdatabase/sql/sql.go:1592\ndatabase/sql.withLock\n\tdatabase/sql/sql.go:3184\ndatabase/sql.(*DB).queryDC\n\tdatabase/sql/sql.go:1587\ndatabase/sql.(*DB).query\n\tdatabase/sql/sql.go:1570\ndatabase/sql.(*DB).QueryContext\n\tdatabase/sql/sql.go:1547\ndatabase/sql.(*DB).QueryRowContext\n\tdatabase/sql/sql.go:1648\ndatabase/sql.(*DB).QueryRow\n\tdatabase/sql/sql.go:1659\ngithub.com/heroiclabs/nakama/v2/migrate.Parse\n\tgithub.com/heroiclabs/nakama/v2@/migrate/migrate.go:151\nmain.main\n\tmain.go:79\nruntime.main\n\truntime/proc.go:203\nruntime.goexit\n\truntime/asm_amd64.s:1357",“stacktrace”:"github.com/heroiclabs/nakama/v2/migrate.Parse\n\tgithub.com/heroiclabs/nakama/v2@/migrate/migrate.go:152\nmain.main\n\tmain.go:79\nruntime.main\n\truntime/proc.go:203”}

I am using the docker container setup for Cockroach and Nakama. Any help on why this might be? When I run the SELECT version() query from DBeaver using the same user, everything works so I don’t think it is a permission thing.

@newatlanta19006 I can’t reproduce your issue with the migrate subcommand of the server. I ran these steps and it worked without any issues.

$> cockroach cert create-ca --certs-dir=crdbcerts --ca-key=crdbsecretcerts/ca.key
$> cockroach cert create-node localhost $(hostname) --certs-dir=crdbcerts --ca-key=crdbsecretcerts/ca.key
$> cockroach cert create-client root --certs-dir=crdbcerts --ca-key=crdbsecretcerts/ca.key
$> cockroach start --certs-dir=crdbcerts --store=node1 --listen-addr=localhost:26257 --http-addr=localhost:8080 --background
$> nakama migrate up --database.address "root@localhost:26257?sslcert=crdbcerts/client.root.crt&sslkey=crdbcerts/client.root.key&sslmode=verify-full&sslrootcert=crdbcerts/ca.crt"

This produces this output from Nakama server:

{"level":"info","ts":"...","msg":"Database connection","dsn":"root@localhost:26257?sslcert=crdbcerts/client.root.crt&sslkey=crdbcerts/client.root.key&sslmode=verify-full&sslrootcert=crdbcerts/ca.crt"}
{"level":"info","ts":"...","msg":"Database information","version":"CockroachDB CCL v19.1.5 (x86_64-apple-darwin18.7.0, built 2019/10/10 02:31:05, go1.13.1)"}
{"level":"info","ts":"...","msg":"Creating new database","name":"nakama"}
{"level":"info","ts":"...","msg":"Successfully applied migration","count":3}

These steps come almost directly from the guide with cockroachdb:

https://www.cockroachlabs.com/docs/v19.2/secure-a-cluster.html#step-1-generate-certificates

It seems that you MUST use root as user. I tried creating another user but database and table permissions were not enough. Without Enterprise CockroachDb, you cant create superusers. Once I switched to root user it worked again. Might be worth documenting.

@newatlanta19006 That’s a good point. Definitely please open a pull request on the docs when you get a chance :+1: