1

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.)?

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)
0

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/

  • Then after the mapping, how do I get the OS username? – Hawk Jan 2 '18 at 7:00
  • This is for map the users only, You can't actually get the names. From the example, I have mapped root user to postgres. So if I just entered psql it'll allow to login as postgres user. – Bhuvanesh Jan 2 '18 at 7:03

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.