Thread: worried about PGPASSWORD drop

worried about PGPASSWORD drop

From
Christoph Dalitz
Date:
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

Re: worried about PGPASSWORD drop

From
Tom Lane
Date:
Christoph Dalitz <christoph.dalitz@hs-niederrhein.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

Re: worried about PGPASSWORD drop

From
Bruce Momjian
Date:
Tom Lane wrote:
> Christoph Dalitz <christoph.dalitz@hs-niederrhein.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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: worried about PGPASSWORD drop

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.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

Re: worried about PGPASSWORD drop

From
Bruce Momjian
Date:
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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: worried about PGPASSWORD drop

From
Alvaro Herrera
Date:
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)


Re: worried about PGPASSWORD drop

From
Bruce Momjian
Date:
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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073