Sharing /etc/passwd with PostgreSQL

Hi,

Could someone confirm that the ability to share the Linux system
/etc/passwd (or shadow) file with PostgreSQL is definitely defunct?

I've searched the archives and, as far as I can tell, this functionality
was removed after version 7.2 as "few were using it". Well we were using
it and we have thus been stuck with using version 7.2 ever since. We'd
really like to finally upgrade to version 8 but would rather not create
separate passwords for everyone unless we really have no choice. Is this
definitely the case?

Thanks,
Ed

On Fri, Aug 7, 2009 at 8:08 AM, Eamonn Martin<mas01em(at)gold(dot)ac(dot)uk> wrote:
>
> Hi,
>
> Could someone confirm that the ability to share the Linux system /etc/passwd
> (or shadow) file with PostgreSQL is definitely defunct?
>
> I've searched the archives and, as far as I can tell, this functionality was
> removed after version 7.2 as "few were using it".  Well we were using it and
> we have thus been stuck with using version 7.2 ever since.  We'd really like
> to finally upgrade to version 8 but would rather not create separate
> passwords for everyone unless we really have no choice.  Is this definitely
> the case?

Yep, it's definitely the case. You could look into using LDAP for
authentication of both your users on regular unix type accounts and
pgsql users.

Eamonn Martin wrote:
>
> Hi,
>
> Could someone confirm that the ability to share the Linux system
> /etc/passwd (or shadow) file with PostgreSQL is definitely defunct?
>
> I've searched the archives and, as far as I can tell, this
> functionality was removed after version 7.2 as "few were using it".
> Well we were using it and we have thus been stuck with using version
> 7.2 ever since. We'd really like to finally upgrade to version 8
> but would rather not create separate passwords for everyone unless
> we really have no choice. Is this definitely the case?

You can authenticate users with PAM, which amounts more or less to the
same thing.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> You can authenticate users with PAM, which amounts more or less to the
> same thing.

I believe though that using PAM against /etc/shadow would require the
postmaster to run as root. You need some external authentication
server; PAM by itself isn't going to solve it. Maybe LDAP or Kerberos?

regards, tom lane

Tom Lane wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> > You can authenticate users with PAM, which amounts more or less to the
> > same thing.
>
> I believe though that using PAM against /etc/shadow would require the
> postmaster to run as root. You need some external authentication
> server; PAM by itself isn't going to solve it. Maybe LDAP or Kerberos?

At least my system seems to provide a setgid helper program that's
supposed to read /etc/shadow, to work around this problem.

BTW I notice that this does not work unless the client supplies the
password the first time around; psql does not retry. It only works if I
do "psql -W".

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> BTW I notice that this does not work unless the client supplies the
> password the first time around; psql does not retry. It only works if I
> do "psql -W".

Huh, that sounds like a bug someplace. Care to trace through it?

regards, tom lane

Tom Lane wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> > BTW I notice that this does not work unless the client supplies the
> > password the first time around; psql does not retry. It only works if I
> > do "psql -W".
>
> Huh, that sounds like a bug someplace. Care to trace through it?

It had to do with me having a bogus password in .pgpass (so psql was
first trying empty password, then the one in .pgpass, and both failing).
Pilot error. However, I'd say that we ought to give a notice if the
password in .pgpass fails.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> It had to do with me having a bogus password in .pgpass (so psql was
> first trying empty password, then the one in .pgpass, and both failing).
> Pilot error. However, I'd say that we ought to give a notice if the
> password in .pgpass fails.

Can we do something like
ERROR: password authentication failed (using password from .pgpass)
ie, just tack on a comment to the error message?

regards, tom lane

Hi Everybody,

I am interested in following this particular thread,
though, yesterday our mail server went down for a few
hours and I don't have the whole account.

Can somebody please tell me how I could retrieve a
history of this particular thread?

Thank you in advance

Regards,

Tena Sakai
tsakai(at)gallo(dot)ucsf(dot)edu

-----Original Message-----
From: pgsql-admin-owner(at)postgresql(dot)org on behalf of Tom Lane
Sent: Thu 8/20/2009 8:37 AM
To: Alvaro Herrera
Cc: Eamonn Martin; pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] Sharing /etc/passwd with PostgreSQL

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> It had to do with me having a bogus password in .pgpass (so psql was
> first trying empty password, then the one in .pgpass, and both failing).
> Pilot error. However, I'd say that we ought to give a notice if the
> password in .pgpass fails.

Can we do something like
ERROR: password authentication failed (using password from .pgpass)
ie, just tack on a comment to the error message?

regards, tom lane

On Thu, 2009-08-20 at 10:31 -0700, Tena Sakai wrote:
> Hi Everybody,
>
> I am interested in following this particular thread,
> though, yesterday our mail server went down for a few
> hours and I don't have the whole account.
>
> Can somebody please tell me how I could retrieve a
> history of this particular thread?
>
> Thank you in advance

http://archives.postgresql.org/

Joshua D. Drake

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering

On Thu, Aug 20, 2009 at 10:31 AM, Tena Sakai<tsakai(at)gallo(dot)ucsf(dot)edu> wrote:

> Can somebody please tell me how I could retrieve a
> history of this particular thread?

Generally:

http://archives.postgresql.org/

Specifically:

http://archives.postgresql.org/pgsql-admin/2009-08/msg00056.php

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

Many thanks!

Tena Sakai

-----Original Message-----
From: Richard Broersma [mailto:richard(dot)broersma(at)gmail(dot)com]
Sent: Thu 8/20/2009 10:58 AM
To: Tena Sakai
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] Sharing /etc/passwd with PostgreSQL

On Thu, Aug 20, 2009 at 10:31 AM, Tena Sakai<tsakai(at)gallo(dot)ucsf(dot)edu> wrote:

> Can somebody please tell me how I could retrieve a
> history of this particular thread?

Generally:

http://archives.postgresql.org/

Specifically:

http://archives.postgresql.org/pgsql-admin/2009-08/msg00056.php

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

Tom Lane wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> > It had to do with me having a bogus password in .pgpass (so psql was
> > first trying empty password, then the one in .pgpass, and both failing).
> > Pilot error. However, I'd say that we ought to give a notice if the
> > password in .pgpass fails.
>
> Can we do something like
> ERROR: password authentication failed (using password from .pgpass)
> ie, just tack on a comment to the error message?

I looked into that but found it difficult to implement because only
libpq knows about pgpass, while the message is printed by psql.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com
PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do
+ If your life is a hard drive, Christ can be your backup. +