2

I have several C# applications where I have to ensure that there is only one instance of the application running against a given schema.

I am migrating from Oracle to PostgreSQL 10.

In Oracle, I would run the following query:

select sid, username, machine, program, logon_time
  from sys.v_$session v
 where status not in ('KILLED')
   and upper('\' || machine) not like upper('%\' || :MACHINE)
   and upper(username) = upper(:USERNAME)
   and upper(program) = upper(:PROGRAM)

I use Environment.MachineName in my applications to get the value for the MACHINE parameter.

I have rewritten the query for PostgreSQL as follows:

select pid, user, client_hostname, application_name, backend_start
  from pg_stat_activity
 where upper(state) not in ('KILLED')
   and upper('\\' || client_hostname) not like upper('%\\' || :MACHINE)
   and upper(user) = upper(:USERNAME)
   and upper(application_name) = upper(:APPNAME)

I have added the ApplicationName to my connection string and enabled the log_hostname setting on my database to populate the application_name and client_hostname columns in pg_stat_activity, respectively.

The problem that I have run into is that, when running the application on a virtual machine, the client_address column shows the IP address of the physical machine that is hosting the VM, not the IP address of the virtual machine, and therefore the client_hostname is wrong also.

Am I doing something wrong? Is there a way to get my application to push the correct information to PostgreSQL when connecting?

  • 1
    Do your VMs have direct access to the network or are they behind a NAT handled by the physical machine? In the later case, PostgreSQL can only see the IP address of the NAT host, not what is behind. This is not in fact a PostgreSQL problem but only a network one. If your VMs have direct network access, any service they query should see their own specific IP address. – Patrick Mevzek Sep 19 '18 at 17:20
  • @PatrickMevzek please make that an answer. – Evan Carroll Sep 19 '18 at 17:33
1

I have added the ApplicationName to my connection string

I believe that the machine name on Oracle is voluntarily reported by the client to the server, which is why it works even behind NAT.

PostgreSQL only has one self-reported field, so I think that what you will have to do is stuff both "program name" and "machine name" into the ApplicationName field with some kind of delimiter between them.

  • This is exactly what I ended up doing. And I wrote a query using regex to separate the delimited contents of the application_name column return it in as application_name and client_hostname, respectively. – Welton v3.58 Sep 26 '18 at 14:50
1

The client_addr is really the IP address at the other end of the TCP/IP connection. If the VM has direct access to the network, hence its own IP address, then that is what PostgreSQL should see.

But if the VM has no direct global network access and instead goes through some NAT (typically on the host) or any application gateway, then PostgreSQL will only be able to see, at the network level, the IP address of this last component that sits in front of the VM.

Also, client_hostname is a name, derived from the IP address. Resolutions may change over time, so it may be preferable to log instead (or in addition) the client_addr. client_hostname is filled out also only if log_hostname is switched on in the configuration file.

Also remember that connection could happen over IPv6 (maybe not now but in the future, depending on your configuration and setup of course), so make sure not to have false assumptions about the content (format and length) of client_addr. If you need to store it somewhere else for example, take care to use the PostgreSQL dedicated type for that, aka inet which will be superior than text.

Note in passing this document: Logging Recommendations for Internet-Facing Servers which boils down to the fact that nowadays, specifically for IPv4, due to the scarcity of this space and how it is managed in various places, it makes sense to log not only the client IP address but also the client TCP port number.

This would be client_port in PostgreSQL.

  • The VM is running on a remote machine, connected to the network via a VPN, so I believe that it does go through some NAT somewhere along the way. (Networking is not my strong suite.) I opted for a more "brute force" solution. ;) – Welton v3.58 Sep 26 '18 at 14:54
  • @Weltonv3.58 Indeed if you use the VPN you are in a similar case: all your IP traffic looks like coming out from the VPN "host" not from any host "behind" it and using it. So the server you connect to, be it PostgreSQL or any other TCP/IP stuff, will see the VPN IP address and nothing below that. – Patrick Mevzek Sep 26 '18 at 16:08

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.