Обсуждение: psql and security

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

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

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