Re: How to store files into the DB with PHP. (bytea ?)

From: Andrew McMillan <andrew(at)morphoss(dot)com>
To: François Delpierre <pgsql(at)pivert(dot)org>
Cc: pgsql-php(at)postgresql(dot)org
Subject: Re: How to store files into the DB with PHP. (bytea ?)
Date: 2009-02-02 21:08:00
Message-ID: 1233608880.5827.67.camel@happy.mcmillan.net.nz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-php

On Mon, 2009-02-02 at 13:55 +0100, François Delpierre wrote:
> On Monday 02 February 2009 12:55:04 tv(at)fuzzy(dot)cz wrote:
> >
> > If the web server is compromised, the attacker will have full access to
> > the database just as the web application (the connection details are
> > stored in some configuration file, and he may load all the data he wants
> > using a custom PHP script uploaded to the server).
> >
> > You may increase the security by encrypting the files, but the password
> > must not be saved in the database. For example if the files are not shared
> > by multiple users, each file may be encrypted using the user password.
> I do not agree, this is in fact the reason why stored procedures are mandatory
> here.

I don't see that this changes things. Whether you use stored
procedures, authenticate against the database, or whatever, your web
application layer has access to the information on the way through and
any compromise of your webserver will necessarily involve having a 'man
in the middle' possibility.

> If you use stored procedures for security, the credentials that are stored on
> your web server are restricted to execute procedures, and eventually have a
> read only access to some tables with no critical informations (ie
> translations), all other tables are simply forbidden for the web server user.

> For this security level to work, it means that you use a "login" stored
> procedure that returns a "ticket" to the web server and/or web browser, valid
> for a certain amount of time and for the source IP. Every other procedures
> will require a valid "ticket" in the arguments before it returns data.)

So an attacker would (e.g.) log the user's credentials as they pass
through and then happily generate their own tickets to use to extract
the data.

> This way, the sessions are handled within the database, and the user/password
> stored in the front end web server cannot read any data from the database
> without going through a stored procedure that enforce the security.
>
> An other option maybe would be to authenticate users on the database directly,
> and only use a stored procedure (with security definer) to create new users. I
> do not know exactly the pro/cons to work this way. I would prefer to keep my
> users within an application table instead of the DB (and in a system table),
> but this may be the easiest solutions as the stored procedures would no more
> be required.

So in this case an attacker would (e.g.) log the user's credentials on
the way through and use them for subsequent connections to the
database...

That's not to say that you aren't adding some security: you are. Making
an intruder understand how your application works before being able to
steal your data will naturally give you a longer window of opportunity
to catch them. Probably you would have raised the bar sufficiently
above the interest level of an opportunistic script kiddy.

Odds are, though, that the level of difference between someone who would
be deliberately cracking your server to pull the data from your
database, and someone who would be deliberately cracking your server,
understanding your application and then pulling the data from your
database isn't that great.

If you take your paranoia one step further, do the user authentication
in the database using some kind of one-time password device then *maybe*
you will be getting a more repeatable level of security. In that case
someone would only be able to sniff the files as they were accessed -
and would not be able to extract them from the database.

Cheers,
Andrew McMillan.

------------------------------------------------------------------------
andrew (AT) morphoss (DOT) com +64(272)DEBIAN
Open Source: the difference between trust and antitrust
------------------------------------------------------------------------

In response to

Browse pgsql-php by date

  From Date Subject
Next Message François Delpierre 2009-02-03 16:16:44 Re: How to store files into the DB with PHP. (bytea ?)
Previous Message François Delpierre 2009-02-02 14:13:10 Re: How to store files into the DB with PHP. (bytea ?)