How to get OS user in Postgres?

Question

The title says it all. In Oracle, we can easily get the OS user using:

select sys_context( 'userenv', 'os_user' ) from dual; 

I usually use that for logging purposes (know who inserted/updated what and when). I searched for something similar for Postgres (I am new to it) but I could not find. Isn't that possible?

In general, if Postgres does not allow me to get the OS user, does it allow retreiving any kind of information outside the DB itself (e.g.: IP of host, .. etc.)?

Answer 1

PostgreSQL has few functions reporting information on the client environment, listed at https://www.postgresql.org/docs/current/static/functions-info.html

Concerning the remote IP address, it provides inet_client_addr().

Concerning the client username, I don't think it has this information at all, because it's not passed at connection time, unless it happens to be the same as the database user name, but the backend can't know that in general.

If the SQL functions need that information,it could be passed up front by your applications in the form of a SET statement. Current versions of PostgreSQL allow to instantiate any namespace for custom variables.

Example with version 9.3:

test=> set session.osuser to 'daniel';
SET

test=> show session.osuser;
 session.osuser 
----------------
 daniel
(1 row)

test=> select current_setting('session.osuser');
 current_setting 
-----------------
 daniel
(1 row)

Answer 2

If you are using a Linux OS you can basically map the users to the PostgreSQL user.

• Linux user: root
• Postgresql User: Postgres

We'll map root user to Postgres user.

Edit the pg_ident.conf file and add the below line.

mapping_root root postgres

• MAPNAME: A friendly name for your mapping.
• SYSTEM-USERNAME: Name of the system user that wants to map.
• PG-USERNAME: Name of the PostgreSQL user that the Linux user should connect.

Edit the pg_hba.conf file to enable this mapping

TYPE DATABASE USER ADDRESS METHOD

local all postgres peer
# -- add mapping configs. 
local all postgres peer map=mapping_root

Restart your PostgreSQL or Reload it.

you should enter the below command to connect PostgreSQL via root user

root@sqladmin#psql
postgres=>

Here is my blog: http://www.sqlgossip.com/map-linux-users-to-postgres-user/