Thread: security checks for largeobjects?
The todo list says, as follows: * Binary Data o Add security checks for large objects http://wiki.postgresql.org/wiki/Todo#Binary_Data Is anyone working on? or interested in? Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei <kaigai@ak.jp.nec.com>
KaiGai Kohei wrote: > The todo list says, as follows: > * Binary Data > o Add security checks for large objects > > http://wiki.postgresql.org/wiki/Todo#Binary_Data > > Is anyone working on? or interested in? OK, it seems to me nobody is working on the todo item. I'll also submit this feature on the v8.5 development cycle. At first, I would have a discussion about its interfaces and functionalities. Here are a few issues. * What permissions should be checked? It is quite natural to check 'read' and 'write' permission for largeobjects. In addition, we may need to consider how 'create' and 'unlink' permission should be handled. When we create a database objects under the certain schema, it checks ACL_CREATE privilege on the parent schema object. But, now largeobjects are not corresponding to any certain schemas. When we drop a database objects, it checks ownership of the target objects. But, now largeobjects does not have its owner identifier within pg_largeobject. My preference is to add a namespace and a owner id for each largeobjects and checks create permissions for the schema object, and unlink permission based on its ownership. * What interface is preferable? We have two options here. The one is an enhancement of current GRANT/REVOKE statement, such as: GRANT READ,WRITE ON LARGE OBJECT 1234 TO kaigai; The other option is a few new largeobject functions to set up permissions on largeobjects, such as: SELECT lo_grant(1234, 'kaigai', 'read,write'); My preference is the later approach because the first one consumes two new permission bits, although here is no fundamental differences to SELECT and UPDATE. Thanks, any comments please. -- OSS Platform Development Division, NEC KaiGai Kohei <kaigai@ak.jp.nec.com>
On Mon, Jun 22, 2009 at 02:00:10PM +0900, KaiGai Kohei wrote: > KaiGai Kohei wrote: > > The todo list says, as follows: > > * Binary Data > > o Add security checks for large objects > > > > http://wiki.postgresql.org/wiki/Todo#Binary_Data > > > > Is anyone working on? or interested in? > > OK, it seems to me nobody is working on the todo item. > I'll also submit this feature on the v8.5 development cycle. This seems related to SQL/MED's file handling portion. Perhaps the TODO should be merged into the SQL/MED one. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Monday 22 June 2009 16:24:44 David Fetter wrote: > On Mon, Jun 22, 2009 at 02:00:10PM +0900, KaiGai Kohei wrote: > > KaiGai Kohei wrote: > > > The todo list says, as follows: > > > * Binary Data > > > o Add security checks for large objects > > > > > > http://wiki.postgresql.org/wiki/Todo#Binary_Data > > > > > > Is anyone working on? or interested in? > > > > OK, it seems to me nobody is working on the todo item. > > I'll also submit this feature on the v8.5 development cycle. > > This seems related to SQL/MED's file handling portion. Perhaps the > TODO should be merged into the SQL/MED one. MED is management of external data, whereas the large objects are internal, no?
On Mon, Jun 22, 2009 at 05:18:51PM +0300, Peter Eisentraut wrote: > On Monday 22 June 2009 16:24:44 David Fetter wrote: > > On Mon, Jun 22, 2009 at 02:00:10PM +0900, KaiGai Kohei wrote: > > > KaiGai Kohei wrote: > > > > The todo list says, as follows: > > > > * Binary Data > > > > o Add security checks for large objects > > > > > > > > http://wiki.postgresql.org/wiki/Todo#Binary_Data > > > > > > > > Is anyone working on? or interested in? > > > > > > OK, it seems to me nobody is working on the todo item. > > > I'll also submit this feature on the v8.5 development cycle. > > > > This seems related to SQL/MED's file handling portion. Perhaps the > > TODO should be merged into the SQL/MED one. > > MED is management of external data, whereas the large objects are internal, > no? It depends on your definition. The lo interface is pretty much to objects on the file system directly. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
David Fetter <david@fetter.org> writes:
> On Mon, Jun 22, 2009 at 05:18:51PM +0300, Peter Eisentraut wrote:
>> MED is management of external data, whereas the large objects are internal,
>> no?
> It depends on your definition. The lo interface is pretty much to
> objects on the file system directly.
LO's are transaction-controlled, and they're not (readily) accessible
from outside the database. Seems rather completely different from
regular filesystem files.
(In any case, there wasn't anything I liked about SQL/MED's ideas about
external files, so I'm not in favor of modeling LO management after that.)
regards, tom lane
On Mon, Jun 22, 2009 at 11:31:45AM -0400, Tom Lane wrote: > David Fetter <david@fetter.org> writes: > > On Mon, Jun 22, 2009 at 05:18:51PM +0300, Peter Eisentraut wrote: > >> MED is management of external data, whereas the large objects are > >> internal, no? > > > It depends on your definition. The lo interface is pretty much to > > objects on the file system directly. > > LO's are transaction-controlled, and they're not (readily) > accessible from outside the database. Seems rather completely > different from regular filesystem files. Not according to SQL/MED. > (In any case, there wasn't anything I liked about SQL/MED's ideas > about external files, so I'm not in favor of modeling LO management > after that.) Good point ;) Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
David Fetter wrote: > On Mon, Jun 22, 2009 at 11:31:45AM -0400, Tom Lane wrote: >> David Fetter <david@fetter.org> writes: >>> On Mon, Jun 22, 2009 at 05:18:51PM +0300, Peter Eisentraut wrote: >>>> MED is management of external data, whereas the large objects are >>>> internal, no? >>> It depends on your definition. The lo interface is pretty much to >>> objects on the file system directly. >> LO's are transaction-controlled, and they're not (readily) >> accessible from outside the database. Seems rather completely >> different from regular filesystem files. > > Not according to SQL/MED. > >> (In any case, there wasn't anything I liked about SQL/MED's ideas >> about external files, so I'm not in favor of modeling LO management >> after that.) > > Good point ;) > > Cheers, > David. I would like to develop the feature independent from SQL/MED. -- OSS Platform Development Division, NEC KaiGai Kohei <kaigai@ak.jp.nec.com>
On Tue, Jun 23, 2009 at 10:38:59AM +0900, KaiGai Kohei wrote: > David Fetter wrote: > > On Mon, Jun 22, 2009 at 11:31:45AM -0400, Tom Lane wrote: > >> David Fetter <david@fetter.org> writes: > >>> On Mon, Jun 22, 2009 at 05:18:51PM +0300, Peter Eisentraut wrote: > >>>> MED is management of external data, whereas the large objects are > >>>> internal, no? > >>> It depends on your definition. The lo interface is pretty much to > >>> objects on the file system directly. > >> LO's are transaction-controlled, and they're not (readily) > >> accessible from outside the database. Seems rather completely > >> different from regular filesystem files. > > > > Not according to SQL/MED. > > > >> (In any case, there wasn't anything I liked about SQL/MED's ideas > >> about external files, so I'm not in favor of modeling LO management > >> after that.) > > > > Good point ;) > > > I would like to develop the feature independent from SQL/MED. If, as I suspect, SQL/MED does something that would collide with your feature, you're about to let yourself in for even more pain, as we tend to go with standard features over ones that would be unique to PostgreSQL, given the choice. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
KaiGai Kohei wrote: > KaiGai Kohei wrote: >> The todo list says, as follows: >> * Binary Data >> o Add security checks for large objects >> >> http://wiki.postgresql.org/wiki/Todo#Binary_Data >> >> Is anyone working on? or interested in? > > OK, it seems to me nobody is working on the todo item. > I'll also submit this feature on the v8.5 development cycle. > > At first, I would have a discussion about its interfaces and > functionalities. Here are a few issues. > > * What permissions should be checked? > It is quite natural to check 'read' and 'write' permission for > largeobjects. In addition, we may need to consider how 'create' > and 'unlink' permission should be handled. > When we create a database objects under the certain schema, > it checks ACL_CREATE privilege on the parent schema object. > But, now largeobjects are not corresponding to any certain > schemas. > When we drop a database objects, it checks ownership of the > target objects. But, now largeobjects does not have its owner > identifier within pg_largeobject. > > My preference is to add a namespace and a owner id for each > largeobjects and checks create permissions for the schema > object, and unlink permission based on its ownership. > > * What interface is preferable? > We have two options here. > The one is an enhancement of current GRANT/REVOKE statement, > such as: > GRANT READ,WRITE ON LARGE OBJECT 1234 TO kaigai; > > The other option is a few new largeobject functions to set up > permissions on largeobjects, such as: > SELECT lo_grant(1234, 'kaigai', 'read,write'); > > My preference is the later approach because the first one consumes > two new permission bits, although here is no fundamental differences > to SELECT and UPDATE. > > Thanks, any comments please. From the viewpoint of the implementation, we have two approaches. Someone needs to have various kind of attributes corresponding to a certain largeobject, such as owner-id, namespace-id and acls. We call them metadata. 1. Metadata within pg_largeobject Now, a largeobject consists of multiple page-frames, stored in pg_largeobject system catalog. This approach adds a few new fields to store the metadata on the pg_largeobject system catalog, and copies the metadata to all the page-frames. It will minimize the changes in largeobject subsystem, but data structure is not smart in my sense. 2. Metadata within separated system catalog. This approach adds a new system catalog to manage the metadata of the largeobjects. It will contain the owner-id, namespace-id and acls, and all the page-frames within pg_largeobject will indicate a tuple within the new system catalog. It will provide natural data structure, but scale of the changes may be larger than the first approach. My preference is the later one. It reduces the pain to manage consistency of the metadata between page-frames. Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei <kaigai@ak.jp.nec.com>
David Fetter wrote: > On Tue, Jun 23, 2009 at 10:38:59AM +0900, KaiGai Kohei wrote: >> David Fetter wrote: >>> On Mon, Jun 22, 2009 at 11:31:45AM -0400, Tom Lane wrote: >>>> David Fetter <david@fetter.org> writes: >>>>> On Mon, Jun 22, 2009 at 05:18:51PM +0300, Peter Eisentraut wrote: >>>>>> MED is management of external data, whereas the large objects are >>>>>> internal, no? >>>>> It depends on your definition. The lo interface is pretty much to >>>>> objects on the file system directly. >>>> LO's are transaction-controlled, and they're not (readily) >>>> accessible from outside the database. Seems rather completely >>>> different from regular filesystem files. >>> Not according to SQL/MED. >>> >>>> (In any case, there wasn't anything I liked about SQL/MED's ideas >>>> about external files, so I'm not in favor of modeling LO management >>>> after that.) >>> Good point ;) >>> >> I would like to develop the feature independent from SQL/MED. > > If, as I suspect, SQL/MED does something that would collide with your > feature, you're about to let yourself in for even more pain, as we > tend to go with standard features over ones that would be unique to > PostgreSQL, given the choice. Since the largeobject is originally a unique feature in PostgreSQL, I think it can be considered independently from the standard feature. However, we have no fixed security design here. If you can provide more preferable security design, could you suggest us? I never disagree to improve the features. Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei <kaigai@ak.jp.nec.com>