psql and security

Tatsuo Ishii:

As you can see, psql reconnect as any user if the password is same as
foo. Of course this is due to the careless password setting, but I
think it's better to prompt ANY TIME the user tries to switch to
another user. Comments?

Does postgres have a concept of a 'root' user? Then the password should
only be prompted when one isn't root; ie. adopt Unix semantics.

Cheers,

Colin

Tatsuo Ishii writes:

As you can see, psql reconnect as any user if the password is same as
foo. Of course this is due to the careless password setting, but I
think it's better to prompt ANY TIME the user tries to switch to
another user.

I'm not sure. A few users have voiced concerns about this before, but we
have no count of the users that might enjoy this convenience. ;-)

Basically, the attack scenario here is that if you have a psql running and
leave your terminal, someone else can come in and get access to any other
database that you might have access to, without knowing your password.
But given a running psql, figuring out the password isn't so hard (running
a debugger or inducing a core dump would be likely options), and
concluding that this password is valid for all databases is trivial since
that's the default setup.

--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter

As you can see, psql reconnect as any user if the password is same

as

foo. Of course this is due to the careless password setting, but I
think it's better to prompt ANY TIME the user tries to switch to
another user.

I'm not sure. A few users have voiced concerns about this before, but

we

have no count of the users that might enjoy this convenience. ;-)

Basically, the attack scenario here is that if you have a psql running

and

leave your terminal, someone else can come in and get access to any

other

database that you might have access to, without knowing your password.
But given a running psql, figuring out the password isn't so hard

(running

a debugger or inducing a core dump would be likely options), and
concluding that this password is valid for all databases is trivial

since

that's the default setup.

This feature was added to conveniently let an already connected user
switch to another database. Imho you could distinguish the exact case at
hand,
where a new user was specified and prompt for a new password.

Andreas

Tatsuo Ishii <t-ishii@sra.co.jp> writes:

As you can see, psql reconnect as any user if the password is same as
foo. Of course this is due to the careless password setting, but I
think it's better to prompt ANY TIME the user tries to switch to
another user. Comments?

Yeah, I agree. Looks like a simple change in dbconnect():

/*
* Use old password if no new one given (if you didn't have an old
* one, fine)
*/
if (!pwparam && oldconn)
pwparam = PQpass(oldconn);

to

/*
* Use old password (if any) if no new one given and we are
* reconnecting as same user
*/
if (!pwparam && oldconn && PQuser(oldconn) && userparam &&
strcmp(PQuser(oldconn), userparam) == 0)
pwparam = PQpass(oldconn);

regards, tom lane

"Colin 't Hart" <cthart@yahoo.com> writes:

Does postgres have a concept of a 'root' user? Then the password should
only be prompted when one isn't root; ie. adopt Unix semantics.

Can't really do that in psql's \c, since it's establishing a whole new
connection; there is no possibility for superuserness on the old
connection to provide any relaxation of the check.

However, see SET SESSION AUTHORIZATION, which does what you're thinking
of within the context of a single connection.

regards, tom lane

Peter Eisentraut <peter_e@gmx.net> writes:

concluding that this password is valid for all databases is trivial since
that's the default setup.

No, I think you're missing the point --- we're concerned about
reconnecting as a different user, not reconnecting to a different
database. The issue is that psql will silently try to use user A's
password to authenticate as user B. While one would hope that this
fails, it doesn't seem like a good idea even to try it.

regards, tom lane

Tom Lane writes:

No, I think you're missing the point --- we're concerned about
reconnecting as a different user, not reconnecting to a different
database.

Oh, of course. I agree, in that case the password shouldn't be reused.

--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter

Patch applied. Thanks Tatsuo and Tom.

Tatsuo Ishii <t-ishii@sra.co.jp> writes:

As you can see, psql reconnect as any user if the password is same as
foo. Of course this is due to the careless password setting, but I
think it's better to prompt ANY TIME the user tries to switch to
another user. Comments?

Yeah, I agree. Looks like a simple change in dbconnect():

/*
* Use old password if no new one given (if you didn't have an old
* one, fine)
*/
if (!pwparam && oldconn)
pwparam = PQpass(oldconn);

to

/*
* Use old password (if any) if no new one given and we are
* reconnecting as same user
*/
if (!pwparam && oldconn && PQuser(oldconn) && userparam &&
strcmp(PQuser(oldconn), userparam) == 0)
pwparam = PQpass(oldconn);

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

-- 
  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