Bug when connecting in SSL mode #158

Closed
liudangyi opened this Issue Jan 13, 2016 · 10 comments

Comments

Projects
None yet
2 participants
@liudangyi

Postico seems to interpose the the SSL certificate chain. I use the same certificates for both nginx and postgresql. But the certificate chain is different, and Postico's is not trusted.

screen shot 2016-01-14 at 1 32 01 am
screen shot 2016-01-14 at 1 32 17 am

@liudangyi

This comment has been minimized.

Show comment
Hide comment
@liudangyi

liudangyi Jan 13, 2016

Postico doesn't ask Keychain for System Root CAs. I'm afraid all certificates will have trouble being verified.

Postico doesn't ask Keychain for System Root CAs. I'm afraid all certificates will have trouble being verified.

@jakob

This comment has been minimized.

Show comment
Hide comment
@jakob

jakob Jan 13, 2016

Owner

Postico uses the OS X Security API to verify certificates. "WoSign" is not a System Root CA. Therefore you need to include the StartCom cert for WoSign with your certificate. It could be that Safari has a copy of that certificate cached somewhere, and therefore doesn't need it, but Postico does.

Try appending the StartCom cert to your server cert. See section 17.9. Secure TCP/IP Connections with SSL in the PostgreSQL manual:

In some cases, the server certificate might be signed by an "intermediate" certificate authority, rather than one that is directly trusted by clients. To use such a certificate, append the certificate of the signing authority to the server.crt file, then its parent authority's certificate, and so on up to a certificate authority, "root" or "intermediate", that is trusted by clients, i.e. signed by a certificate in the clients' root.crt files.

Usually you should get a "bundle.crt" file or similar from your issuer, containing all the additional certs you need. Try appending that file to "server.crt", then restart your PostgreSQL server, and it should work.

Owner

jakob commented Jan 13, 2016

Postico uses the OS X Security API to verify certificates. "WoSign" is not a System Root CA. Therefore you need to include the StartCom cert for WoSign with your certificate. It could be that Safari has a copy of that certificate cached somewhere, and therefore doesn't need it, but Postico does.

Try appending the StartCom cert to your server cert. See section 17.9. Secure TCP/IP Connections with SSL in the PostgreSQL manual:

In some cases, the server certificate might be signed by an "intermediate" certificate authority, rather than one that is directly trusted by clients. To use such a certificate, append the certificate of the signing authority to the server.crt file, then its parent authority's certificate, and so on up to a certificate authority, "root" or "intermediate", that is trusted by clients, i.e. signed by a certificate in the clients' root.crt files.

Usually you should get a "bundle.crt" file or similar from your issuer, containing all the additional certs you need. Try appending that file to "server.crt", then restart your PostgreSQL server, and it should work.

@liudangyi

This comment has been minimized.

Show comment
Hide comment
@liudangyi

liudangyi Jan 13, 2016

Actually WoSign is not a root CA, it's an intermediate certificate authority. But Postico recognized it as the root CA.

I've also tried Psequel with SSL enabled. It connected without any errors.

The server is available to the Internet with host name "aws.leedy.me". You can try with different tools (with SSL forced).

Actually WoSign is not a root CA, it's an intermediate certificate authority. But Postico recognized it as the root CA.

I've also tried Psequel with SSL enabled. It connected without any errors.

The server is available to the Internet with host name "aws.leedy.me". You can try with different tools (with SSL forced).

@jakob

This comment has been minimized.

Show comment
Hide comment
@jakob

jakob Jan 15, 2016

Owner

This is not a bug in Postico. You need to configure your server to send all the certificates, not just the server certificate. Please read my comment again, and then read the documentation I linked too.

(Psequel probably just doesn't verify the cert.)

Owner

jakob commented Jan 15, 2016

This is not a bug in Postico. You need to configure your server to send all the certificates, not just the server certificate. Please read my comment again, and then read the documentation I linked too.

(Psequel probably just doesn't verify the cert.)

@jakob jakob closed this Jan 15, 2016

@liudangyi

This comment has been minimized.

Show comment
Hide comment
@liudangyi

liudangyi Jan 15, 2016

I have two servers. Server A's certificate is self-signed. Server B's is signed correctly.

Server A with PSequel

screen shot 2016-01-15 at 3 29 06 pm

Server B with PSequel

screen shot 2016-01-15 at 3 29 35 pm

Server A with Postico

screen shot 2016-01-15 at 3 31 03 pm

Server B with Postico

screen shot 2016-01-15 at 3 30 48 pm

Using psql

$ psql -h aws.leedy.me -a "sslmode=verify-full"
Password:
$ psql -h do.leedy.me -a "sslmode=verify-full"
psql: SSL error: certificate verify failed

I have two servers. Server A's certificate is self-signed. Server B's is signed correctly.

Server A with PSequel

screen shot 2016-01-15 at 3 29 06 pm

Server B with PSequel

screen shot 2016-01-15 at 3 29 35 pm

Server A with Postico

screen shot 2016-01-15 at 3 31 03 pm

Server B with Postico

screen shot 2016-01-15 at 3 30 48 pm

Using psql

$ psql -h aws.leedy.me -a "sslmode=verify-full"
Password:
$ psql -h do.leedy.me -a "sslmode=verify-full"
psql: SSL error: certificate verify failed
@jakob

This comment has been minimized.

Show comment
Hide comment
@jakob

jakob Jan 15, 2016

Owner

psql checks the certificate against root certificates stored in ~/.postgresql/root.crt. Postico does not. It checks them against System Root certificates stored in the Keychain.

You have two ways of fixing this problem:

  • Add the root cert from ~/.postgresql/root.crt to your keychain
  • Alternatively configure the server to include the WoSign certificate signed by StartCom
Owner

jakob commented Jan 15, 2016

psql checks the certificate against root certificates stored in ~/.postgresql/root.crt. Postico does not. It checks them against System Root certificates stored in the Keychain.

You have two ways of fixing this problem:

  • Add the root cert from ~/.postgresql/root.crt to your keychain
  • Alternatively configure the server to include the WoSign certificate signed by StartCom
@liudangyi

This comment has been minimized.

Show comment
Hide comment
@liudangyi

liudangyi Jan 15, 2016

The problem still exists after I import ~/.postgresql/root.crt. In fact, root.crt is StartCom CA which is already included in System Root certificates.

The problem still exists after I import ~/.postgresql/root.crt. In fact, root.crt is StartCom CA which is already included in System Root certificates.

@jakob

This comment has been minimized.

Show comment
Hide comment
@jakob

jakob Jan 15, 2016

Owner

OK, sorry, you are right. Let me check what is happening.

Owner

jakob commented Jan 15, 2016

OK, sorry, you are right. Let me check what is happening.

@jakob jakob reopened this Jan 15, 2016

@jakob

This comment has been minimized.

Show comment
Hide comment
@jakob

jakob Jan 15, 2016

Owner

I've now discovered the source of the bug and fixed it. The bug caused Postico to ignore additional certificates sent by the server, just like you said. This means that certificates from intermediate CAs like WoSign were rejected.

Thank you for telling me about this bug. I am really sorry for not believing you at first.

Here is a build of Postico that should correctly check the certificate:
https://eggerapps-downloads.s3.amazonaws.com/postico-1298.zip

Owner

jakob commented Jan 15, 2016

I've now discovered the source of the bug and fixed it. The bug caused Postico to ignore additional certificates sent by the server, just like you said. This means that certificates from intermediate CAs like WoSign were rejected.

Thank you for telling me about this bug. I am really sorry for not believing you at first.

Here is a build of Postico that should correctly check the certificate:
https://eggerapps-downloads.s3.amazonaws.com/postico-1298.zip

@liudangyi

This comment has been minimized.

Show comment
Hide comment
@liudangyi

liudangyi Jan 15, 2016

It works! Thank you.

It works! Thank you.

@liudangyi liudangyi closed this Jan 15, 2016

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment