Thread: Certficates

Certficates

From
Shankar Bhaskaran
Date:
Hi All,

This is a very basic question . i have to import the server.crt on the client side java trustore to connect using jdbc to postgres server secured by ssl.
But when i try psql from same machine , it shows the connection as encrypted . How does psql import the server certificate?

 psql "postgresql://$POSTGRES_HOST:$PG_PORT/postgres" -U postgres
psql (9.6.18)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.


Regards,
Shankar

Re: Certficates

From
Martin Gainty
Date:
cant you use keytool ?
assume server certificate is called server.crt
assume truststore file is called server.truststore.jks

cd %JRE_HOME%/lib/security
keytool -import -alias %ALIAS% -file server.crt -keystore server.truststore.jks

m.

From: Shankar Bhaskaran <mailshankarb@gmail.com>
Sent: Monday, August 10, 2020 1:53 PM
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Certficates
 
Hi All,

This is a very basic question . i have to import the server.crt on the client side java trustore to connect using jdbc to postgres server secured by ssl.
But when i try psql from same machine , it shows the connection as encrypted . How does psql import the server certificate?

 psql "postgresql://$POSTGRES_HOST:$PG_PORT/postgres" -U postgres
psql (9.6.18)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.


Regards,
Shankar

Re: Certficates

From
"David G. Johnston"
Date:
On Mon, Aug 10, 2020 at 10:54 AM Shankar Bhaskaran <mailshankarb@gmail.com> wrote:
How does psql import the server certificate?

See:


Namely the "PGSSL*" prefixed environment variables.

It works by default because both the server and client are usually installed from the same source and the same default certificate files are provided to each.

David J.

Re: Certficates

From
"David G. Johnston"
Date:
The convention on these lists is to inline or bottom-post.

On Mon, Aug 10, 2020 at 11:11 AM Martin Gainty <mgainty@hotmail.com> wrote:
cant you use keytool ?

That wasn't the question, the OP already indicated they can do this successfully in JDBC.

David J.

Re: Certficates

From
Tom Lane
Date:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Mon, Aug 10, 2020 at 10:54 AM Shankar Bhaskaran <mailshankarb@gmail.com>
> wrote:
>> How does psql import the server certificate?

> It works by default because both the server and client are usually
> installed from the same source and the same default certificate files are
> provided to each.

Actually I suspect the answer is "it works because the default behavior
is to just encrypt the connection, not to try to verify the server
certificate".  If you want it to fail when it doesn't recognize the server
cert, you need sslmode=verify-ca or sslosslmode=verify-full in your
connection string.  See sslmode here:

https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS

            regards, tom lane



Re: Certficates

From
Shankar Bhaskaran
Date:
Hi All ,

Thank you for all the replies , I think even if psql does not verify the certificate , it still has to import it. I guess like David mentioned it might have default certificates in the client and server.

Regards,
Shankar

On Tue, Aug 11, 2020 at 1:45 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Mon, Aug 10, 2020 at 10:54 AM Shankar Bhaskaran <mailshankarb@gmail.com>
> wrote:
>> How does psql import the server certificate?

> It works by default because both the server and client are usually
> installed from the same source and the same default certificate files are
> provided to each.

Actually I suspect the answer is "it works because the default behavior
is to just encrypt the connection, not to try to verify the server
certificate".  If you want it to fail when it doesn't recognize the server
cert, you need sslmode=verify-ca or sslosslmode=verify-full in your
connection string.  See sslmode here:

https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS

                        regards, tom lane