Received: from unix.intermedia.com (unix.intermedia.com [162.47.1.20]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g04KYRm88171 for ; Fri, 4 Jan 2002 15:34:27 -0500 (EST) (envelope-from RCDavis@exchange.intermedia.com) Received: from ici_exchgateway.intermedia.com (exchange.intermedia.com [162.47.244.120]) by unix.intermedia.com (8.11.6/8.11.6) with ESMTP id g04KXjr24776; Fri, 4 Jan 2002 15:33:45 -0500 (EST) Received: by exchange.intermedia.com with Internet Mail Service (5.5.2653.19) id ; Fri, 4 Jan 2002 15:33:44 -0500 Message-ID: <77DA8BE17C46D2118B7A00805FA7D0510990B54B@TPAEXCH2> From: "Davis, Ricardo C." To: "'Andrew McMillan'" , Stephan Borg Cc: "'Vince Vielhaber'" , pgsql-php@postgresql.org Subject: Re: WWW-Authentication and Postgresql Date: Fri, 4 Jan 2002 15:33:40 -0500 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2653.19) Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C1955F.1848CAE0" X-Archive-Number: 200201/4 X-Sequence-Number: 811 This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01C1955F.1848CAE0 Content-Type: text/plain; charset="iso-8859-1" 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 <> 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. ------_=_NextPart_001_01C1955F.1848CAE0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable RE: [PHP] WWW-Authentication and Postgresql

Andrew,

Good design of the database schema and the application wi= ll 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 Pos= tgreSQL superuser.  These two users can DROP TABLES at will. &nbs= p; 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 app= ropriate) for your application and specifically grant the privileges that u= ser should have on each table used by the application.  NEVER connect = to the database as the PUBLIC user, assuming that user only has been grante= d READ privs on database objects.  Got a lookup table?  Then expl= icitly grant your app's user READ privs to it.

* Rule 3:  Don't depend upon the application to impl= ement all the business rules.  I find it better to implement constrain= ts in the table's schema.


Paranoia is sometimes good for security, but malicious SQ= L usually requires knowledge about the database schema (yet another for Rul= e 1).  You can catch other browser client-side foolishness by limiting= the use of client-side data in app computations.  If you build your a= pp 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 =3D '$unsafe_= value';"

If $unsafe_value is "X" then all is well and go= od, but if $unsafe_value
is "X'; DROP TABLE my_table; SELECT 'hahaha"&n= bsp; then someone has just
hosed your database...

I think that what Vince was getting at particularly, in r= eplying to my
post suggesting not to use database-level users, was tha= t 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 th= is 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 box= es or hidden
fields into input fields, or edit the cookies, for examp= le) and submit
crap at your system.  Of _course_ it is a rare pers= on 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,
            &nbs= p;              = ;             Andrew.

------_=_NextPart_001_01C1955F.1848CAE0--