{"action":"created","issue":{"url":"https://api.github.com/repos/dimitri/pgloader/issues/308","labels_url":"https://api.github.com/repos/dimitri/pgloader/issues/308/labels{/name}","comments_url":"https://api.github.com/repos/dimitri/pgloader/issues/308/comments","events_url":"https://api.github.com/repos/dimitri/pgloader/issues/308/events","html_url":"https://github.com/dimitri/pgloader/issues/308","id":115727604,"number":308,"title":"sslmode and client certificates?","user":{"login":"bazeli","id":9267316,"avatar_url":"https://avatars.githubusercontent.com/u/9267316?v=3","gravatar_id":"","url":"https://api.github.com/users/bazeli","html_url":"https://github.com/bazeli","followers_url":"https://api.github.com/users/bazeli/followers","following_url":"https://api.github.com/users/bazeli/following{/other_user}","gists_url":"https://api.github.com/users/bazeli/gists{/gist_id}","starred_url":"https://api.github.com/users/bazeli/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/bazeli/subscriptions","organizations_url":"https://api.github.com/users/bazeli/orgs","repos_url":"https://api.github.com/users/bazeli/repos","events_url":"https://api.github.com/users/bazeli/events{/privacy}","received_events_url":"https://api.github.com/users/bazeli/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/dimitri/pgloader/labels/Corner%20case","name":"Corner case","color":"fbca04"},{"url":"https://api.github.com/repos/dimitri/pgloader/labels/WishList","name":"WishList","color":"fef2c0"}],"state":"closed","locked":false,"assignee":null,"milestone":null,"comments":6,"created_at":"2015-11-08T11:50:22Z","updated_at":"2015-11-09T23:58:30Z","closed_at":"2015-11-09T10:32:26Z","body":"Hello,\r\n\r\nI'm looking to connect to a postgres server on the same subnet using SSL with client certificate authentication.  While I've read in the pgloader documentation that the only user definable option is `sslmode`, I'm assuming pgloader follows the postgres standard of looking for client certificates in `~/.postgresql/postgresql.crt`.    Unforunately, I'm getting the following error:\r\n\r\n`Database error 28000: connection requires a valid client certificate`\r\n\r\n**Postgres connection string** portion of pgloader is:\r\n\r\n`postgresql://dbuser@dbserver/userdb?sslmode=prefer`\r\n\r\n**pg_hba.conf:**\r\n\r\n`hostssl    userdb             +sslcertusers             privateip          cert   clientcert=1`\r\n\r\n(sslcertusers is a table containing a valid record for \"dbuser\")\r\n\r\n**postgresql.conf**\r\n\r\n```\r\nssl = true\t\r\nssl_cert_file = '/var/lib/postgresql/9.4/main/server.crt'\r\nssl_key_file = '/var/lib/postgresql/9.4/main/server.key'\r\nssl_ca_file = '/var/lib/postgresql/9.4/main/root.crt'\r\n```\r\n\r\nClient certificates are signed via in-house CA.  The client certificate itself is functioning correctly when using **psql on the command line**:\r\n\r\n```\r\npsql -h dbserver -U dbuser userdb\r\npsql (9.4.4)\r\nSSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)\r\nType \"help\" for help.\r\n```\r\n\r\nand **verified as OK by openssl**:\r\n\r\n```user@webserver:~/.postgresql$ openssl verify -CAfile root.crt -purpose sslclient postgresql.crt\r\npostgresql.crt: OK```\r\n\r\nI have also tried exporting the PGSSLCERT and PGSSLCERT env vars standard to postgres, but still can't seem to connect with pgloader.   As a last resort, I have confirmed that pgloader does in fact connect fine when I modify my pg_hba.conf file to allow for md5 based authentication without SSL.\r\n\r\nSurely I must be missing something - any help would be most appreciated."},"comment":{"url":"https://api.github.com/repos/dimitri/pgloader/issues/comments/155238010","html_url":"https://github.com/dimitri/pgloader/issues/308#issuecomment-155238010","issue_url":"https://api.github.com/repos/dimitri/pgloader/issues/308","id":155238010,"user":{"login":"bazeli","id":9267316,"avatar_url":"https://avatars.githubusercontent.com/u/9267316?v=3","gravatar_id":"","url":"https://api.github.com/users/bazeli","html_url":"https://github.com/bazeli","followers_url":"https://api.github.com/users/bazeli/followers","following_url":"https://api.github.com/users/bazeli/following{/other_user}","gists_url":"https://api.github.com/users/bazeli/gists{/gist_id}","starred_url":"https://api.github.com/users/bazeli/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/bazeli/subscriptions","organizations_url":"https://api.github.com/users/bazeli/orgs","repos_url":"https://api.github.com/users/bazeli/repos","events_url":"https://api.github.com/users/bazeli/events{/privacy}","received_events_url":"https://api.github.com/users/bazeli/received_events","type":"User","site_admin":false},"created_at":"2015-11-09T23:58:30Z","updated_at":"2015-11-09T23:58:30Z","body":"Thank you very much for the patch - I've tested and encountered the following error:\r\n\r\n```\r\nAn unhandled error condition has been signalled:\r\n   Failed to connect to pgsql at \"dbserver\" (port 5432) as user \"dbuser\": /home/dbuser/.postgresql/postgresql.key is not a Lisp string or pointer.\r\n```\r\n\r\n"}}