4

PostGIS offers a view called pg_stat_activity, where you can see all the active connections to the database. This view includes the interesting column application_name. When a connection comes from a QGIS instance, the text "QGIS" appears there.

An application can send this parameter to PostGIS in the connection string, and I suppose that QGIS do it so. The connection string looks than more or less like that:

host=... port=... dbname=... user=... password=... application_name=QGIS

I would like to change this value in QGIS to include the username (that I will get from the operating system). Instead of "QGIS", I would like to see something like "QGIS for user1". Is there a was to do that, may be by changing the connection string using a pyQGIS script or any other solution?

Edit:

I just tried following thing: using the python console I made use of the function setEncodedUri which seems allow all possible connection parameters. But it doesn't work: The application_name appears in the layer properties in QGIS but in PostGIS I still just see "QGIS" and nothing more. This are the lines I was using for this test:

    uri = QgsDataSourceURI()
    uri.setEncodedUri("host=...&port=...&dbname=...&user=...&password=...&application_name=QGIS for user1")
    uri.setDataSource("MySchema", "MyTable", "wkb_geometry", "")
    vlayer = QgsVectorLayer(uri.uri(), "MyLayerName", "postgres")
    QgsMapLayerRegistry.instance().addMapLayer(vlayer)

Edit2: I had a look in the source code of QGIS 2.8: the trick with setEncodedUri can't work, because the code calls SET application_name='QGIS' just after opening the connection and that overwrites my setting!

I'm still looking for a working solution...

Edit3

It possible to set the environment variable PGOPTIONS to something like "-c application_name=QGISuser1" or even easier the environment varaible PGAPPNAME (see fallback_application_name) before starting QGIS. It's used to change some postgres configuration variables for the current session. I tried it with over variables and it worked. But as QGIS overwrites the value of application_name after opening the connection, it doesn't work for this variable. And I'm now thinking, that there are probably no ways to do what I want with the current version of QGIS.

  • Can you use the environment variables from QGIS? In the settings menu - Options - System (at the bottom) I haven't tried it yet but it's just a thought... – Max Jun 11 '15 at 14:33
  • @Max: thanks for your suggestion. I just had a look, but no, there doesn't seems to be an environment variable for the application_name. – Name Jun 11 '15 at 14:55
  • @Name is this a PostGIS issue or a PostgreSQL issue? Just asking because dba.stackexchange might be a place to ask this... – DPSSpatial Jun 11 '15 at 15:00
  • @mapBaker: this is more a QGIS issue. – Name Jun 11 '15 at 15:18
2

A solution with following restrictions:

  • QGIS will be started through a batch file (I'm on Windows).
  • The user will be working with a predefined QGIS project (as the project will be opened through the batch file).
  • Username and password for the PostGIS connection are be stored in the QGIS project.
  • All PostGIS layers of the project must be in the same PostGIS database.
  • This isn't useful for cases where application_name is used in triggers, as it will only change in a parallel connection to the main QGIS connection.

The batch file set the environment variable PGAPPNAME, start QGIS for the predefined project and run a pyQGIS script that will open a second connection to PostGIS:

    SET PGAPPNAME=QGIS(%USERNAME%)
    qgis.bat --project MyPredefinedProject.qgs --code ScriptForSecondConnection.py

The pyQGIS script gets its connection parameters from the first PostGIS layer it can find and open a second connection to PostGIS. This connection remains opened until exit. This is only a partial solution: for the second connection the application_name is now something like "QGIS(user1)", but for the main connection from QGIS to PostGIS it's still "QGIS" and nothing more.

    from PyQt4.QtCore import *
    from PyQt4.QtGui import *
    from PyQt4.QtSql import *
    from qgis.core import *

    ###### functions ##########
    def get_first_postgres_layer():
        for layer in QgsMapLayerRegistry.instance().mapLayers().viewvalues():
            provider = layer.dataProvider()
            if provider.name() == 'postgres':
                return layer
                break
        return None
    ###########################

    # get a PostGIS layer
    layer = get_first_postgres_layer()
    if layer is None:
        raise RuntimeError('No PostGIS layer found')

    # Reuse the layer parameters to open a new connection to PostGIS
    uri = QgsDataSourceURI(layer.dataProvider().dataSourceUri())
    db = QSqlDatabase.addDatabase('QPSQL')
    if not db.isValid():
        raise RuntimeError('Database problem')
    db.setHostName(uri.host())
    db.setDatabaseName(uri.database())
    db.setPort(int(uri.port()))
    db.setUserName(uri.username())
    db.setPassword(uri.password())
    if not db.open():
        raise RuntimeError('Connection to PostGIS failed')

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.