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

Local Setup
1

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.

1 Like
2

@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

1 Like
3

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.

1 Like
4

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

1 Like
5

I am replying to this thread just in case other people have the same problem. I faced this problem when I tried to start a secure single-node cockroach cluster. I did not want to use the root user, so here is how I did it.
After creating the cluster using single-node cluster provided by cockroach documentation, or as @novabyte mentioned earlier, you have to create a user with enough privileges to access the database and use that user and its password when starting up nakama. Creating this user also benefits you when you want to access the cockroach dashboard UI (which is accessible by default on your $(hostname):8080).

// create the user as stated by cockroach documentation, for more info: https://www.cockroachlabs.com/docs/stable/create-user.html
server$ cockroach sql --certs-dir /path/to/certs --host name-of-the-host 
cockroach sql> CREATE USER username WITH PASSWORD 'cockroach';
// give the newly created user admin access; if that is too much, you have to look at https://www.cockroachlabs.com/docs/v20.2/grant 
cockroach sql> GRANT admin TO username

Now, you should be able to access the cockroachDB dashboard and see your databases by logging in at {hostname}:8080 by the username and password you just specified.
now you can use this usename to do the migrate up command. I use docker-compose to start my nakama server, so this is how I do it:

version: '3'
services:
 nakama:
   build:
     context: .
   container_name: game_backend
   entrypoint:
     - "/bin/sh"
     - "-ecx"
     - >
        /nakama/nakama migrate up --database.address "sentinel:${PASSWORD}@IP:26257?sslcert=/usr/.certs/client.sentinel.crt&sslkey=/usr/.certs/client.sentinel.key&sslmode=require" &&
        exec /nakama/nakama --name nakama1 --database.address "sentinel:${PASSWORD}@IP:26257?sslcert=/usr/.certs/client.sentinel.crt&sslkey=/usr/.certs/client.sentinel.key&sslmode=require"
   expose:
     - "7349"
     - "7350"
     - "7351"
   healthcheck:
     test: ["CMD", "curl", "-f", "http://localhost:7350/"]
     interval: 10s
     timeout: 5s
     retries: 5
   ports:
     - "7349:7349"
     - "7350:7350"
     - "7351:7351"
   restart: unless-stopped

   volumes:
     - /home/neo/.certs:/usr/.certs

I have a env file as following:

PASSWORD="some pass"

I provide docker-compose with the env file when I want to start my nakama server:
docker-compose --env-file /path/to/env/file -f /path/to/docker-compose.yml up
running this gives me the following results:

game_backend | {"level":"info","ts":"2020-11-15T17:01:06.375Z","caller":"migrate/migrate.go:139","msg":"Database connection","dsn":"postgresql://sentinel:xxxxx@IP:26257?sslcert=/usr/.certs/client.sentinel.crt&sslkey=/usr/.certs/client.sentinel.key&sslmode=require"}
game_backend | {"level":"info","ts":"2020-11-15T17:01:06.572Z","caller":"migrate/migrate.go:154","msg":"Database information","version":"CockroachDB CCL v20.2.0 (x86_64-unknown-linux-gnu, built 2020/11/09 16:01:45, go1.13.14)"}
game_backend | {"level":"info","ts":"2020-11-15T17:01:06.576Z","caller":"migrate/migrate.go:158","msg":"Using existing database","name":"nakama"}
game_backend | {"level":"info","ts":"2020-11-15T17:01:06.752Z","caller":"migrate/migrate.go:192","msg":"Successfully applied migration","count":0}
3 Likes