AWS Redshift using Postgres fails to connect

Grafana PostgreSQL
1

Issue: I am having a 500 error to my AWS Redshift instance using the Postgres connection.
Version: 4.6.2 Windows

I am able to maintain connection to localhost version of Postgres no issues.

The difference? My Redshift requires SSL and cert is copied into system via MMC->Certificates. I can connect via python/pandas/url without specifying location of SSL file with:

url = postgres://user:secret@host:port/database

The same url fails in Grafana, I assume the 500 error is due to the SSL cert. There is an option in mySql to specify the cert, is there any config for Postgres? I have tried adding the data url in the custom.ini (used for port 8080), but the Grafana service will not start with the url included in the ini file. I am out of ideas… any suggestions?

2

The configuration docs page is slightly out of date. Postgres now supports the config same fields for certs as MySQL.

Updating the docs now…

3

Now, I can get it to connect to my Redshift DB through the custom.ini file by adding:

ssl_mode = verify-ca

However, this seems to be the application database (saving layouts, etc) that is referenced in this config file and not a data source (time series).

I put in the same login creds into the Grafana UI (Datasource → Add Datasource → Postgres) with verify-ca and get:
Internal Server Error and see the 500 error in the log. Is this a bug?

This is what I see in the log:

t=2017-12-12T13:22:06-0600 lvl=dbug msg=getEngine logger=tsdb.postgres connection=“postgres://un:pw@sever.redshift.amazonaws.com:5439/analytics?sslmode=verify-ca”

The github link provided above tomkozlowski says that ?sslmode= is not valid in the postgres url and must be added to db config object. Again, looking to use a cert for a datasource, not application db .ini.

Thanks!

4

The issue above is for when using Postgres as the Grafana db.

sslmode is a valid querystring parameter for a postgres connection string (and for Redshift):

http://docs.aws.amazon.com/redshift/latest/mgmt/connecting-ssl-support.html

What is the error you are getting? Usually it says something more than just 500.

5

Here is the log dump at “debug” level the data URL is tested on other connections successfully:

Looks like the error is:

nil pointer dereference

t=2017-12-13T12:14:34-0600 lvl=dbug msg=getEngine logger=tsdb.postgres connection=““postgres://un:pw@sever.redshift.amazonaws.com:5439/analytics?sslmode=verify-ca”
t=2017-12-13T12:14:34-0600 lvl=eror msg=“Request error” logger=context userId=1 orgId=1 uname=admin error=“runtime error: invalid memory address or nil pointer dereference” stack="C:/go/src/runtime/panic.go:491 (0x42f510)
gopanic: reflectcall(nil, unsafe.Pointer(d.fn), deferArgs(d), uint32(d.siz), uint32(d.siz))
C:/go/src/runtime/panic.go:63 (0x42e394)
panicmem: panic(memoryError)
C:/go/src/runtime/signal_windows.go:161 (0x4452a2)
sigpanic: panicmem()
c:/gopath/src/github.com/grafana/grafana/vendor/github.com/go-xorm/xorm/engine.go:191 (0x8c3949)
c:/gopath/src/github.com/grafana/grafana/pkg/tsdb/sql_engine.go:60 (0xbc87be)
c:/gopath/src/github.com/grafana/grafana/pkg/tsdb/postgres/postgres.go:38 (0xd68668)
c:/gopath/src/github.com/grafana/grafana/pkg/tsdb/query_endpoint.go:24 (0xbc84ad)
c:/gopath/src/github.com/grafana/grafana/pkg/tsdb/request.go:12 (0xbc85f5)
c:/gopath/src/github.com/grafana/grafana/pkg/api/metrics.go:46 (0xe0ab25)
C:/go/src/runtime/asm_amd64.s:511 (0x45c9d8)
call128: CALLFN(·call128, 128)
C:/go/src/reflect/value.go:434 (0x4d57ab)
Value.call: call(frametype, fn, args, uint32(frametype.size), uint32(retOffset))
C:/go/src/reflect/value.go:302 (0x4d4d6a)
Value.Call: return v.call(“Call”, in)
c:/gopath/src/github.com/grafana/grafana/vendor/github.com/go-macaron/inject/inject.go:177 (0x7e0abf)
c:/gopath/src/github.com/grafana/grafana/vendor/github.com/go-macaron/inject/inject.go:137 (0x7e03b0)
c:/gopath/src/github.com/grafana/grafana/pkg/api/common.go:37 (0xe26cb2)
C:/go/src/runtime/asm_amd64.s:509 (0x45c8a1)
call32: CALLFN(·call32, 32)
C:/go/src/reflect/value.go:434 (0x4d57ab)
Value.call: call(frametype, fn, args, uint32(frametype.size), uint32(retOffset))
C:/go/src/reflect/value.go:302 (0x4d4d6a)
Value.Call: return v.call(“Call”, in)
c:/gopath/src/github.com/grafana/grafana/vendor/github.com/go-macaron/inject/inject.go:177 (0x7e0abf)
c:/gopath/src/github.com/grafana/grafana/vendor/github.com/go-macaron/inject/inject.go:137 (0x7e03b0)
c:/gopath/src/github.com/grafana/grafana/vendor/gopkg.in/macaron.v1/context.go:121 (0x7fde94)
c:/gopath/src/github.com/grafana/grafana/vendor/gopkg.in/macaron.v1/context.go:112 (0x7fddbc)
c:/gopath/src/github.com/grafana/grafana/pkg/middleware/request_tracing.go:25 (0xbb013a)
C:/go/src/runtime/asm_amd64.s:509 (0x45c8a1)
call32: CALLFN(·call32, 32)
C:/go/src/reflect/value.go:434 (0x4d57ab)
Value.call: call(frametype, fn, args, uint32(frametype.size), uint32(retOffset))
C:/go/src/reflect/value.go:302 (0x4d4d6a)
Value.Call: return v.call(“Call”, in)
c:/gopath/src/github.com/grafana/grafana/vendor/github.com/go-macaron/inject/inject.go:177 (0x7e0abf)
c:/gopath/src/github.com/grafana/grafana/vendor/github.com/go-macaron/inject/inject.go:137 (0x7e03b0)
c:/gopath/src/github.com/grafana/grafana/vendor/gopkg.in/macaron.v1/context.go:121 (0x7fde94)
c:/gopath/src/github.com/grafana/grafana/vendor/gopkg.in/macaron.v1/context.go:112 (0x7fddbc)
c:/gopath/src/github.com/grafana/grafana/pkg/middleware/request_metrics.go:17 (0xbaf9bb)
C:/go/src/runtime/asm_amd64.s:509 (0x45c8a1)
call32: CALLFN(·call32, 32)
C:/go/src/reflect/value.go:434 (0x4d57ab)
Value.call: call(frametype, fn, args, uint32(frametype.size), uint32(retOffset))
C:/go/src/reflect/value.go:302 (0x4d4d6a)
Value.Call: return v.call(“Call”, in)
c:/gopath/src/github.com/grafana/grafana/vendor/github.com/go-macaron/inject/inject.go:177 (0x7e0abf)
c:/gopath/src/github.com/grafana/grafana/vendor/github.com/go-macaron/inject/inject.go:137 (0x7e03b0)
c:/gopath/src/github.com/grafana/grafana/vendor/gopkg.in/macaron.v1/context.go:121 (0x7fde94)
c:/gopath/src/github.com/grafana/grafana/vendor/gopkg.in/macaron.v1/context.go:112 (0x7fddbc)
c:/gopath/src/github.com/grafana/grafana/pkg/middleware/session.go:79 (0xbb04d1)
C:/go/src/runtime/asm_amd64.s:509 (0x45c8a1)
call32: CALLFN(·call32, 32)
C:/go/src/reflect/value.go:434 (0x4d57ab)
Value.call: call(frametype, fn, args, uint32(frametype.size), uint32(retOffset))
C:/go/src/reflect/value.go:302 (0x4d4d6a)
Value.Call: return v.call(“Call”, in)
c:/gopath/src/github.com/grafana/grafana/vendor/github.com/go-macaron/inject/inject.go:177 (0x7e0abf)
c:/gopath/src/github.com/grafana/grafana/vendor/github.com/go-macaron/inject/inject.go:137 (0x7e03b0)
c:/gopath/src/github.com/grafana/grafana/vendor/gopkg.in/macaron.v1/context.go:121 (0x7fde94)
c:/gopath/src/github.com/grafana/grafana/vendor/gopkg.in/macaron.v1/context.go:112 (0x7fddbc)
c:/gopath/src/github.com/grafana/grafana/pkg/middleware/recovery.go:146 (0xbaf8d7)
c:/gopath/src/github.com/grafana/grafana/vendor/gopkg.in/macaron.v1/context.go:79 (0x7fdc67)
c:/gopath/src/github.com/grafana/grafana/vendor/github.com/go-macaron/inject/inject.go:157 (0x7e077a)
c:/gopath/src/github.com/grafana/grafana/vendor/github.com/go-macaron/inject/inject.go:135 (0x7e04a1)
c:/gopath/src/github.com/grafana/grafana/vendor/gopkg.in/macaron.v1/context.go:121 (0x7fde94)
c:/gopath/src/github.com/grafana/grafana/vendor/gopkg.in/macaron.v1/context.go:112 (0x7fddbc)
c:/gopath/src/github.com/grafana/grafana/pkg/middleware/logger.go:33 (0xbad438)
C:/go/src/runtime/asm_amd64.s:509 (0x45c8a1)
call32: CALLFN(·call32, 32)
C:/go/src/reflect/value.go:434 (0x4d57ab)
Value.call: call(frametype, fn, args, uint32(frametype.size), uint32(retOffset))
C:/go/src/reflect/value.go:302 (0x4d4d6a)
Value.Call: return v.call(“Call”, in)
c:/gopath/src/github.com/grafana/grafana/vendor/github.com/go-macaron/inject/inject.go:177 (0x7e0abf)
c:/gopath/src/github.com/grafana/grafana/vendor/github.com/go-macaron/inject/inject.go:137 (0x7e03b0)
c:/gopath/src/github.com/grafana/grafana/vendor/gopkg.in/macaron.v1/context.go:121 (0x7fde94)
c:/gopath/src/github.com/grafana/grafana/vendor/gopkg.in/macaron.v1/router.go:187 (0x80fbb9)
c:/gopath/src/github.com/grafana/grafana/vendor/gopkg.in/macaron.v1/router.go:296 (0x80a042)
c:/gopath/src/github.com/grafana/grafana/vendor/gopkg.in/macaron.v1/macaron.go:220 (0x80279e)
C:/go/src/net/http/server.go:2619 (0x69935a)
serverHandler.ServeHTTP: handler.ServeHTTP(rw, req)
C:/go/src/net/http/server.go:1801 (0x695503)
(*conn).serve: serverHandler{c.server}.ServeHTTP(w, w.req)
C:/go/src/runtime/asm_amd64.s:2337 (0x45f1b0)
goexit: BYTE $0x90 // NOP

t=2017-12-13T12:14:34-0600 lvl=eror msg=“Request Completed” logger=context userId=1 orgId=1 uname=admin method=POST path=/api/tsdb/query status=500 remote_addr=[::1] time_ms=25 size=1166 referer=http://localhost:8080/datasources/edit/2

6

Aha, this is probably something to with the password containing a symbol that breaks the connections string/querystring. There is a fix for this in master and I got a PR accepted for a fix in Xorm (the ORM framework that Grafana uses) so this will be fixed in future versions of Grafana.

Do you have a # or something similar in the password?

Reference:

7

I saw the comment in the log file about if a PW has a “#” to wrap it in triple quotes.

Mine did not. I even created a new account for Grafana before posting this issue with a much more simple password but it did have a “$” in it. Which my original also had, so there you go, case solved.

No “$” in the db passwords. I removed them and can now connect! … and WOW! Pure awesomeness.

Thank you!