1 <?xml version="1.0" encoding="UTF-8" standalone="no"?>
2 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>32.19. SSL Support</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="libpq-ldap.html" title="32.18. LDAP Lookup of Connection Parameters" /><link rel="next" href="libpq-oauth.html" title="32.20. OAuth Support" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">32.19. SSL Support</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="libpq-ldap.html" title="32.18. LDAP Lookup of Connection Parameters">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="libpq.html" title="Chapter 32. libpq — C Library">Up</a></td><th width="60%" align="center">Chapter 32. <span class="application">libpq</span> — C Library</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="libpq-oauth.html" title="32.20. OAuth Support">Next</a></td></tr></table><hr /></div><div class="sect1" id="LIBPQ-SSL"><div class="titlepage"><div><div><h2 class="title" style="clear: both">32.19. SSL Support <a href="#LIBPQ-SSL" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="libpq-ssl.html#LIBQ-SSL-CERTIFICATES">32.19.1. Client Verification of Server Certificates</a></span></dt><dt><span class="sect2"><a href="libpq-ssl.html#LIBPQ-SSL-CLIENTCERT">32.19.2. Client Certificates</a></span></dt><dt><span class="sect2"><a href="libpq-ssl.html#LIBPQ-SSL-PROTECTION">32.19.3. Protection Provided in Different Modes</a></span></dt><dt><span class="sect2"><a href="libpq-ssl.html#LIBPQ-SSL-FILEUSAGE">32.19.4. SSL Client File Usage</a></span></dt><dt><span class="sect2"><a href="libpq-ssl.html#LIBPQ-SSL-INITIALIZE">32.19.5. SSL Library Initialization</a></span></dt></dl></div><a id="id-1.7.3.26.2" class="indexterm"></a><p>
3 <span class="productname">PostgreSQL</span> has native support for using <acronym class="acronym">SSL</acronym>
4 connections to encrypt client/server communications using
5 <acronym class="acronym">TLS</acronym> protocols for increased security.
6 See <a class="xref" href="ssl-tcp.html" title="18.9. Secure TCP/IP Connections with SSL">Section 18.9</a> for details about the server-side
7 <acronym class="acronym">SSL</acronym> functionality.
9 <span class="application">libpq</span> reads the system-wide
10 <span class="productname">OpenSSL</span> configuration file. By default, this
11 file is named <code class="filename">openssl.cnf</code> and is located in the
12 directory reported by <code class="literal">openssl version -d</code>. This default
13 can be overridden by setting environment variable
14 <code class="envar">OPENSSL_CONF</code> to the name of the desired configuration
16 </p><div class="sect2" id="LIBQ-SSL-CERTIFICATES"><div class="titlepage"><div><div><h3 class="title">32.19.1. Client Verification of Server Certificates <a href="#LIBQ-SSL-CERTIFICATES" class="id_link">#</a></h3></div></div></div><p>
17 By default, <span class="productname">PostgreSQL</span> will not perform any verification of
18 the server certificate. This means that it is possible to spoof the server
19 identity (for example by modifying a DNS record or by taking over the server
20 IP address) without the client knowing. In order to prevent spoofing,
21 the client must be able to verify the server's identity via a chain of
22 trust. A chain of trust is established by placing a root (self-signed)
23 certificate authority (<acronym class="acronym">CA</acronym>) certificate on one
24 computer and a leaf certificate <span class="emphasis"><em>signed</em></span> by the
25 root certificate on another computer. It is also possible to use an
26 <span class="quote">“<span class="quote">intermediate</span>”</span> certificate which is signed by the root
27 certificate and signs leaf certificates.
29 To allow the client to verify the identity of the server, place a root
30 certificate on the client and a leaf certificate signed by the root
31 certificate on the server. To allow the server to verify the identity
32 of the client, place a root certificate on the server and a leaf
33 certificate signed by the root certificate on the client. One or more
34 intermediate certificates (usually stored with the leaf certificate)
35 can also be used to link the leaf certificate to the root certificate.
37 Once a chain of trust has been established, there are two ways for
38 the client to validate the leaf certificate sent by the server.
39 If the parameter <code class="literal">sslmode</code> is set to <code class="literal">verify-ca</code>,
40 libpq will verify that the server is trustworthy by checking the
41 certificate chain up to the root certificate stored on the client.
42 If <code class="literal">sslmode</code> is set to <code class="literal">verify-full</code>,
43 libpq will <span class="emphasis"><em>also</em></span> verify that the server host
44 name matches the name stored in the server certificate. The
45 SSL connection will fail if the server certificate cannot be
46 verified. <code class="literal">verify-full</code> is recommended in most
47 security-sensitive environments.
49 In <code class="literal">verify-full</code> mode, the host name is matched against the
50 certificate's Subject Alternative Name attribute(s) (SAN), or against the
51 Common Name attribute if no SAN of type <code class="literal">dNSName</code> is
52 present. If the certificate's name attribute starts with an asterisk
53 (<code class="literal">*</code>), the asterisk will be treated as
54 a wildcard, which will match all characters <span class="emphasis"><em>except</em></span> a dot
55 (<code class="literal">.</code>). This means the certificate will not match subdomains.
56 If the connection is made using an IP address instead of a host name, the
57 IP address will be matched (without doing any DNS lookups) against SANs of
58 type <code class="literal">iPAddress</code> or <code class="literal">dNSName</code>. If no
59 <code class="literal">iPAddress</code> SAN is present and no
60 matching <code class="literal">dNSName</code> SAN is present, the host IP address is
61 matched against the Common Name attribute.
62 </p><div class="note"><h3 class="title">Note</h3><p>
63 For backward compatibility with earlier versions of PostgreSQL, the host
64 IP address is verified in a manner different
65 from <a class="ulink" href="https://datatracker.ietf.org/doc/html/rfc6125" target="_top">RFC 6125</a>.
66 The host IP address is always matched against <code class="literal">dNSName</code>
67 SANs as well as <code class="literal">iPAddress</code> SANs, and can be matched
68 against the Common Name attribute if no relevant SANs exist.
70 To allow server certificate verification, one or more root certificates
71 must be placed in the file <code class="filename">~/.postgresql/root.crt</code>
72 in the user's home directory. (On Microsoft Windows the file is named
73 <code class="filename">%APPDATA%\postgresql\root.crt</code>.) Intermediate
74 certificates should also be added to the file if they are needed to link
75 the certificate chain sent by the server to the root certificates
78 Certificate Revocation List (CRL) entries are also checked
79 if the file <code class="filename">~/.postgresql/root.crl</code> exists
80 (<code class="filename">%APPDATA%\postgresql\root.crl</code> on Microsoft
83 The location of the root certificate file and the CRL can be changed by
85 the connection parameters <code class="literal">sslrootcert</code> and <code class="literal">sslcrl</code>
86 or the environment variables <code class="envar">PGSSLROOTCERT</code> and <code class="envar">PGSSLCRL</code>.
87 <code class="literal">sslcrldir</code> or the environment variable <code class="envar">PGSSLCRLDIR</code>
88 can also be used to specify a directory containing CRL files.
89 </p><div class="note"><h3 class="title">Note</h3><p>
90 For backwards compatibility with earlier versions of PostgreSQL, if a
91 root CA file exists, the behavior of
92 <code class="literal">sslmode=require</code> will be the same
93 as that of <code class="literal">verify-ca</code>, meaning the server certificate
94 is validated against the CA. Relying on this behavior is discouraged,
95 and applications that need certificate validation should always use
96 <code class="literal">verify-ca</code> or <code class="literal">verify-full</code>.
97 </p></div></div><div class="sect2" id="LIBPQ-SSL-CLIENTCERT"><div class="titlepage"><div><div><h3 class="title">32.19.2. Client Certificates <a href="#LIBPQ-SSL-CLIENTCERT" class="id_link">#</a></h3></div></div></div><p>
98 If the server attempts to verify the identity of the
99 client by requesting the client's leaf certificate,
100 <span class="application">libpq</span> will send the certificate(s) stored in
101 file <code class="filename">~/.postgresql/postgresql.crt</code> in the user's home
102 directory. The certificates must chain to the root certificate trusted
103 by the server. A matching
104 private key file <code class="filename">~/.postgresql/postgresql.key</code> must also
106 On Microsoft Windows these files are named
107 <code class="filename">%APPDATA%\postgresql\postgresql.crt</code> and
108 <code class="filename">%APPDATA%\postgresql\postgresql.key</code>.
109 The location of the certificate and key files can be overridden by the
110 connection parameters <code class="literal">sslcert</code>
111 and <code class="literal">sslkey</code>, or by the
112 environment variables <code class="envar">PGSSLCERT</code> and <code class="envar">PGSSLKEY</code>.
114 On Unix systems, the permissions on the private key file must disallow
115 any access to world or group; achieve this by a command such as
116 <code class="command">chmod 0600 ~/.postgresql/postgresql.key</code>.
117 Alternatively, the file can be owned by root and have group read access
118 (that is, <code class="literal">0640</code> permissions). That setup is intended
119 for installations where certificate and key files are managed by the
120 operating system. The user of <span class="application">libpq</span> should
121 then be made a member of the group that has access to those certificate
122 and key files. (On Microsoft Windows, there is no file permissions
123 check, since the <code class="filename">%APPDATA%\postgresql</code> directory is
126 The first certificate in <code class="filename">postgresql.crt</code> must be the
127 client's certificate because it must match the client's private key.
128 <span class="quote">“<span class="quote">Intermediate</span>”</span> certificates can be optionally appended
129 to the file — doing so avoids requiring storage of intermediate
130 certificates on the server (<a class="xref" href="runtime-config-connection.html#GUC-SSL-CA-FILE">ssl_ca_file</a>).
132 The certificate and key may be in PEM or ASN.1 DER format.
135 stored in cleartext or encrypted with a passphrase using any algorithm
136 supported by <span class="productname">OpenSSL</span>, like AES-128. If the key
137 is stored encrypted, then the passphrase may be provided in the
138 <a class="xref" href="libpq-connect.html#LIBPQ-CONNECT-SSLPASSWORD">sslpassword</a> connection option. If an
139 encrypted key is supplied and the <code class="literal">sslpassword</code> option
140 is absent or blank, a password will be prompted for interactively by
141 <span class="productname">OpenSSL</span> with a
142 <code class="literal">Enter PEM pass phrase:</code> prompt if a TTY is available.
143 Applications can override the client certificate prompt and the handling
144 of the <code class="literal">sslpassword</code> parameter by supplying their own
145 key password callback; see
146 <a class="xref" href="libpq-connect.html#LIBPQ-PQSETSSLKEYPASSHOOK-OPENSSL"><code class="function">PQsetSSLKeyPassHook_OpenSSL</code></a>.
148 For instructions on creating certificates, see <a class="xref" href="ssl-tcp.html#SSL-CERTIFICATE-CREATION" title="18.9.5. Creating Certificates">Section 18.9.5</a>.
149 </p></div><div class="sect2" id="LIBPQ-SSL-PROTECTION"><div class="titlepage"><div><div><h3 class="title">32.19.3. Protection Provided in Different Modes <a href="#LIBPQ-SSL-PROTECTION" class="id_link">#</a></h3></div></div></div><p>
150 The different values for the <code class="literal">sslmode</code> parameter provide different
151 levels of protection. SSL can provide
152 protection against three types of attacks:
154 </p><div class="variablelist"><dl class="variablelist"><dt><span class="term">Eavesdropping</span></dt><dd><p>If a third party can examine the network traffic between the
155 client and the server, it can read both connection information (including
156 the user name and password) and the data that is passed. <acronym class="acronym">SSL</acronym>
157 uses encryption to prevent this.
158 </p></dd><dt><span class="term">Man-in-the-middle (<acronym class="acronym">MITM</acronym>)</span></dt><dd><p>If a third party can modify the data while passing between the
159 client and server, it can pretend to be the server and therefore see and
160 modify data <span class="emphasis"><em>even if it is encrypted</em></span>. The third party can then
161 forward the connection information and data to the original server,
162 making it impossible to detect this attack. Common vectors to do this
163 include DNS poisoning and address hijacking, whereby the client is directed
164 to a different server than intended. There are also several other
165 attack methods that can accomplish this. <acronym class="acronym">SSL</acronym> uses certificate
166 verification to prevent this, by authenticating the server to the client.
167 </p></dd><dt><span class="term">Impersonation</span></dt><dd><p>If a third party can pretend to be an authorized client, it can
168 simply access data it should not have access to. Typically this can
169 happen through insecure password management. <acronym class="acronym">SSL</acronym> uses
170 client certificates to prevent this, by making sure that only holders
171 of valid certificates can access the server.
172 </p></dd></dl></div><p>
174 For a connection to be known SSL-secured, SSL usage must be configured
175 on <span class="emphasis"><em>both the client and the server</em></span> before the connection
176 is made. If it is only configured on the server, the client may end up
177 sending sensitive information (e.g., passwords) before
178 it knows that the server requires high security. In libpq, secure
179 connections can be ensured
180 by setting the <code class="literal">sslmode</code> parameter to <code class="literal">verify-full</code> or
181 <code class="literal">verify-ca</code>, and providing the system with a root certificate to
182 verify against. This is analogous to using an <code class="literal">https</code>
183 <acronym class="acronym">URL</acronym> for encrypted web browsing.
185 Once the server has been authenticated, the client can pass sensitive data.
186 This means that up until this point, the client does not need to know if
187 certificates will be used for authentication, making it safe to specify that
188 only in the server configuration.
190 All <acronym class="acronym">SSL</acronym> options carry overhead in the form of encryption and
191 key-exchange, so there is a trade-off that has to be made between performance
192 and security. <a class="xref" href="libpq-ssl.html#LIBPQ-SSL-SSLMODE-STATEMENTS" title="Table 32.1. SSL Mode Descriptions">Table 32.1</a>
193 illustrates the risks the different <code class="literal">sslmode</code> values
194 protect against, and what statement they make about security and overhead.
195 </p><div class="table" id="LIBPQ-SSL-SSLMODE-STATEMENTS"><p class="title"><strong>Table 32.1. SSL Mode Descriptions</strong></p><div class="table-contents"><table class="table" summary="SSL Mode Descriptions" border="1"><colgroup><col class="col1" /><col class="col2" /><col class="col3" /><col class="col4" /></colgroup><thead><tr><th><code class="literal">sslmode</code></th><th>Eavesdropping protection</th><th><acronym class="acronym">MITM</acronym> protection</th><th>Statement</th></tr></thead><tbody><tr><td><code class="literal">disable</code></td><td>No</td><td>No</td><td>I don't care about security, and I don't want to pay the overhead
197 </td></tr><tr><td><code class="literal">allow</code></td><td>Maybe</td><td>No</td><td>I don't care about security, but I will pay the overhead of
198 encryption if the server insists on it.
199 </td></tr><tr><td><code class="literal">prefer</code></td><td>Maybe</td><td>No</td><td>I don't care about encryption, but I wish to pay the overhead of
200 encryption if the server supports it.
201 </td></tr><tr><td><code class="literal">require</code></td><td>Yes</td><td>No</td><td>I want my data to be encrypted, and I accept the overhead. I trust
202 that the network will make sure I always connect to the server I want.
203 </td></tr><tr><td><code class="literal">verify-ca</code></td><td>Yes</td><td>Depends on CA policy</td><td>I want my data encrypted, and I accept the overhead. I want to be
204 sure that I connect to a server that I trust.
205 </td></tr><tr><td><code class="literal">verify-full</code></td><td>Yes</td><td>Yes</td><td>I want my data encrypted, and I accept the overhead. I want to be
206 sure that I connect to a server I trust, and that it's the one I
208 </td></tr></tbody></table></div></div><br class="table-break" /><p>
209 The difference between <code class="literal">verify-ca</code> and <code class="literal">verify-full</code>
210 depends on the policy of the root <acronym class="acronym">CA</acronym>. If a public
211 <acronym class="acronym">CA</acronym> is used, <code class="literal">verify-ca</code> allows connections to a server
212 that <span class="emphasis"><em>somebody else</em></span> may have registered with the <acronym class="acronym">CA</acronym>.
213 In this case, <code class="literal">verify-full</code> should always be used. If
214 a local <acronym class="acronym">CA</acronym> is used, or even a self-signed certificate, using
215 <code class="literal">verify-ca</code> often provides enough protection.
217 The default value for <code class="literal">sslmode</code> is <code class="literal">prefer</code>. As is shown
218 in the table, this makes no sense from a security point of view, and it only
219 promises performance overhead if possible. It is only provided as the default
220 for backward compatibility, and is not recommended in secure deployments.
221 </p></div><div class="sect2" id="LIBPQ-SSL-FILEUSAGE"><div class="titlepage"><div><div><h3 class="title">32.19.4. SSL Client File Usage <a href="#LIBPQ-SSL-FILEUSAGE" class="id_link">#</a></h3></div></div></div><p>
222 <a class="xref" href="libpq-ssl.html#LIBPQ-SSL-FILE-USAGE" title="Table 32.2. Libpq/Client SSL File Usage">Table 32.2</a> summarizes the files that are
223 relevant to the SSL setup on the client.
224 </p><div class="table" id="LIBPQ-SSL-FILE-USAGE"><p class="title"><strong>Table 32.2. Libpq/Client SSL File Usage</strong></p><div class="table-contents"><table class="table" summary="Libpq/Client SSL File Usage" border="1"><colgroup><col /><col /><col /></colgroup><thead><tr><th>File</th><th>Contents</th><th>Effect</th></tr></thead><tbody><tr><td><code class="filename">~/.postgresql/postgresql.crt</code></td><td>client certificate</td><td>sent to server</td></tr><tr><td><code class="filename">~/.postgresql/postgresql.key</code></td><td>client private key</td><td>proves client certificate sent by owner; does not indicate
225 certificate owner is trustworthy</td></tr><tr><td><code class="filename">~/.postgresql/root.crt</code></td><td>trusted certificate authorities</td><td>checks that server certificate is signed by a trusted certificate
226 authority</td></tr><tr><td><code class="filename">~/.postgresql/root.crl</code></td><td>certificates revoked by certificate authorities</td><td>server certificate must not be on this list</td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect2" id="LIBPQ-SSL-INITIALIZE"><div class="titlepage"><div><div><h3 class="title">32.19.5. SSL Library Initialization <a href="#LIBPQ-SSL-INITIALIZE" class="id_link">#</a></h3></div></div></div><p>
227 Applications which need to be compatible with older versions of
228 <span class="productname">PostgreSQL</span>, using <span class="productname">OpenSSL</span>
229 version 1.0.2 or older, need to initialize the SSL library before using it.
230 Applications which initialize <code class="literal">libssl</code> and/or
231 <code class="literal">libcrypto</code> libraries should call
232 <a class="xref" href="libpq-ssl.html#LIBPQ-PQINITOPENSSL"><code class="function">PQinitOpenSSL</code></a> to tell <span class="application">libpq</span>
233 that the <code class="literal">libssl</code> and/or <code class="literal">libcrypto</code> libraries
234 have been initialized by your application, so that
235 <span class="application">libpq</span> will not also initialize those libraries.
236 However, this is unnecessary when using <span class="productname">OpenSSL</span>
237 version 1.1.0 or later, as duplicate initializations are no longer problematic.
239 Refer to the documentation for the version of <span class="productname">PostgreSQL</span>
240 that you are targeting for details on their use.
242 </p><div class="variablelist"><dl class="variablelist"><dt id="LIBPQ-PQINITOPENSSL"><span class="term"><code class="function">PQinitOpenSSL</code><a id="id-1.7.3.26.9.4.1.1.1.2" class="indexterm"></a></span> <a href="#LIBPQ-PQINITOPENSSL" class="id_link">#</a></dt><dd><p>
243 Allows applications to select which security libraries to initialize.
244 </p><pre class="synopsis">
245 void PQinitOpenSSL(int do_ssl, int do_crypto);
248 This function is deprecated and only present for backwards compatibility,
250 </p></dd><dt id="LIBPQ-PQINITSSL"><span class="term"><code class="function">PQinitSSL</code><a id="id-1.7.3.26.9.4.1.2.1.2" class="indexterm"></a></span> <a href="#LIBPQ-PQINITSSL" class="id_link">#</a></dt><dd><p>
251 Allows applications to select which security libraries to initialize.
252 </p><pre class="synopsis">
253 void PQinitSSL(int do_ssl);
256 This function is equivalent to
257 <code class="literal">PQinitOpenSSL(do_ssl, do_ssl)</code>.
258 This function is deprecated and only present for backwards compatibility,
261 <a class="xref" href="libpq-ssl.html#LIBPQ-PQINITSSL"><code class="function">PQinitSSL</code></a> and <a class="xref" href="libpq-ssl.html#LIBPQ-PQINITOPENSSL"><code class="function">PQinitOpenSSL</code></a>
262 are maintained for backwards compatibility, but are no longer required
263 since <span class="productname">PostgreSQL</span> 18.
264 <a class="xref" href="libpq-ssl.html#LIBPQ-PQINITSSL"><code class="function">PQinitSSL</code></a> has been present since
265 <span class="productname">PostgreSQL</span> 8.0, while <a class="xref" href="libpq-ssl.html#LIBPQ-PQINITOPENSSL"><code class="function">PQinitOpenSSL</code></a>
266 was added in <span class="productname">PostgreSQL</span> 8.4, so <a class="xref" href="libpq-ssl.html#LIBPQ-PQINITSSL"><code class="function">PQinitSSL</code></a>
267 might be preferable for applications that need to work with older
268 versions of <span class="application">libpq</span>.
269 </p></dd></dl></div><p>
270 </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="libpq-ldap.html" title="32.18. LDAP Lookup of Connection Parameters">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="libpq.html" title="Chapter 32. libpq — C Library">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="libpq-oauth.html" title="32.20. OAuth Support">Next</a></td></tr><tr><td width="40%" align="left" valign="top">32.18. LDAP Lookup of Connection Parameters </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 32.20. OAuth Support</td></tr></table></div></body></html>