Verified AZ pg require_secure_transport = "on"

Without any ssl and no SET PGSSLMODE=require, following command in Windows' cmd succeeded:

pg_restore  --format=custom -d "port=5432 host=mypg.postgres.database.azure.com user=myuser dbname=mydb" my.dump

Checking AZ's Log Analytics workspace shows

connection authorized: user=myuser database=mydb application_name=pg_restore SSL enabled (protocol=TLSv1.3, cipher=TLS_AES_256_GCM_SHA384, bits=256, compression=off)

So how does this SSL/Secure established or it's enabled but operation isn't on SSL channel?

CodePudding user response：

pg_restore is based on libpq, and follows its conventions in this area. An unset sslmode has the default behavior of 'prefer'. It first attempts to set up an SSL connection, then tries without SSL only if that first attempt fails. So 'prefer' is identical to 'require' provided the first attempt does succeed. If you want to prove that your servers setting require_secure_transport = "on" is doing something, you should try setting sslmode=disable on the client. Then you should see a failure driven by that mismatch between client and server.

In your comment you mention sslcert and sslkey. Those are used only for client certificate authentication. This is optional, unusual, and as far as I know is not even supported by Azure's hosted PostgreSQL. SSL is generally done only with a server certificate, not with a client certificate (for example, pretty much all of the web over https uses server certs only, it isn't just PostgreSQL).

By what is unusual about libpq (in the world of SSL) is that by default, the server's cert does not need to be verified. By default, the cert is just used as a way to negotiate Diffie-Hellman-like key exchange that protects you from eavesdropping but not from impersonation. If you want to verify that the server you connect to is the right one, you would need to set sslmode=verify-full, as well as configure sslrootcert. This is all controlled by the client. While the server can insist that the client use SSL, it has no way to insist that the client actually verify the server's certificate.