{"action":"created","issue":{"url":"https://api.github.com/repos/concourse/concourse/issues/631","repository_url":"https://api.github.com/repos/concourse/concourse","labels_url":"https://api.github.com/repos/concourse/concourse/issues/631/labels{/name}","comments_url":"https://api.github.com/repos/concourse/concourse/issues/631/comments","events_url":"https://api.github.com/repos/concourse/concourse/issues/631/events","html_url":"https://github.com/concourse/concourse/issues/631","id":175048457,"number":631,"title":"concourse fails with postgres with ssl mode enabled (since 2.0)","user":{"login":"stepanstipl","id":3027227,"avatar_url":"https://avatars.githubusercontent.com/u/3027227?v=3","gravatar_id":"","url":"https://api.github.com/users/stepanstipl","html_url":"https://github.com/stepanstipl","followers_url":"https://api.github.com/users/stepanstipl/followers","following_url":"https://api.github.com/users/stepanstipl/following{/other_user}","gists_url":"https://api.github.com/users/stepanstipl/gists{/gist_id}","starred_url":"https://api.github.com/users/stepanstipl/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/stepanstipl/subscriptions","organizations_url":"https://api.github.com/users/stepanstipl/orgs","repos_url":"https://api.github.com/users/stepanstipl/repos","events_url":"https://api.github.com/users/stepanstipl/events{/privacy}","received_events_url":"https://api.github.com/users/stepanstipl/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/concourse/concourse/labels/unscheduled","name":"unscheduled","color":"e4eff7"}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":2,"created_at":"2016-09-05T11:05:32Z","updated_at":"2016-09-05T16:07:12Z","closed_at":null,"body":"After update to concourse 2.0.2 (from 1.6.1), web will not start anymore *(running official docker containter from Docker Hub)*, the only message I get in logs is:\r\n```\r\n{\"timestamp\":\"1472836271.278480053\",\"source\":\"atc\",\"message\":\"atc.db.migrations.migration-lock-acquired\",\"log_level\":1,\"data\":{\"session\":\"2\"}}\r\nx509: certificate signed by unknown authority\r\n```\r\n\r\n(running with -d flag doesn't give any more output.)\r\n\r\nBy trial/error - it seems to be related to ssl connection to the DB, I had `?sslmode=verify-full&sslrootcert=/concourse-certs/aws-rds-ca-2015-root.pem` in postgres connection string and this used to work previously (golang postgres client supports this), if I disable ssl, concourse will start fine.\r\n\r\nRunning with `sslmode=prefer` throws\r\n```\r\n{\"timestamp\":\"1473070877.764324665\",\"source\":\"atc\",\"message\":\"atc.db.migrations.failed-to-acquire-lock-retrying\",\"log_level\":2,\"data\":{\"error\":\"pq: unsupported sslmode \\\"prefer\\\"; only \\\"require\\\" (default), \\\"verify-full\\\", \\\"verify-ca\\\", and \\\"disable\\\" supported\",\"session\":\"2\"}}\r\n```\r\n\r\nThe problem seems to be `https://github.com/jackc/pgx` which in comparison to `https://github.com/lib/pq` has only limited support for ssl related settings in Postgres connection string.\r\n\r\nI've come up with a workaround that seems to be working fine - instead of passing location of trusted root ca in postgres string (`sslrootcert=/concourse-certs/aws-rds-ca-2015-root.pem`), add these certs to system bundle.\r\n\r\nIn my case (running CoreOS) it means:\r\n- copy desired ca certificates to `/etc/ssl/certs/`\r\n- run `/sbin/update-ca-certificates` to update system bundle\r\n- mount these inside Concourse web container `-v /etc/ssl/certs/ca-certificates.crt:/etc/ssl/certs/ca-certificates.crt`\r\n\r\n\r\nPassing custom location in postgres string used to work in 1.6 I've used previously and is kind of standard way how to do it (https://www.postgresql.org/docs/9.1/static/libpq-ssl.html).\r\n\r\nI understand this will probably depend on support from `pgx` library, but I would suggest at least to add some documentation abut this. It's something that was working just fine before 2.0.\r\n\r\n(related to #449)"},"comment":{"url":"https://api.github.com/repos/concourse/concourse/issues/comments/244778713","html_url":"https://github.com/concourse/concourse/issues/631#issuecomment-244778713","issue_url":"https://api.github.com/repos/concourse/concourse/issues/631","id":244778713,"user":{"login":"vito","id":1880,"avatar_url":"https://avatars.githubusercontent.com/u/1880?v=3","gravatar_id":"","url":"https://api.github.com/users/vito","html_url":"https://github.com/vito","followers_url":"https://api.github.com/users/vito/followers","following_url":"https://api.github.com/users/vito/following{/other_user}","gists_url":"https://api.github.com/users/vito/gists{/gist_id}","starred_url":"https://api.github.com/users/vito/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/vito/subscriptions","organizations_url":"https://api.github.com/users/vito/orgs","repos_url":"https://api.github.com/users/vito/repos","events_url":"https://api.github.com/users/vito/events{/privacy}","received_events_url":"https://api.github.com/users/vito/received_events","type":"User","site_admin":false},"created_at":"2016-09-05T16:07:12Z","updated_at":"2016-09-05T16:07:12Z","body":"We should maybe just split that field up into separate flags. That'd at least make it more obvious to configure things like this rather than figuring out the connection string syntax, and which bits are portable."}}
{"actor":{"display_login":"vito"}}