Thread: I can't seem to put the right combination of magic into the pg_hba and pg_ident files.

I want to accomplish what I would think would be a simple thing. I
want the root user to be able to connect to the postgres database as
user postgres from the local machine without passwords.  Since I am
doing this from a program I don't want to use the su facility.

I have tried a lot of different combinations of things into the
pg_hba.conf and pg_ident.conf but I can't make anything work.

Here is my pg_ident file

pg_map  root    postgres


Here is the line from pg_hba

local   all         all                               ident map=pg_map

What am I doing wrong here?

Tim Uckun <timuckun@gmail.com> writes:
> I want to accomplish what I would think would be a simple thing. I
> want the root user to be able to connect to the postgres database as
> user postgres from the local machine without passwords.  Since I am
> doing this from a program I don't want to use the su facility.

I suspect you are expecting that the map will cause root to be
logged in as postgres without asking for that.  It won't.
What it will do is allow "psql -U postgres" and similar to work.

BTW, one has to wonder why you are using the root account for this
work in the first place.  Wouldn't it be a lot more secure to use
a less privileged account (oh, I don't know, maybe postgres)?

            regards, tom lane

> I suspect you are expecting that the map will cause root to be
> logged in as postgres without asking for that.  It won't.
> What it will do is allow "psql -U postgres" and similar to work.

That's exactly what I am looking to do. In my case I have a script
that runs as root. I want to log in as postgres user from that script
but the script is running as root.

The way I have it set up doesn't permit that. I want to know what I
need to do in order to make that happen.

>
> BTW, one has to wonder why you are using the root account for this
> work in the first place.  Wouldn't it be a lot more secure to use
> a less privileged account (oh, I don't know, maybe postgres)?
>

This script is a part of the initial setup script for the server. It
has to run as root because when it starts running postgres is not
installed and there is no postgres user.

On Sun, Nov 8, 2009 at 9:08 PM, Tim Uckun <timuckun@gmail.com> wrote:
>> I suspect you are expecting that the map will cause root to be
>> logged in as postgres without asking for that.  It won't.
>> What it will do is allow "psql -U postgres" and similar to work.
>
> That's exactly what I am looking to do. In my case I have a script
> that runs as root. I want to log in as postgres user from that script
> but the script is running as root.
>
> The way I have it set up doesn't permit that. I want to know what I
> need to do in order to make that happen.

then say you're postgres in the script with the -U (if you're using psql)

AS ROOT:
psql -U postgres -h remote_db dbname

Note that ident doesn't work so well between machines, so you might
want to look at .pgpass

> then say you're postgres in the script with the -U (if you're using psql)
>
> AS ROOT:
> psql -U postgres -h remote_db dbname
>
> Note that ident doesn't work so well between machines, so you might
> want to look at .pgpass
>

That's what I am trying to get working. In actuality I am using ruby
and using a db library but the concept is the same. I need to log in
as postgres when the script is running as root.  I could trust all
local connections or something but I don't want to do that either.

 When I do a psql -U postgres I get this

psql -U postgres
psql: FATAL:  Ident authentication failed for user "postgres"


Obviously  I need to tell postgres to trust the user root when
connected locally as postgres.

How do I do that?

Tim Uckun wrote:
> psql -U postgres
> psql: FATAL:  Ident authentication failed for user "postgres"
>
>
> Obviously  I need to tell postgres to trust the user root when
> connected locally as postgres.
>
> How do I do that?
>


either create a postgres user named 'root' and give it superuser
privileges, or switch to a different method of authentication for LOCAL
users

>
>
> either create a postgres user named 'root' and give it superuser privileges,

In order to do that I need to connect to the database with my script
which is running under the root account.


> or switch to a different method of authentication for LOCAL users

I am confused. I presumed the proper way to do this was the pg_ident
file. Is this not possible with the pg_ident file?

    Tim Uckun wrote:

> This script is a part of the initial setup script for the server. It
> has to run as root because when it starts running postgres is not
> installed and there is no postgres user.

But afterwards, inside the script, you could use su to temporarily switch to
a less priviledged user:

... commands running as root
su postgres -c 'psql ....'  # running as postgres
... running as root again

And su doesn't ask for a password when it's run by root.

Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org

On Sunday 08 November 2009 10:48:49 pm Tim Uckun wrote:
> > then say you're postgres in the script with the -U (if you're using psql)
> >
> > AS ROOT:
> > psql -U postgres -h remote_db dbname
> >
> > Note that ident doesn't work so well between machines, so you might
> > want to look at .pgpass
>
> That's what I am trying to get working. In actuality I am using ruby
> and using a db library but the concept is the same. I need to log in
> as postgres when the script is running as root.  I could trust all
> local connections or something but I don't want to do that either.
>
>  When I do a psql -U postgres I get this
>
> psql -U postgres
> psql: FATAL:  Ident authentication failed for user "postgres"
>
>
> Obviously  I need to tell postgres to trust the user root when
> connected locally as postgres.
>
> How do I do that?

I think in order to solve this we will need to see at least a skeleton outline
of the steps you are taking in your script. My guess is that what you are
seeing is an out of sequence problem, not a connection problem.


--
Adrian Klaver
aklaver@comcast.net

Tim Uckun wrote:
>> either create a postgres user named 'root' and give it superuser privileges,
>>
>
> In order to do that I need to connect to the database with my script
> which is running under the root account.
>
>

if you are root, use

    su -c "psql -f /path/to/script.sql" postgres




>
>> or switch to a different method of authentication for LOCAL users
>>
>
> I am confused. I presumed the proper way to do this was the pg_ident
> file. Is this not possible with the pg_ident file?
>


authenication type is controlled via the pg_hba.conf file.

frankly, I've never used the pg_ident file, it just seems like it would
add more confusion to things.   But, it appears to use it you need a
map=/mapname/ primitive in your pg_hba.conf

> But afterwards, inside the script, you could use su to temporarily switch to
> a less priviledged user:
>
> ... commands running as root
> su postgres -c 'psql ....'  # running as postgres
> ... running as root again


OK I will try this.

I am very confused about something though. Not one person here has
said anything about how pg_ident works or what I did wrong.  Is
pg_ident deprecated? Is there no way to accomplish this with pg_ident?
 Why has everybody suggested either I don't do what I want/need to do
or that I should do it via the su mechanism?

>
> authenication type is controlled via the pg_hba.conf file.
>
> frankly, I've never used the pg_ident file, it just seems like it would add
> more confusion to things.   But, it appears to use it you need a
> map=/mapname/ primitive in your pg_hba.conf
>

That's why I attempted to do.  I read the documentation, followed the
examples and configured the service in a way I thought would work.
When that didn't work I tried variation after variation. Nothing I did
seemed to work so I thought I would ask the mailing list.

From the responses I gather pg_ident is the wrong way to go. I guess
you are supposed to use su.  In my case (in this particular instance
anyway) su will probably work.  I guess that's good enough for now.

    Tim Uckun wrote:


> I am very confused about something though. Not one person here has
> said anything about how pg_ident works or what I did wrong.  Is
> pg_ident deprecated? Is there no way to accomplish this with pg_ident?

I just tried with 8.4.1. Started with the default configuration, created
data/pg_ident.conf with:
pg_map root postgres
pg_map postgres postgres

Replaced in pg_hba.conf:
<   local   all     all                  trust
by
>   local   all         all                               ident map=pg_map

Restarted the server, and then:
$ su -
# /usr/local/pg84/bin/psql -U postgres
psql (8.4.1)
Type "help" for help.

postgres=#

... it appears to works.

Now if I remove that line in data/pg_ident.conf:
pg_map root postgres
and reload the server and retry, I get the expected rejection:
psql: FATAL:  Ident authentication failed for user "postgres"
and in the server logs:
LOG:  no match in usermap for user "postgres" authenticated as "root"
CONTEXT:  usermap "pg_map"
FATAL:    Ident authentication failed for user "postgres"

That's on ubuntu 9.04, with postgres compiled from source.

> Why has everybody suggested either I don't do what I want/need to do
> or that I should do it via the su mechanism?

On unix systems, it's a standard recommendation not to run anything as root
when it's possible to do otherwise, so we just apply this to psql I guess.

Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org

----- "Daniel Verite" <daniel@manitou-mail.org> wrote:

> Tim Uckun wrote:
>
>
> > I am very confused about something though. Not one person here has
> > said anything about how pg_ident works or what I did wrong.  Is
> > pg_ident deprecated? Is there no way to accomplish this with
> pg_ident?
>
> I just tried with 8.4.1. Started with the default configuration,
> created
> data/pg_ident.conf with:
> pg_map root postgres
> pg_map postgres postgres
>
> Replaced in pg_hba.conf:
> <   local   all     all                  trust
> by
> >   local   all         all                               ident
> map=pg_map
>
> Restarted the server, and then:
> $ su -
> # /usr/local/pg84/bin/psql -U postgres
> psql (8.4.1)
> Type "help" for help.
>
> postgres=#
>
> ... it appears to works.
>
> Now if I remove that line in data/pg_ident.conf:
> pg_map root postgres
> and reload the server and retry, I get the expected rejection:
> psql: FATAL:  Ident authentication failed for user "postgres"
> and in the server logs:
> LOG:  no match in usermap for user "postgres" authenticated as "root"
> CONTEXT:  usermap "pg_map"
> FATAL:    Ident authentication failed for user "postgres"
>
> That's on ubuntu 9.04, with postgres compiled from source.

Which is why I think this is an out of order problem. The Ruby script is trying to connect before the proper
informationis in pg_ident.conf and/or pg_hba.conf. 

>
> > Why has everybody suggested either I don't do what I want/need to
> do
> > or that I should do it via the su mechanism?
>
> On unix systems, it's a standard recommendation not to run anything as
> root
> when it's possible to do otherwise, so we just apply this to psql I
> guess.
>
> Best regards,
> --
> Daniel
> PostgreSQL-powered mail user agent and storage:
> http://www.manitou-mail.org

Adrian Klaver
aklaver@comcast.net

> I just tried with 8.4.1. Started with the default configuration, created
> data/pg_ident.conf with:
> pg_map root postgres
> pg_map postgres postgres
>
> Replaced in pg_hba.conf:
> <   local   all         all                               trust
> by
>>   local   all         all                               ident map=pg_map
>
> Restarted the server, and then:
> $ su -
> # /usr/local/pg84/bin/psql -U postgres
> psql (8.4.1)
> Type "help" for help.
>
> postgres=#
>
> ... it appears to works.
>

I am sad to report that this does not work with ubuntu 9.04 postgres
8.3 installed from the packages.   I have removed everything from
pg_hba.conf except for the one line what says


local    all        all                              ident map=pg_map

My pg_ident /etc/postgres/8.3/main/pg_ident.conf file says

pg_map          root            postgres
pg_map          postgres        postgres


When I restart postgres and type

psql -U postgres
psql: FATAL:  Ident authentication failed for user "postgres"


If I replace the line in pg_hba.conf with this.

local    all        all                              trust


It works.

the "ident_file" setting in postgresql.conf is pointing to the right file.

At this point I am going to go with the trust method and go on with my project.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Tim Uckun <timuckun@gmail.com> writes:
> I am sad to report that this does not work with ubuntu 9.04 postgres
> 8.3 installed from the packages.   I have removed everything from
> pg_hba.conf except for the one line what says

> local    all        all                              ident map=pg_map

That's an 8.4 syntax; 8.3 wants just "ident pg_map".

            regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

> I just tried with 8.4.1. Started with the default configuration, created
> data/pg_ident.conf with:
> pg_map root postgres
> pg_map postgres postgres
>
> Replaced in pg_hba.conf:
> <   local   all         all                               trust
> by
>>   local   all         all                               ident map=pg_map
>
> Restarted the server, and then:
> $ su -
> # /usr/local/pg84/bin/psql -U postgres
> psql (8.4.1)
> Type "help" for help.
>
> postgres=#
>
> ... it appears to works.
>

I am sad to report that this does not work with ubuntu 9.04 postgres
8.3 installed from the packages.   I have removed everything from
pg_hba.conf except for the one line what says


local    all        all                              ident map=pg_map

My pg_ident /etc/postgres/8.3/main/pg_ident.conf file says

pg_map          root            postgres
pg_map          postgres        postgres


When I restart postgres and type

psql -U postgres
psql: FATAL:  Ident authentication failed for user "postgres"


If I replace the line in pg_hba.conf with this.

local    all        all                              trust


It works.

the "ident_file" setting in postgresql.conf is pointing to the right file.

At this point I am going to go with the trust method and go on with my project.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Tim Uckun <timuckun@gmail.com> writes:
> I am sad to report that this does not work with ubuntu 9.04 postgres
> 8.3 installed from the packages.   I have removed everything from
> pg_hba.conf except for the one line what says

> local    all        all                              ident map=pg_map

That's an 8.4 syntax; 8.3 wants just "ident pg_map".

            regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general