0

I have situation that I need to audit a table in Postgres.

But in Postgres there is no Function or method from which I can get the OS User.

Please suggest me how can I get the OS User in Postgres or EDB.

Also, there is an extension in pg_available_extensions table as insert_username.

Can this be used to get the OS User. If yes, how can it be used.

Nikhil Krishnatrey
  • 3
  • 5
  • OS user that runs postmaster? or OS user that connects to database remotely?.. No database engine would give you that – Vao Tsun Aug 21 '15 at 09:31
  • OS user that connects to database remotely. In oracle we can get the same using sys_context. – Nikhil Krishnatrey Aug 21 '15 at 09:35
  • There are some workarounds. Describe better your scenario: which os, how do you execute sql (psql?) etc... – Tom-db Aug 21 '15 at 09:36
  • Suppose I have a table which stores some parameters and table structure like param_table (param_id,param_value, create_user_id,update_user_id). I want to update the create_user_id and update_user_id using a trigger. and user_id would be OS user id. – Nikhil Krishnatrey Aug 21 '15 at 09:40
  • I see. Postgres does not enforce client to provide this info, have a look at http://stackoverflow.com/questions/1934050/postgresql-and-or-sql-server-alternatives-to-oracles-sys-context – Vao Tsun Aug 21 '15 at 09:45
  • Do not add details as comment, add them to the question. How do the clients connect to the server? – Tom-db Aug 21 '15 at 09:45

2 Answers2

1

You cannot, for the general case, because PostgreSQL talks to the client over a network socket. There is no reliable way to determine the operating system user name from a TCP/IP connection from a remote host.

For unix domain sockets it's possible to require that the connecting user be the same as the PostgreSQL user using peer mode authentication. PostgreSQL doesn't expose the client OS username from a unix domain socket to SQL, so if you use another authentication method you can't easily find out their username even if they connected over a unix domain socket.

For loopback TCP/IP connections the host OS might run an identd, though these days almost none do. This might allow PostgreSQL to ask the other end what the username is. It supports this via the ident auth method, but like peer, you can't ask for the OS username from SQL once the user is connected.

For other users you can't reliably find out what user they are on the host OS. You would have to just trust what the client told you, and it's trivial to modify the client to lie.

You can't even get this information using a C extension function, since PostgreSQL doesn't remember it after authentication completes, and for most authentication methods it never asks the client for it in the first place. This would require protocol changes. Since the client can just lie about the host OS username, there doesn't seem to be much point.

If you want to authenticate client identity, SSL client certificates or Kerberos (SSPI, on Windows) authentication would be a better option.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • hello @craig Ringer, Is this possible now as we are in Postgresql 14? Just want to know if they made any changes recently – user1720827 Mar 02 '22 at 16:44
0

You will have to rely on the client providing the information in a session/transaction context variable, e.g. using Postgres' SET command:

SET LOCAL audit.AUDIT_USER = 'whatever-you-like';

This answer to a related question describes how you can implement auditing based on this.

If your client is a Java application, you may also want to look at this demo Gist that implements auditing of INSERTED_BY/UPDATED_BY using the above approach.

blubb
  • 9,510
  • 3
  • 40
  • 82