Re: WWW-Authentication and Postgresql - Mailing list 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.