Re: Password Encryption to replicate MySQL PASSWORD function

Hi David,

Thanks for the reply.

Unfortunately, thats not quite the problem. I want to create passwords
that will work in the pg_shadow table. So, I need them to be calculated
in exactly the same way PostgreSQL does when you do a CREATE USER
matthew WITH PASSWORD testing.

For example, If I create a user in PostgreSQL called 'matthew' with
password 'testing', I get pg_shadow entry with passwd:

md5759af56ffaf865413f7a50b4fae20ea3

but, if I do a simple md5 of 'testing' like you've done below, I get:

ae2b1fca515949e5d54fb22b8ed95575

As you can see, those don't match.

Perhaps I'm missing something though?

Cheers

Matthew.

On Tuesday, January 21, 2003, at 05:27 AM, David Busby wrote:

> Matthew,
> I just use something like
> $pass = $_POST['pass'];
> $e_pass = md5($passs);
> Maybe not as secure as two md5s, but have you ever tried to
> reverse one
> md5 checksum?

--
Matthew Horoschun
Network Administrator
CanPrint Communications Pty. Ltd.

Mobile: 0417 282 378
Direct: (02) 6295 4544
Telephone: (02) 6295 4422
Facsimile: (02) 6295 4473

I have a program that set's a database up with a PHP script.
It automatically inserts a generic 'user' into a 'members' table.
Each user has a password.

In mysql I have used the 'PASSWORD('someString')' function to encrypt each
users password. When authenticating a user for system use, I use the same
function to compare encrypted password.

How is this easiily achieved in Postgresql? (as there is no 'PASSWORD'
function)

Below is the table structure (simplified for this example)

CREATE TABLE users (
userid serial (auto_increment if using mysql),
name varchar(100),
password varchar(200),
PRIMARY KEY(userid, name) );

Here is what I would do with MySQL to set up the default user:
INSERT INTO users (name, password) VALUES ('john citizen',
PASSWORD('someString'));

Is there any way to replicate this with PostgreSQL or a better way to
authenticate users with both databases (md5 or similar) ????

I'd rather keep the encryption/and or md5 logic out of the scripts and in
the database if possible. (unless there is a reason not to..)

-----Original Message-----
From: pgsql-php-owner(at)postgresql(dot)org
[mailto:pgsql-php-owner(at)postgresql(dot)org]On Behalf Of Matthew Horoschun
Sent: Tuesday, 21 January 2003 9:10 AM
To: David Busby
Cc: pgsql-php(at)postgresql(dot)org
Subject: Re: [PHP] Creating md5 passwords in PHP for the PostgreSQL
pg_shadow table

Hi David,

Thanks for the reply.

Unfortunately, thats not quite the problem. I want to create passwords
that will work in the pg_shadow table. So, I need them to be calculated
in exactly the same way PostgreSQL does when you do a CREATE USER
matthew WITH PASSWORD testing.

For example, If I create a user in PostgreSQL called 'matthew' with
password 'testing', I get pg_shadow entry with passwd:

md5759af56ffaf865413f7a50b4fae20ea3

but, if I do a simple md5 of 'testing' like you've done below, I get:

ae2b1fca515949e5d54fb22b8ed95575

As you can see, those don't match.

Perhaps I'm missing something though?

Cheers

Matthew.

On Tuesday, January 21, 2003, at 05:27 AM, David Busby wrote:

> Matthew,
> I just use something like
> $pass = $_POST['pass'];
> $e_pass = md5($passs);
> Maybe not as secure as two md5s, but have you ever tried to
> reverse one
> md5 checksum?

--
Matthew Horoschun
Network Administrator
CanPrint Communications Pty. Ltd.

Mobile: 0417 282 378
Direct: (02) 6295 4544
Telephone: (02) 6295 4422
Facsimile: (02) 6295 4473

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

Hi Luke,

I've just been playing with this myself (as you've seen). I'm no
expert... so maybe somebody else can jump in if what I say is incorrect.

On Wednesday, January 22, 2003, at 02:00 PM, Luke Woollard wrote:

> How is this easiily achieved in Postgresql? (as there is no 'PASSWORD'
> function)

As far as I know there aren't any similar functions available in
PostgreSQL. Additionally, I don't see anything wrong with sticking that
logic on the application-side rather than in the database.

Of course, if you do your access-control on the application side, then
you're vulnerable to faults in your PHP code potentially causing
complete database compromise.

> Is there any way to replicate this with PostgreSQL or a better way to
> authenticate users with both databases (md5 or similar) ????

One of the reasons we've moved from MySQL to PostgreSQL was to provide
more stringent security by using views and schemas. We decided that the
safest method was to create real users in the PostgreSQL system user
table, and then let Postgres worry about authenticating users. Then,
even if your PHP code is flawed, the SQL commands still execute with
only the users permissions.

This doesn't solve your original problem though. You still end up
needing to do the md5 hashing in the application layer. I'm curious to
know why you're opposed to this?

I'm keen to hear other peoples views on the cleanest way to
authenticate users...

Cheers

Matthew.

--
Matthew Horoschun
Network Administrator
CanPrint Communications Pty. Ltd.

Mobile: 0417 282 378
Direct: (02) 6295 4544
Telephone: (02) 6295 4422
Facsimile: (02) 6295 4473

Luke Woollard wrote:
> In mysql I have used the 'PASSWORD('someString')' function to encrypt each
> users password. When authenticating a user for system use, I use the same
> function to compare encrypted password.
>

From the MySQL manual:
"The PASSWORD() function is used by the authentication system in MySQL Server,
you should *not* use it in your own applications. For that purpose, use MD5()
or SHA1() instead." (emphasis added)

FWIW, the algorithm used in PASSWORD() must be pretty weak, as it appears to
only create an 8-byte (16 hex chars) hash. MD5() (16 bytes/32 hex chars) and
SHA1() (20 bytes/40 hex chars) are available in contrib/pgcrypto.
Alternatively you could use the PHP functions by the same names.

HTH,

Joe

Hi Matthew + List,

CAPS BELOW..

-----Original Message-----
From: Matthew Horoschun [mailto:mhoroschun(at)canprint(dot)com(dot)au]
Sent: Wednesday, 22 January 2003 2:49 PM
To: Luke Woollard
Cc: pgsql-php(at)postgresql(dot)org; Farran Rebbeck
Subject: Re: [PHP] Password Encryption to replicate MySQL PASSWORD
function

Hi Luke,

I've just been playing with this myself (as you've seen). I'm no
expert...
ME EITHER.

so maybe somebody else can jump in if what I say is incorrect.
DITTO.

On Wednesday, January 22, 2003, at 02:00 PM, Luke Woollard wrote:

> How is this easiily achieved in Postgresql? (as there is no 'PASSWORD'
> function)

As far as I know there aren't any similar functions available in
PostgreSQL. I HAVEN'T FOUND ANY EITHER.

Additionally, I don't see anything wrong with sticking that
logic on the application-side rather than in the database.
FAIR ENOUGH.

Of course, if you do your access-control on the application side, then
you're vulnerable to faults in your PHP code potentially causing
complete database compromise. YEP

> Is there any way to replicate this with PostgreSQL or a better way to
> authenticate users with both databases (md5 or similar) ????

One of the reasons we've moved from MySQL to PostgreSQL was to provide
more stringent security by using views and schemas. We decided that the
safest method was to create real users in the PostgreSQL system user
table, and then let Postgres worry about authenticating users. Then,
even if your PHP code is flawed, the SQL commands still execute with
only the users permissions.
INTERESTING

This doesn't solve your original problem though. You still end up
needing to do the md5 hashing in the application layer. I'm curious to
know why you're opposed to this?
NOT EXACTLY OPPOSED -> JUST WANT TO KEEP IT SIMPLE. THE LESS CODE TO
MAINTAIN -> THE BETTER. WOULD RATHER RELY ON DATABASE SYSTEM TO PERFORM
ENCRYPTION TECHNIQUE IF POSSIBLE..

I'm keen to hear other peoples views on the cleanest way to authenticate
users...
ME TOO. THERE'S A LIMITED AMOUNT OF QUALITY INFORMATION ON USING PHP WITH
POSTGRESQL OUT THERE..

Cheers
PEACE

Matthew.
LUKE

--
Matthew Horoschun
Network Administrator
CanPrint Communications Pty. Ltd.

Mobile: 0417 282 378
Direct: (02) 6295 4544
Telephone: (02) 6295 4422
Facsimile: (02) 6295 4473

COOL - THANKS FOR THE INFORMATION.

LW

-----Original Message-----
From: pgsql-php-owner(at)postgresql(dot)org
[mailto:pgsql-php-owner(at)postgresql(dot)org]On Behalf Of Joe Conway
Sent: Wednesday, 22 January 2003 3:14 PM
To: Luke Woollard
Cc: pgsql-php(at)postgresql(dot)org
Subject: Re: [PHP] Password Encryption to replicate MySQL PASSWORD
function

Luke Woollard wrote:
> In mysql I have used the 'PASSWORD('someString')' function to encrypt each
> users password. When authenticating a user for system use, I use the same
> function to compare encrypted password.
>

From the MySQL manual:
"The PASSWORD() function is used by the authentication system in MySQL
Server,
you should *not* use it in your own applications. For that purpose, use
MD5()
or SHA1() instead." (emphasis added)

FWIW, the algorithm used in PASSWORD() must be pretty weak, as it appears to
only create an 8-byte (16 hex chars) hash. MD5() (16 bytes/32 hex chars) and
SHA1() (20 bytes/40 hex chars) are available in contrib/pgcrypto.
Alternatively you could use the PHP functions by the same names.

HTH,

Joe

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

>We decided that the
> safest method was to create real users in the PostgreSQL system user
> table, and then let Postgres worry about authenticating users. Then,
> even if your PHP code is flawed, the SQL commands still execute with
> only the users permissions.

I thought that way too but now I think it is better to create users and do
the encrypting in the application layer and store logins and passwords in
regular tables. The reason is that if, by any means, someone enters the
database as user postgres or any other with total priviledges then you can
see in the pg_shadow table the paswords in clear, this is a bigger risk than
having your passwords encrypted in PHP and store them as encrypted TEXT in
any table.

Besides if you move from one postgresql server to another you have to worry
a lot about how postgresql is configured (permissions and such). If you put
it all in the application and regular tables you can go to any typical
postgresql installation and install the database and application quickly and
safely.

At least that's what I think.

Adrian Tineo