pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
WWW-Authentication and Postgresql
8+ messages / 5 participants
[nested] [flat]

* WWW-Authentication and Postgresql
@ 2001-12-25 23:24 Stephan Borg <wolff_borg@yahoo.com.au>
  2001-12-26 00:19 ` Re: WWW-Authentication and Postgresql Dorin Grunberg <dorin@visgen.com>
  2001-12-26 02:52 ` Re: WWW-Authentication and Postgresql Andrew McMillan <andrew@catalyst.net.nz>
  0 siblings, 2 replies; 8+ messages in thread

From: Stephan Borg @ 2001-12-25 23:24 UTC (permalink / raw)
  To: pgsql-php

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




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: WWW-Authentication and Postgresql
  2001-12-25 23:24 WWW-Authentication and Postgresql Stephan Borg <wolff_borg@yahoo.com.au>
@ 2001-12-26 00:19 ` Dorin Grunberg <dorin@visgen.com>
  1 sibling, 0 replies; 8+ messages in thread

From: Dorin Grunberg @ 2001-12-26 00:19 UTC (permalink / raw)
  To: Stephan Borg <wolff_borg@yahoo.com.au>; +Cc: pgsql-php

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





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: WWW-Authentication and Postgresql
  2001-12-25 23:24 WWW-Authentication and Postgresql Stephan Borg <wolff_borg@yahoo.com.au>
@ 2001-12-26 02:52 ` Andrew McMillan <andrew@catalyst.net.nz>
  2001-12-26 03:24   ` Re: WWW-Authentication and Postgresql Vince Vielhaber <vev@michvhf.com>
  1 sibling, 1 reply; 8+ messages in thread

From: Andrew McMillan @ 2001-12-26 02:52 UTC (permalink / raw)
  To: Stephan Borg <wolff_borg@yahoo.com.au>; +Cc: pgsql-php

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?




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: WWW-Authentication and Postgresql
  2001-12-25 23:24 WWW-Authentication and Postgresql Stephan Borg <wolff_borg@yahoo.com.au>
  2001-12-26 02:52 ` Re: WWW-Authentication and Postgresql Andrew McMillan <andrew@catalyst.net.nz>
@ 2001-12-26 03:24   ` Vince Vielhaber <vev@michvhf.com>
  2001-12-27 03:00     ` Re: WWW-Authentication and Postgresql Stephan Borg <wolff_borg@yahoo.com.au>
  0 siblings, 1 reply; 8+ messages in thread

From: Vince Vielhaber @ 2001-12-26 03:24 UTC (permalink / raw)
  To: Andrew McMillan <andrew@catalyst.net.nz>; +Cc: Stephan Borg <wolff_borg@yahoo.com.au>; pgsql-php

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
==========================================================================






^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: WWW-Authentication and Postgresql
  2001-12-25 23:24 WWW-Authentication and Postgresql Stephan Borg <wolff_borg@yahoo.com.au>
  2001-12-26 02:52 ` Re: WWW-Authentication and Postgresql Andrew McMillan <andrew@catalyst.net.nz>
  2001-12-26 03:24   ` Re: WWW-Authentication and Postgresql Vince Vielhaber <vev@michvhf.com>
@ 2001-12-27 03:00     ` Stephan Borg <wolff_borg@yahoo.com.au>
  2001-12-27 08:54       ` Re: WWW-Authentication and Postgresql Andrew McMillan <andrew@catalyst.net.nz>
  0 siblings, 1 reply; 8+ messages in thread

From: Stephan Borg @ 2001-12-27 03:00 UTC (permalink / raw)
  To: 'Vince Vielhaber' <vev@michvhf.com>; +Cc: pgsql-php

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




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: WWW-Authentication and Postgresql
  2001-12-25 23:24 WWW-Authentication and Postgresql Stephan Borg <wolff_borg@yahoo.com.au>
  2001-12-26 02:52 ` Re: WWW-Authentication and Postgresql Andrew McMillan <andrew@catalyst.net.nz>
  2001-12-26 03:24   ` Re: WWW-Authentication and Postgresql Vince Vielhaber <vev@michvhf.com>
  2001-12-27 03:00     ` Re: WWW-Authentication and Postgresql Stephan Borg <wolff_borg@yahoo.com.au>
@ 2001-12-27 08:54       ` Andrew McMillan <andrew@catalyst.net.nz>
  2001-12-27 15:51         ` Re: WWW-Authentication and Postgresql Vince Vielhaber <vev@michvhf.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Andrew McMillan @ 2001-12-27 08:54 UTC (permalink / raw)
  To: Stephan Borg <wolff_borg@yahoo.com.au>; +Cc: 'Vince Vielhaber' <vev@michvhf.com>; pgsql-php

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?




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: WWW-Authentication and Postgresql
  2001-12-25 23:24 WWW-Authentication and Postgresql Stephan Borg <wolff_borg@yahoo.com.au>
  2001-12-26 02:52 ` Re: WWW-Authentication and Postgresql Andrew McMillan <andrew@catalyst.net.nz>
  2001-12-26 03:24   ` Re: WWW-Authentication and Postgresql Vince Vielhaber <vev@michvhf.com>
  2001-12-27 03:00     ` Re: WWW-Authentication and Postgresql Stephan Borg <wolff_borg@yahoo.com.au>
  2001-12-27 08:54       ` Re: WWW-Authentication and Postgresql Andrew McMillan <andrew@catalyst.net.nz>
@ 2001-12-27 15:51         ` Vince Vielhaber <vev@michvhf.com>
  0 siblings, 0 replies; 8+ messages in thread

From: Vince Vielhaber @ 2001-12-27 15:51 UTC (permalink / raw)
  To: Andrew McMillan <andrew@catalyst.net.nz>; +Cc: Stephan Borg <wolff_borg@yahoo.com.au>; pgsql-php

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
==========================================================================






^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: WWW-Authentication and Postgresql
@ 2002-01-04 20:33 Davis, Ricardo C. <RCDavis@intermedia.com>
  0 siblings, 0 replies; 8+ messages in thread

From: Davis, Ricardo C. @ 2002-01-04 20:33 UTC (permalink / raw)
  To: 'Andrew McMillan' <andrew@catalyst.net.nz>; Stephan Borg <wolff_borg@yahoo.com.au>; +Cc: 'Vince Vielhaber' <vev@michvhf.com>; pgsql-php

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.

^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2002-01-04 20:33 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-12-25 23:24 WWW-Authentication and Postgresql Stephan Borg <wolff_borg@yahoo.com.au>
2001-12-26 00:19 ` Dorin Grunberg <dorin@visgen.com>
2001-12-26 02:52 ` Andrew McMillan <andrew@catalyst.net.nz>
2001-12-26 03:24   ` Vince Vielhaber <vev@michvhf.com>
2001-12-27 03:00     ` Stephan Borg <wolff_borg@yahoo.com.au>
2001-12-27 08:54       ` Andrew McMillan <andrew@catalyst.net.nz>
2001-12-27 15:51         ` Vince Vielhaber <vev@michvhf.com>
2002-01-04 20:33 Re: WWW-Authentication and Postgresql Davis, Ricardo C. <RCDavis@intermedia.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox