Hi,

This is not a real security issue but it seems not very appropreate
behavior for me.

$ psql -U foo test
Password: XXX

Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

test=> \c - postgres
You are now connected as new user postgres

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?
--
Tatsuo Ishii

Fri, 21 Sep 2001 19:56:27 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
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



Fri, 21 Sep 2001 14:08:44 +0200
"Colin 't Hart" <cthart@yahoo.com>
Re: [HACKERS] psql and security
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


Fri, 21 Sep 2001 15:16:37 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: psql and security
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

Fri, 21 Sep 2001 10:29:38 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: psql and security 
"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

Fri, 21 Sep 2001 10:32:33 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] psql and security 
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

Fri, 21 Sep 2001 10:36:38 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: psql and security 
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


Fri, 21 Sep 2001 20:08:45 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: psql and security 

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

Thu, 11 Oct 2001 12:54:22 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: psql and security