Thread: WWW-Authentication and Postgresql
Hello there, I'm hoping that someone has tried this already. I am currently trying to write code, that prompts the user with a www-authentication logon and proceeds to verify if the credentials are a valid Postgresql user (incl password) before proceeding. Regards, Stephan _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
I have not tried it but, have a look at mod_auth_pgsql. It works with the apache web server and it allows authentication against a PostgreSQL database of user names and passwords. I hope it helps, Dorin Stephan Borg wrote: > Hello there, > > I'm hoping that someone has tried this already. I am currently trying to > write code, that prompts the user with a www-authentication logon and > proceeds to verify if the credentials are a valid Postgresql user (incl > password) before proceeding. > > Regards, > > Stephan > > > _________________________________________________________ > Do You Yahoo!? > Get your free @yahoo.com address at http://mail.yahoo.com > > > ---------------------------(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 > > >
On Wed, 2001-12-26 at 12:24, Stephan Borg wrote:
> Hello there,
>
> I'm hoping that someone has tried this already. I am currently trying to
> write code, that prompts the user with a www-authentication logon and
> proceeds to verify if the credentials are a valid Postgresql user (incl
> password) before proceeding.
We have code to do this in the office somewhere. It isn't hard. Follow
the PHP examples and try a connect to the database with the credentials
you have got from the auth.
I don't actually do this for any of my sites, however. I find that
database user != system user in 99.9% of cases. In addition I find that
the www-authentication method makes for a crude login process.
E-mail me off-list if you can't figure it out and I will dig out the
code for you.
Cheers,
Andrew.
--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267
Are you enrolled at http://schoolreunions.co.nz/ yet?
On 26 Dec 2001, Andrew McMillan wrote: > On Wed, 2001-12-26 at 12:24, Stephan Borg wrote: > > Hello there, > > > > I'm hoping that someone has tried this already. I am currently trying to > > write code, that prompts the user with a www-authentication logon and > > proceeds to verify if the credentials are a valid Postgresql user (incl > > password) before proceeding. > > We have code to do this in the office somewhere. It isn't hard. Follow > the PHP examples and try a connect to the database with the credentials > you have got from the auth. > > I don't actually do this for any of my sites, however. I find that > database user != system user in 99.9% of cases. In addition I find that > the www-authentication method makes for a crude login process. > > E-mail me off-list if you can't figure it out and I will dig out the > code for you. A couple of quick gotchas. 1) make sure you filter out all unwanted characters so someone can't execute sql calls inside of a username or password. 2) On failure make sure you send a 401 to the browser just like you do initially when asking for the password to clear out the old one - you can also use this to handle logouts. Vince. -- ========================================================================== Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ==========================================================================
I have found the mod_auth_pgsql module to be the easiest way to implement this function. Does anyone know if it takes the points mentioned below into consideration? Stephan -----Original Message----- From: Vince Vielhaber [mailto:vev@michvhf.com] Sent: Wednesday, 26 December 2001 2:25 PM To: Andrew McMillan Cc: Stephan Borg; pgsql-php@postgresql.org Subject: Re: [PHP] WWW-Authentication and Postgresql <snip> A couple of quick gotchas. 1) make sure you filter out all unwanted characters so someone can't execute sql calls inside of a username or password. 2) On failure make sure you send a 401 to the browser just like you do initially when asking for the password to clear out the old one - you can also use this to handle logouts. Vince. -- ======================================================================== == Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ======================================================================== == _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
On Thu, 2001-12-27 at 16:00, Stephan Borg wrote:
> I have found the mod_auth_pgsql module to be the easiest way to
> implement this function. Does anyone know if it takes the points
> mentioned below into consideration?
>
> Stephan
>
> -----Original Message-----
> From: Vince Vielhaber [mailto:vev@michvhf.com]
> Sent: Wednesday, 26 December 2001 2:25 PM
> To: Andrew McMillan
> Cc: Stephan Borg; pgsql-php@postgresql.org
> Subject: Re: [PHP] WWW-Authentication and Postgresql
> <snip>
> A couple of quick gotchas. 1) make sure you filter out all unwanted
> characters so someone can't execute sql calls inside of a username or
> password. 2) On failure make sure you send a 401 to the browser just
> like you do initially when asking for the password to clear out the old
> one - you can also use this to handle logouts.
These points don't really relate so much to authentication - they are
much more related to when you construct SQL calls. Remember: the
browser is an untrusted client.
For example, if you trust someone to enter a value into a field which is
a single character, and you then construct a query:
"SELECT * FROM my_table WHERE my_field = '$unsafe_value';"
If $unsafe_value is "X" then all is well and good, but if $unsafe_value
is "X'; DROP TABLE my_table; SELECT 'hahaha" then someone has just
hosed your database...
I think that what Vince was getting at particularly, in replying to my
post suggesting not to use database-level users, was that if you are not
using database level users then there is a greater risk of this being a
problem. I would tend to dispute that - I think this is a risk
_anytime_. Paranoia rules.
Think what can happen if (e.g.) someone were to save one of your web
pages locally, edit the values in it (turn the combo boxes or hidden
fields into input fields, or edit the cookies, for example) and submit
crap at your system. Of _course_ it is a rare person who will do that,
but if there is 1 in 10,000, and if you are vulnerable, it is really
only a matter of time before someone starts playing.
Cheers,
Andrew.
--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267
Are you enrolled at http://schoolreunions.co.nz/ yet?
On 27 Dec 2001, Andrew McMillan wrote: > > <snip> > > A couple of quick gotchas. 1) make sure you filter out all unwanted > > characters so someone can't execute sql calls inside of a username or > > password. 2) On failure make sure you send a 401 to the browser just > > like you do initially when asking for the password to clear out the old > > one - you can also use this to handle logouts. <snip> > I think that what Vince was getting at particularly, in replying to my > post suggesting not to use database-level users, was that if you are not > using database level users then there is a greater risk of this being a > problem. I would tend to dispute that - I think this is a risk > _anytime_. Paranoia rules. Nope, all I was saying was to filter out all input from the browser. you don't want any apostrophes, or probably anything other than a-z, A-Z, 0-9. and to use the 401 to clear out failures. Vince. -- ========================================================================== Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ==========================================================================
hi,
here is how I handle a basic authentication form against postgresql
put your dbname, user and passwd on $dbcredentials
put your table on $dbtable and the user and passwd cols on $dbid and $dbpw
see you,
teixi.
ps: so in my case the testdb contains a test_table where the user column is called test_login and the passwd column is
test_passw
<?php
$dbcredentials= "dbname=testdb user=testuser password=xxxxxx";
$dbtable = "test_table";
$dbid= "test_login";
$dbpw= "test_passw";
function authy($inside) {
echo "<form action=\"$PHP_SELF\" method=\"POST\" enctype=\"application/x-www-form-urlencoded\">\n";
echo "Login: <input type=\"text\" name=\"authy_user\" size=\"23\" maxlength=\"23\"><br>\n";
echo "Password: <input type=\"password\" name=\"authy_pw\" size=\"20\" maxlength=\"20\"><br>\n\n";
echo "<input name=\"Submit\" value=\"Submit\" type=\"submit\">\n";
echo "<input name=\"Reset\" value=\"Reset\" type=\"reset\">\n";
echo "</form>\n";
if($inside=='-1') { echo "<strong><p>Invalid credentials.</p></strong>"; }
exit;
}
if( $authy_user=='' || $authy_pw=='' ) {
$inside = 0;
authy($inside);
} else {
$conn = pg_pconnect($dbcredentials) or die ('DB Unavailable');
$login = strtolower($authy_user);
$data = "SELECT * FROM $dbtable WHERE $dbid='$login' AND $dbpw='$authy_pw'";
$query = pg_exec($conn, $data) or die ('Unable to execute query');
if (!pg_numrows($query)) {
$inside = -1;
authy($inside);
} else {
$inside = 1;
pg_close($conn);
}
}
echo "<strong><p>You're Inside :-)</p></strong><br>";
?>
On Thu, 27 Dec 2001 10:51:03 -0500 (EST)
Vince Vielhaber <vev@michvhf.com> was typing:
> On 27 Dec 2001, Andrew McMillan wrote:
>
> > > <snip>
> > > A couple of quick gotchas. 1) make sure you filter out all unwanted
> > > characters so someone can't execute sql calls inside of a username or
> > > password. 2) On failure make sure you send a 401 to the browser just
> > > like you do initially when asking for the password to clear out the old
> > > one - you can also use this to handle logouts.
>
> <snip>
>
> > I think that what Vince was getting at particularly, in replying to my
> > post suggesting not to use database-level users, was that if you are not
> > using database level users then there is a greater risk of this being a
> > problem. I would tend to dispute that - I think this is a risk
> > _anytime_. Paranoia rules.
>
> Nope, all I was saying was to filter out all input from the browser.
> you don't want any apostrophes, or probably anything other than a-z,
> A-Z, 0-9. and to use the 401 to clear out failures.
>
> Vince.
Andrew,
Good design of the database schema and the application will deal with your greatest fears.
* Rule 1: Never allow the application to connect to the database as the owner (the user that executed the CREATE TABLE) or PostgreSQL superuser. These two users can DROP TABLES at will. The set of privileges held by the table owner is always implicit and is never revocable.
* Rule 2: Create a PostgreSQL user (or users as appropriate) for your application and specifically grant the privileges that user should have on each table used by the application. NEVER connect to the database as the PUBLIC user, assuming that user only has been granted READ privs on database objects. Got a lookup table? Then explicitly grant your app's user READ privs to it.
* Rule 3: Don't depend upon the application to implement all the business rules. I find it better to implement constraints in the table's schema.
Paranoia is sometimes good for security, but malicious SQL usually requires knowledge about the database schema (yet another for Rule 1). You can catch other browser client-side foolishness by limiting the use of client-side data in app computations. If you build your app and database well, you will sleep better at night.
Have a blessed (and secure) New Year,
-Ricardo
________________________________________________________________________
Ricardo Davis
Intermedia Communications - Advanced Building Networks
-----Original Message-----
From: Andrew McMillan [mailto:andrew@catalyst.net.nz]
Sent: Thursday, December 27, 2001 3:54 AM
To: Stephan Borg
Cc: 'Vince Vielhaber'; pgsql-php@postgresql.org
Subject: Re: [PHP] WWW-Authentication and Postgresql
<<snip!>>
For example, if you trust someone to enter a value into a field which is
a single character, and you then construct a query:
"SELECT * FROM my_table WHERE my_field = '$unsafe_value';"
If $unsafe_value is "X" then all is well and good, but if $unsafe_value
is "X'; DROP TABLE my_table; SELECT 'hahaha" then someone has just
hosed your database...
I think that what Vince was getting at particularly, in replying to my
post suggesting not to use database-level users, was that if you are not
using database level users then there is a greater risk of this being a
problem. I would tend to dispute that - I think this is a risk
_anytime_. Paranoia rules.
Think what can happen if (e.g.) someone were to save one of your web
pages locally, edit the values in it (turn the combo boxes or hidden
fields into input fields, or edit the cookies, for example) and submit
crap at your system. Of _course_ it is a rare person who will do that,
but if there is 1 in 10,000, and if you are vulnerable, it is really
only a matter of time before someone starts playing.
Cheers,
Andrew.