Re: worried about PGPASSWORD drop

In the TODO list on http://developer.postgresql.org/todo.php,
I found the following entry:

- Remove PGPASSWORD because it is insecure on some OS's, in 7.4

Why?

I see the following problems:
- This will make psql no longer usable in scripts as PGPASSWORD is
currently the *only* way to pass a password to psql
- The alternative (a new command line option for password) is much more insecure,
as then the password is readable by everybody from the process table

In case PGPASSWORD is dropped, there should be a working way to use psql
in scripts. Maybe you could manage to make the following code work:

psql -U user dbname <<EOF
password
/* SQL-Statements */
EOF

(For some strange reason this works with Oracle's sqlplus, but not with psql)

Christoph Dalitz

Christoph Dalitz <christoph(dot)dalitz(at)hs-niederrhein(dot)de> writes:
> In the TODO list on http://developer.postgresql.org/todo.php,
> I found the following entry:
> - Remove PGPASSWORD because it is insecure on some OS's, in 7.4
> Why?

I don't agree with removing the feature either, since it's perfectly
useful on many OSes. However your assumption:

> - The alternative (a new command line option for password)

is completely wrong; that is not the alternative being introduced.
See http://candle.pha.pa.us/main/writings/pgsql/sgml/libpq-envars.html

regards, tom lane

Tom Lane wrote:
> Christoph Dalitz <christoph(dot)dalitz(at)hs-niederrhein(dot)de> writes:
> > In the TODO list on http://developer.postgresql.org/todo.php,
> > I found the following entry:
> > - Remove PGPASSWORD because it is insecure on some OS's, in 7.4
> > Why?
>
> I don't agree with removing the feature either, since it's perfectly
> useful on many OSes. However your assumption:

The reason for the suggested removal is that we don't have a way of
knowing with OS's are secure, and which are not. If we could determine
which OS's were secure, and enable it only on those, it would be OK to
keep it.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> The reason for the suggested removal is that we don't have a way of
> knowing with OS's are secure, and which are not. If we could determine
> which OS's were secure, and enable it only on those, it would be OK to
> keep it.

It is not our job to dictate security policy to users. Even on a
platform where environment variables are insecure, the user might be
willing to use PGPASSWORD. For example, suppose it's a laptop with
only one user, connecting via psql to a remote server that demands
passwords. PGPASSWORD could be a perfectly convenient and safe
solution.

We should deprecate it, explain exactly why it's deprecated (which the
current docs fail to do), and leave it up to the user to decide whether
it's safe to use in his context.

If you want to put in security restrictions that are actually useful,
where is the code to verify that PGPASSWORDFILE points at a
non-world-readable file? That needs to be there now, not later, or
we'll have people moaning about backward compatibility when we finally
do plug that hole.

regards, tom lane

Tom Lane wrote:
> It is not our job to dictate security policy to users. Even on a
> platform where environment variables are insecure, the user might be
> willing to use PGPASSWORD. For example, suppose it's a laptop with
> only one user, connecting via psql to a remote server that demands
> passwords. PGPASSWORD could be a perfectly convenient and safe
> solution.

Good point.

> We should deprecate it, explain exactly why it's deprecated (which the
> current docs fail to do), and leave it up to the user to decide whether
> it's safe to use in his context.
>
> If you want to put in security restrictions that are actually useful,
> where is the code to verify that PGPASSWORDFILE points at a
> non-world-readable file? That needs to be there now, not later, or
> we'll have people moaning about backward compatibility when we finally
> do plug that hole.

Agreed.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian dijo:

> Tom Lane wrote:

> > If you want to put in security restrictions that are actually useful,
> > where is the code to verify that PGPASSWORDFILE points at a
> > non-world-readable file? That needs to be there now, not later, or
> > we'll have people moaning about backward compatibility when we finally
> > do plug that hole.
>
> Agreed.

Point taken, will look into it later.

--
Alvaro Herrera (<alvherre[a]atentus.com>)
"La realidad se compone de muchos sueños, todos ellos diferentes,
pero en cierto aspecto, parecidos..." (Yo, hablando de sueños eróticos)

Alvaro Herrera wrote:
> Bruce Momjian dijo:
>
> > Tom Lane wrote:
>
> > > If you want to put in security restrictions that are actually useful,
> > > where is the code to verify that PGPASSWORDFILE points at a
> > > non-world-readable file? That needs to be there now, not later, or
> > > we'll have people moaning about backward compatibility when we finally
> > > do plug that hole.
> >
> > Agreed.
>
> Point taken, will look into it later.

Here is some code from postmaster.c that may help:

if (stat(checkdir, &stat_buf) == -1)
{
if (errno == ENOENT)
elog(FATAL, "data directory %s was not found", checkdir);
else
elog(FATAL, "could not read permissions of directory %s: %m",
checkdir);
}

if (stat_buf.st_mode & (S_IRWXG | S_IRWXO))
elog(FATAL, "data directory %s has group or world access; permissions should be u=rwx (0700)",
checkdir);

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

En Wed, 28 Aug 2002 17:33:34 -0400 (EDT)
Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> escribió:

> Alvaro Herrera wrote:
> > Bruce Momjian dijo:
> >
> > > Tom Lane wrote:
> >
> > > > If you want to put in security restrictions that are actually useful,
> > > > where is the code to verify that PGPASSWORDFILE points at a
> > > > non-world-readable file? That needs to be there now, not later, or
> > > > we'll have people moaning about backward compatibility when we finally
> > > > do plug that hole.
> > >
> > > Agreed.
> >
> > Point taken, will look into it later.
>
> Here is some code from postmaster.c that may help:

Thank you. Patch attached. Note that it also checks group access; I think
that is desired as well.

--
Alvaro Herrera (<alvherre[a]atentus.com>)
"Cuando mañana llegue pelearemos segun lo que mañana exija" (Mowgli)

Your patch has been added to the PostgreSQL unapplied patches list at:

http://candle.pha.pa.us/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

---------------------------------------------------------------------------

Alvaro Herrera wrote:
> En Wed, 28 Aug 2002 17:33:34 -0400 (EDT)
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> escribi?:
>
> > Alvaro Herrera wrote:
> > > Bruce Momjian dijo:
> > >
> > > > Tom Lane wrote:
> > >
> > > > > If you want to put in security restrictions that are actually useful,
> > > > > where is the code to verify that PGPASSWORDFILE points at a
> > > > > non-world-readable file? That needs to be there now, not later, or
> > > > > we'll have people moaning about backward compatibility when we finally
> > > > > do plug that hole.
> > > >
> > > > Agreed.
> > >
> > > Point taken, will look into it later.
> >
> > Here is some code from postmaster.c that may help:
>
> Thank you. Patch attached. Note that it also checks group access; I think
> that is desired as well.
>
> --
> Alvaro Herrera (<alvherre[a]atentus.com>)
> "Cuando ma?ana llegue pelearemos segun lo que ma?ana exija" (Mowgli)

[ Attachment, skipping... ]

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Wed, 28 Aug 2002, Alvaro Herrera wrote:

> En Wed, 28 Aug 2002 17:33:34 -0400 (EDT)
>
> Thank you. Patch attached. Note that it also checks group access; I think
> that is desired as well.

+
+ /* If password file is insecure, alert the user and ignore it. */
+ if (stat_buf.st_mode & (S_IRWXG | S_IRWXO))

Should there also be a S_IFREG check to make sure no one is trying any other
tricks? I'm not sure of what an exploit would be but for the sake of paranoia
it seems a cheap test.

I take it no one wants to start checking directory tree permissions etc.

--
Nigel J. Andrews
Director

---
Logictree Systems Limited
Computer Consultants

Nigel J. Andrews wrote:
> On Wed, 28 Aug 2002, Alvaro Herrera wrote:
>
> > En Wed, 28 Aug 2002 17:33:34 -0400 (EDT)
> >
> > Thank you. Patch attached. Note that it also checks group access; I think
> > that is desired as well.
>
> +
> + /* If password file is insecure, alert the user and ignore it. */
> + if (stat_buf.st_mode & (S_IRWXG | S_IRWXO))
>
>
> Should there also be a S_IFREG check to make sure no one is trying any other
> tricks? I'm not sure of what an exploit would be but for the sake of paranoia
> it seems a cheap test.
>
> I take it no one wants to start checking directory tree permissions etc.

They may want a symlink to point to somewhere else. I can see that. In
fact, I can see settings for Unix group sharing a password file but I am
not going to suggest loosening the group permissions until someone says
they want that.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Thu, 29 Aug 2002, Bruce Momjian wrote:

> Nigel J. Andrews wrote:
> > On Wed, 28 Aug 2002, Alvaro Herrera wrote:
> >
> > > En Wed, 28 Aug 2002 17:33:34 -0400 (EDT)
> > >
> > > Thank you. Patch attached. Note that it also checks group access; I think
> > > that is desired as well.
> >
> > +
> > + /* If password file is insecure, alert the user and ignore it. */
> > + if (stat_buf.st_mode & (S_IRWXG | S_IRWXO))
> >
> >
> > Should there also be a S_IFREG check to make sure no one is trying any other
> > tricks? I'm not sure of what an exploit would be but for the sake of paranoia
> > it seems a cheap test.
> >
> > I take it no one wants to start checking directory tree permissions etc.
>
> They may want a symlink to point to somewhere else. I can see that. In
> fact, I can see settings for Unix group sharing a password file but I am
> not going to suggest loosening the group permissions until someone says
> they want that.

Doesn't stat() resolve all symlinks?

I must admit it's not something I've check but I thought it went through until
it found a non symlink.

I'm probably just being too paranoid about pipes etc. though.

I'd wait and see about the group permissions as well. I can't really see the
need myself. I'm not very imaginative at times though. May be in a teaching
environment.

--
Nigel J. Andrews

Patch applied. Thanks.

---------------------------------------------------------------------------

Alvaro Herrera wrote:
> En Wed, 28 Aug 2002 17:33:34 -0400 (EDT)
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> escribi?:
>
> > Alvaro Herrera wrote:
> > > Bruce Momjian dijo:
> > >
> > > > Tom Lane wrote:
> > >
> > > > > If you want to put in security restrictions that are actually useful,
> > > > > where is the code to verify that PGPASSWORDFILE points at a
> > > > > non-world-readable file? That needs to be there now, not later, or
> > > > > we'll have people moaning about backward compatibility when we finally
> > > > > do plug that hole.
> > > >
> > > > Agreed.
> > >
> > > Point taken, will look into it later.
> >
> > Here is some code from postmaster.c that may help:
>
> Thank you. Patch attached. Note that it also checks group access; I think
> that is desired as well.
>
> --
> Alvaro Herrera (<alvherre[a]atentus.com>)
> "Cuando ma?ana llegue pelearemos segun lo que ma?ana exija" (Mowgli)

[ Attachment, skipping... ]

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Nigel J. Andrews wrote:
> > They may want a symlink to point to somewhere else. I can see that. In
> > fact, I can see settings for Unix group sharing a password file but I am
> > not going to suggest loosening the group permissions until someone says
> > they want that.
>
> Doesn't stat() resolve all symlinks?

Yep, only lstat() sees symlinks.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073