{
 "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
}