Обсуждение: Password encryption method

Hi,

looking at the source code I find out that this works:

  sandbox=# create role joe login password 'verysecret';
  CREATE ROLE
  sandbox=# create function validate_user_8_1(text,text) returns boolean immutable language 'sql' as $$ select
'md5'||md5($2||$1)= rolpassword from pg_authid where rolname=$1; $$; 
  CREATE FUNCTION
  sandbox=# select validate_user_8_1('joe','verysecret');
   validate_user_8_1
  -------------------
   t
  (1 Zeile)

May I rely on this in future versions or are there more
sophisticated ways to do it?

Thanks in advance,

Bertram


--
Bertram Scharpf
Stuttgart, Deutschland/Germany
http://www.bertram-scharpf.de

On Fri, Jan 19, 2007 at 09:31:49AM +0100, Bertram Scharpf wrote:
> Hi,
>
> looking at the source code I find out that this works:

<snip>

> May I rely on this in future versions or are there more
> sophisticated ways to do it?

Umm, how much more sophisticated do you want? It's more sophicticated
than a standard UNIX password file, for example. For password
authentication the server either needs to be able to verify the
password supplied by the user, and you have the same information the
server does, so you can do it too.

Only superusers have access to pg_authid anyway, and they can already
login as anybody.

If you don't like it, don't use password authentication, there are a
number of other methods.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

On Fri, Jan 19, 2007 at 09:31:49 +0100,
  Bertram Scharpf <lists@bertram-scharpf.de> wrote:
> Hi,
>
> looking at the source code I find out that this works:
>
>   sandbox=# create role joe login password 'verysecret';
>   CREATE ROLE
>   sandbox=# create function validate_user_8_1(text,text) returns boolean immutable language 'sql' as $$ select
'md5'||md5($2||$1)= rolpassword from pg_authid where rolname=$1; $$; 
>   CREATE FUNCTION
>   sandbox=# select validate_user_8_1('joe','verysecret');
>    validate_user_8_1
>   -------------------
>    t
>   (1 Zeile)
>
> May I rely on this in future versions or are there more
> sophisticated ways to do it?

I don't know that I would 'rely' on it, but it doesn't seem like something
that is likely to change any time soon. But I could see there being alternate
hash functions being used eventually.

It might make more sense to use your own table of users and hashed passwords
rather than postgres'. This would depend somewhat on the overlap of users who
are using your application and those who connect directly to the database.
If there isn't much overlap, having a separate table is probably better.

> It might make more sense to use your own table of users and hashed
> passwords
> rather than postgres'. This would depend somewhat on the overlap of users
> who
> are using your application and those who connect directly to the database.
> If there isn't much overlap, having a separate table is probably better.

Using own table requires storing Postgres user name and password in client
computer. Thus this information is available to virtually everyone haveing
access to client computer.
So this is very bad idea and should avoided at all.

Andrus.

On Fri, Jan 19, 2007 at 18:24:32 +0200,
  Andrus <kobruleht2@hot.ee> wrote:
> > It might make more sense to use your own table of users and hashed
> > passwords
> > rather than postgres'. This would depend somewhat on the overlap of users
> > who
> > are using your application and those who connect directly to the database.
> > If there isn't much overlap, having a separate table is probably better.
>
> Using own table requires storing Postgres user name and password in client
> computer. Thus this information is available to virtually everyone haveing
> access to client computer.
> So this is very bad idea and should avoided at all.

No, the tables would be on the server, the same as was already being done.
Using a separate table makes it more future proof.

On Sun, Jan 21, 2007 at 15:16:37 +0200,
  Andrus <kobruleht2@hot.ee> wrote:
>
> >No, the tables would be on the server, the same as was already being done.
> >Using a separate table makes it more future proof.
>
> To access tables in server, you need to login into server.
> To login into server, you need postresql user name and password sent by
> client and thus stored in client computer.
>
> It is possible to obtain this information from client computer and use it
> for unauthirized access to data.

This is the same problem as checking the password versus the native (to
postgres) password hashes. I suggested having private tables as an alternative
to that in order for the OP to not have problems with future upgrades, which
was the original question.

I didn't give an opinion on whether or not the whole approach was a good
idea or not, since there wasn't enough detail in the original question.

Hi,

Am Montag, 22. Jan 2007, 10:25:33 -0600 schrieb Bruno Wolff III:
> I didn't give an opinion on whether or not the whole approach was a good
> idea or not, since there wasn't enough detail in the original question.

What I want to do is the following:

  1. Login in from a program on a client as a particualar user.
  2. Login from a series of scripts run by Apache on localhost
     ('trust' authentication method). Of course, I won't hand the
     password through web pages. Therefore I store something like a
     'session cookie' in a table. Next time I log in as a superuser,
     read the appropriate entry and immediately do a "set session
     autorization". The first step can be done in two ways: (a) I write
     a special login routine, (b) I log in as any other script and do
     the password check against pg_authid using the function I proposed.

Before I decide how I will solve it: thanks a lot for your
answers and for the discussion.

Bertram


--
Bertram Scharpf
Stuttgart, Deutschland/Germany
http://www.bertram-scharpf.de

> No, the tables would be on the server, the same as was already being done.
> Using a separate table makes it more future proof.

To access tables in server, you need to login into server.
To login into server, you need postresql user name and password sent by
client and thus stored in client computer.

It is possible to obtain this information from client computer and use it
for unauthirized access to data.

Andrus.

On Mon, Jan 22, 2007 at 20:25:48 +0100,
  Bertram Scharpf <lists@bertram-scharpf.de> wrote:
>
> What I want to do is the following:
>
>   1. Login in from a program on a client as a particualar user.

For this case you shouldn't need to do anything tricky as long as the user
is login in as themselves. Just prompt the user for their password and use it
when you open a connection to the database. If you are trying to have the
program login without the user being able to steal or borrow the credentials,
then you have a serious design flaw.

>   2. Login from a series of scripts run by Apache on localhost
>      ('trust' authentication method). Of course, I won't hand the
>      password through web pages. Therefore I store something like a
>      'session cookie' in a table. Next time I log in as a superuser,
>      read the appropriate entry and immediately do a "set session
>      autorization". The first step can be done in two ways: (a) I write
>      a special login routine, (b) I log in as any other script and do
>      the password check against pg_authid using the function I proposed.

If you use trust, be sure to limit that authentication rule to expected
IP addresses and take steps to prevent spoofed packets from getting into
your network. If the web server is running on the same machine as the DB,
then consider using ident authentication and connecting using domain sockets.
(This is available under Windows.)

Hi Bruno,

Am Montag, 22. Jan 2007, 23:11:41 -0600 schrieb Bruno Wolff III:
> If the web server is running on the same machine as the DB,
> then consider using ident authentication and connecting using domain sockets.

Ah, a good suggestion. Thanks!

I found an exhaustive documentation on
<http://developer.postgresql.org/pgdocs/postgres/auth-methods.html>.

> (This is available under Windows.)

What is "Windows"?

Bertram


--
Bertram Scharpf
Stuttgart, Deutschland/Germany
http://www.bertram-scharpf.de

On Mon, 22 Jan 2007, Bruno Wolff III wrote:
> On Mon, Jan 22, 2007 at 20:25:48 +0100,
>   Bertram Scharpf <lists@bertram-scharpf.de> wrote:
> >
> > What I want to do is the following:
> >
> >   1. Login in from a program on a client as a particualar user.
>
> For this case you shouldn't need to do anything tricky as long as the user
> is login in as themselves. Just prompt the user for their password and use it
> when you open a connection to the database. If you are trying to have the
> program login without the user being able to steal or borrow the credentials,
> then you have a serious design flaw.

I'm quite certain I missed the start of this thread, but just looking at
the above paragraph as it stands:

Design flaw? Perhaps an _incomplete_ design, but it's only a design flaw
if not finished off properly. One way to do this cleanly is to use a
program that has the suid bit set so it runs as the program's file owner
(optionally group), and this program accesses the password and provides
the database access.

Richard


--
Richard Troy, Chief Scientist
Science Tools Corporation
510-924-1363 or 202-747-1263
rtroy@ScienceTools.com, http://ScienceTools.com/

On Tue, Jan 23, 2007 at 09:44:28 +0100,
  Bertram Scharpf <lists@bertram-scharpf.de> wrote:
> Hi Bruno,
>
> Am Montag, 22. Jan 2007, 23:11:41 -0600 schrieb Bruno Wolff III:
> > If the web server is running on the same machine as the DB,
> > then consider using ident authentication and connecting using domain sockets.
>
> Ah, a good suggestion. Thanks!
>
> I found an exhaustive documentation on
> <http://developer.postgresql.org/pgdocs/postgres/auth-methods.html>.
>
> > (This is available under Windows.)
>
> What is "Windows"?

It was supposed to say domain sockets are NOT available under windows.

Just in case you weren't being funny, I meant the OS sold by Microsoft.

On Tue, Jan 23, 2007 at 09:01:56 -0800,
  Richard Troy <rtroy@ScienceTools.com> wrote:
>
> On Mon, 22 Jan 2007, Bruno Wolff III wrote:
> > On Mon, Jan 22, 2007 at 20:25:48 +0100,
> >   Bertram Scharpf <lists@bertram-scharpf.de> wrote:
> > >
> > > What I want to do is the following:
> > >
> > >   1. Login in from a program on a client as a particualar user.
> >
> > For this case you shouldn't need to do anything tricky as long as the user
> > is login in as themselves. Just prompt the user for their password and use it
> > when you open a connection to the database. If you are trying to have the
> > program login without the user being able to steal or borrow the credentials,
> > then you have a serious design flaw.
>
> I'm quite certain I missed the start of this thread, but just looking at
> the above paragraph as it stands:
>
> Design flaw? Perhaps an _incomplete_ design, but it's only a design flaw
> if not finished off properly. One way to do this cleanly is to use a
> program that has the suid bit set so it runs as the program's file owner
> (optionally group), and this program accesses the password and provides
> the database access.

You are correct. I over generalized. I should have added :and you don't control
the computer the user is running the client program on". In the case where you
do control the computer, setuid can be used to do things securely.