Search code examples
postgresqlauthenticationuriqgispyqgis

pyQGIS : How to use an URI connection with a pg_service.conf connection type in QGIS 3.16?

I'd like to open a postgreSQL table in QGIS 3.16 with pyGQIS.

I've found the QgsDataSourceUri.setConnection method to do this. The problem for me is that the database password is written in clear text. If I put my code on Github or FramaGit, for example...it would be a terrible security flaw.

I found the authConfigId parameter, which lets me use the QGIS authentication database. It's much more secure, and a part of the solution.

This is my code for now :

self.uri = QgsDataSourceURI()
self.uri.setConnection("192.168.x.xx", "port", "bdd", "user", "passwordIDontWantToGive")

OR

 self.uri = QgsDataSourceURI()
 self.uri.setConnection("192.168.x.xx", "port", "bdd", authConfigId = 'xxx')

But my connection from QGIS to postgreSQL database is made with a .pg_service.conf file. Do you know how to manage an URI connection command with that type of connection?

Thanks for advance

N.B. : I found this way of accessing a postgreSQL table with a service file:

uri = QgsDataSourceUri()
if service:
    uri.setConnection(service, db, username, password, sslmode)

But it seems to require the password in clear text in the command line too...

Solution

  • Just use this code:

    layername = "myLayer"
service = "myService"
geomcol = "geom"

from qgis.core import QgsVectorLayer, QgsDataSourceUri
uri = QgsDataSourceUri()
uri.setEncodedUri(f"service={service}")
uri.setDataSource ("public", layername, geomcol)
vlayer=QgsVectorLayer (uri .uri(False), layername, "postgres")
QgsProject.instance().addMapLayer(vlayer)

    I just did not figure out how to define sslmode, srid and featuretype. You can see the difference if you load the layer once per Code and once from the Browser. After that hover over the Layers. Just the one from dragged from the Browser has this informations.

    Perhaps someone else have a codesnippet.