0

I want to run queries on my postgres databases from scripts and whatnot, without having to do what I do currently:

sudo su - postgres
psql -U USER ...etc

This is cumbersome because I have to type 2 different passwords. Is there a way to configure postgres so that it sees that I'm already logged in as a user that matches a postgres user's name, and log in like that?

Carson McManus
  • 101
  • 1

2 Answers2

2

PostgreSQL supports an authentication method called peer, which looks up the username of the connecting user on the OS and in the database, and if they match allows the login. This is only supported for local connection and can be configured in your pga_hba.conf like so:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer

This configuration should allow you to login to PostgreSQL as the database postgres user without a password so long as you are running the script or connection as the postgres user on the Linux side too.

See also PostgreSQL's documentation on the pg_hba.conf file and peer authentication

sjzelle
  • 21
  • 1
0

You can use User Name Maps for peer or ident authentication methods.

Suppose that you have a linux user with name admin. You should have a line in pg_hba.conf file like this;

#local      database  user        auth-method  [auth-options]
local       all       postgres    peer         map=linuxusers

Then you should create a user mapping in pg_ident.conf;

# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
linuxusers      postgres                 postgres
linuxusers      sahap                    postgres
linuxusers      carson                   postgres

Finally reload postgresql.

Sahap Asci
  • 123
  • 2