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

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 information is 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.

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