The reliable way to connect Python to PostgreSQL on a VPS is to combine four controls: a reachable PostgreSQL listener, a narrowly scoped pg_hba.conf rule, valid application credentials, and a firewall that permits the database port only from the application network. In Python, install Psycopg 3, call psycopg.connect() with keyword parameters or a libpq URI, and use TLS with sslmode=verify-full for a network connection. The walkthrough below covers an Ubuntu self-managed server, least-privilege roles, certificate validation, secrets, testing, pooling, and a systematic response to timeouts and authentication errors.
Need a server to try this on? Antihost offers Linux VPS plans with NVMe storage, full root access and a 14-day free trial.
What must be true before Python can connect
A connection from a Python process on one machine to PostgreSQL on another is a chain of dependencies. The client must resolve the database hostname, route packets to the server, reach the PostgreSQL listening address and port, pass the VPS or cloud firewall, match an allowed line in pg_hba.conf, authenticate as a role that can access the requested database, and complete TLS validation when encryption is required. A failure at any earlier layer can look like a database problem, so test the layers in that order.
As an Amazon Associate I earn from qualifying purchases.
Recommended Free Tools
- Client: a supported Python runtime and Psycopg 3.
- Server: PostgreSQL running and listening on an address reachable from the application host.
- Access policy: a
pg_hba.confrule limited to the application source network, database, role, and authentication method. - Network policy: host and cloud firewalls allowing the PostgreSQL port only from that source network.
- Identity and encryption: a dedicated role, a protected password, and a trusted server certificate whose name matches the hostname used by Python.
For a single VPS running both processes, prefer a Unix-domain socket or loopback address and keep TCP exposure disabled unless another host actually needs it. For separate application and database VPSs, use a private network address whenever the provider offers one; do not expose PostgreSQL to the entire internet merely because the client is remote.
Install Psycopg 3 in an isolated Python environment
Psycopg 3 is the current adapter covered by the project documentation. Its binary extra is the shortest installation path on supported systems because it supplies the client components needed by the wheel.
#1 Best Overall
- [Ultra-Lightweight Ear Buds Designed for Small Ears] Each earbud weighs only 3.7g and features a compact, ergonomic in-ear design made especially for small ears. Secure, low-profile, and comfortable for workouts, all-day wear or overnight sleeping.The ultra-slim profile sits flush in your ear with no hard edges poking or rubbing, so you can even sleep on your side without discomfort.
- [Immersive Stereo Sound with TOZO OrigX Technology] TOZO OrigX tuning delivers clear vocals, balanced mids, and natural stereo sound for music, podcasts, and videos.
- [Long Battery Life for Daily Use] Get up to 7 hours of playtime on a single charge, with up to 32 hours total using the charging case—ideal for workdays, commuting, and extended listening sessions.
- [Bluetooth 5.3 & Stable Connection] Bluetooth 5.3 provides fast pairing, stable wireless performance, and reduced dropouts as you move around home or office.
- [Deep Bass with Clear Vocals] High-performance drivers produce punchy bass while keeping vocals clean and detailed for everyday listening.
python3 -m venv .venv. .venv/bin/activatepython -m pip install --upgrade pippython -m pip install 'psycopg[binary]'
Run the commands as the same deployment user that will start the application. Confirm the Python and PostgreSQL versions against Psycopg’s support table before pinning a production image. The binary package is convenient for a quick setup; teams that need system-managed client libraries or a custom build should follow Psycopg’s alternative installation instructions instead of mixing libraries ad hoc.
Verify that the interpreter sees the adapter:
python -c 'import psycopg; print(psycopg.__version__)'Keep the resulting dependency in your normal lock or requirements workflow so a rebuild does not silently select a different adapter version.
Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Scan for outdated or missing drivers - takes under a minute3Repair Windows errors before they cause bigger problemsCreate a dedicated role and database
Do not run the application as PostgreSQL’s superuser. A dedicated login role limits the damage from an application bug or leaked credential and makes it possible to rotate access without changing administrative accounts.
- Log in locally as the operating-system account that administers PostgreSQL:
sudo -u postgres psql. - Create a database owned by an application role, or create the role first and assign ownership. The command-line tools can prompt for the password without placing it in shell history:
sudo -u postgres createuser --pwprompt app_userandsudo -u postgres createdb --owner=app_user app_db. - Grant only the privileges the application needs. For an existing database, connect as an administrator and issue grants for specific schemas and tables rather than granting broad superuser or database-owner rights.
- Store the password in a protected password file or a secrets manager. Restrict the file to the service account and rotate the value when staff, hosts, or deployment systems change.
At minimum, record the exact database name, role name, server hostname, port, and certificate authority file needed by the application. Those values belong in deployment configuration, not in a committed source file.
Configure PostgreSQL to accept only the required network
On Ubuntu, the main configuration directory follows /etc/postgresql/<version>/main. The version component varies with the package installed on the VPS, so inspect the directory rather than copying a version number from another server.
Set the listening address
In postgresql.conf, set listen_addresses to the private address needed by the topology. A value of localhost is sufficient when the application is on the same machine. A specific private interface is safer than a wildcard when the database has more than one interface.
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Rank #2
- 【IP68 Waterproof Multifunctinal Smart Watch】Torjalph fitness activity tracker smart watch is IP68 waterproof technology ,you can wear it when swimming,handwashing,it's rain-proof which means that you perform multiple exercise modes outside without worrying about sweat or rain corroding your fitness smart watch,and this watch equipped with a 230 mAh large capacity battery magnetic charging ,and this sports activity tracker watch can be used 5-7 days If fully charged.
- 【Larger 1.83'' Full Screen Watch & Multi-dial Selection】1.83 inch full HD color touch screen smart watch for men / women provides excellent 240*284 resolution image quality ,This Bluetooth smart watch offers a better touch experience and remarkable immersion and ,more than 200 watch faces for you to choose from App"Da Fit ", you can change the dial style according to your mood to wear keeping the world on your wrist
- 【Accurate Activity Tracker Smart Watch 】sports watch activity tracker uses sweat and dust resistant breathable silicone strap, our Fitness Tracker Smart Watch for Android iOS Phones supports 12 sports modes,walking,running,bicycle,swimming,badminton,basketball,football,rope skipping. This bluetooth calls smart watch for men women is lightweight and stylish, bringing more convenient and relaxing sports experience while also providing more protection.
- 【Your 24 Hour Personal Daily Health Monitor】Fitness watch for women men full touch screen IP67 waterproof bluetooth smartwatch for Android iOS Phones with heart rate blood pressure monitor multifunction Andriod smart watch with steps counter sleep monitor sports fitness watch, monitoring blood oxygen saturation allows you to understand your physical condition in real time to track fitness progress ,to know your health status in time.
- 【More Functions Bluetooth Call Smart Watch】This fitness activity smart watch full screen for iPhone and Android phones includes a bunch of new features including:The bluetooth smart watch can hang up the phone, receive and display WhatsAPP /Text /Facebook /Twitter /Messenger reminder.Support sedentary reminder, alarm clock, stopwatch, timer,weather information,music control, remote camera control compatible with Android 5.0 and above or iOS 9.0 and above smartphones.
listen_addresses = '10.20.0.10'The address must exist on the database VPS. If PostgreSQL is listening only on loopback, a remote application will time out or receive a connection refusal even when the firewall is open. PostgreSQL normally uses port 5432; retain that default only if it fits your policy, and make the selected port consistent in the firewall and client configuration.
Match the application in pg_hba.conf
pg_hba.conf is evaluated from top to bottom. The first matching line determines the authentication method, so a broad earlier rule can defeat a carefully written rule later in the file. For a private application subnet, a TLS-required entry can look like this:
hostssl app_db app_user 10.20.0.0/24 scram-sha-256The fields mean TCP over SSL, the database, the role, the allowed source CIDR, and SCRAM password authentication. Replace the example CIDR with the smallest network that contains the application hosts. If one fixed application address is enough, use a single-host CIDR such as 10.20.0.21/32. Do not use 0.0.0.0/0 for an application login.
Use hostssl when non-TLS TCP connections must be rejected. The role’s password must be stored in a form compatible with scram-sha-256; reset it after changing an older cluster’s password-encryption policy if necessary. A plain host rule can allow both encrypted and unencrypted negotiation depending on the client settings, so it is not an equivalent substitute for a TLS-only policy.
Reload or restart after editing
After changing the listener or access rules, restart the Ubuntu service so the active configuration is unambiguous:
sudo systemctl restart postgresql.serviceUse the service status and PostgreSQL logs immediately if the restart fails. A syntax error in either configuration file prevents the intended policy from loading. Once the service is up, confirm locally which addresses and port are listening before testing from another host.
Rank #3
- [Low Noise High Fidelity] New bee guitar cord with 22AWG oxygen-free-copper center conductors provides enhanced signal transfer, the PVC insulation material and the high-density stranded copper double shield provide noise rejection for quiet operation
- [Ideal for Choice] New bee 1/4 straight and right angle metal connectors, ensures a secure connection in any instrument, pedal, or amp jack. L shape great fro on stage guitar stands
- [Wide Compatible] Suitable for mixer, power amplifier, microphone, effector, decoder, equalizer, electoronic organ, electric guitar, electric bass and other electronic equipments. Widely used in the installation of smart home, karaoke speaker, professional stage audio, etc
- [Easy Disassemble] Making incidental repairs quick and easy! Very nice, flexible cable
- [Dont Hesite to Order] 24 hours customer services and professional technology team are standing by
Restrict the VPS and cloud firewalls
Open the database port only to the application’s private address or private network. Apply the restriction in every layer that exists: the host firewall, the VPS provider’s security group, and any network ACL between the machines. A firewall rule permitting the port from the public internet undermines a narrow pg_hba.conf rule by exposing the server to unnecessary scanning and denial-of-service traffic.
Use TLS and validate the server identity
Encryption protects credentials and queries in transit, but encryption alone does not prove that the client reached your PostgreSQL server. PostgreSQL’s SSL documentation notes that certificate verification is not performed by default unless it is configured.
Free tools Windows power users keep installed
One-click scans. No signup required.
Choose the right sslmode
| Setting | What it enforces | Important limitation |
|---|---|---|
verify-full |
Requires encryption, validates the certificate chain against a trusted CA, and checks that the requested hostname matches the certificate. | The hostname in the connection settings must be the name covered by the certificate. |
require |
Requires an encrypted connection. | It does not provide the same CA and hostname validation as verify-full. |
For a remote application, use verify-full. Install the CA certificate that signed the server certificate, set sslrootcert to its protected path, and connect using the DNS name listed in the certificate’s subject alternative names. Connecting by an IP address when the certificate names only a DNS host produces a hostname-validation error; fix the name or issue a certificate that covers the address you actually use.
Keep the CA file readable by the service account but not writable by the application process. Check the server certificate’s validity dates and the VPS clock when diagnosing certificate failures. A correct CA file cannot validate an expired certificate, an incomplete chain, or a name mismatch.
Keep credentials out of source control and process listings
libpq, which Psycopg uses for connection options, recognizes environment variables including PGHOST, PGPORT, PGDATABASE, PGUSER, and PGSSLMODE. PostgreSQL documentation specifically warns that PGPASSWORD can be visible to other users through process inspection. Do not treat it as durable secret management.
Prefer one of these arrangements:
- Inject the password at service start from a VPS secret store and pass it directly to the process without writing it to the repository.
- Use a libpq password file with strict permissions, normally readable only by the account running the application.
- Use a deployment platform’s secret manager and map the secret to an application-specific environment variable such as
PGAPP_PASSWORD.
Do not log the complete connection URI, exception objects that contain credentials, or environment dumps. Rotate a password immediately if it appears in a commit, build log, shell history, or support transcript.
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Rank #4
- Digital Family Calendar: BSIMB digital calendar wall touch screen puts an end to your chaotic life. Designed for busy families, the digital calendar keeps you organised and in step with your family. Assign different colours and tasks to each family member to make family scheduling easier and more intuitive.
- Easy Setup and Auto-Sync: Plug in, connect to WLAN and auto-syn your electric calendar in minutes. Digital wall calendar compatible with Google calendar, iCloud calendar, Outlook calendar, Cozi calendar and Yahoo calendar. Add events directly on the device or through the ecalendar app. Electronic calendar stays current whether you're at work or traveling—no manual updates needed.
- All-In-One Home Assistant: Smart calendar builds habits and encourage independence with personalised chores. Display daily meal plan to reduce the daily ‘What's for dinner?’ stress. To-do lists keep track of everything you want. Turning chores into valuable achievements with stars and rewards, making life easier and more enjoyable.
- Manage Anytime Anywhere: BSIMB electronic calendar for family stay connected with the powerful ecalendar app. Access and manage your digital wall calendar anytime, anywhere with free features like stars & rewards, photo screensaver, chore charts, meals plans, to-do lists etc. Electronic planner paid features like Magic Import tailor advanced tools for users with special needs.
- Stay in Touch with Loved Ones: Share photos or videos from your phone to electronic wall calendar with app, instantly update your life moment, and keep in touch with your family, especially those who are far away; you can also surprise your smart calendar digital wall by sending him a wish card; it's also great for keeping tech-illiterate elders in touch with the youngsters.
Connect with Psycopg keyword parameters
Keyword parameters make each security decision visible and avoid the quoting and percent-encoding mistakes common in URI strings. This example uses a ten-second per-host connection limit and a custom password variable so the standard PGPASSWORD warning is avoided:
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 outer context manager closes the connection deterministically and commits or rolls back the transaction according to Psycopg’s context-manager behavior. The cursor context manager releases the cursor after the query. connect_timeout limits how long a connection attempt waits for each host; it prevents a web worker from hanging indefinitely when routing or a firewall is broken.
If your configuration system already supplies a libpq URI, Psycopg accepts it directly:
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
with psycopg.connect(os.environ['DATABASE_URL']) as conn:
with conn.cursor() as cur:
cur.execute('SELECT current_database(), current_user')
print(cur.fetchone())URI passwords must be percent-encoded when they contain reserved characters. Avoid putting the password in the URI when a secret can be supplied separately, because URIs are easy to leak through logs, process arguments, and crash reports.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Perform a controlled first test
Run the test from the same VPS user, virtual environment, and service environment that will run the application. A successful test from an administrator’s shell does not prove that the service can read the CA file or secret.
- Print non-secret values such as the resolved hostname, port, database, and username. Do not print the password or full URI.
- Resolve the hostname from the application VPS and confirm it returns the intended private address.
- Check TCP reachability to the selected port with the operating system’s diagnostic utility. A failure here is a routing or firewall issue, not a password issue.
- Run the Psycopg
SELECT 1program and then querycurrent_database()andcurrent_userto verify identity. - Confirm that the connection used TLS and the expected certificate policy in your PostgreSQL and application logs, without retaining verbose credentials.
Once this smoke test passes, exercise the application’s real migration and query paths with a non-production database before changing traffic routing.
Understand transactions and close connections predictably
Psycopg starts a transaction when a statement requires one. A connection that remains open after a request can therefore hold locks or an old snapshot even when the application performed only a read. Use a context manager for short units of work, commit deliberately after writes, and roll back on exceptions before returning a connection to a pool.
For a request handler, acquire a connection, execute the smallest practical transaction, and release it before doing network calls or lengthy application work. Never keep a database transaction open while waiting for an external API, a user upload, or a background job.
Add pooling only when concurrency requires it
Opening a new TCP and TLS session for every request adds latency and can exhaust PostgreSQL’s process and connection limits. For concurrent synchronous applications, Psycopg’s pooling support is available through the psycopg_pool package. Size the pool from measured concurrency and the database’s connection budget; a pool per worker can multiply quickly.
An asyncio-based framework should use Psycopg’s asynchronous connections or an async pool rather than blocking synchronous calls in the event loop. Keep checkout timeouts finite, return connections in a finally path or context manager, and discard connections that fail health checks. Pooling does not replace transactions, least privilege, TLS, or firewall rules; it only manages reuse.
Troubleshoot by symptom and layer
| Symptom | Most likely layer | What to check next |
|---|---|---|
| Connection waits until the timeout expires | DNS, routing, firewall, or listener address | Resolve the hostname, test the port from the application host, inspect cloud and host firewall rules, and verify listen_addresses. |
| Connection is refused immediately | No process is listening on that address and port, or an active reject rule exists | Check PostgreSQL service status, the configured port, and local listening sockets. |
| “No pg_hba.conf entry” | The first matching access rule does not allow this source, database, role, or TLS mode | Review rules from top to bottom, correct the source CIDR, and use hostssl when TLS is required. |
| Password authentication failed | Wrong role, database, password, or authentication rule | Confirm the exact role and database, reset the role password, and verify that the intended SCRAM rule is the one that matches. |
| Certificate verify or hostname error | CA path, certificate chain, certificate dates, hostname, or VPS clock | Check sslrootcert, the chain and expiry, the hostname covered by the certificate, and system time. |
| Works in a shell but fails under the service | Different environment, permissions, working directory, or secret visibility | Run the test as the service account and inspect only non-secret environment values and file permissions. |
Use a finite timeout while investigating
Set connect_timeout to a value such as 10 during diagnosis. This turns an ambiguous hang into a repeatable failure and prevents each web worker from consuming a slot indefinitely. Remove the setting only if you have a deliberate reason to accept a longer wait.
PC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Crashes, 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 minuteInspect Psycopg connection logging temporarily
Psycopg can emit connection diagnostics through Python’s standard logging module. Enable it only for a short troubleshooting window, reproduce one failure, protect the logs, and then return the logger to its normal level:
import logging
logging.basicConfig(level=logging.DEBUG)
logging.getLogger('psycopg').setLevel(logging.DEBUG)Verbose connection logs can reveal hostnames, options, and operational details. Redact them before sharing and disable debug logging after the cause is identified.
Self-managed VPS or managed PostgreSQL?
Running PostgreSQL yourself gives direct control over the operating system, network boundaries, extensions, and maintenance schedule. It also leaves you responsible for patching, backups, restore tests, certificate renewal, monitoring, connection limits, failover design, and incident response. A managed PostgreSQL service can reduce those chores, but you must verify its private-network support, TLS certificate process, backup retention, restore workflow, pooling options, observability, failover behavior, region, and total cost before adopting it.
Regardless of hosting model, the Python side remains the same: use a dedicated role, validate the server certificate, set a finite timeout, close or pool connections deliberately, and keep secrets outside source control. A managed endpoint is not automatically private or correctly authenticated.
Quick Recap
Production hardening checklist
- Use a private application-to-database route where available; otherwise allow only the application source CIDR through every firewall.
- Set
listen_addressesto the required interface instead of a wildcard. - Place a specific
hostsslandscram-sha-256rule before any broader rule, and review the file from top to bottom. - Use a dedicated non-superuser role and grant only required database and schema privileges.
- Connect with
sslmode=verify-full, a trusted CA file, and the certificate’s hostname. - Keep passwords in a protected password file or secret manager; do not rely on
PGPASSWORDfor long-lived deployment secrets. - Set
connect_timeout, use context managers, and keep transactions short. - Choose pool sizes with the total number of application workers in mind.
- Test a restore, certificate rotation, password rotation, and firewall change before an outage forces you to improvise.
- Monitor failed logins, connection saturation, long transactions, disk space, and replication or backup health if those features are part of your design.
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.
