pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: tv@fuzzy.cz
To: François Delpierre <pgsql@pivert.org>
Cc: pgsql-php@postgresql.org
Subject: Re: How to store files into the DB with PHP. (bytea ?)
Date: Mon, 2 Feb 2009 12:55:04 +0100 (CET)
Message-ID: <38255.62.40.76.70.1233575704.squirrel@sq.gransy.com> (raw)
In-Reply-To: <200902021102.06157.pgsql@pivert.org>
References: <200902021102.06157.pgsql@pivert.org>

> 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




view thread (7+ messages)  latest in thread

Message-ID: <38255.62.40.76.70.1233575704.squirrel@sq.gransy.com>
Permalink:  ../38255.62.40.76.70.1233575704.squirrel@sq.gransy.com/
Also on:    postgresql.org/message-id/38255.62.40.76.70.1233575704.squirrel@sq.gransy.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-php@postgresql.org
  Cc: tv@fuzzy.cz, pgsql@pivert.org
  Subject: Re: How to store files into the DB with PHP. (bytea ?)
  In-Reply-To: <38255.62.40.76.70.1233575704.squirrel@sq.gransy.com>
  References: <200902021102.06157.pgsql@pivert.org> <38255.62.40.76.70.1233575704.squirrel@sq.gransy.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox