In an ideal world Postgresql ports would not be open to the internet however it is never a bad idea to add a layer of security. If you do have public facing postgresql ports then it is essential.
For this tutorial I have written a couple of script to help with creating a Certificate authority so that you can better manage your ssl certificates.
As with all the tutorials on this site it assumes that you have your postgresql database in /pgsql rather than the standard location. You will be creating the certificate authority in /pgsql/CA.
Theoretically this method can be used to create a CA for any purpose you like, albeit you may wish to amend the paths.
Setting up the certificate authority
The initial setup has been taken from these instructions: https://help.ubuntu.com/community/OpenSSL
If you create your certificate authority in “/pgsql/postgresCA” then you should not have to modify these files however if you wish to locate it elsewhere, for branding reasons etc. then modification of the process will be required. These instructions are assuming that you wish to create a certificate authority called “inplicareCA” in the afore mentioned path.
First and foremost create some directories:
cd /pgsql && mkdir -p /pgsql/CA/signedcerts && mkdir /pgsql/CA/private && cd /pgsql/CA
The previous command will create a folder in /pgsql called “CA” containing 2 subfolders “signedcerts” and “private“.
Within this initial working environment, the significance of the subdirectories, and their contents is as follows:
- /pgsql/CA : contains CA certificate, certificates database, generated certificates, keys, and requests
- /pgsql/CA/signedcerts : contains copies of each signed certificate
- /pgsql/CA/private : contains the private key
In order to ensure that the permissions are mostly automatically set, for the rest of this process you need to su to the postgres user.
su postgres
Next, create an initial certificate database in the /pgsql/CA subdirectory with the following command at a terminal prompt:
echo '01' > serial && touch index.txt
The certificate database is literally a handful of text files, the serial file initially contains 01 which the CA uses as the serial number for the first signed certificate. Once it has singed that certificate it increments the value, but once you have done the preliminary setup the rest is done automatically for you.
Now create an initial postgresCA.cnf (you can name it anything you like, just as long as you remember) file suitable for the creation of CA certificates. Using your favorite editor, edit the file /pgsql/CA/postgresCA.cnf
This file content is in essence a configuration file for your new certificate authority that contains information about the directories and the files that it will rely on in order to operate. If you are giving your certificate authority a different name then you are likely going to have to change directives in the “[ local_ca ]” stanza and in nearly all instances you are going to need to change some directives in the “[ req ]” and “[ root_ca_distinguished_name ]” stanza’s. The directives to change are all fairly obvious.
# My sample caconfig.cnf file. # # Default configuration to use when one is not provided on the command line. # [ ca ] default_ca = local_ca # # # Default location of directories and files needed to generate certificates. # [ local_ca ] dir = /pgsql/CA certificate = $dir/postgresCA.pem database = $dir/index.txt new_certs_dir = $dir/signedcerts private_key = $dir/private/postgresCA.pem serial = $dir/serial # # # Default expiration and encryption policies for certificates. # default_crl_days = 365 default_days = 1825 default_md = sha256 # policy = local_ca_policy x509_extensions = local_ca_extensions # # # Copy extensions specified in the certificate request # copy_extensions = copy # # # Default policy to use when generating server certificates. The following # fields must be defined in the server certificate. # [ local_ca_policy ] commonName = supplied stateOrProvinceName = supplied countryName = supplied emailAddress = supplied organizationName = supplied organizationalUnitName = supplied # # # x509 extensions to use when generating server certificates. # [ local_ca_extensions ] basicConstraints = CA:false # # # The default root certificate generation policy. # [ req ] default_bits = 4096 default_keyfile = /pgsql/CA/private/postgresCA.pem default_md = sha256 # prompt = no distinguished_name = root_ca_distinguished_name x509_extensions = root_ca_extensions # # # Root Certificate Authority distinguished name. Change these fields to match # your local environment! # [ root_ca_distinguished_name ] commonName = CHANGE ME stateOrProvinceName = CHANGE ME countryName = CHANGE ME emailAddress = CHANGE_MY@EMAIL.ADDRESS organizationName = CHANGE ME organizationalUnitName = CHANGE ME # [ root_ca_extensions ] basicConstraints = CA:true
Directives marked “CHANGE” should be amended to reflect your organisation then save the file as postgresCA.cnf in /pgsql/CA.
Once you have the configuration file you need to tell the terminal where to look for it; Alternatively it can form part of the command line to generate the certificate using the “-config” directive, but the instructions that I initially used did it this way so I am sticking to it.
export OPENSSL_CONF=~/inplicareCA/inplicareCA.cnf
Next create an empty text file for the revocation list.
touch postgresCRL.pem
Now you can generate your certificate authority’s own certrificate.
IMPORTANT!!! The name of the certificate **MUST** match that given in the “certificate” directive of the previously created configuration file (in our example “postgresCA.cnf”).
openssl req -x509 -newkey rsa:4096 -out postgresCA.pem -outform PEM -days 3650
The above commend will generate a certificate that will be valid for around 10 years. During the generation process openssl will ask you to provide a password. You will need this every time you wish to sign a certificate with your certificate authority so do not forget it.
When complete you should have two files, one in /pgsql/CA called postgresCA.pem and another in /pgsql/CA/private named in accordance with the name provided under the the “private_key” directive in the postgresCA.cnf configuration file (in our example postgresCA.pem).
Next, while not strictly necessary, strip the text from the certificate by running:
openssl x509 -in postgresCA.pem -out postgresCA.crt
NOTE: the suffix has changed to .crt When I did this I ended up with 2 identical files so I am not sure what benefit this might be, but you will be using the postgresCA.crt file on the client in later steps.
Congratulations, you now have a working certificate authority.
Client Certificates
Getting this right annoyed me off so much that I wrote a script to do it for me. If you wish to know how to do it the manual way then read the wiki here: https://help.ubuntu.com/community/OpenSSL, or read the script. We are only going to deal with what we need to use the script here.
The script is as follows; just save it to a file named /pgsql/CA/newcert.sh and make it executable.
#!/bin/bash
#
prompt_confirm() {
while true; do
read -r -n 1 -p "${1:-Continue?} [y/n]: " REPLY
case $REPLY in
[yY]) echo ; return 0 ;;
[nN]) echo ; return 1 ;;
*) printf " \033[31m %s \n\033[0m" "invalid input"
esac
done
}
if [[ $# -eq 0 ]] ; then
echo 'USAGE: You need to pass a valid configuration (.cnf) file to the script. e.g. "./newcert -c mycert.cnf" Use -h flag for more help. '
exit 1
fi
CONF_FILE=$1
TMP_KEYFILE='ictempkey.pem'
TMP_REQFILE='ictempreq.pem'
BUFFER_FILE='buffer.pem'
CA_CNF='postgresCA.cnf'
CRL='postgresCRL.pem'
PASSWORD=''
while getopts c:hp option
do
case "${option}"
in
p) PASSWORD="FAKEPASS";;
c) CONF_FILE=${OPTARG};;
h) echo "*----------------------------------------------------------------------------------------*"
echo "* *"
echo "* USAGE *"
echo "* ./newcert.sh -c filename.cnf -p *"
echo "* *"
echo "* -c (required) The openssl configuration file for the certificate you wish to *"
echo "* create. *"
echo "* *"
echo "* NOTE: The name of this file will determine the name of the certificate *"
echo "* *"
echo "* *"
echo "* -p (optional) Do not prompt for a password the script will use a fake password *"
echo "* to create the certificate and then remove it automatically without prompting. *"
echo "* *"
echo "*----------------------------------------------------------------------------------------*"
exit 0
esac
done
if [[ $CONF_FILE == *.cnf ]]; then
if [[ -f $CONF_FILE ]]; then
echo 'Building certificate using ' $CONF_FILE
export OPENSSL_CONF=./$CONF_FILE
if [[ -z "$PASSWORD" ]]; then
openssl req -newkey rsa:4096 -keyout $TMP_KEYFILE -keyform PEM -out $TMP_REQFILE -outform PEM
else
openssl req -newkey rsa:4096 -passout pass:$PASSWORD -keyout $TMP_KEYFILE -keyform PEM -out $TMP_REQFILE -outform PEM
fi
status=$?
if [[ status -eq 0 ]]; then
if [[ -z "$PASSWORD" ]]; then
if prompt_confirm "Would you like to remove the password from the certificate (You will be prompted for a password every time you use the certificate if you don't)"; then
echo 'Removing password'
openssl rsa < $TMP_KEYFILE > $BUFFER_FILE
fi
else
openssl rsa -passin pass:$PASSWORD < $TMP_KEYFILE > $BUFFER_FILE
fi
status=$?
if [[ status -eq 0 ]]; then
mv $BUFFER_FILE $TMP_KEYFILE
if prompt_confirm "Would you like to sign the certificate"; then
export OPENSSL_CONF=./$CA_CNF
CERTNAME=${CONF_FILE%.*}
echo 'Signing Certificate "'$CERTNAME'"'
openssl ca -in $TMP_REQFILE -out $CERTNAME.crt
status=$?
if [[ status -eq 0 ]]; then
mv $TMP_KEYFILE $CERTNAME.key
echo 'SUCCESS CERTIFICATE SIGNED'
echo 'Congratulations, the following files have now been created: "' $CERTNAME'.crt" and "'$CERTNAME'.key"'
if prompt_confirm "Would you like to test the certificate?"; then
openssl verify -CRLfile ./$CRL -crl_check_all -CAfile ./${CA_CNF%.*}.crt $CERTNAME.crt
fi
else
echo 'ERROR or SIGNING CERTIFICATE ABORTED'
echo 'If you wish to create a certificate later please re run this script, the current unsigned and invalid certificates have been deleted'
rm $TMP_REQFILE
rm $TMP_KEYFILE
fi
else
echo 'CERTIFICATE SIGNING ABORTED'
echo 'If you wish to create a certificate later please re run this script, the current unsigned and invalid certificates have been deleted'
rm $TMP_REQFILE
rm $TMP_KEYFILE
fi
else
echo 'ERROR: An error occurred while removeing the password, exiting!'
exit 1
fi
else
echo 'ERROR: Building the certificate failed, exiting!'
exit 1
fi
else
echo 'ERROR: ' $CONF_FILE ' does not exist, exiting!'
exit 1
fi
else
echo 'ERROR: Invalid file format, you need to pass a .cnf file to the script, exiting!'
exit 1
fi
exit 1
Now we need a configuration file. The name of this file will determine the name of the certificate so think about it carefully. In this example I am simply going to call it server.
In /pgsql/CA create a file called server.cnf.
touch server.cnf
NOTE: The .cnf suffix is important, the script will not work without it.
Now edit this file with the appropriate information. The only things that are of importance are the “commonName” and any “alt_names“. The reason these are important is that they are not reusable so in order to issue a further certificate using the same commonName or alt_name you will first need to revoke this one.
The file should look something like:
# # server.cnf # [ req ] prompt = no distinguished_name = server_distinguished_name req_extensions = v3_req [ server_distinguished_name ] commonName = CHANGE.ME.FQDN stateOrProvinceName = CHANGE ME countryName = CHANGE ME emailAddress = CHANGE.ME.EMAIL organizationName = CHANGE ME organizationalUnitName = CHANGE ME [ v3_req ] basicConstraints = CA:FALSE keyUsage = nonRepudiation, digitalSignature, keyEncipherment subjectAltName = @alt_names [ alt_names ] DNS.1 = CHANGE ME
When it is finished; you can have as many alt_names as you like, you simply increment the DNS. directive on a new line. i.e. DNS.1, DNS.2 etc..
Save the file as /pgsql/CA/server.cnf and then run the script to generate the the certificate and sign it.
NOTE: this script is not bomb proof, but as long as you haven’t done anything too daft it should work ok.
Method 1
./newcert.sh server.cnf
and follow the instructions in the terminal.
You will be prompted for a password for the client certificate, then you will be asked if you want to remove it; it is all part of the process, just provide any password of 5 characters or more, but make sure you remember it as you will be prompted for it one more time in order to remove it. THE SCRIPT HAS BEEN UPDATED NOW SO THAT YOU DO NOT HAVE TO DO THIS IF YOU PASS IT THE -p FLAG; IF YOU CHOOSE THIS METHOD THEN YOU WILL NEED TO PASS THE -c FLAG ALONG WITH THE FILENAME.
Method 2
./newcert.sh -p -c server.cnf
You will still be prompted for the CA password.
NOTE: If you are having trouble running this script then you may need to edit it; This will most likely be the case if your configuration file for your CA is something other than postgresCA.cnf, you will have to change the CA_CNF= directive in the script to match.
In our example the script should have generated 2 files: “server.crt” and “server.key” you will need these along with the public key of your certificate authority “postgresCA.crt to use them with your postgresql servers.
The last thing you need to do is change the permissions on your .key file.
WARNING: Failure to do this will prevent postgresql from starting correctly when you assign the certificates.
I recommend creating a certificate for each individual client.
chmod 400 ./server.key
Setting up postgresql for SSL authentication
You will need your certificates somewhere where postgresql can access them; if you are using them on the server that you set the CA up on then you can leave them where they are. If it is on a replication server then I usually put them directly in the /pgsql directory.
If you are still logged in as the postgres user then exit and change the user to root or use sudo as is your desire.
The first file you need to edit is /etc/postgresql/15/main/postgresql.conf.(remember this path is version specific).
Look for the ssl directives and change/uncomment them so it looks like
# - SSL - ssl = on ssl_ca_file = '/pgsql/CA/postgresCA.crt' ssl_cert_file = '/pgsql/CA/server.crt' ssl_crl_file = '' ssl_key_file = '/pgsql/CA/server.key' ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers ssl_prefer_server_ciphers = on #ssl_ecdh_curve = 'prime256v1' #ssl_dh_params_file = '' #ssl_passphrase_command = '' #ssl_passphrase_command_supports_reload = off
then save the file.
The next bit is somewhat specific to your user setup as you are amending pg_hba.conf to tell your configuration to use ssl when negotiating connections. This is on a per user basis so if you have user “foo” and user “bar” you will have to turn on ssl for each individually.
To do so simply change host to hostssl
hostssl mydatabase foo XXX.XXX.XXX.XXX/XX scram-sha-256 hostssl mydatabase bar XXX.XXX.XXX.XXX/XX scram-sha-256
Now if you restart postgresql when you log in to psql as one of the hostssl users you should see something like:
psql (15.7 (Debian 15.7-0+deb12u1)) SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off) Type "help" for help.
This means that your connection is secure.
Revoking client certificates
Every certificate generated will authenticate against the server until either it is revoked or it runs out. The server is reliant on a certificate revocation list in order to determine which certificates have been revoked by the CA so we need to generate a certificate revocation list every time we revoke a certificate.
Again I have wrote a small script to do this as it is so easy to miss a step if you do it manually.
#!/bin/bash
#
# REVOKE A CERTIFICATE
prompt_confirm() {
while true; do
read -r -n 1 -p "${1:-Continue?} [y/n]: " REPLY
case $REPLY in
[yY]) echo ; return 0 ;;
[nN]) echo ; return 1 ;;
*) printf " \033[31m %s \n\033[0m" "invalid input"
esac
done
}
if [[ $# -eq 0 ]] ; then
echo 'USAGE: "./revoke -c mycert.crt -p capassword"'
exit 1
fi
CERTFILE=$1
while getopts c:p: option
do
case "${option}"
in
c) CERTFILE=${OPTARG};;
p) PASSWORD=${OPTARG};;
esac
done
echo $PASSWORD
#You may need to change these directives to reflect your real CAServer name (not to be confused with the
#actual server name) and the name that you have given to your certificate revocation list.
CA='postgresCA'
CRL='postgresCRL'
if [[ $CERTFILE == *.crt ]]; then
if [[ -f $CERTFILE ]]; then
echo 'Revoking '$CERTFILE
export OPENSSL_CONF=./$CA.cnf
if [[ -z "$PASSWORD" ]]; then
openssl ca -config $CA.cnf -revoke ./$CERTFILE
else
echo 'openssl ca -config '$CA.cnf' -passin pass:'$PASSWORD' -revoke ./'$CERTFILE
openssl ca -config $CA.cnf -passin pass:$PASSWORD -revoke ./$CERTFILE
fi
status=$?
if [[ status -eq 0 ]]; then
if prompt_confirm "Would you like to update the CERTIFICATE REVOCATION LIST?"; then
if [[ -z "$PASSWORD" ]]; then
openssl ca -config $CA.cnf -gencrl -keyfile ./private/$CA.pem -cert $CA.pem -out $CRL.pem
else
openssl ca -config $CA.cnf -gencrl -keyfile ./private/$CA.pem -cert $CA.pem -out $CRL.pem -passin pass:$PASSWORD
fi
status=$?
if [[ status -eq 0 ]]; then
echo 'CERTIFCATE REVOKED: The certificate file ' $CRL'.pem has been updated.'
echo 'Additional steps my be required in order to propogate the changes'
if prompt_confirm "The certificate and private key can now be safely deleted, would you like to do this automatically now?"; then
rm ./$CERTFILE
rm ./${CERTFILE%.*}.key
fi
fi
fi
else
echo 'ERROR: The certificate revocation failed, exiting.'
exit 1
fi
else
echo 'The file "'$CERTFILE'" does not exist; exiting!'
exit 1
fi
else
echo 'You need to provide the script with a certificate file with the extension ".crt" in order to proceed.'
exit 1
fi
Copy the above and save it to a file called /pgsql/CA/revoke.sh and make it executable.
This script requires the name of the certificate to be revoked to passed as an argument.
./revoke.sh -c server.crt -p secretpassword
Follow the instructions on the screen and you will revoke the certificate created earlier.
NOTE: When a certificate expires you will need to revoke it before creating a new one.
Showing revoked certificates
You can do this from the command line quite simply by passing running the following:
openssl crl -in postgresCRL.pem -noout -text
This will display the certificates that have been revoked according to the certificate revocation list. If for some reason the CRL has not been updated then the crl will not display the revoked certificates. The serial numbers of the revoked certificates should match those marked with an “R” in the database (index.txt file).
NOTE: The CRL can be updated any time by running:
openssl ca -config inplicareCA.cnf -gencrl -keyfile ./private/postgresCA.pem -cert postgresCA.pem -out postgresCRL.pem
however this should not ordinarily be necessary as is done in the script.
Recent Comments