Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1aKszL-0007RT-8g for pgsql-sql@arkaria.postgresql.org; Sun, 17 Jan 2016 19:27:47 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1aKszK-0002nA-Rd for pgsql-sql@arkaria.postgresql.org; Sun, 17 Jan 2016 19:27:46 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1aKsyJ-0001Ra-5Z for pgsql-sql@postgresql.org; Sun, 17 Jan 2016 19:26:44 +0000 Received: from nm40-vm7.bullet.mail.bf1.yahoo.com ([72.30.239.215]) by magus.postgresql.org with esmtps (TLS1.0:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84) (envelope-from ) id 1aKsyE-0002Lc-9Q for pgsql-sql@postgresql.org; Sun, 17 Jan 2016 19:26:42 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ymail.com; s=s2048; t=1453058795; bh=9GGgGQIKaXU9mJHhi33UrDAp70BYSf8WKwOg4+GafAk=; h=Date:From:Reply-To:To:Cc:In-Reply-To:References:Subject:From:Subject; b=hhA0CrCHeQBx1dybBTraB9N3ETuCuxr/uCeVDtSZBKV1wsrLwXmF40w2cz8QIWZOgebIT6ruIh//uQjYTj6LOXb/jzLV2yXgGIeI8PRPaL6Twh9eV/4wXiXUHPOAl8V4GzAwd5lnCkoigcm6q7F5QNLJAUJlKcUSHiApwzLGjgkNqw0IzMaqitWlEXo35TATZDYQMR9NoPTj4LxOyQSac8ISyb7fwDaB/29BYmXrWlsnt99sfjUTCiDM/eipVG9z6Avyx2yXYwLYQlWcaVopNmxwP/easgMVfw1Om4eFcYThAJH+upWcl8WlP+HqYt4m8c/e4pMohQrBmKJGX4d8cA== Received: from [98.139.215.140] by nm40.bullet.mail.bf1.yahoo.com with NNFMP; 17 Jan 2016 19:26:35 -0000 Received: from [98.139.215.253] by tm11.bullet.mail.bf1.yahoo.com with NNFMP; 17 Jan 2016 19:26:35 -0000 Received: from [127.0.0.1] by omp1066.mail.bf1.yahoo.com with NNFMP; 17 Jan 2016 19:26:35 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 537606.54337.bm@omp1066.mail.bf1.yahoo.com X-YMail-OSG: 0Bhod_EVM1n4l18RKeQvWFhyohIOM_bGyrGrZmkbKFoHytsdFy2Pxz27yA7NxD. En9P.2ZYySLF.ziI4VNPowrBxTBiMUZ3sdbJdD0ey0MYQ_VMQBsI2VZORI8t_mGvoovZu57DCss5 7qbyD4axETrkHm_YfDe.uVVnefOS0WklmgVz26pY4V9dVtsnIqADNvHtA7bJyYKR5fg3foEDIxrb 2AdFQuPJQInNM2PXiTLdeHrcdnEXhhdlagmBCQdhmDJZUXYoQMYKQyLtBH43zdNEovjxuTNbzmnX OpFOzH5o6p5tE7lhsfc.IykMDQ4gaFwe5xyhuG2Dg9t6YB5TmGqlzucOOQeXVqqa6FZUL7nrwqUy oiRHBEqx18tSKiFgOko5fY2tsE1.YkUyqc0D15fQee3uAUGeOUNiueJUINJnmMd2LzFuBj4V5afE 9WkHiagmL7o3PYo6WUMA8nA_bnFSDTSjhgZ1kqNkhiRlZW7b4MAt3ShriSOPjcTPgTCOUbanoiJd _Kjv6S4UQ7ScBxqQF8.7deArl_tsT8_rBKw-- Received: by 66.196.80.145; Sun, 17 Jan 2016 19:26:35 +0000 Date: Sun, 17 Jan 2016 19:26:34 +0000 (UTC) From: Eugene Yin Reply-To: Eugene Yin To: Andreas Joseph Krogh Cc: Postgres List Message-ID: <825207667.6348678.1453058794606.JavaMail.yahoo@mail.yahoo.com> In-Reply-To: References: Subject: Re: BLOBs MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_Part_6348677_2111205192.1453058794606" Content-Length: 15174 X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org ------=_Part_6348677_2111205192.1453058794606 Content-Type: multipart/alternative; boundary="----=_Part_6348676_1786868675.1453058794594" ------=_Part_6348676_1786868675.1453058794594 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable BLOB binary large object see Large Object Support =20=20=20 - =C2=A0Minuses=20 =20=20=20 - must use different interface from what is normally used to access BLO= Bs.=20 - Need to track OID. Normally a separate table with additional meta dat= a is used to describe what each OID is. - (8.4 and <8.4) No access controls in database. - Sometimes advised against (basically you only need them if your entry= is so large you need/want to seek and read bits and pieces of it at a time= ).=C2=A0 https://wiki.postgresql.org/wiki/BinaryFilesInDB Do one really:=C2=A0=20=20=20 - Need to track OID. Normally a separate table with additional meta data= is used to describe what each OID is. Thanks Eugene =20 On Monday, January 11, 2016 11:10 PM, Andreas Joseph Krogh wrote: =20 P=C3=A5 tirsdag 12. januar 2016 kl. 02:32:46, skrev Eugene Yin : I did some search on the OID data type. =C2=A0Here is something I found reg= arding to the deletion of the OID data.=C2=A0QUOTE:=C2=A0"The Large Object = method for storing binary data is better suited to storing very large value= s, but it has its own limitations. Specifically deleting a row that contain= s a Large Object reference does not delete the Large Object.=C2=A0=C2=A0Del= eting the Large Object is a separate operation that needs to be performed.= =C2=A0=C2=A0Large Objects also have some security issues since anyone conne= cted to the database can view and/or modify any Large Object, even if they = don't have permissions to view/update the row containing the Large Object r= eference."=C2=A0From:=C2=A0https://jdbc.postgresql.org/documentation/84/bin= ary-data.html=C2=A0=C2=A0So I have two questions:=C2=A01) =C2=A0If it is tr= ue that "Deleting the Large Object is a separate operation that needs to be= performed.", after the deletion, what operation I need to perform, in orde= r to delete the OID data in the table? =C2=A0Possiblely put into an after t= rigger=C2=A02) "Large Objects also have some security issues since anyone c= onnected to the database can view and/or modify any Large Object". =C2=A0Wi= ll this pose a real risk to the security? =C2=A0or just a forethought? =C2=A01) You don't need to perform any "after delete"-operation as a develo= per. But the DBA (or someone else) has to execute vacuumlo (see "man vacuum= lo" for more info) using cron or some other periodic scheduling tool.=C2=A0= 2) Your mileage may vary, but for our app this isn't an issue.=C2=A0PS: 8.4= is EOL, use a more current version, preferably 9.5.I also recommend the -n= g driver as it's the only one with proper BLOB-support, as mentioned earlie= r in this thread.=C2=A0--Andreas Joseph KroghCTO / Partner - Visena ASMobil= e: +47 909 56 963andreas@visena.comwww.visena.com=C2=A0 =20=20= ------=_Part_6348676_1786868675.1453058794594 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable

BLOB binary large object see Large Object Support


  •  Minuses=20
      • must use different int= erface from what is normally used to access BLOBs.=20
    • Need to track OID. Normally a separate table w= ith additional meta data is used to describe what each OID is.
    • (8.4 and = <8.4) No access controls in database.
    • Sometimes advised against (basically you only need them if y= our entry is so large you need/want to seek and read bits and pieces of it = at a time). 

    <= /div>

    Do one really: 
    • Need to track OID. Normally a separate table with additional meta d= ata is used to describe what each OID is.


    <= b>
    Thanks<= /div>

    Eugene

    <= br>
    On Monday, Ja= nuary 11, 2016 11:10 PM, Andreas Joseph Krogh <andreas@visena.com> wr= ote:


    P=C3=A5 tirsdag 12. januar 2016 kl. 02:32:46, skrev= Eugene Yin <eu= geneymail@ymail.com>:
    I di= d some search on the OID data type.  Here is something I found regardi= ng to the deletion of the OID data.
    &nbs= p;
    Q= UOTE:
    &nbs= p;
    "The= Large Object method for storing binary data is better suited to storing ve= ry large values, but it has its own limitations. Specifically deleting a ro= w that contains a Large Object reference does not delete the Large Object.&= nbsp;
    &nbs= p;
    Dele= ting the Large Object is a separate operation that needs to be performed.&n= bsp;
    &nbs= p;
    Larg= e Objects also have some security issues since anyone connected to the data= base can view and/or modify any Large Object, even if they don't have permi= ssions to view/update the row containing the Large Object reference."
    &nbs= p;
    &nbs= p;
    &nbs= p;
    So I= have two questions:
    &nbs= p;
    1) &= nbsp;If it is true that "Deleting the Large Object is a separate operation = that needs to be performed.", after the deletion, what operation I need to = perform, in order to delete the OID data in the table?  Possiblely put= into an after trigger
    &nbs= p;
    2) "= Large Objects also have some security issues since anyone connected to the = database can view and/or modify any Large Object".  Will this pose a r= eal risk to the security?  or just a forethought?
     
    1) You don't need to perform any "after delete"-operation as a develop= er. But the DBA (or someone else) has to execute vacuumlo (see "man vacuuml= o" for more info) using cron or some other periodic scheduling tool.
     
    2) Your mileage may vary, but for our app this isn't an issue.
     
    PS: 8.4 is EOL, use a more current version, preferably 9.5.
    I also recommend the -ng driver as it's the only one with proper BLOB-= support, as mentioned earlier in this thread.
     
    --
    Andr= eas Joseph Krogh
    CTO / Partne= r - Visena AS
    Mobile: +47 = 909 56 963
    3D""
     


    = ------=_Part_6348676_1786868675.1453058794594-- ------=_Part_6348677_2111205192.1453058794606 Content-Type: image/png Content-Transfer-Encoding: base64 Content-Disposition: inline Content-ID: iVBORw0KGgoAAAANSUhEUgAAAIUAAAAYCAYAAADUIj6hAAAABHNCSVQICAgI fAhkiAAABzBJREFUaEPtmNFxHDcMhmVP3i1VECpvnjzkVIHWFfhcgVcVRKrA UgWRK/C6Al8H3lTgy0PGbzFdQc4VJP/HADs43q6kROeJNbOYgQACIAgCWJKn g4MZ5gxUGXh0U0b+SIuF9G+EWXj2Q15vbrE/NPtk9uub7Gfdt5mBx1NhqSEo 8HshjbEUtlO2yIM9tsx5dZP9rPt2MzDZFAqZE4LGcFjdsg3saQaHt7fY70X9 49OnaS+OZidDBkavD33157L4xaw2os+Mp/DAC10l2XhOCeStj0W5ajrJL8Wf Ci80Xgf9vVlrhg9yRONe/f7xI2vNsIcM7JwU9o6oGyJrLT8JOA1aX9sKP4wl 94bAniukEbo/n7YPmuTETzIab4Y9ZWCrKexd8M58lxPCvvD6aihfvexbEQoP YO8NQRO0JnddGO6FJYaVsBde7cXj7KRk4LsqDxQzCYeGsJNgaXZe+JXkyGgW INq3Gp+bHNILz+wEYs5qH1eJrouNrpDXtk4O6x1IvtD40GWyJYYdkF0jIbYZ lN16xygIgl9smbMDsmFdfAKb23xGB8H/mv3tOJfArs1kukk79DEPdQ5u0g1v CvvqKXIW8mZYW+F3Tg4r8HvZkQCCLydK8EFMQCe5N8RgL9mRG0SqQFuNvdE6 beSs0qPDBuCdg0+gvClso8SbTO4ki7mQzQqBrcMHQPwReg2wW8umEe/+L8T/ LEzBGF9nXjzZ46s+ITHvhegW8LL39xm6App7KYL/GE+vcYnFbJiP/0aYhdiC nRC7jaj7eiK2ETInC5PRF6LYkSN0+HabF75WuT6syCyI0YkVGGMvUC33AmfZ TDUEj8u6IVhuEhRUJ2U2g6UlugyNb03Hl9obHwlxJbcRzcYjO4QPjVfGFTQa v4vrmp7cpMp2qbHnBxWJbisbho2QXI6C1sIHDUFhH4Hij4UbIev66eANeiwb kA+LBsO36zAHzoXU7AhbqLAXEiOYTXcSdO8VS9L4wN8UBIYhBd6oSUgYMijO kecRuTdQa/YiBXhbXFcnCnI2SrfeBG9NydrLYMhGHa5qB9pQIxlzAE6Okjzx JO5afGe6kmhBicWKQNKuTZ5EW+MjQY8v/9rQ0bhJSJyNGUe/JH1t8h1iMbdS PAvxHYin6YmN9YBXQmTYZZNh14vHhhhal5vtcIrJjmvszPRJdExH3MWHvyko BEc9CoCGWJisOLOGoCORs1FvIMbYA8z3kwM59oemy6LlWrLxFLmWgiQAfEGd 8S+NssbK+EhyGLxUkhhiy717waBqHOJYSEacwBejkFNhjJNjv/gANOdQxPfc iP/JVBCK2cOIcg3RRJ+CPrLPNVhhN6F38VKMF3XLlIJrjU5C8gMFxvKDvOcP c4rVNjCn7KM0BV+161V8viSCuJZ8SITGJGEh7IUUlxOFMYUH1kJOCN4Wh+I5 pqCuK01k40kSNtnKiKIlUfxAgW5sU5JlS05rtt5YFJF1SWoqHv6BRgQcA4/b db9WRjmMk3jyUMAbIoyJq9e4cVmgzKt9j5gNJ/aYDhk+hhjExwaPcz5PObA5 Zd9bvz5UzCTZuZDidu5AchpiKSwPR+TV1bCWqBQ9nCjJ5neivC82Nr4LeS2j 1gw5LUqwBuhGQQU5UwHeSslXkwIynz2U2A2yKDgG7OffwLA3TpGRpk0Tzpj3 ZEJXi/GRa6GN0e0NtppChePdcAz1FTS+FN8Kpxoiykk+J8fC5tenjbu9kSqp HLu9jBrhUuhNwVGbpyZrzrl0HPVD8SXj5EOOD/eDC64VjvYBZLuUbIVAfBN1 t/C/SU+cAGtdGo+fVnzycUX5wmn6eCKPmWYJG2E/ppTsuRCbvUD9fwquksG5 GqLVKhzDQ3GrEyLKSXhsiK3T5j9EyxffCFOYi2wUlPyFFDQAhehESDjQGoX0 ho0oj8RPou6TxHJdcT0NTcWkO0AnG7+uXsnHqcas/72wvWF+mSf7N/Watp9k TXoluzeS0fB99CcZ/hvhSZTfh99pispZOXL9KrGrARkNUMu9ITbScZWs7xOY Nt9pwxSZtYDsX/GE3xTkrXgwAsXmfud08FiTeC+m2/o7ppo+PTS/NBK5ARpD G5YHr++DpmVfreYdiX8mnp+DzPEG9WbqJON0JBenZofssxBA1gj5NXGvfJu/ Qh7HwQh/VDUEyUxCit5hH94QfKkEdu+GwK8BX0hvCF+D67xhjmXQCXMwJCZ+ olK0A1EKRCE4snsh42w8Mv/Zhxw9iD7Cjo7CyQC/KyF6oBfShL4PYgG+CHsY KyZxvxZSZBAgjhIzYDz+AbfD37GtbaoSKzgGt+lKfI/GZtay6vG4VXTpPsh+ IcQhOk9I7WYeP5AM3HZ9+DaSGIp9HIuuhuC4pCGGx+YD2fcc5tfIAA0h/Et4 /jX8zz4fWAasIf4UbR9Y6HO4d8jAnd4U0Y8ageuCB+c+H5R3CHU2mTMwZ+B/ y8DfSMBLLOYXVuEAAAAASUVORK5CYII= ------=_Part_6348677_2111205192.1453058794606 Content-Type: text/plain Content-Disposition: inline Content-Transfer-Encoding: 8bit MIME-Version: 1.0 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql ------=_Part_6348677_2111205192.1453058794606--