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

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

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

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.

regards
Tomas