alexey-turapin · October 18, 2019 06:35 · Aug 9, 2019 · Mar 7, 2018 · Mar 7, 2018 · Mar 7, 2018
diff --git a/powerbi-postgres-cert-steps.md b/powerbi-postgres-cert-steps.md
@@ -1,6 +1,6 @@
 Essentially, the problem is that the default SSL cert for Postgres `/etc/ssl/certs/ssl-cert-snakeoil.pem` does not have enough information when copied over to the Guest machine running PowerBI which is trying to connect to Postgres, specifically, the  `Common Name` field.
 
-There are various posts out there about this e.g. https://community.powerbi.com/t5/Desktop/PostgreSQL-powerbi-desktop-connection-error/m-p/90689 
+There are various posts out there about this e.g. https://community.powerbi.com/t5/Desktop/PostgreSQL-powerbi-desktop-connection-error/m-p/90689. This one was good to point me at npgsql 3.1.8 specifically https://blogs.msdn.microsoft.com/chmitch/2018/06/04/complete-guide-to-setting-up-power-bi-connecting-to-postgres-w-refresh-enabled/
 
 1. create a new SSL certificate (roughly) following these guides: https://uit.stanford.edu/service/ssl/selfsigned + https://www.postgresql.org/docs/9.1/static/ssl-tcp.html be sure to set the `Common Name` field value to the IP address of the server which is hosting the Postgres instance you're connecting to from PowerBI. `-days 3650` will set the expiration in 10 years, default is 30 days if not specified.  
 

diff --git a/powerbi-postgres-cert-steps.md b/powerbi-postgres-cert-steps.md
@@ -8,6 +8,7 @@ Exact steps below:
 ```
 openssl req -days 3650 -new -text -out server.req
 openssl rsa -in privkey.pem -out server.key
+rm privkey.pem
 openssl req -days 3650 -x509 -in server.req -text -key server.key -out server.crt
 chmod og-rwx server.key
 chown root:ssl-cert /etc/ssl/private/server.key

diff --git a/powerbi-postgres-cert-steps.md b/powerbi-postgres-cert-steps.md
@@ -3,7 +3,8 @@ Essentially, the problem is that the default SSL cert for Postgres `/etc/ssl/cer
 There are various posts out there about this e.g. https://community.powerbi.com/t5/Desktop/PostgreSQL-powerbi-desktop-connection-error/m-p/90689 
 
 1. create a new SSL certificate (roughly) following these guides: https://uit.stanford.edu/service/ssl/selfsigned + https://www.postgresql.org/docs/9.1/static/ssl-tcp.html be sure to set the `Common Name` field value to the IP address of the server which is hosting the Postgres instance you're connecting to from PowerBI. `-days 3650` will set the expiration in 10 years, default is 30 days if not specified.  
-1a. exact steps below:
+
+Exact steps below:  
 ```
 openssl req -days 3650 -new -text -out server.req
 openssl rsa -in privkey.pem -out server.key

diff --git a/powerbi-postgres-cert-steps.md b/powerbi-postgres-cert-steps.md
@@ -2,7 +2,7 @@ Essentially, the problem is that the default SSL cert for Postgres `/etc/ssl/cer
 
 There are various posts out there about this e.g. https://community.powerbi.com/t5/Desktop/PostgreSQL-powerbi-desktop-connection-error/m-p/90689 
 
-1. create a new SSL certificate (roughly) following these guides: https://uit.stanford.edu/service/ssl/selfsigned + https://www.postgresql.org/docs/9.1/static/ssl-tcp.html be sure to set the `Common Name` field value to the IP address of the server which is hosting the Postgres instance you're connecting to from PowerBI.
+1. create a new SSL certificate (roughly) following these guides: https://uit.stanford.edu/service/ssl/selfsigned + https://www.postgresql.org/docs/9.1/static/ssl-tcp.html be sure to set the `Common Name` field value to the IP address of the server which is hosting the Postgres instance you're connecting to from PowerBI. `-days 3650` will set the expiration in 10 years, default is 30 days if not specified.  
 1a. exact steps below:
 ```
 openssl req -days 3650 -new -text -out server.req

diff --git a/powerbi-postgres-cert-steps.md b/powerbi-postgres-cert-steps.md
@@ -15,9 +15,9 @@ cp /etc/ssl/private/server.crt /etc/ssl/certs/
 ```
 skip to #5
 
-~2. Change the appropriate group of the private key `chown root:ssl-cert /etc/ssl/private/server.key`.~
-~3. Change the approprite permissions of the private key `chmod 640 /etc/ssl/private/server.key`.~
-~4. Copy the cert to where Postgres expects it: `ssl_cert_file = '/etc/ssl/certs/server.crt'`.~
+~2. Change the appropriate group of the private key `chown root:ssl-cert /etc/ssl/private/server.key`.~  
+~3. Change the approprite permissions of the private key `chmod 640 /etc/ssl/private/server.key`.~  
+~4. Copy the cert to where Postgres expects it: `ssl_cert_file = '/etc/ssl/certs/server.crt'`.~  
 
 5. Restart the server `service postgresql restart`.
 6. Copy the certificate over to the (Windows) machine running PowerBI.

diff --git a/powerbi-postgres-cert-steps.md b/powerbi-postgres-cert-steps.md
@@ -2,13 +2,26 @@ Essentially, the problem is that the default SSL cert for Postgres `/etc/ssl/cer
 
 There are various posts out there about this e.g. https://community.powerbi.com/t5/Desktop/PostgreSQL-powerbi-desktop-connection-error/m-p/90689 
 
-1. create a new SSL certificate (roughly) following these guides: https://uit.stanford.edu/service/ssl/selfsigned https://www.postgresql.org/docs/9.1/static/ssl-tcp.html be sure to set the `Common Name` field value to the IP address of the server which is hosting the Postgres instance you're connecting to from PowerBI.
-2. Change the appropriate group of the private key `chown root:ssl-cert /etc/ssl/private/server.key`.
-3. Change the approprite permissions of the private key `chmod 640 /etc/ssl/private/server.key`.
-4. Copy the cert to where Postgres expects it: `ssl_cert_file = '/etc/ssl/certs/server.crt'`.
-4. Restart the server `service postgresql restart`.
-5. Copy the certificate over to the (Windows) machine running PowerBI.
-6. Steps below to add certificate:
+1. create a new SSL certificate (roughly) following these guides: https://uit.stanford.edu/service/ssl/selfsigned + https://www.postgresql.org/docs/9.1/static/ssl-tcp.html be sure to set the `Common Name` field value to the IP address of the server which is hosting the Postgres instance you're connecting to from PowerBI.
+1a. exact steps below:
+```
+openssl req -days 3650 -new -text -out server.req
+openssl rsa -in privkey.pem -out server.key
+openssl req -days 3650 -x509 -in server.req -text -key server.key -out server.crt
+chmod og-rwx server.key
+chown root:ssl-cert /etc/ssl/private/server.key
+chmod 640 /etc/ssl/private/server.key
+cp /etc/ssl/private/server.crt /etc/ssl/certs/
+```
+skip to #5
+
+~2. Change the appropriate group of the private key `chown root:ssl-cert /etc/ssl/private/server.key`.~
+~3. Change the approprite permissions of the private key `chmod 640 /etc/ssl/private/server.key`.~
+~4. Copy the cert to where Postgres expects it: `ssl_cert_file = '/etc/ssl/certs/server.crt'`.~
+
+5. Restart the server `service postgresql restart`.
+6. Copy the certificate over to the (Windows) machine running PowerBI.
+7. Steps below to add certificate:
     * Administrators is the minimum group membership required to complete this procedure.
     * To add certificates to the Trusted Root Certification Authorities store for a local computer.
     * Click Start, click Start Search, type mmc, and then press ENTER.
@@ -21,4 +34,4 @@ There are various posts out there about this e.g. https://community.powerbi.com/
     * Right-click the Trusted Root Certification Authorities store.
     * Click Import to import the certificates and follow the steps in the Certificate Import Wizard.
 
-7. Retry your connection in PowerBI - hopefully it works!
+8. Retry your connection in PowerBI - hopefully it works!
diff --git a/powerbi-postgres-cert-steps.md b/powerbi-postgres-cert-steps.md
@@ -2,9 +2,10 @@ Essentially, the problem is that the default SSL cert for Postgres `/etc/ssl/cer
 
 There are various posts out there about this e.g. https://community.powerbi.com/t5/Desktop/PostgreSQL-powerbi-desktop-connection-error/m-p/90689 
 
-1. create a new SSL certificate following these steps: https://www.postgresql.org/docs/9.5/static/ssl-tcp.html#SSL-CERTIFICATE-CREATION be sure to set the `Common Name` field value to the IP address of the server which is hosting the Postgres instance you're connecting to from PowerBI.
+1. create a new SSL certificate (roughly) following these guides: https://uit.stanford.edu/service/ssl/selfsigned https://www.postgresql.org/docs/9.1/static/ssl-tcp.html be sure to set the `Common Name` field value to the IP address of the server which is hosting the Postgres instance you're connecting to from PowerBI.
 2. Change the appropriate group of the private key `chown root:ssl-cert /etc/ssl/private/server.key`.
 3. Change the approprite permissions of the private key `chmod 640 /etc/ssl/private/server.key`.
+4. Copy the cert to where Postgres expects it: `ssl_cert_file = '/etc/ssl/certs/server.crt'`.
 4. Restart the server `service postgresql restart`.
 5. Copy the certificate over to the (Windows) machine running PowerBI.
 6. Steps below to add certificate:

diff --git a/powerbi-postgres-cert-steps.md b/powerbi-postgres-cert-steps.md
@@ -1,4 +1,4 @@
-Essentially, the problem is that the default SSL cert for Postgres `/etc/ssl/certs/ssl-cert-snakeoil.pem` does not have enough information when copied over to the Guest machine, specifically, the  `Common Name` field.
+Essentially, the problem is that the default SSL cert for Postgres `/etc/ssl/certs/ssl-cert-snakeoil.pem` does not have enough information when copied over to the Guest machine running PowerBI which is trying to connect to Postgres, specifically, the  `Common Name` field.
 
 There are various posts out there about this e.g. https://community.powerbi.com/t5/Desktop/PostgreSQL-powerbi-desktop-connection-error/m-p/90689 
 

diff --git a/Untitled-1 → powerbi-postgres-cert-steps.md b/Untitled-1 → powerbi-postgres-cert-steps.md
@@ -1,5 +1,3 @@
-# PowerBI, PostgreSQL setup notes
-
 Essentially, the problem is that the default SSL cert for Postgres `/etc/ssl/certs/ssl-cert-snakeoil.pem` does not have enough information when copied over to the Guest machine, specifically, the  `Common Name` field.
 
 There are various posts out there about this e.g. https://community.powerbi.com/t5/Desktop/PostgreSQL-powerbi-desktop-connection-error/m-p/90689 

diff --git a/Untitled-1 b/Untitled-1
@@ -0,0 +1,25 @@
+# PowerBI, PostgreSQL setup notes
+
+Essentially, the problem is that the default SSL cert for Postgres `/etc/ssl/certs/ssl-cert-snakeoil.pem` does not have enough information when copied over to the Guest machine, specifically, the  `Common Name` field.
+
+There are various posts out there about this e.g. https://community.powerbi.com/t5/Desktop/PostgreSQL-powerbi-desktop-connection-error/m-p/90689 
+
+1. create a new SSL certificate following these steps: https://www.postgresql.org/docs/9.5/static/ssl-tcp.html#SSL-CERTIFICATE-CREATION be sure to set the `Common Name` field value to the IP address of the server which is hosting the Postgres instance you're connecting to from PowerBI.
+2. Change the appropriate group of the private key `chown root:ssl-cert /etc/ssl/private/server.key`.
+3. Change the approprite permissions of the private key `chmod 640 /etc/ssl/private/server.key`.
+4. Restart the server `service postgresql restart`.
+5. Copy the certificate over to the (Windows) machine running PowerBI.
+6. Steps below to add certificate:
+    * Administrators is the minimum group membership required to complete this procedure.
+    * To add certificates to the Trusted Root Certification Authorities store for a local computer.
+    * Click Start, click Start Search, type mmc, and then press ENTER.
+    * On the File menu, click Add/Remove Snap-in.
+    * Under Available snap-ins, click Certificates, and then click Add.
+    * Under This snap-in will always manage certificates for, click Computer account, and then click Next.
+    * Click Local computer, and click Finish.
+    * If you have no more snap-ins to add to the console, click OK.
+    * In the console tree, double-click Certificates.
+    * Right-click the Trusted Root Certification Authorities store.
+    * Click Import to import the certificates and follow the steps in the Certificate Import Wizard.
+
+7. Retry your connection in PowerBI - hopefully it works!