Connect to a PostGIS Database in PyQGIS

The connection is the part everyone writes once and copies forever, which is exactly why it is worth getting right the first time. A hand-assembled connection string works until a password contains a space, a table lives outside the public schema, or the script is committed to a repository with credentials still in it. QgsDataSourceUri handles the quoting, and QGIS's authentication database handles the secret.

This recipe belongs to PostGIS and Database Workflows in PyQGIS. It covers building the URI, storing credentials properly, reusing connections the user already configured in the QGIS browser, and confirming the layer really loaded before a script starts iterating it.

Three ways to supply credentials, only two of them safeThe top route puts a literal password in the script and is marked unsafe because the secret ends up in version control. The middle route stores the credential in the QGIS authentication database and passes only its identifier. The bottom route resolves a connection the user already saved in the QGIS browser by name. All three converge on the same valid vector layer.Where the password lives is the whole decisionsetConnection(..., "hunter2")secret is now in your repositorysetAuthConfigId("a1b2c3d")encrypted in the QGIS auth databasecreateConnection("cityworks")reuses what the user configuredQgsVectorLayer(uri, name, "postgres")identical downstream codecheck isValid() before using itA plugin should prefer the saved connection; a scheduled job should prefer the auth database

Prerequisites

  • QGIS 3.34 LTR (bundled Python 3.12) or newer, with the PostgreSQL provider — it ships with every standard QGIS package.
  • A reachable PostGIS database and a role that can at least SELECT from the target table.
  • The table's schema, geometry column and primary key column. If you do not know them, \d public.parcels in psql prints all three.
  • Comfort with running code in the QGIS Python console, or a configured standalone script environment.

Build the URI and load the layer

from qgis.core import QgsDataSourceUri, QgsVectorLayer, QgsProject

uri = QgsDataSourceUri()
uri.setConnection("db.example.org", "5432", "cityworks", "gis_reader", "")
uri.setAuthConfigId("a1b2c3d")
uri.setDataSource("public", "parcels", "geom", "", "gid")

layer = QgsVectorLayer(uri.uri(False), "Parcels", "postgres")

if not layer.isValid():
    raise RuntimeError(f"connection failed: {layer.dataProvider().error().message()}")

QgsProject.instance().addMapLayer(layer)
print(layer.featureCount(), "features,", layer.crs().authid())

Breakdown: The five arguments of setDataSource() are schema, table, geometry column, an SQL WHERE clause and the key column, in that order. The empty fourth argument means "no filter"; the fifth is the one people omit, and omitting it is what produces a read-only layer. uri.uri(False) renders the connection string without expanding the stored password into it, which matters because that string is what appears in the project file. error().message() from the provider is far more useful than the bare isValid() boolean — it distinguishes "host unreachable" from "relation does not exist" from "permission denied".

The connection is lazy. QgsVectorLayer opens a connection immediately to read the table's metadata, but no features move until something asks for them, so a layer for a hundred-million-row table costs the same to create as one for ten rows.

Store the credential instead of writing it down

The auth config id in the snippet above refers to an entry in QGIS's authentication database — an encrypted store protected by a master password. Create one interactively in Settings → Options → Authentication, or from Python:

from qgis.core import QgsApplication, QgsAuthMethodConfig

auth_manager = QgsApplication.authManager()

config = QgsAuthMethodConfig()
config.setName("cityworks reader")
config.setMethod("Basic")
config.setConfig("username", "gis_reader")
config.setConfig("password", "the-actual-secret")

auth_manager.storeAuthenticationConfig(config)
print("store this id in your script:", config.id())

Breakdown: storeAuthenticationConfig() writes the encrypted entry and populates config.id() with a generated seven-character identifier — that id, not the password, is what your script and your project files carry. The Basic method covers username-and-password; QGIS also ships methods for PKI certificates and identity stores. Run this once, interactively, on the machine that will execute the job; the master password unlocks the store, and on a server that means either an interactive first run or QGIS_AUTH_PASSWORD_FILE pointing at a file with restrictive permissions.

What the authentication database actually doesA script file contains only an authentication configuration identifier. At connection time the provider asks the authentication manager to resolve that identifier, the manager decrypts the stored username and password using the master password, and the credential is injected into the connection sent to the PostGIS server. The script and the project file never contain the secret.The secret never enters your source filescript.pyauthcfg=a1b2c3dsafe to commitauthentication databaseqgis-auth.db, encryptedgis_reader••••••••••providerinjects credentialat connect timePostGISserverA master password unlocks the store — on a server, supply it with QGIS_AUTH_PASSWORD_FILEProject files then travel without carrying anyone's credentials

Reuse a connection the user already saved

Inside a plugin, asking the user to re-enter connection details they already configured in the QGIS browser is a small insult. The provider connection API resolves them by name.

from qgis.core import QgsProviderRegistry, QgsVectorLayer

metadata = QgsProviderRegistry.instance().providerMetadata("postgres")

print(sorted(metadata.connections().keys()))          # names in the browser panel

connection = metadata.createConnection("cityworks")
for table in connection.tables("public"):
    print(table.tableName(), table.geometryColumnTypes())

layer = QgsVectorLayer(connection.tableUri("public", "parcels"), "Parcels", "postgres")

Breakdown: metadata.connections() returns the saved connections as a dictionary keyed by the name shown in the browser, so a plugin can populate a combo box from it — see populating widgets from project state for the UI side. tables() returns table properties including geometry type and CRS, which lets you filter the list to spatial tables before showing it. tableUri() builds a correct data source string for a table on that connection, including its stored credentials, so you never assemble the URI by hand. The same three calls work against a GeoPackage connection by asking for the ogr provider metadata instead.

Confirm the connection before trusting it

An invalid layer is not an exception; it is an object that answers featureCount() with -1 and iterates zero features. Scripts that skip the check report success on an empty result.

def load_postgis_table(uri, name):
    layer = QgsVectorLayer(uri.uri(False), name, "postgres")
    if not layer.isValid():
        raise RuntimeError(f"{name}: {layer.dataProvider().error().message()}")
    if layer.dataProvider().pkAttributeIndexes() == []:
        raise RuntimeError(f"{name}: no usable key column — the layer will be read-only")
    return layer

Breakdown: pkAttributeIndexes() returns the field indexes the provider treats as the identity of a row. An empty list is the machine-readable form of "you forgot the key column", and catching it at load time turns a mysterious failure hundreds of lines later into an immediate, specific error. Raising rather than returning None keeps the failure loud in a scheduled job, which matters for the unattended error-handling patterns.

Survive a network that is not perfect

A connection that works from your desk over a wired network behaves differently from one crossing a VPN at two in the morning. Two settings and one habit cover almost all of it.

uri.setParam("connect_timeout", "10")
uri.setParam("keepalives", "1")
uri.setParam("keepalives_idle", "30")
uri.setParam("application_name", "nightly_export")

Breakdown: connect_timeout turns an unreachable host from an indefinite hang into a failure after ten seconds, which is the difference between a job that alerts and a job that is still running at nine the next morning. The keepalive parameters make the client send periodic probes so an idle connection is not silently dropped by a firewall — the classic cause of "the first query works and the second one fails". application_name is free and repays itself the first time a database administrator asks which client is holding a long-running query: it appears in pg_stat_activity next to your statement.

For a job that must not fail on a single blip, retry the connection but never blindly retry the write:

import time

def connect_with_retry(uri, name, attempts=3, delay=5):
    for attempt in range(1, attempts + 1):
        layer = QgsVectorLayer(uri.uri(False), name, "postgres")
        if layer.isValid():
            return layer
        if attempt < attempts:
            time.sleep(delay * attempt)
    raise RuntimeError(f"{name}: could not connect after {attempts} attempts")

Breakdown: The delay grows with each attempt, so a database restarting gets time to come back rather than being hit three times in three seconds. Retrying a read is safe because it has no side effects; retrying a write without knowing whether the first attempt committed is how duplicate rows appear, which is why the write path uses a transaction and a unique constraint instead — see Append Features to a PostGIS Table in PyQGIS.

Bounded connection attempts against an unbounded oneWithout a connect timeout, a single attempt against an unreachable host runs until the job is killed hours later. With a ten second timeout and growing retry delays, three attempts complete within about half a minute and the job then fails with a specific error a scheduler can act on.A hang is a worse failure than an errorno timeoutone attempt, still waiting when the shift starts∞boundedtry 1 · 10 swait 5 s · try 2wait 10 s · try 3exit 1, with a reasonRetry the connection, never the uncommitted write — that is what the unique constraint is forapplication_name makes the connection identifiable in pg_stat_activity

QGIS version compatibility

The examples target QGIS 3.34 LTR (Python 3.12).

QGIS versionPythonNotes
3.22 LTR3.9QgsDataSourceUri identical. The provider connection API exists but tableUri() is missing — build the URI manually.
3.28 LTR3.9Full connection API including tableUri() and createVectorTable().
3.34 LTR3.12Baseline for this page.
3.40 / 3.443.12Adds connection-level sqlOptions helpers; everything shown here is unchanged.

QgsDataSourceUri has been stable across the whole 3.x series, so connection code written today keeps working. The authentication API is equally stable; what changes between systems is where qgis-auth.db lives, which follows the QGIS profile directory.

Troubleshooting

  • isValid() is False and the message mentions authentication. The master password was never entered in this session. In a desktop session QGIS prompts; in a headless run set QGIS_AUTH_PASSWORD_FILE before creating QgsApplication.
  • "relation does not exist". The role can connect but cannot see the table — usually a schema that is not on the role's search_path, or a genuine permissions gap. Name the schema explicitly rather than relying on the default.
  • The layer loads but is read-only. No key column was supplied, or the nominated column is not unique. Check pkAttributeIndexes().
  • Connecting works in QGIS but not from a standalone script. The standalone process has its own profile and therefore its own authentication database. Point it at the same profile with QgsApplication.setPrefixPath() and the --profiles-path equivalent, or export the credential to the server profile.
  • Timeouts on a slow link. Append connect_timeout=10 through uri.setParam("connect_timeout", "10") so a dead host fails in seconds rather than hanging the job.
  • Everything works, but the CRS is wrong. The provider reads the SRID from the geometry column's constraint. A table registered as SRID 0 loads with an unknown CRS — fix the table, or set the layer CRS explicitly as described in Handling Missing CRS in PyQGIS.

Conclusion

A robust PostGIS connection is four decisions: build the URI with QgsDataSourceUri rather than string formatting, name the key column so the layer is writable, keep the password in the authentication database, and check isValid() before a single feature is read. Inside a plugin, prefer resolving a saved connection by name so the user's existing configuration is reused rather than duplicated.

Frequently Asked Questions

Do I need psycopg2 installed? No. The postgres provider is compiled into QGIS and speaks the PostgreSQL protocol itself. A separate driver is only worth adding when you need database features that have nothing to do with layers.

How do I connect over SSL? Set the mode on the URI with uri.setParam("sslmode", "require"), or use an authentication configuration of the PKI type when the server expects client certificates. verify-full is the mode to prefer when the certificate chain is properly set up.

Can I list the tables without loading any of them? Yes — metadata.createConnection(name).tables(schema) returns table properties, including geometry type and CRS, without creating a single layer. It is the right way to populate a picker in a plugin dialog.

Why does my project file contain the username? The rendered URI keeps the username so QGIS can prompt for the matching password. Only the password is withheld when an authentication configuration is used. If even the username is sensitive, store it in the auth entry and leave the URI's user field empty.

Is one connection per layer wasteful? The provider pools connections per unique connection string, so ten layers from the same database share sockets. Ten layers built from slightly different strings — one with a port, one without — will not pool, which is another reason to build the URI in one helper function.