The reliable way to connect Python to PostgreSQL on a VPS is to install Psycopg 3, create a dedicated database role, make PostgreSQL listen only where your network requires, permit that application network in pg_hba.conf, allow the database port through a restricted firewall rule, and connect with TLS using sslmode=verify-full. The Python process should use a finite connection timeout and obtain its password from a protected secret rather than source code.
Need a server to try this on? Antihost offers Linux VPS plans with NVMe storage, full root access and a 14-day free trial.
This guide covers a self-managed PostgreSQL instance on a Linux VPS, with Ubuntu paths and service commands where they are relevant. The same connection parameters work when PostgreSQL is on another private server or a managed service; only the server and network administration steps change.
As an Amazon Associate I earn from qualifying purchases.
The Tool Desk
Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →What the connection consists of
A Python connection is not just a driver call. Four independent layers must work:
- Application layer: Psycopg 3 must be installed in the Python environment, and the code must supply a host, port, database, role, password, and TLS settings.
- PostgreSQL layer: the server must be running and listening on an interface reachable from the application.
- Authentication layer: a matching
pg_hba.confrule must permit the application source address, database, and role. - Network and identity layer: the VPS or cloud firewall must permit only the intended source, while the server certificate must validate the hostname used by the client.
A failure in any one layer can look like a generic connection error. Configure and test them in that order instead of changing credentials or firewall rules at random.
#1 Best Overall
Choose the network topology first
| Topology | What the client uses as host |
Exposure and administration |
|---|---|---|
| Python and PostgreSQL on the same VPS | localhost or a local socket |
No public database exposure is needed. A local socket can avoid TCP, while localhost still exercises TCP settings. |
| Application VPS and database VPS on a private network | The database server’s private DNS name or private address | Permit only the application subnet or host address in the database and firewall rules. |
| Application connecting over the public Internet | A public DNS name | Use TLS with hostname verification and a firewall rule limited to fixed application addresses. Avoid opening port 5432 to the entire Internet. |
If your hosting platform offers a private network, use it for server-to-server traffic. It reduces exposure, but it does not replace PostgreSQL authentication, TLS, or a least-privilege role.
Install Psycopg 3 in an isolated Python environment
Psycopg 3 is the current Python adapter documented by the project. The binary extra is the shortest installation path on supported systems because it supplies the client components needed by the wheel.
Recommended Free Tools
- Create and activate a virtual environment in the application directory:
python -m venv .venv
. .venv/bin/activate- Upgrade packaging tools and install Psycopg:
python -m pip install --upgrade pip
python -m pip install "psycopg[binary]"- Check the Python and PostgreSQL versions against Psycopg’s support table before pinning a production deployment. Record the resulting dependency in your deployment lockfile so a later rebuild does not silently select a different adapter release.
The import name is psycopg, not psycopg3. Run a small import check from the same virtual environment used by your service:
python -c "import psycopg; print(psycopg.__version__)"If the import fails, the usual cause is that pip installed into a different interpreter. Compare python -m pip --version with the Python executable that launches your application.
Create a dedicated PostgreSQL role and database
Do not run the application as the PostgreSQL superuser. A separate login role limits the damage from a leaked password or an application bug and makes access decisions visible in the database configuration.
On a self-managed Ubuntu server, open a PostgreSQL session as the operating-system PostgreSQL account:
sudo -u postgres psqlCreate a login role without putting its password in shell history. The interactive password command prompts for it without echoing the value:
CREATE ROLE app_user LOGIN;
password app_user
CREATE DATABASE app_db;
GRANT CONNECT ON DATABASE app_db TO app_user;The role and database names above are examples. Use names that identify the service, and keep the password in a VPS secret store or a permissions-restricted libpq password file. If the application creates tables during deployment, perform migrations with a separately controlled migration role where practical; the runtime role should have only the privileges it needs to read and write application data.
Connect to the new database to grant schema privileges appropriate to your application. For a runtime role that needs to use the default schema, an administrator can apply grants such as:
c app_db
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO app_user;Those grants affect existing objects. If future migrations create additional tables, make the migration process apply equivalent grants or configure default privileges deliberately. Avoid granting broad ownership or superuser rights merely to make an initial connection succeed.
Quick wins for a faster PC:
Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Clear out junk files and repair common Windows errorsFree Scan →Configure PostgreSQL to accept the application safely
Find the active configuration files
Ubuntu stores PostgreSQL configuration below /etc/postgresql/, in a directory named for the installed major version and the main cluster. Do not assume a version number from a tutorial. Ask the running server for its active files:
sudo -u postgres psql -c "SHOW config_file;"
sudo -u postgres psql -c "SHOW hba_file;"Edit the paths returned by those commands, or use the distribution’s configuration tools if your installation is managed differently.
Set the listener address
In postgresql.conf, set listen_addresses only as broadly as the topology requires. A local-only application can use localhost. An application on a private network needs the database server’s private interface as well. A public interface should be enabled only when there is no suitable private route and the firewall and TLS controls are ready.
listen_addresses = 'localhost,10.0.0.5'The address in this example is illustrative; replace it with the private address assigned to the database VPS. Binding to * is broader than most application deployments need and increases the number of interfaces that can receive connection attempts.
Windows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallCrashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteAdd a narrowly scoped pg_hba.conf rule
pg_hba.conf is evaluated in order. PostgreSQL uses the first rule that matches the connection’s type, source address, database, and role, so place a specific application rule before broader rules that could change the result.
hostssl app_db app_user 10.0.2.0/24 scram-sha-256This pattern requires a TLS TCP connection to app_db as app_user from the stated application subnet and uses SCRAM password authentication. If the application has one fixed address, use a single-host CIDR instead of an entire subnet. Do not use a catch-all source such as 0.0.0.0/0 for an application role.
Rank #3
hostssl rejects non-TLS TCP connections that match the rule. The server still needs a certificate and key configured for PostgreSQL’s SSL support. Ubuntu’s PostgreSQL package includes SSL support and is configured to work with it by default, but you must still select a client verification mode and provide a trusted CA when the certificate is not publicly trusted.
Reload or restart after changes
Use the service command appropriate to the change and package layout. Ubuntu’s documented restart form is:
Do these 3 things before closing this tab:
1Scan for outdated or missing drivers - takes under a minute2Repair Windows errors before they cause bigger problems3Fix the driver behind crashes, sound loss and screen glitchessudo systemctl restart postgresql.serviceA restart is simple but interrupts existing sessions. In a controlled production window, a configuration reload may be sufficient for settings that PostgreSQL can reload; confirm the setting’s reload behavior before choosing that route. Verify the active values after the service returns:
sudo -u postgres psql -c "SHOW listen_addresses;"
sudo -u postgres psql -c "SHOW ssl;"Restrict the VPS and cloud firewalls
Allow inbound TCP port 5432 only from the application’s private subnet or fixed source address. Apply the restriction in every layer present in your hosting setup:
- The provider’s cloud firewall or security group should have one inbound rule for PostgreSQL, with the application network as its source.
- The VPS host firewall should enforce the same source restriction rather than allowing the whole Internet.
- Outbound rules on the application host must permit traffic to the database address and port.
- Remove temporary broad rules created during setup after testing.
A firewall cannot correct a wrong listen_addresses value, and a correct firewall rule cannot bypass a missing pg_hba.conf match. Keep the controls aligned: the address permitted by the firewall should be no broader than the address range named in the PostgreSQL rule.
Configure TLS and certificate verification
Encryption protects database traffic in transit, but encryption alone does not prove that the client reached the intended server. PostgreSQL does not perform certificate verification unless the client is configured to do so.
Free tools Windows power users keep installed
One-click scans. No signup required.
Use sslmode=verify-full for a network connection. It requires encryption, validates the server certificate against a trusted CA, and checks that the requested hostname appears in the certificate. The hostname in the connection parameters must therefore be the certificate’s DNS name, not an unrelated IP address, unless the certificate explicitly contains that IP.
sslmode=require also requires encryption, but it does not provide the same hostname validation. It can be useful as a temporary diagnostic distinction, not as the normal identity check for an Internet or shared-network connection.
Place the issuing CA certificate in a protected path on the application host and provide it with sslrootcert. Keep the file readable by the service account but not writable by the application process. Check the server certificate’s validity period, chain, and hostname, and make sure the VPS clock is correct; an incorrect clock can make an otherwise valid certificate appear expired or not yet valid.
Connect from Python with keyword parameters
Keyword parameters are easy to audit because each setting is visible and can be supplied from environment variables or a secret manager:
import os
import psycopg
conninfo = {
"host": os.environ["PGHOST"],
"port": os.getenv("PGPORT", "5432"),
"dbname": os.environ["PGDATABASE"],
"user": os.environ["PGUSER"],
"password": os.environ["PGAPP_PASSWORD"],
"sslmode": "verify-full",
"sslrootcert": os.environ["PGSSLROOTCERT"],
"connect_timeout": 10,
}
with psycopg.connect(**conninfo) as conn:
with conn.cursor() as cur:
cur.execute("SELECT 1")
print(cur.fetchone())The context manager closes the connection when the block exits and commits or rolls back the transaction according to Psycopg’s normal context behavior. connect_timeout limits how long the attempt waits for a host, which prevents a dead route from holding an application worker indefinitely. Ten seconds is an example starting point; choose a value that fits your network and failure budget.
Use a libpq URI when configuration is naturally a single string
Psycopg accepts the same libpq connection information as a URI. A URI contains the PostgreSQL scheme, credentials, hostname, port, database name, and query parameters such as sslmode, sslrootcert, and connect_timeout. Percent-encode reserved characters in usernames or passwords. Keep the URI in a secret store or inject it at runtime; do not commit it to a repository or paste it into shell history.
Run a first connection test
Start with SELECT 1. It proves that DNS or the supplied address resolved, the TCP path opened, PostgreSQL accepted the session, authentication succeeded, and the client completed its TLS checks. After that succeeds, run an application-specific query using the same role and database, then exercise a transaction and rollback path before deploying traffic.
Keep passwords and connection settings out of source control
libpq recognizes PGHOST, PGPORT, PGDATABASE, PGUSER, and PGSSLMODE. Psycopg also accepts the values directly as keyword arguments, as in the example above. A deployment system can inject non-secret settings this way and deliver the password through a protected secret mechanism.
Do not treat PGPASSWORD as durable secret storage. PostgreSQL’s documentation warns that process environments can be inspected by other users on some systems. Prefer a permissions-restricted libpq password file, a VPS secret store, or the secret facility provided by your deployment platform. Ensure the file is owned by the service account, has restrictive permissions, and is excluded from backups or logs where appropriate.
Never print the complete connection URI, password, or certificate contents in exception messages. Redact connection details in application logs, and rotate the role password if it may have appeared in a repository, process listing, shell history, or diagnostic output.
Best Value
Diagnose failures in a fixed order
Set a finite timeout, reproduce the error once, and classify the result before changing configuration.
| Symptom | Likely layer | Checks and corrective action |
|---|---|---|
| The attempt waits until the timeout | DNS, routing, firewall, or listener | Resolve the database hostname from the application host, verify that the route uses the intended private or public interface, test TCP reachability to port 5432, confirm the cloud and host firewall source rules, and check listen_addresses. |
| “Connection refused” immediately | No process listening at the target address and port | Check PostgreSQL service status, the active listen_addresses, the configured port, and whether the firewall is rejecting or forwarding the request. |
| “No pg_hba.conf entry” | Authentication rule mismatch | Compare the actual source address, database, role, and connection type with the first matching rule. Add a specific hostssl rule for the application network and reload or restart PostgreSQL as required. |
| Password authentication failed | Role, password, or authentication method | Confirm the role name and target database, reset the role password interactively, and ensure the matching rule uses the method you intend, such as scram-sha-256. |
| Certificate verification failed | CA, hostname, certificate chain, or clock | Confirm that sslrootcert points to the correct CA, that the requested hostname is listed in the server certificate, that intermediate certificates are available, and that both systems have accurate time. |
| The test works locally but not remotely | Remote listener or network policy | Local success may use a Unix socket or localhost. Test the private or public address explicitly, then inspect listen_addresses, pg_hba.conf, and both firewall layers. |
| The connection succeeds but requests hang later | Pool exhaustion, long transactions, or server capacity | Close connections deterministically, keep transactions short, check how many sessions the application opens, and introduce a bounded pool rather than creating an unbounded connection per request. |
Turn on Psycopg connection logging only while diagnosing
Psycopg can emit connection-time diagnostics through Python’s standard logging module. Enable a temporary logger at the level needed to identify the failing stage, reproduce the problem, and then disable or reduce verbose output. Connection logs can contain hostnames, database names, or other sensitive operational details, so route them to a protected log destination and redact values before sharing them.
Choose a production connection pattern
One connection per short-lived command
For a migration, health check, or low-frequency command, a context-managed connection is straightforward. Open it immediately before the work, keep the transaction short, and let the context close it even when an exception occurs.
Connection pooling for concurrent services
A web service that opens a new TCP and TLS session for every request can exhaust PostgreSQL’s connection limit and add avoidable latency. Psycopg’s pooling support, provided by the psycopg_pool package, keeps a bounded set of reusable connections. Set pool limits based on the database’s capacity and the number of application workers, and return every borrowed connection promptly, including on error.
Asynchronous applications
If the framework is built around asyncio, use Psycopg’s asynchronous connection interfaces rather than blocking synchronous calls in the event loop. The same host, authentication, TLS, and timeout rules apply. Pool asynchronous connections when concurrent tasks share the database, and cancel or close them cleanly during application shutdown.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Self-managed PostgreSQL versus managed hosting
Running PostgreSQL directly on a VPS gives control over the operating system and database configuration, but it also makes the application owner responsible for more operational work. A managed PostgreSQL service can move some of that work to the provider. Compare the responsibilities before choosing an architecture.
| Concern | Self-managed PostgreSQL on a VPS | Managed PostgreSQL or managed VPS service |
|---|---|---|
| Network isolation | You configure private networking, listen_addresses, pg_hba.conf, and cloud and host firewalls. |
The service may supply private endpoints and security-group controls, but you must still configure client allowlists and credentials. |
| TLS certificates | You maintain the server certificate, key permissions, CA distribution, and renewal process. | The service often manages the server certificate; you still need the provider’s CA or trust instructions and should use hostname verification. |
| Backups and restore | You schedule backups, protect them, test restores, and monitor available storage. | Backup retention and point-in-time recovery may be built in, subject to the service plan and its documented limits. Test a restore rather than assuming it works. |
| Patching | You patch the operating system and PostgreSQL, plan restarts, and verify extensions after upgrades. | The provider schedules much of the maintenance, while you remain responsible for application compatibility and maintenance windows. |
| Pooling and capacity | You size PostgreSQL, workers, and any pooler, then monitor connection counts and memory. | The service may offer pooling or documented limits; configure the application pool within those limits. |
| Observability and failover | You build monitoring, alerting, log retention, replication, and failover procedures. | Metrics and failover options may be included, but verify recovery objectives, regions, and what is actually covered. |
| Total operating cost | Lower service fees can be offset by administration time, backup storage, monitoring, and outage risk. | Higher recurring fees can buy automation and support. Compare the complete operating scope rather than only the server rental. |
Deployment checklist
- Install
psycopg[binary]in the same virtual environment that runs the service. - Confirm Python and PostgreSQL compatibility before locking the deployment.
- Create a dedicated login role and database; do not use the PostgreSQL superuser.
- Store the password in a protected secret mechanism or restricted password file.
- Identify the active
postgresql.confandpg_hba.confinstead of assuming a versioned path. - Bind
listen_addressesonly to required interfaces. - Add a first-match
hostsslrule for the exact database, role, and application CIDR, usingscram-sha-256. - Permit TCP 5432 only from the application network in cloud and host firewalls.
- Use
sslmode=verify-fulland a trustedsslrootcertwhose CA validates the requested hostname. - Set
connect_timeoutand test withSELECT 1. - Close connections with context managers, and use a bounded pool or asynchronous interface when the framework requires it.
- Remove temporary diagnostic logging and broad firewall rules after troubleshooting.
The secure baseline
A working connection is the combination of a reachable listener, a precise pg_hba.conf match, valid least-privilege credentials, a restricted network path, and verified TLS. Psycopg 3 supplies the Python interface, but it cannot compensate for an exposed port, an overbroad role, an unverified certificate, or a password stored in source code. Build and test each layer separately, then keep the same constraints in production through secret rotation, bounded pooling, backups, and monitoring.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
