{"id":345,"date":"2018-04-19T03:03:25","date_gmt":"2018-04-19T03:03:25","guid":{"rendered":"https:\/\/www.spotonoracle.com\/?p=345"},"modified":"2018-04-20T00:06:28","modified_gmt":"2018-04-20T00:06:28","slug":"secure-postgres-connection-from-sql-developer","status":"publish","type":"post","link":"https:\/\/www.spotonoracle.com\/?p=345","title":{"rendered":"Secure Postgres connection from SQL Developer"},"content":{"rendered":"<p>Searching the web it seems most people use &#8220;org.postgresql.ssl.NonValidatingFactory&#8221; to connect to Postgres from SQL Developer.<\/p>\n<p>This doesn&#8217;t seem to be a good idea as the <a href=\"https:\/\/jdbc.postgresql.org\/documentation\/publicapi\/org\/postgresql\/ssl\/NonValidatingFactory.html\" rel=\"noopener\" target=\"_blank\">documentation<\/a> states it is not secure:<br \/>\n<code>Provide a SSLSocketFactory that allows SSL connections to be made without validating the server's certificate. This is more convenient for some applications, but is less secure as it allows \"man in the middle\" attacks.<\/code><\/p>\n<p>Looking into the <a href=\"https:\/\/jdbc.postgresql.org\/download.html\" rel=\"noopener\" target=\"_blank\">jar file<\/a> under &#8220;\/org\/postgres\/ssl&#8221; you&#8217;ll also find <a href=\"https:\/\/jdbc.postgresql.org\/documentation\/publicapi\/org\/postgresql\/ssl\/SingleCertValidatingFactory.html\" rel=\"noopener\" target=\"_blank\">SingleCertValidatingFactory<\/a>. Now that&#8217;s much better:<br \/>\n<code>Provides a SSLSocketFactory that authenticates the remote server against an explicit pre-shared SSL certificate. This is more secure than using the NonValidatingFactory as it prevents \"man in the middle\" attacks. It is also more secure than relying on a central CA signing your server's certificate as it pins the server's certificate.<\/code><\/p>\n<p>Let&#8217;s get to it&#8230;<\/p>\n<p><strong>The boring part:<\/strong> load the Postgres JDBC driver<br \/>\nGo to &#8220;Tools&#8221; -> &#8220;Preferences&#8230;&#8221; -> &#8220;Third Party JDBC Drivers&#8221; and add the jar file<br \/>\n<a href=\"https:\/\/www.spotonoracle.com\/wp-content\/uploads\/2018\/04\/pg-aws-conn-1.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.spotonoracle.com\/wp-content\/uploads\/2018\/04\/pg-aws-conn-1.png\" alt=\"\" width=\"300\" height=\"187\" class=\"alignnone size-medium wp-image-348\" \/><\/a><\/p>\n<p><strong>The tricky part:<\/strong> getting the connect string right (it&#8217;s actually not that hard)<br \/>\nSyntax:<\/p>\n<pre class=\"brush: sql; collapse: false; title: ; wrap-lines: false; notranslate\" title=\"\">\r\n&lt;hostname&gt;\/&lt;database&gt;?ssl=true&amp;sslfactory=org.postgresql.ssl.SingleCertValidatingFactory&amp;sslfactoryarg=file:&lt;path-to-cert-file&gt;&amp;\r\n<\/pre>\n<p>For example:<\/p>\n<pre class=\"brush: sql; collapse: false; title: ; wrap-lines: false; notranslate\" title=\"\">\r\nabc.xzy.us-east-1.rds.amazonaws.com\/postgres?ssl=true&amp;sslfactory=org.postgresql.ssl.SingleCertValidatingFactory&amp;sslfactoryarg=file:\/home\/btr\/certs\/amazon-rds.crt&amp;\r\n<\/pre>\n<p>Enter this entire string in the &#8220;Hostname&#8221; field.<br \/>\n<a href=\"https:\/\/www.spotonoracle.com\/wp-content\/uploads\/2018\/04\/pg-aws-conn-2.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.spotonoracle.com\/wp-content\/uploads\/2018\/04\/pg-aws-conn-2.png\" alt=\"\" width=\"300\" height=\"235\" class=\"alignnone size-medium wp-image-349\" \/><\/a><\/p>\n<p><strong>The exciting part:<\/strong> playing with your newly connected Postgres database \ud83d\ude42<\/p>\n<p>Keep it secure!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Searching the web it seems most people use &#8220;org.postgresql.ssl.NonValidatingFactory&#8221; to connect to Postgres from SQL Developer. This doesn&#8217;t seem to be a good idea as the documentation states it is not secure: Provide a SSLSocketFactory that allows SSL connections to be made without validating the server&#8217;s certificate. This is more convenient for some applications, but [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[12,6],"tags":[],"class_list":["post-345","post","type-post","status-publish","format-standard","hentry","category-security","category-sqldev"],"_links":{"self":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/345","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=345"}],"version-history":[{"count":10,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/345\/revisions"}],"predecessor-version":[{"id":361,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/345\/revisions\/361"}],"wp:attachment":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=345"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=345"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=345"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}