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

**URL:** <https://forum.heroiclabs.com/t/error-in-migrate-up-command-when-connecting-in-secure-mode/549>\
**Category:** Local Setup\
**Created:** [March 6, 2020, 3:02am UTC](https://forum.heroiclabs.com/t/error-in-migrate-up-command-when-connecting-in-secure-mode/549 "2020-03-06T03:02:42Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![newatlanta19006](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.heroiclabs.com/newatlanta19006/32/132_2.png) [@newatlanta19006](https://forum.heroiclabs.com/u/newatlanta19006)\
**Post date:** [March 6, 2020, 3:02am UTC](https://forum.heroiclabs.com/t/error-in-migrate-up-command-when-connecting-in-secure-mode/549/1 "2020-03-06T03:02:42Z")

</div>

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](http://ngithub.com/jackc/pgx.(*Conn)).prepareEx\n\[tgithub.com/jackc/pgx@v3.5.0+incompatible/conn.go:1137\ngithub.com/jackc/pgx.(\*Conn](http://tgithub.com/jackc/pgx@v3.5.0+incompatible/conn.go:1137%5Cngithub.com/jackc/pgx.(*Conn)).PrepareEx\n\[tgithub.com/jackc/pgx@v3.5.0+incompatible/conn.go:1065\ngithub.com/jackc/pgx/stdlib.(\*Conn](http://tgithub.com/jackc/pgx@v3.5.0+incompatible/conn.go:1065%5Cngithub.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](http://tgithub.com/jackc/pgx@v3.5.0+incompatible/stdlib/sql.go:365%5Cndatabase/sql.ctxDriverQuery%5Cn%5Ctdatabase/sql/ctxutil.go:48%5Cndatabase/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](http://ngithub.com/heroiclabs/nakama/v2/migrate.Parse%5Cn%5Ctgithub.com/heroiclabs/nakama/v2@/migrate/migrate.go:151%5Cnmain.main%5Cn%5Ctmain.go:79%5Cnruntime.main%5Cn%5Ctruntime/proc.go:203%5Cnruntime.goexit%5Cn%5Ctruntime/asm_amd64.s:1357%22,%22stacktrace%22:%22github.com/heroiclabs/nakama/v2/migrate.Parse%5Cn%5Ctgithub.com/heroiclabs/nakama/v2@/migrate/migrate.go:152%5Cnmain.main%5Cn%5Ctmain.go:79%5Cnruntime.main%5Cn%5Ctruntime/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.

---

<div class="post-metadata">

**Author:** ![novabyte](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.heroiclabs.com/novabyte/32/1324_2.png) [@novabyte](https://forum.heroiclabs.com/u/novabyte)\
**Post date:** [March 8, 2020, 6:42pm UTC](https://forum.heroiclabs.com/t/error-in-migrate-up-command-when-connecting-in-secure-mode/549/2 "2020-03-08T18:42:41Z")

</div>

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

```shell
$> 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:

```auto
{"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](https://www.cockroachlabs.com/docs/v19.2/secure-a-cluster.html#step-1-generate-certificates)

---

<div class="post-metadata">

**Author:** ![newatlanta19006](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.heroiclabs.com/newatlanta19006/32/132_2.png) [@newatlanta19006](https://forum.heroiclabs.com/u/newatlanta19006)\
**Post date:** [March 8, 2020, 6:56pm UTC](https://forum.heroiclabs.com/t/error-in-migrate-up-command-when-connecting-in-secure-mode/549/3 "2020-03-08T18:56:11Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![novabyte](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.heroiclabs.com/novabyte/32/1324_2.png) [@novabyte](https://forum.heroiclabs.com/u/novabyte)\
**Post date:** [March 8, 2020, 9:02pm UTC](https://forum.heroiclabs.com/t/error-in-migrate-up-command-when-connecting-in-secure-mode/549/4 "2020-03-08T21:02:33Z")

</div>

@newatlanta19006 That’s a good point. Definitely please open a pull request on the [docs](https://github.com/heroiclabs/nakama-docs) when you get a chance 👍

---

<div class="post-metadata">

**Author:** ![Mahdad-Baghani](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.heroiclabs.com/mahdad-baghani/32/465_2.png) [@Mahdad-Baghani](https://forum.heroiclabs.com/u/Mahdad-Baghani)\
**Post date:** [November 15, 2020, 6:29pm UTC](https://forum.heroiclabs.com/t/error-in-migrate-up-command-when-connecting-in-secure-mode/549/5 "2020-11-15T18:29:13Z")

</div>

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](https://www.cockroachlabs.com/docs/v20.2/cockroach-start-single-node#synopsis), 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).

```auto
// 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:

```auto
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:

```auto
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:

```auto
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}
```