AWS Redshift using Postgres fails to connect

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?

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

Updating the docs now…

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!

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.

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

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:

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!