1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
|
{
"cells": [
{
"cell_type": "markdown",
"id": "a1bb15b1",
"metadata": {},
"source": [
"# Configuring TLS for Postgres Database Connections"
]
},
{
"cell_type": "markdown",
"id": "50b0ec16",
"metadata": {},
"source": [
"Assume we want to our connection to Postgres to be encrypted and the server to be authenticated.\n",
"\n",
"Further assume that we're running a Postgres from some GNU/Linux distro (these examples were prepared with Trisquel, but it should be fairly similar in all cases)."
]
},
{
"cell_type": "markdown",
"id": "d3954949",
"metadata": {},
"source": [
"We'll set up _Tansport Layer Security_ (TLS) using a self-signed certificate in a format called _X.509_. We can do so using the broadly available `openssl` command."
]
},
{
"cell_type": "markdown",
"id": "2fec37ee",
"metadata": {},
"source": [
"```sql\n",
"openssl req -new -x509 -days 365 -nodes -text -out server.crt \\\n",
" -keyout server.key -subj \"/CN=pgmaster.db_lectures\"\n",
"```"
]
},
{
"cell_type": "markdown",
"id": "798dc9be",
"metadata": {},
"source": [
"Here, the genereted certificate holds a _Common Name_ of \"pgmaster.db_lectures\". A domain name is usually used here. If we're just securing Postgres for our internal use, we could make somthing up here, as long as it follows the format of the Common Name field.\n",
"\n",
"The generated files `server.crt` and `server.key` hold the **certificate** (something that the client must process to verify the server) and the **private key** (which the server must have in order to be able to authenticate itself to clients), respectively."
]
},
{
"cell_type": "markdown",
"id": "3e68a073",
"metadata": {},
"source": [
"The key file should have permissions that disallow reading it by other system users.\n",
"\n",
"```shell\n",
"ls -lh server.*\n",
"```\n",
"\n",
"These files can be copied to an appropriate location. One suitable place can be the configuration directory of our database cluster, `/etc/postgresql/14/main/` in this case. It is important that the files (especially the key) are owned by the UNIX user that Postgres runs as.\n",
"\n",
"```shell\n",
"sudo cp server.* /etc/postgresql/14/main/\n",
"sudo chown postgres:postgres /etc/postgresql/14/main/server.*\n",
"ls -lh /etc/postgresql/14/main/\n",
"```"
]
},
{
"cell_type": "markdown",
"id": "7d1a15f3",
"metadata": {},
"source": [
"Before the server can start using TLS, it has to be configured to do so. In the `postgresql.conf` file (in this case also residing inside `/etc/postgresql/14/main/`) we need lines like the following.\n",
"\n",
"```\n",
"ssl = on\n",
"ssl_cert_file = '/etc/postgresql/14/main/server.crt'\n",
"ssl_key_file = '/etc/postgresql/14/main/server.key'\n",
"```\n",
"\n",
"Note that TLS-related settings are named after the precursor of TLS, a protocol named _SSL_.\n",
"\n",
"Once we point Postgres to the right key and cert files, we need to make it pick up the changes. One way to do this is with `sudo systemctl restart postgresql` (on systems with Systemd)."
]
},
{
"cell_type": "markdown",
"id": "86abff99",
"metadata": {},
"source": [
"If we now try connecting to the server, we might face a few issues. Assume the following command is used.\n",
"\n",
"```shell\n",
"psql --user=theodore --host 192.168.6.201 postgres\n",
"```\n",
"\n",
"If we get a \"Connection refused\" error, it might mean that Postgres is not listening on address 192.168.6.201. In fact, the default configuration under some systems makes it only listen on the loopback interface on address 127.0.0.1. To have Postgres listen for IPv4 connections on all interfaces, we can put the following line inside `postgresql.conf`.\n",
"\n",
"```\n",
"listen_addresses = '0.0.0.0'\n",
"```\n",
"\n",
"Of course, one can specify a list of addresses, like `'127.0.0.1,192.168.6.201'` instead of using the catch-all address. When we're done, we can once again restart the server."
]
},
{
"cell_type": "markdown",
"id": "baad8f1b",
"metadata": {},
"source": [
"Another problem might result in an error message like the following.\n",
"\n",
"```\n",
"no pg_hba.conf entry for host \"192.168.0.18\", user \"theodore\", database \"postgres\", SSL encryption\n",
"```\n",
"\n",
"Postgres allows users to connect (and chooses authentication method) based on configuration in `pg_hba.conf`, in our case also inside `/etc/postgresql/14/main/`. We can tell Postgres to allow connections as theodore from anywhere and to any database as long as the connection is encrypted (using TLS) and the user authenticates with a password.\n",
"\n",
"```\n",
"hostssl all theodore 0.0.0.0/0 scram-sha-256\n",
"```\n",
"\n",
"With a line like above added to `pg_hba.conf`, we can reload or restart Postgres."
]
},
{
"cell_type": "markdown",
"id": "216cfe9f",
"metadata": {},
"source": [
"The client might now be able to connect and report that TLS is in use.\n",
"\n",
"```\n",
"psql (14.19 (Ubuntu 14.19-0ubuntu0.22.04.1))\n",
"SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)\n",
"Type \"help\" for help.\n",
"\n",
"postgres=>\n",
"```\n",
"\n",
"This does not yet mean success. The actual **verification** of the certificates is likely to be disabled by default in the client. We can enable it in a few ways. One of them is the command line of `psql`. We can change it to the following.\n",
"\n",
"```shell\n",
"psql \"sslmode=verify-ca user=theodore host=192.168.6.201 dbname=postgres\"\n",
"```\n",
"\n",
"The default is `sslmode=prefer`, which causes TLS connections to be set up where possible, but does not verify the server certificate. `sslmode=verify-ca` does this.\n",
"\n",
"With `sslmode=verify-ca`, we should get an error like the following one.\n",
"\n",
"```\n",
"root certificate file \"/home/matematyka/.postgresql/root.crt\" does not exist\n",
"```\n",
"\n",
"As the error suggests, the Postgres command line client tool, `psql`, expects the issuer's certificate (or, in our case of a self-signed certificate, the very `server.crt`) to be present under `~/.postgresql/root.crt`. Once we place it there, the connection with `sslmode=verify-ca` should succeed.\n",
"\n",
"When using other tool than `psql`, we might need to provide the root certificate in some other way, specific to that tool."
]
},
{
"cell_type": "markdown",
"id": "67ee7619",
"metadata": {},
"source": [
"At the end, it is worth mentioning that it is also possible (although not covered here) to have the clients authenticate themselves to the server with similar X.509 certificates.\n",
"\n",
"Last but not least, it is possible (and recommended when running one's own, internal _Certificate Authority_ rather than using self-signed certs) to verify the Common Name in the server certificate. If we specify `sslmode=verify-full` in `psql`'s command line, the host name / address used to connect to the server must be the same as the value of the CN field in the cert. Or, as one of the _Subject Alternative Name_ (SAN) fields that can also be included in x509 certs."
]
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3 (ipykernel)",
"language": "/gnu/store/pv50hmdxs15c32laa1vn03wkfl023wwk-python-3.11.14/bin/python3",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.11.14"
}
},
"nbformat": 4,
"nbformat_minor": 5
}
|
