FW: [ppa-dev] Severe bug in debian - phppgadmin opens up databases for anyone!

This is a known problem. I just updated the documentation today to
stress that local users have full access to any database by default, and
that initdb -W and changing pg_hba.conf to password/md5 are the best
ways to fix this.

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

Hi guys,

This came across the phpPgAdmin list, and I'm reposting it here in case it
is actually true...? If it is, is it a Postgres or a Debian package issue?

Chris

-----Original Message-----
From: phppgadmin-devel-admin@lists.sourceforge.net
[mailto:phppgadmin-devel-admin@lists.sourceforge.net]On Behalf Of Guilherme
Barile
Sent: Wednesday, 28 November 2001 3:58 AM
To: phpPgAdmin-devel@lists.sourceforge.net
Subject: [ppa-dev] Severe bug in debian - phppgadmin opens up databases for
anyone!

Debian comes with a severe configuration fault in postgresql ... in
pg_hba.conf, it uses TRUST as the default authentication method (from
localhost) ... as phpPgAdmin runs on localhost, anyone can login without a
password.

There are DOZENS of sites out there running without any security! And this
is terrible! If I weren't a very nice person and simply didn't change
anything (I could, as postgres is superuser and I can log as it).
Here's how to fix it (on debian, don't know if any other distribution is
affected):
log in as postgres
run psql
check the pg_shadow table (SELECT * FROM pg_shadow;)
see if everyone has a password (especially user postgres)

After setting all the passwords, edit /etc/postgres/pg_hba.conf to match the
following lines:

local all password
host all 127.0.0.1 255.0.0.0 password

Then it will require a password.
Also, If you wish to block connections from the internet, add this also:

host all 0.0.0.0 0.0.0.0 reject

Please put this on the page or together with PhpPgAdmin's documentation.
(Search google.com with "phppgadmin local:5432" and check for yourself ...
login as postgres and type anything as password!)

Thank you very much for your attention (Please be kind and reply)

Guilherme Barile
Infoage Web Solutions
Sao Paulo - SP - Brazil

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:

This came across the phpPgAdmin list, and I'm reposting it here in case it
is actually true...? If it is, is it a Postgres or a Debian package issue?

The default installation is indeed insecure with respect to other local
users; you don't want to use TRUST auth method on a multi-user box. We
need to document that more prominently. But the default install is not
insecure w.r.t. to outside connections, because it doesn't allow any.
In particular, this advice is horsepucky:

Also, If you wish to block connections from the internet, add this also:
host all 0.0.0.0 0.0.0.0 reject

because that will happen anyway.

regards, tom lane

"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:

This came across the phpPgAdmin list, and I'm reposting it here in case it
is actually true...? If it is, is it a Postgres or a Debian package issue?

The default installation is indeed insecure with respect to other local
users; you don't want to use TRUST auth method on a multi-user box. We
need to document that more prominently. But the default install is not
insecure w.r.t. to outside connections, because it doesn't allow any.
In particular, this advice is horsepucky:

Let me tell you what bothers me about our default install. If some
software installed all its data files in a world-writable directory, we
would consider it a security hole. But because we are Internet-enabled,
and because our insecurity is only local, it seems OK to people.

I am not sure about a solution, but I am shocked we haven't been beaten
up about this more often.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman@candle.pha.pa.us> writes:

... But because we are Internet-enabled,
and because our insecurity is only local, it seems OK to people.

It's not that it's "okay", it's that we haven't got any good
alternatives. Password auth sucks from a convenience point of view
(or even from a possibility point of view, for scripts; don't forget
the changes that you yourself recently applied to guarantee that a
script *cannot* supply a password to psql). Ident auth doesn't work,
or isn't secure, in a lot of cases. Kerberos, well, not a lot to
offer there either. What else do you want to make the default?

regards, tom lane

It's not that it's "okay", it's that we haven't got any good
alternatives. Password auth sucks from a convenience point of view
(or even from a possibility point of view, for scripts; don't forget
the changes that you yourself recently applied to guarantee that a
script *cannot* supply a password to psql). Ident auth doesn't work,
or isn't secure, in a lot of cases. Kerberos, well, not a lot to
offer there either. What else do you want to make the default?

Someone, somewhere, please help us. :-)

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

At 01:08 AM 11/28/01 -0500, Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

... But because we are Internet-enabled,
and because our insecurity is only local, it seems OK to people.

It's not that it's "okay", it's that we haven't got any good
alternatives. Password auth sucks from a convenience point of view
(or even from a possibility point of view, for scripts; don't forget
the changes that you yourself recently applied to guarantee that a
script *cannot* supply a password to psql). Ident auth doesn't work,

Ack. We can't send in passwords to psql anymore? :(

Is there a safe way to send username and password to psql?

Cheerio,
Link.

Lincoln Yeoh wrote:

At 01:08 AM 11/28/01 -0500, Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

... But because we are Internet-enabled,
and because our insecurity is only local, it seems OK to people.

It's not that it's "okay", it's that we haven't got any good
alternatives. Password auth sucks from a convenience point of view
(or even from a possibility point of view, for scripts; don't forget
the changes that you yourself recently applied to guarantee that a
script *cannot* supply a password to psql). Ident auth doesn't work,

Ack. We can't send in passwords to psql anymore? :(

Is there a safe way to send username and password to psql?

smbclient does it via a file which must be 0600, but I don't know if
psql has anything like that.

-----------
Hannu

Lincoln Yeoh <lyeoh@pop.jaring.my> writes:

At 01:08 AM 11/28/01 -0500, Tom Lane wrote:

... Password auth sucks from a convenience point of view
(or even from a possibility point of view, for scripts; don't forget
the changes that you yourself recently applied to guarantee that a
script *cannot* supply a password to psql).

Ack. We can't send in passwords to psql anymore? :(

Well, Bruce, you were the one that was hot to make that /dev/tty change.
Time to defend it.

Is there a safe way to send username and password to psql?

If you want to put those things in a script, you could still do

export PGUSER=whatever
export PGPASSWORD=whatever
psql ...

This would actually work a lot better than other ways for cases such
as doing pg_dumpall, where you'd otherwise need to supply the password
multiple times.

regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

Is there a safe way to send username and password to psql?

If you want to put those things in a script, you could still do

export PGUSER=whatever
export PGPASSWORD=whatever
psql ...

But this way the password ends up in the environment, which on many
systems is visible to other processes/users (via /proc or the 'ps'
command). Might as well use "trust"...

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863

Doug McNaught <doug@wireboard.com> writes:

But this way the password ends up in the environment, which on many
systems is visible to other processes/users (via /proc or the 'ps'
command).

Your *environment* is visible to other users? Geez, what a broken
system ...

regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

Doug McNaught <doug@wireboard.com> writes:

But this way the password ends up in the environment, which on many
systems is visible to other processes/users (via /proc or the 'ps'
command).

Your *environment* is visible to other users? Geez, what a broken
system ...

True on Solaris (/usr/ucb/ps -eax) at least. Other systems too I'm
pretty sure. I thought that Linux let you do it but I just checked
and /proc/<pid>/environ is mode 0400...

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863

Doug McNaught <doug@wireboard.com> writes:

But this way the password ends up in the environment, which on many
systems is visible to other processes/users (via /proc or the 'ps'
command).

Your *environment* is visible to other users? Geez, what a broken
system ...

Try "ps axewww" ? Doesn't work on your platform ?
Works on AIX, Linux?, ...

Andreas

Zeugswetter Andreas SB SD wrote:

Doug McNaught <doug@wireboard.com> writes:

But this way the password ends up in the environment, which on many
systems is visible to other processes/users (via /proc or the 'ps'
command).

Your *environment* is visible to other users? Geez, what a broken
system ...

Try "ps axewww" ? Doesn't work on your platform ?
Works on AIX, Linux?, ...

Linux Debian Unstable (updated 1 week ago).

For a non-root user, only her processes' environment appears.
(and /proc/*/environ permissions are 400, the user being the process owner)

For root, all processes' environment is shown.

Antonio

At 01:08 AM 11/28/01 -0500, Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

... But because we are Internet-enabled,
and because our insecurity is only local, it seems OK to people.

It's not that it's "okay", it's that we haven't got any good
alternatives. Password auth sucks from a convenience point of view
(or even from a possibility point of view, for scripts; don't forget
the changes that you yourself recently applied to guarantee that a
script *cannot* supply a password to psql). Ident auth doesn't work,

Ack. We can't send in passwords to psql anymore? :(

Is there a safe way to send username and password to psql?

The standard way I know of is to use 'expect' and wrap your psql call
around that.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Lincoln Yeoh <lyeoh@pop.jaring.my> writes:

At 01:08 AM 11/28/01 -0500, Tom Lane wrote:

... Password auth sucks from a convenience point of view
(or even from a possibility point of view, for scripts; don't forget
the changes that you yourself recently applied to guarantee that a
script *cannot* supply a password to psql).

Ack. We can't send in passwords to psql anymore? :(

Well, Bruce, you were the one that was hot to make that /dev/tty change.
Time to defend it.

Hey, if people want it back, it is easy to do.

My only goal was to make psql consistent with other applications that
require passwords.

Is there a safe way to send username and password to psql?

If you want to put those things in a script, you could still do

export PGUSER=whatever
export PGPASSWORD=whatever
psql ...

This would actually work a lot better than other ways for cases such
as doing pg_dumpall, where you'd otherwise need to supply the password
multiple times.

What about 'ps -e' that shows all environment variables? This is in
some ways worse than piping the password into psql. At least there was
some chance that they were using 'cat' from a file with the proper
permissions. WIth PGPASSWORD, there is no way to restrict who can see
it via 'ps -e'.

Seems we shouldn't allow PGPASSWORD either.

The idea of allowing the password to be stored in a file with 600
permissions seems quite standard. CVS does this.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Doug McNaught <doug@wireboard.com> writes:

But this way the password ends up in the environment, which on many
systems is visible to other processes/users (via /proc or the 'ps'
command).

Your *environment* is visible to other users? Geez, what a broken
system ...

Try "ps axewww" ? Doesn't work on your platform ?
Works on AIX, Linux?, ...

Works on BSD/OS too, so I assume it works on all the BSD's.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Is there a safe way to send username and password to psql?

The standard way I know of is to use 'expect' and wrap your psql call
around that.

Didn't you break that by making psql read the password from /dev/tty?
Or can 'expect' take control of /dev/tty?

regards, tom lane

Zeugswetter Andreas SB SD wrote:

Doug McNaught <doug@wireboard.com> writes:

But this way the password ends up in the environment, which on many
systems is visible to other processes/users (via /proc or the 'ps'
command).

Your *environment* is visible to other users? Geez, what a broken
system ...

Try "ps axewww" ? Doesn't work on your platform ?
Works on AIX, Linux?, ...

Linux Debian Unstable (updated 1 week ago).

For a non-root user, only her processes' environment appears.
(and /proc/*/environ permissions are 400, the user being the process owner)

For root, all processes' environment is shown.

On BSD/OS, it doesn't matter what user you are. You can see the
environment of all processes.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Is there a safe way to send username and password to psql?

The standard way I know of is to use 'expect' and wrap your psql call
around that.

Didn't you break that by making psql read the password from /dev/tty?
Or can 'expect' take control of /dev/tty?

Expect communicates with the process via pseudo-ttys, so it works fine.
Also, if it can't read /dev/tty, it will read from stdin. At least that
is that standard way to do it.

getpass() docs said:

The getpass() function displays a prompt to, and reads in a password
from, /dev/tty. If this file is not accessible, getpass displays the
prompt on the standard error output and reads from the standard input.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Lincoln Yeoh <lyeoh@pop.jaring.my> writes:

At 01:08 AM 11/28/01 -0500, Tom Lane wrote:

... Password auth sucks from a convenience point of view
(or even from a possibility point of view, for scripts; don't forget
the changes that you yourself recently applied to guarantee that a
script *cannot* supply a password to psql).

Ack. We can't send in passwords to psql anymore? :(

Well, Bruce, you were the one that was hot to make that /dev/tty change.
Time to defend it.

OK, I remember now. The issue was how to handle:

cat file | psql test

In previous releases, you _had_ to have the password as the first line
in file. In the current code, if you are running from a terminal, you
supply the password from the keyboard. If you are running from a batch
job that has no terminal (/dev/tty), you must have the password as the
first line in the file.

People were complaining about the old behavior.

I modeled the changes after the BSD getpass(), which I assume is the
standard behavior on most unixes.

It would be nice to extend .psqlrc to allow storage of passwords, but
that is only read by psql and not by all libpq applications. Not sure
how to handle this.

I will document the security problem with PGPASSWORD and add a TODO item
to remove it in 7.3. Is that OK with everyone?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I will document the security problem with PGPASSWORD and add a TODO item
to remove it in 7.3. Is that OK with everyone?

I don't think we should remove it. Documenting that using it is a
security risk on some platforms seems a good idea, however.

regards, tom lane

Bruce Momjian <pgman@candle.pha.pa.us> writes:

The idea of allowing the password to be stored in a file with 600
permissions seems quite standard. CVS does this.

Seems it would be nice if psql could accept a switch along the lines of
--password-is-in-file filename
and go off to read the password from the named file (which we hope is
secured correctly).

Or take it a little further: what about defining a PGPASSWORDFILE
environment variable that libpq would consult, before or instead of
PGPASSWORD? That would give us the same feature for free across all
libpq-using apps, not only psql. Exposing a file name in the
environment is not a security risk, I hope.

regards, tom lane

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I will document the security problem with PGPASSWORD and add a TODO item
to remove it in 7.3. Is that OK with everyone?

I don't think we should remove it. Documenting that using it is a
security risk on some platforms seems a good idea, however.

OK, new text is:

<envar>PGPASSWORD</envar>
sets the password used if the backend demands password
authentication. This is not recommended because the password can
be read by others using <command>ps -e</command>.

I am unsure if Linux has this problem but it seems most other Unix's do.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman@candle.pha.pa.us> writes:

The idea of allowing the password to be stored in a file with 600
permissions seems quite standard. CVS does this.

Seems it would be nice if psql could accept a switch along the lines of
--password-is-in-file filename
and go off to read the password from the named file (which we hope is
secured correctly).

We can check security of the file if we wish.

Or take it a little further: what about defining a PGPASSWORDFILE
environment variable that libpq would consult, before or instead of
PGPASSWORD? That would give us the same feature for free across all
libpq-using apps, not only psql. Exposing a file name in the
environment is not a security risk, I hope.

Yes, seems like a good idea. Seems we may need both. Either we allow
multiple host/password combinations in the file or we need a psql flag,
but then again, a psql flag doesn't cover the other interfaces. We
could require they use one password file per host.

Added to TODO:

* Add PGPASSWORDFILE password file capability to libpq and psql flag

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman@candle.pha.pa.us> writes:

OK, new text is:

<envar>PGPASSWORD</envar>
sets the password used if the backend demands password
authentication. This is not recommended because the password can
be read by others using <command>ps -e</command>.

Just a nit--the 'e' option is for Berkeley-style ps (/usr/ucb/ps on
Solaris). SysV ps doesn't have an equivalent from what I can see,
(though I may have missed it) and '-e' does something totally
different.

I am unsure if Linux has this problem but it seems most other Unix's do.

Modern versions (of Linux) don't seem to--you can see the env for your
processes but not for others'.

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863

Bruce Momjian <pgman@candle.pha.pa.us> writes:

OK, new text is:

<envar>PGPASSWORD</envar>
sets the password used if the backend demands password
authentication. This is not recommended because the password can
be read by others using <command>ps -e</command>.

Just a nit--the 'e' option is for Berkeley-style ps (/usr/ucb/ps on
Solaris). SysV ps doesn't have an equivalent from what I can see,
(though I may have missed it) and '-e' does something totally
different.

Yes, I debated that one. I wanted to mention the environment issue
without being verbose. I believe 'ps e', without the dash, does show
environment, doesn't it?

I am unsure if Linux has this problem but it seems most other Unix's do.

Modern versions (of Linux) don't seem to--you can see the env for your
processes but not for others'.

If Linux doesn't have this problem, I should mention it is a problem on
_some_ platforms.

New text is:

<envar>PGPASSWORD</envar>
sets the password used if the backend demands password
authentication. This is not recommended because the password can
be read by others using <command>ps e</command> on some
platforms.

I am glad to continue revising it until we are all happy. I throw these
texts out so people can make comments and improve upon it.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman@candle.pha.pa.us> writes:

OK, new text is:

<envar>PGPASSWORD</envar>
sets the password used if the backend demands password
authentication. This is not recommended because the password can
be read by others using <command>ps -e</command>.

Just a nit--the 'e' option is for Berkeley-style ps (/usr/ucb/ps on
Solaris). SysV ps doesn't have an equivalent from what I can see,
(though I may have missed it) and '-e' does something totally
different.

OK, I reread what you said and I see now I am not going to get away with
being brief. Text is now:

<envar>PGPASSWORD</envar>
sets the password used if the backend demands password
authentication. This is not recommended because the password can
be read by others using a <command>ps</command> environment flag
on some platforms.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman@candle.pha.pa.us> writes:

OK, I reread what you said and I see now I am not going to get away with
being brief. Text is now:

<envar>PGPASSWORD</envar>
sets the password used if the backend demands password
authentication. This is not recommended because the password can
be read by others using a <command>ps</command> environment flag
on some platforms.

Looks good to me. I think the 'ps' options are the most annoying
difference between SysV and BSD Unices. Linux 'ps' tries to be both
by acting BSDish if you supply options without a dash, and SysVish if
you use one. Ambitious, but it's bitten me once or twice...

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863

Tom Lane writes:

It's not that it's "okay", it's that we haven't got any good
alternatives. Password auth sucks from a convenience point of view
(or even from a possibility point of view, for scripts; don't forget
the changes that you yourself recently applied to guarantee that a
script *cannot* supply a password to psql). Ident auth doesn't work,
or isn't secure, in a lot of cases. Kerberos, well, not a lot to
offer there either. What else do you want to make the default?

unix_socket_permissions = 0700

--
Peter Eisentraut peter_e@gmx.net

Tom Lane writes:

It's not that it's "okay", it's that we haven't got any good
alternatives. Password auth sucks from a convenience point of view
(or even from a possibility point of view, for scripts; don't forget
the changes that you yourself recently applied to guarantee that a
script *cannot* supply a password to psql). Ident auth doesn't work,
or isn't secure, in a lot of cases. Kerberos, well, not a lot to
offer there either. What else do you want to make the default?

unix_socket_permissions = 0700

Interesting.

This means only the super-user can connect. Hmm, seems like an
interesting idea. You have to _open_ up the database to other users on
your local system. If you are running a server, you are the only person
so there is no downside.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman@candle.pha.pa.us> writes:

OK, I reread what you said and I see now I am not going to get away with
being brief. Text is now:

<envar>PGPASSWORD</envar>
sets the password used if the backend demands password
authentication. This is not recommended because the password can
be read by others using a <command>ps</command> environment flag
on some platforms.

Looks good to me. I think the 'ps' options are the most annoying
difference between SysV and BSD Unices. Linux 'ps' tries to be both
by acting BSDish if you supply options without a dash, and SysVish if
you use one. Ambitious, but it's bitten me once or twice...

I dealt with this in pgmonitor. Got it working by trying different
combinations and checking the result.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian writes:

OK, new text is:

<envar>PGPASSWORD</envar>
sets the password used if the backend demands password
authentication. This is not recommended because the password can
be read by others using <command>ps -e</command>.

I am unsure if Linux has this problem but it seems most other Unix's do.

Please qualify "most".

I thought SysV had an environment switch, but in researching I see it
doesn't. Seems it came from BSD, so it is probably not most.

Current SGML comment says "some platforms".

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian writes:

OK, new text is:

<envar>PGPASSWORD</envar>
sets the password used if the backend demands password
authentication. This is not recommended because the password can
be read by others using <command>ps -e</command>.

I am unsure if Linux has this problem but it seems most other Unix's do.

Please qualify "most".

--
Peter Eisentraut peter_e@gmx.net

On Wed, 28 Nov 2001, Bruce Momjian wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

OK, new text is:

<envar>PGPASSWORD</envar>
sets the password used if the backend demands password
authentication. This is not recommended because the password can
be read by others using <command>ps -e</command>.

Just a nit--the 'e' option is for Berkeley-style ps (/usr/ucb/ps on
Solaris). SysV ps doesn't have an equivalent from what I can see,
(though I may have missed it) and '-e' does something totally
different.

Yes, I debated that one. I wanted to mention the environment issue
without being verbose. I believe 'ps e', without the dash, does show
environment, doesn't it?

Some OSs, I know AIX for example, use SysV options if you have a dash, and
BSD options if you don't. So ps -e does the SysV -e option, while ps e
does the BSD -e option.

Take care,

Bill

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

authentication. This is not recommended because the password can
be read by others using <command>ps -e</command>.

[snip long discussion on the shortcoming of 'ps'...]

Can't we just say something generic like:

"because on some (inferior) platforms other user's
environment variables can be read."?

(Ok, without the "inferior"...mmmm..Linux :)

Greg Sabino Mullane
greg@turnstep.com
PGP Key: 0x14964AC8 200111281755

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iQA/AwUBPAVrmLybkGcUlkrIEQLGiwCg9bMUAXXFSj5z8Cu6aPV+IhyWAPQAniSo
ZP5v6C4w2nJLi0+dbpuXUBBy
=Bl6c
-----END PGP SIGNATURE-----

Try "ps axewww" ? Doesn't work on your platform ?
Works on AIX, Linux?, ...

Linux Debian Unstable (updated 1 week ago).

For a non-root user, only her processes' environment appears.
(and /proc/*/environ permissions are 400, the user being the
process owner)

For root, all processes' environment is shown.

Antonio

I've tried it on FreeBSD and it seems an unprivlileged user can only see his
or her own environmental variables, it doesn't show variables for any other
user.

Chris

On Wed, 28 Nov 2001, Tom Lane wrote:

Is there a safe way to send username and password to psql?

The standard way I know of is to use 'expect' and wrap your psql call
around that.

Didn't you break that by making psql read the password from /dev/tty?
Or can 'expect' take control of /dev/tty?

Expect sets up a pty:

$ tty
/dev/pts/6
$ cat script.exp
spawn tty
expect eof
$ expect -f script.exp
spawn tty
/dev/pts/5

Matthew.

Try "ps axewww" ? Doesn't work on your platform ?
Works on AIX, Linux?, ...

Linux Debian Unstable (updated 1 week ago).

For a non-root user, only her processes' environment appears.
(and /proc/*/environ permissions are 400, the user being the
process owner)

For root, all processes' environment is shown.

Antonio

I've tried it on FreeBSD and it seems an unprivlileged user can only see his
or her own environmental variables, it doesn't show variables for any other
user.

Yes, I see that now. Seems maybe my OS is the only one that isn't fixed
yet. :-(

Anyway, I based my dislike of passwords in the environment on prior
practice of other programs. I knew one of the reasons it isn't used is
because of 'ps', but there is also the issue of the passwords passed to
subprocesses, across 'su' calls, and into 'core' files. It just seems
like a bad practice.

Passwords stored in a file, though not ideal, seems more secure, are
used by cvs and a few other programs, and allow us to define a format
that can be used to store different user/host/password combinations in
the same file, if we wish.

Of course, given that most OS's don't have the 'ps' environment problem,
maybe we have to keep PGPASSWORD around. It is up to the group. Do
people want me to change my wording of the option in the SGML sources?

<envar>PGPASSWORD</envar>
sets the password used if the backend demands password
authentication. This is not recommended because the password can
be read by others using a <command>ps</command> environment flag
on some platforms.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Tom Lane writes:

It's not that it's "okay", it's that we haven't got any good
alternatives. Password auth sucks from a convenience point of view
(or even from a possibility point of view, for scripts; don't forget
the changes that you yourself recently applied to guarantee that a
script *cannot* supply a password to psql). Ident auth doesn't work,
or isn't secure, in a lot of cases. Kerberos, well, not a lot to
offer there either. What else do you want to make the default?

unix_socket_permissions = 0700

Added to TODO:

* Allow secure single-user use without passwords using Unix socket permissions

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026