These steps are generally generic. I’ve marked this as a Postgres setup as the shared container that will be created will have a block size of 8kb to closely match the internal Postgres pagesize. Some people prefer to lump all their docker application directories under one share but at least for Postgres I like to have a seperate share.
Create Docker User & Collect Details
1) Create A Docker User
Open up your QNAP settings and select create user. Choose an appropriate username, I choose the user name dockeruser.
2) Enable SSH To Your QNAP
We need to SSH into the QNAP to get further details. Enable SSH connections.
User ssh user@<QNAP ipaddress> to access your QNAP. You will be asked for a password, it is the same password you use to login to your QNAP web login.
3) Get User & Group Details
To get the group and user id information for your new user apply the following command.
[~]$ id dockeruser
uid=1003(dockeruser) gid=100(everyone) groups=100(everyone)
[~]$ Remember to deactivate SSH access when you no longer need it.
Create Docker Data Share Directory
We are going to want to create a share folder for our docker user. You could create one share and have subdirectories for each application. For a Postgres docker application I prefer to have its own share to match the internal needs of Postgres.
Create Application Share Directory
I’ve settled on the name share name dockerdatapostgres and am setting the quota to maximum pool capacity.
I’ve set the block size to 8K, the same page size that Postgres has.
Give the user dockeruser Read/Write access to the share,
and finally set the folder properties,
these properties are chosen so that the database fully controls the share contents and doesn’t for example find files it thought it deleted because the nas was backing them up.
Create Directory Structure
Inside the dockerdatapostgres share we will create three other directories.
Create data and wal directories
Two of these directories are for the postgres database, data and wal.
The data directory is the central storage area containing the actual database files, initial transaction logs, and essential configuration files, serving as the backbone for the database’s operation and management.
The wal directory holds Write-Ahead Logging files, crucial for recording all changes to the database,
ensuring data integrity, and facilitating recovery operations.
Storing the data and wal directories outside of a Docker container ensures data persistence across container restarts and system upgrades, and allows for performance optimization by separating database files from intensive write-ahead logging operations. This approach also simplifies database backup, recovery, and monitoring by leveraging host system tools and storage solutions.
Create pgadmin4 directories
The pgadmin4 directory is for the web page based admin console for pgadmin4.
Change Directory Ownership and Permissions
We need to change the directory permissions to be docker friendly. First we need to ssh into the QNAP, then change to the admin user. The following commands will change the ownership and permissions of these directories.
[admin@qnap dockerdatapostgres]# mkdir data
[admin@qnap dockerdatapostgres]# chown 999:administrators data
[admin@qnap dockerdatapostgres]# chmod 700 data/
[admin@qnap dockerdatapostgres]# mkdir wal
[admin@qnap dockerdatapostgres]# chown 999:administrators wal
[admin@qnap dockerdatapostgres]# chmod 700 wal
[admin@qnap dockerdatapostgres]# mkdir pgadmin4
[admin@qnap dockerdatapostgres]# chown 999:administrators pgadmin4
[admin@qnap dockerdatapostgres]# chmod 700 pgadmin4 Remember to logout and deactivate the ssh service when you are not using it.
Create Docker Compose File
The following docker compose file will successfully create the postgres and pgadmin containers.
Container data persistence is accomplished through bind mounts to the shares created earlier.
Note that the PGID and PUID numbers match the user dockeruser we created earlier and these may differ from yours.
version: '3'
services:
postgres:
image: postgres:16.1
restart: unless-stopped
ports:
- 0.0.0.0:5432:5432
volumes:
- /share/dockerdatapostgres/data:/var/lib/postgresql/data
- /share/dockerdatapostgres/wal:/var/lib/postgresql/wal
environment:
- POSTGRES_USER=postgres_qnap_user
- POSTGRES_PASSWORD=postgres_qnap_pwd
- PGID=100
- PUID=1003
- POSTGRES_HOST_AUTH_METHOD=scram-sha-256
- POSTGRES_INITDB_ARGS=--data-checksums
- POSTGRES_INITDB_WALDIR=/var/lib/postgresql/wal
- POSTGRES_SSL_CERT_FILE=/var/lib/postgresql/data/server.crt
- POSTGRES_SSL_KEY_FILE=/var/lib/postgresql/data/server.key
- POSTGRES_SSL_CA_FILE=/var/lib/postgresql/data/ca.crt
pgadmin4:
image: dpage/pgadmin4:8.2
restart: unless-stopped
ports:
- "5050:80"
environment:
- [email protected]
- PGADMIN_DEFAULT_PASSWORD=admin
- PGID=100
- PUID=1003
volumes:
- /share/dockerdatapostgres/pgadmin4:/var/lib/pgadmin
depends_on:
- postgres
Copy this compose file to the create application dialogue in your QNAP Container Station application.
Apply your own username and passwords, your own PGID and PUID values, and also the share locations you want to persist your data.
Under advanced settings I selected the maximum value for CPU and Memory usage. In the same advanced area you will see the following settings, add your pgadmin service name the same one in the compose file and the port. This setting helps QNAP create that “box with arrow” quick link to your web based service.
Click the Create button and after a few short moments the following docker containers will be created and up and running.
The pgadmin4 console will be accessible at your.qnap.ip.address:5050 or by clicking the “box with arrow” icon.
You will also find that your data, wal and pgadmin4 directories are now populated and will persist your data. With this data persistence all you need to worry about is backing up your docker-compose file in the case where you need to adjust it or recreate the application in Container Station.
SHUTDOWN YOUR CONTAINER APPLICATION
Now that we have a Docker Compose file in place and operational, it’s important to secure access to our setup, given that it involves a network-connected device.
Shutdown your container and read the following brief overview of the security options available.
Afterwards we’ll pick one and i’ll talk you through how to set it up.
On installing or running a docker container the default security configuration for a PostgreSQL installation utilizes ‘md5’ for client authentication, denoted by clientcert=0. This configuration mandates authentication via a username and a hashed password, but it does not incorporate SSL/TLS encryption for the connection.
For enhancing security, PostgreSQL supports SSL/TLS options, such as:
clientcert=verify-ca: This SSL mode enables server-level verification. The server authenticates clients by confirming that their certificates are issued by a trusted certificate authority (CA). However, this mode alone does not authenticate the database user’s identity at the database level. It ensures the security of the connection but typically requires the client to also provide traditional login credentials (username and password) for database access.
Multiple users/systems can use the same certificate.clientcert=verify-full: This mode extendsverify-caby not only verifying the client’s SSL certificate but also ensuring that the certificate’s common name (CN) aligns with the requested database username. This enforces stricter security by linking the certificate directly to a specific database user. Similar toverify-ca, it usually necessitates the provision of username and password credentials for database access.
So in this case a certificate can only be used by one person.
For both SSL certificate options, it’s essential to specify a domain name. In environments that do not resolve domain names (like a typical home network), you should also provide IP addresses to ensure correct connection establishment and validation.
Please make sure to shut down your application within the QNAP Container Station during this process to prevent any potential conflicts or interruptions.
We will be opting for the clientcert=verify-ca option for our home setup to simplify the ability for multiple machines to connect to the database. It’s important to note that while this option secures the connection at the SSL/TLS layer, each machine will still require traditional username and password credentials to access the database in addition to the ssl certificate.
Further details on the implementation and configuration will follow.
PostgreSQL SSL Certificate Generation
Verification by ssl clientcert=verify-ca is our method of choice.
The Three Authentication Entities
There are 3 entities involved in the ssl authentication process, the Server, the Client, and
an overseeing Certificate Authority.
The overseeing Certificate Authority entity acts as a third-party verifier that your Client and Server
machines can trust, it is responsible for issuing and managing certificates.
There are many well known Certificate Authorities (or SSL providers) which you might recognise GoDaddy, Symantec, Cloudflare Verizon/DigiCert to name a few. For our ‘home-brew’ implementation of SSL authentication we are going to play the part of a Certificate Authority and ‘self sign’ our server and clients.
Required Key & Certificate Files
Here in short are the end public and private files each entity needs to operate.
Certificate Authority
- [Private] Certificate Authority (CA) key, generate ca.key file.
- [Public] Certificate Authority (CA) certificate, generate ca.crt file.
Server
- [Private] Server Key, generate server.key file.
- [Public] Server Certificate, generate server.crt file.
Client
- [Private] Client Key, generate client.key file.
- [Public] Client Certificate, generate client.crt file.
There are intermediate steps where a certificate signing request file is created by the Server and Client before the Certificate Authority grants and creates the server.crt and client.crt files. More on this later.
Generating Key & Certificate Files
Since we are going to be playing the part of all three entities we are going to generate them all on the same machine and copy them to the client and server when generated. I’m going to generate these locally on my laptop.
Step1) Create Directory Structure
We are going to need a directory to hold the config files and run the commands. Pick any location on your file system and create a directory and within that directory run the following command to generate some required directory structures.
mkdir -p certs crl newcerts && touch index.txt && echo 1000 > serial && echo 1000 > crlnumberStep 2) Create ssl Configuration File (openssl.cnf)
This ssl file contains settings for the generation of Certificate Authority, Server and Client.
Copy the following code and place it in a file named, openssl.cnf, in your directory.
[ ca ]
default_ca = CA_default
[ CA_default ]
dir = . # The current directory
certs = $dir/certs # Where the issued certs are kept
crl_dir = $dir/crl # Where the issued CRLs are kept
database = $dir/index.txt # Database index file
new_certs_dir = $dir/newcerts # Default place for new certs.
certificate = $dir/ca.crt # The CA certificate
serial = $dir/serial # The current serial number
crlnumber = $dir/crlnumber # The current crl number
crl = $dir/crl.pem # The current CRL
private_key = $dir/ca.key # The private key
RANDFILE = $dir/private/.rand # Private random number file
x509_extensions = v3_ca # The extensions to add to the cert
name_opt = ca_default # Subject Name options
cert_opt = ca_default # Certificate field options
default_days = 365 # How long to certify for
default_crl_days = 30 # How long before next CRL
default_md = default # Use public key default MD
preserve = no # Keep passed DN ordering
policy = policy_anything
[ policy_anything ]
countryName = optional
stateOrProvinceName = optional
localityName = optional
organizationName = optional
organizationalUnitName = optional
commonName = supplied
emailAddress = optional
[ req ]
default_bits = 2048
distinguished_name = req_distinguished_name
prompt = yes
encrypt_key = no
[ req_distinguished_name ]
countryName = Country Name (2 letter code)
countryName_default = US
stateOrProvinceName = State or Province Name (full name)
stateOrProvinceName_default = YourState
localityName = Locality Name (eg, city)
localityName_default = YourCity
0.organizationName = Organization Name (eg, company)
0.organizationName_default = YourCompany
organizationalUnitName = Organizational Unit Name (eg, section)
organizationalUnitName_default = YourUnit
commonName = Common Name (e.g., server FQDN or YOUR name)
commonName_default = YourName
emailAddress = Email Address
emailAddress_default = [email protected]
[ v3_ca ]
# Extensions for a typical CA
subjectKeyIdentifier = hash
authorityKeyIdentifier = keyid:always,issuer
basicConstraints = critical,CA:true
keyUsage = critical, cRLSign, keyCertSign
[ v3_server_req ]
# Extensions for a typical CSR
basicConstraints = CA:FALSE
keyUsage = digitalSignature, keyEncipherment
extendedKeyUsage = serverAuth
subjectAltName = @server_alt_names
[ v3_client_req ]
# Extensions for a typical CSR
basicConstraints = CA:FALSE
keyUsage = digitalSignature, keyEncipherment
extendedKeyUsage = clientAuth
subjectAltName = @client_alt_names
[ v3_server ]
# Extensions for server certificates
basicConstraints = CA:FALSE
nsCertType = server
nsComment = "OpenSSL Generated Server Certificate"
subjectKeyIdentifier = hash
authorityKeyIdentifier = keyid,issuer
keyUsage = critical, digitalSignature, keyEncipherment
extendedKeyUsage = serverAuth
subjectAltName = @server_alt_names
[ v3_client ]
# Extensions for client certificates
basicConstraints = CA:FALSE
nsCertType = client
nsComment = "OpenSSL Generated Client Certificate"
subjectKeyIdentifier = hash
authorityKeyIdentifier = keyid,issuer
keyUsage = critical, digitalSignature, keyEncipherment
extendedKeyUsage = clientAuth
subjectAltName = @client_alt_names
[ server_alt_names ]
IP.1 = 192.168.0.183
[ client_alt_names ]
IP.1 = 192.168.0.243You must state the IP addresses of the client and server machines for the ssl protocol to work.
If you are in a domain resolution environment you can add DNS names instead like so,
[ server_alt_names ]
DNS.1 = serverdomain.example.com
[ client_alt_names ]
DNS.1 = clientdomain.example.comStep 3) Generate Certificate Authority Files
Run the following code in the same directory. This will generate the private ca.key file.
openssl ecparam -genkey -name prime256v1 -out ca.keyWe then generate the public ca.crt certificate file with the following code. Note the reference to the config file in this command and keep an eye out for this in further commands.
openssl req -new -x509 -days 3650 -key ca.key -out ca.crt -config openssl.cnfThis will launch an interactive dialogue. You must at a minimum add Country Name, and Common Name.
Common Name in this case can be a descriptive string, I am using the value ‘MySigningAuthority’ which I think is relevant to the file being generated.
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [US]:UK
State or Province Name (full name) [YourState]:
Locality Name (eg, city) [YourCity]:
Organization Name (eg, company) [YourCompany]:
Organizational Unit Name (eg, section) [YourUnit]:
Common Name (e.g., server FQDN or YOUR name) [YourName]:MySigningAuthority
Email Address [[email protected]]:Step 4) Generate Server Files
The following code will generate the private server.key file.
openssl ecparam -genkey -name prime256v1 -out server.keyWe then start the process of generating the public server.crt file by first generating a server.csr (Certificate Signing Request) file. The following command does this. Note that the openssl.cnf file is referenced and the specific extensions (v3_server_req) in that config file for the server signing request process is also referenced.
openssl req -new -key server.key -out server.csr -config openssl.cnf -extensions v3_server_reqSimilarly to before an interactive dialogue is launched , note that my choice for the common name value this time is ‘MyServer’.
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [US]:UK
State or Province Name (full name) [YourState]:
Locality Name (eg, city) [YourCity]:
Organization Name (eg, company) [YourCompany]:
Organizational Unit Name (eg, section) [YourUnit]:
Common Name (e.g., server FQDN or YOUR name) [YourName]:MyServer
Email Address [[email protected]]:A server.csr file will be generated which is then checked and signed by the Certificate Authority by using the following command.
openssl ca -in server.csr -out server.crt -config openssl.cnf -extensions v3_server -days 3650 -md sha256You will see the following output. Select yes when prompted.
Using configuration from openssl.cnf
Check that the request matches the signature
Signature ok
Certificate Details:
Serial Number: 4096 (0x1000)
Validity
Not Before: Feb 4 19:16:31 2024 GMT
Not After : Feb 1 19:16:31 2034 GMT
Subject:
countryName = UK
stateOrProvinceName = YourState
localityName = YourCity
organizationName = YourCompany
organizationalUnitName = YourUnit
commonName = MyServer
emailAddress = [email protected]
X509v3 extensions:
X509v3 Basic Constraints:
CA:FALSE
Netscape Cert Type:
SSL Server
Netscape Comment:
OpenSSL Generated Server Certificate
X509v3 Subject Key Identifier:
9D:D2:72:C0:E7:83:FC:02:47:65:00:05:E2:EE:9A:21:DC:A1:DA:50
X509v3 Authority Key Identifier:
DirName:/C=UK/ST=YourState/L=YourCity/O=YourCompany/OU=YourUnit/CN=MySigningAuthority/[email protected]
serial:27:B7:DC:BF:40:03:F8:E5:E2:EE:F7:04:EB:4E:88:19:EA:3C:06:77
X509v3 Key Usage: critical
Digital Signature, Key Encipherment
X509v3 Extended Key Usage:
TLS Web Server Authentication
X509v3 Subject Alternative Name:
IP Address:192.168.0.183
Certificate is to be certified until Feb 1 19:16:31 2034 GMT (3650 days)
Sign the certificate? [y/n]:y
1 out of 1 certificate requests certified, commit? [y/n]y
Write out database with 1 new entries
Data Base UpdatedYou will find in the directory a server certificate file server.crt.
Step 5) Generate Client Files
The process for generating the client files is the same as for the server files. I will provide only the commands here and not list the output. Remember to use a different Common Name when prompted.
Here is the command to generate the private client.key file.
openssl ecparam -genkey -name prime256v1 -out client.keyHere is the command to generate the client.crt Certificate Signing Request.
openssl req -new -key client.key -out client.csr -config openssl.cnf -extensions v3_client_reqHere is the command to sign the client.crt file, which generate public client.crt certificate file.
openssl ca -in client.csr -out client.crt -config openssl.cnf -extensions v3_client -days 3650 -md sha256You will find in the directory a server certificate file client.crt.
PostgreSQL SSL Server Setup
We are now going to setup the postgres server, make sure you know where your public ca.crt file, your private server.key file, and your public server.crt file.
Copy Files To Server
You need to ssh in to your qnap server and navigate to the directory holding the postgres data, this is the volume mapping you created in the docker compose file.
For your reference here is the section from that docker compose file,
/share/dockerdatapostgres/data:/var/lib/postgresql/dataChange to the /share/dockerdatapostgres/data directory.
Copy/Move by any means you find comfortable the files ca.crt, server.key and server.crt to this location.
Change the ownership of these files to match the docker container standard,
[admin@qnap data]# chown 999:999 server.crt server.key ca.crtthen set the following appropriate permissions for these types of file,
[admin@qnap data]# chmod 400 server.key
[admin@qnap data]# chmod 444 server.crt
[admin@qnap data]# chmod 444 ca.crtyour postgres data directory should look like this,
[admin@qnap data]# pwd
/share/dockerdatapostgres/data
[admin@qnap data]# ls -l
total 242
drwxrwxrwx 5 999 999 5 2024-02-05 12:04 base
-r--r--r-- 1 999 999 839 2024-02-05 12:54 ca.crt
drwxrwxrwx 2 999 999 65 2024-02-05 13:10 global
drwxrwxrwx 2 999 999 2 2024-02-05 12:04 pg_commit_ts
drwxrwxrwx 2 999 999 2 2024-02-05 12:04 pg_dynshmem
-rw------- 1 999 999 6045 2024-02-05 13:21 pg_hba.conf
-rw------- 1 999 999 2640 2024-02-05 12:04 pg_ident.conf
drwxrwxrwx 4 999 999 5 2024-02-05 13:14 pg_logical
drwxrwxrwx 4 999 999 4 2024-02-05 12:04 pg_multixact
drwxrwxrwx 2 999 999 2 2024-02-05 12:04 pg_notify
drwxrwxrwx 2 999 999 2 2024-02-05 12:04 pg_replslot
drwxrwxrwx 2 999 999 2 2024-02-05 12:04 pg_serial
drwxrwxrwx 2 999 999 2 2024-02-05 12:04 pg_snapshots
drwxrwxrwx 2 999 999 2 2024-02-05 13:09 pg_stat
drwxrwxrwx 2 999 999 2 2024-02-05 12:04 pg_stat_tmp
drwxrwxrwx 2 999 999 3 2024-02-05 12:04 pg_subtrans
drwxrwxrwx 2 999 999 2 2024-02-05 12:04 pg_tblspc
drwxrwxrwx 2 999 999 2 2024-02-05 12:04 pg_twophase
-rwxrwxrwx 1 999 999 3 2024-02-05 12:04 PG_VERSION
lrwx------ 1 999 999 23 2024-02-05 12:04 pg_wal -> /var/lib/postgresql/wal
drwxrwxrwx 2 999 999 3 2024-02-05 12:04 pg_xact
-rw------- 1 999 999 88 2024-02-05 12:04 postgresql.auto.conf
-rw------- 1 999 999 29771 2024-02-05 13:06 postgresql.conf
-rwxrwxrwx 1 999 999 36 2024-02-05 13:09 postmaster.opts
-rwxrwxrwx 1 999 999 94 2024-02-05 13:09 postmaster.pid
-r--r--r-- 1 999 999 3542 2024-02-05 12:53 server.crt
-r-------- 1 999 999 302 2024-02-05 12:52 server.key
Postgres Configuration File Editing
We need to modify the pg_hba.conf, and postgresql.conf files.
postgresql.conf – Enable SSL
Open this file and find the SSL section in this file, uncomment and set the values to turn on SSL and set the file names.
# - SSL -
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'ca.crt'pg_hba.conf – Set Authentication Rules
Open this file and find the following section.
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust
host all all all scram-sha-256We need to modify this to,
- Set authentication from the same host to md5 only.
- Set authentication for network connections to md5, and clientcert=verify-ca.
Here are the modifications to that part of the file,
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust
# [REXRULES] Allow password authentication for local connections
host all all samenet md5
# [REXRULES] Require SSL cert authentication for external network connections
hostssl all all 192.168.0.0/24 md5 clientcert=verify-ca
host all all all scram-sha-256Note that for your network the value 192.168.0.0/24 may need to be changed.
Now restart your docker application!
PostgreSQL SSL Client Setup
You need to make sure your docker application is running on the QNAP container station before testing the client side.
To demonstrate how you would use the client certificate files we are going to use some python code.
On the client machine, the one with the IP address you used to generate the client cert, create a directory and place this code in that directory. Also copy your client.crt, client.key and ca.crt files to the same directory. Change the permissions as if its is not secure enough some systems won’t let you start the ssl process.
goodboy@rexbytes:~/Desktop/connect$ chmod 600 client.key
goodboy@rexbytes:~/Desktop/connect$ chmod 644 client.crt
goodboy@rexbytes:~/Desktop/connect$ chmod 644 ca.crt I named the python file connect.py,
#!/usr/bin/env python3
from sqlalchemy import create_engine
from sqlalchemy.sql import text
# Connection parameters
user = 'postgres_qnap_user'
password = 'postgres_qnap_pwd'
host = '192.168.0.183'
port = '5432'
database = 'postgres'
# SSL certificate files
ssl_cert = 'client.crt' # Path to the client certificate
ssl_key = 'client.key' # Path to the client private key
ssl_root_cert = 'ca.crt' # Path to the CA certificate
# SQLAlchemy engine using psycopg2 as the driver
engine = create_engine(
f"postgresql+psycopg2://{user}:{password}@{host}:{port}/{database}",
connect_args={
'sslmode': 'verify-ca',
'sslcert': ssl_cert,
'sslkey': ssl_key,
'sslrootcert': ssl_root_cert
}
)
# Sample query to test the connection and output some database information
query = text("SELECT version();")
# Execute the query and print the result
with engine.connect() as connection:
result = connection.execute(query)
for row in result:
print(row)
# Note: Replace '/path/to/...' with the actual file paths to your SSL certificate filesNote that the connection arguments must match your server setup, in this case ‘verify-ca’.
Make sure the file is executable,
goodboy@rexbytes:~/Desktop/connect$ chmod 755 connect.pyWhen you run this example file you will see that a successful connection is made and an SQL query is run against the postgres database.
goodboy@rexbytes:~/Desktop/connect$ ls -l
total 16
-rw-r--r-- 1 goodboy goodboy 839 Feb 5 08:44 ca.crt
-rw-r--r-- 1 goodboy goodboy 3541 Feb 5 08:46 client.crt
-rw------- 1 goodboy goodboy 302 Feb 5 08:46 client.key
-rwxr-xr-x 1 goodboy goodboy 1068 Feb 6 16:15 connect.py
oodboy@rexbytes:~/Desktop/connect$ ./connect.py
('PostgreSQL 16.1 (Debian 16.1-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit',)
With this example you should now be able to create scripts to run on machines in your network to connect with the postgres database hosted on your QNAP.
pgadmin4 Database Registration (Local Authentication)
If you plan on running applications that access your postgres database locally from the same QNAP host you can just use standard authentication. Note your current container IP address, but actually I think the IP address of the QNAP server itself will work too.
pgadmin4 is an application now running locally on your QNAP, let’s head over and set that up.
Head over to <your.nas.ip.address>:5050 and login to your pgadmin4 console and select server registration.
Give your server a name, I chose qnap.
Under the connection tab enter your postgres container IP address in to the Host name/address field.
Then add the username and password credentials you placed in the docker-compose file.
Click Save. After a few moments you will see the postgres database running in your postgres container will appear as a registered server ready to administrate using pgadmin4.
Final Say
Well… this was a bit of work but well worth it.
If you want to explore creating certificates for specific users experiment by changing clientcert=verify-ca to clientcert=verify-full. You will then need to set the Common Name variable to the username used to access the database.
[…] already covered how to run docker-compose.yml files in Container Station here , you won’t need to mess around with UID and GID values for the Portainer install. Create a […]