Thread: Creating md5 passwords in PHP for the PostgreSQL pg_shadow table
Hi All, I want to be able to allow non-superusers the ability to create other PostgreSQL users in limited circumstances. My plan was to create a dummy table with rules that actually made changes to the pg_shadow table. Therefore I need to be able to create password entries in the pg_shadow table. From browsing the code and the mailing lists I'm pretty certain I need to do the following in PHP to create a MD5 password for PostgreSQL: $passwd = "md5" . md5( md5( $password . $username ) . $salt ); Has anybody done this before? Is this the right way to get the md5?... and if so... how do I get PostgreSQL to give me the salt? Thanks in advance for any assistance! 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
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
Dear All,
When I do CREATE USER [user] WITH PASSWORD 'password'
why the encryption didn't work ? I saw in the pg_shadow table look like as
i set the password ?
SELECT * FROM PG_SHADOW
in password field will shown : 'password' ??????
Is there any way to activate md5 encryption in PostgreSQL ? or It was done
by compiling it when first installation ????
thankss
Best Regards,
Fery Gideon
IT Specialist
PT. Mitra Integrasi Komputindo
ASPAC Kuningan - 8th floor, Suite 805
Jl. H.R. Rasuna Said Kav. X-2 No. 4, Jakarta 12950
Phone: (62-21) 522-8322(ext 102), Fax: (62-21) 522-8321
e-mail: fery@mik.co.id
Matthew Horoschun
<mhoroschun@canprint To: "David Busby" <busby@pnts.com>
.com.au> cc: pgsql-php@postgresql.org
Sent by: Subject: Re: [PHP] Creating md5 passwords in PHP for the PostgreSQL
pgsql-php-owner@post pg_shadow table
gresql.org
01/21/2003 05:10 AM
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@postgresql.org)
Hi Fery, Try password_encryption = true in postgresql.conf (In my build it appears to be on by default). Matthew. On Wednesday, January 22, 2003, at 01:51 PM, Fery@mik.co.id wrote: > Is there any way to activate md5 encryption in PostgreSQL ? or It was > done > by compiling it when first installation ????
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@postgresql.org
[mailto:pgsql-php-owner@postgresql.org]On Behalf Of Matthew Horoschun
Sent: Tuesday, 21 January 2003 9:10 AM
To: David Busby
Cc: pgsql-php@postgresql.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@postgresql.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@canprint.com.au] Sent: Wednesday, 22 January 2003 2:49 PM To: Luke Woollard Cc: pgsql-php@postgresql.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@postgresql.org
[mailto:pgsql-php-owner@postgresql.org]On Behalf Of Joe Conway
Sent: Wednesday, 22 January 2003 3:14 PM
To: Luke Woollard
Cc: pgsql-php@postgresql.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