Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 30E4C632C32 for ; Mon, 2 Feb 2009 08:55:43 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 96593-08 for ; Mon, 2 Feb 2009 08:55:39 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mirapoint3.brutele.be (mirapoint3.brutele.be [212.68.199.148]) by mail.postgresql.org (Postfix) with ESMTP id 3A25D632C1D for ; Mon, 2 Feb 2009 08:55:38 -0400 (AST) Received: from abraham.pivert.org (user-85-201-95-140.static.tvcablenet.be [85.201.95.140] (may be forged)) by mirapoint3.brutele.be (MOS 3.8.7a) with ESMTP id AUA71708; Mon, 2 Feb 2009 13:55:05 +0100 (CET) Received: from localhost (localhost [127.0.0.1]) by abraham.pivert.org (Postfix) with ESMTP id 78A021DD5A for ; Mon, 2 Feb 2009 13:55:34 +0100 (CET) X-Virus-Scanned: amavisd-new at appart.pivert.org Received: from abraham.pivert.org ([127.0.0.1]) by localhost (mail.pivert.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id yWS5iWh1YXgT for ; Mon, 2 Feb 2009 13:55:32 +0100 (CET) Received: from bart.localnet (unknown [192.168.232.34]) by abraham.pivert.org (Postfix) with ESMTP id 0B7CC1DC3C for ; Mon, 2 Feb 2009 13:55:32 +0100 (CET) From: =?iso-8859-2?q?Fran=E7ois_Delpierre?= To: pgsql-php@postgresql.org Subject: Re: How to store files into the DB with PHP. (bytea ?) Date: Mon, 2 Feb 2009 13:55:34 +0100 User-Agent: KMail/1.11.0 (Linux/2.6.27-11-generic; KDE/4.2.0; i686; ; ) References: <200902021102.06157.pgsql@pivert.org> <38255.62.40.76.70.1233575704.squirrel@sq.gransy.com> In-Reply-To: <38255.62.40.76.70.1233575704.squirrel@sq.gransy.com> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-2" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Message-Id: <200902021355.35100.pgsql@pivert.org> X-Junkmail-Status: score=10/50, host=mirapoint3.brutele.be X-Junkmail-SD-Raw: score=unknown, refid=str=0001.0A090209.4986ED48.022F,ss=1,fgs=0, ip=85.201.95.140, so=2008-05-01 23:44:25, dmn=5.7.1/2008-09-02 X-Junkmail-IWF: false X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200902/3 X-Sequence-Number: 3979 On Monday 02 February 2009 12:55:04 tv@fuzzy.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 minimi= ze > > 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 mandat= ory=20 here. If you use stored procedures for security, the credentials that are stored = on=20 your web server are restricted to execute procedures, and eventually have a= =20 read only access to some tables with no critical informations (ie=20 translations), all other tables are simply forbidden for the web server use= r. =46or this security level to work, it means that you use a "login" stored=20 procedure that returns a "ticket" to the web server and/or web browser, val= id=20 for a certain amount of time and for the source IP. Every other procedures= =20 will require a valid "ticket" in the arguments before it returns data.)=20 This way, the sessions are handled within the database, and the user/passwo= rd=20 stored in the front end web server cannot read any data from the database=20 without going through a stored procedure that enforce the security. An other option maybe would be to authenticate users on the database direct= ly,=20 and only use a stored procedure (with security definer) to create new users= =2E I=20 do not know exactly the pro/cons to work this way. I would prefer to keep m= y=20 users within an application table instead of the DB (and in a system table)= ,=20 but this may be the easiest solutions as the stored procedures would no mor= e=20 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 stora= ge > > 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= =20 escape ? I assume that you're talking about memory_limit parameter of PHP on the web= =20 server. Can we still use streaming through a stored procedure ?=20 > > 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 forc= ed=20 to use a stored procedure to read or write data. So, all the PHP fuctions o= n=20 LOB are useless I guess. > > regards > Tomas Regards, =2D-=20 =46ran=E7ois Delpierre