Postgresql – Setting connection properties (application_name) for Postgres connections

connectivitypostgresql

I want to attach an application name to my connection like PgAdmin does here:
application_name for pgAdmin

(Query in text form: select pid,query,wait_event,state,* from pg_stat_activity;)

My connections are created this way (here in VBA):

Dim cnDB As New ADODB.Connection
cnDB.Open "PostgreSQLDsnHere"

Where that DSN is created by:

  • for 32 bit: go here: %WINDIR%\SysWOW64\odbcad32.exe
  • for 64 bit: go here: %windir%\system32\odbcad32.exe
  • Go to the System DSN tab and enter the information (Data Source,
    Database,Server IP, User Name, Password, Port)

I could not figure out how to add the application_name in this context/connection setup (using a DSN).

I tried adding this in various places in my VBA code, but it didn't work:

cnDB.Properties("ApplicationName") = "testing"

Where do I enter/set the application_name?

I know I can set the setting if I use a standard plain text connection string, but I cannot do that for security reasons. I have to use a DSN.

Non-VBA answers very welcome!

Best Answer

If you can't configure it via the DSN itself, you can always have your connection establishing subroutine set the application name by executing a SQL command on the connection before it hands that connection back to the caller:

set application_name to 'my application name';

In VBScript, this looks like

dim cn 
set cn = CreateObject("ADODB.Connection") 
cn.ConnectionString = "DSN=PostgreSQL35WDsnHere" 
cn.open 
cn.execute("set application_name to 'my_application_name';")
Related Question