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

On Monday 02 February 2009 12:55:04 tv(at)fuzzy(dot)cz wrote:
> > Hi,
> >
> > I know this question came quite often, but I never found a clear answer
> > in the
> > mailing list.
> >
> > My problem :
> > - I need to store files (pdf, png, jpg) from a web page in PHP.
> > - I have a strong need of security and high availability. I must minimize
> > the
> > risk of having these documents stolen if the web server is compromised.
>
> 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.

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

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.

>
> > My questions :
> > - Which datatype ? (once again, sorry.). (Text, bytea, lo) ?
> > - Most of samples redirect to lo (large objects), and PHP has a set of
> > dedicated functions for them. But in the documentation we can read :
> >
> > All large objects are placed in a single system table called
> > pg_largeobject.
> > PostgreSQL also supports a storage system called "TOAST" that
> > automatically
> > stores values larger than a single database page into a secondary storage
> > area
> > per table. This makes the large object facility partially obsolete.
> >
> > Why obsolete ? Is it now because of TOAST that we can store up to 1GB
> > into bytea ? If bytea is the easiest solution, why do so many people
> > redirect to LO
> > ?
> > LO stores the datas within only one table, pg_largeobject, I really
> > prefer to
> > store the big datas within my custom table stored on a dedicated
> > tablespace.
>
> Choice between BYTEA and LOB is mostly about memory requirements - when
> loading / storing BYTEA columns, you need enough memory to keep the whole
> object (data stored in the column). So when constructing the INSERT
> statement you have to place the (escaped) data into it, and that may
> exceed the memory_limit. With LOB objects this is not a problem thanks to
> streaming (you may open the LOB as a stream and read / write).
So, there is no way to push binary directly into BYTEA without the need to
escape ?
I assume that you're talking about memory_limit parameter of PHP on the web
server.
Can we still use streaming through a stored procedure ?

>
> On the other side, LOB columns do not have some interesting features
> (foreign keys, etc.)
>
> > Due to the high security level of my web application, all the datas must
> > be
> > accessed through stored procedures. Does it change something to the
> > choice ?
> >
> > Where can I find samples of files stored through PHP & stored procedures
> > ?
>
> Sorry, but you have some custom layer of stored procedures, so we may only
> guess. But LOBs are identified using an OID, so the procedures might
> return OIDs.
The web server user cannot read (or write) any table directly, so it's forced
to use a stored procedure to read or write data. So, all the PHP fuctions on
LOB are useless I guess.

>
> regards
> Tomas
Regards,

--
François Delpierre