security checks for largeobjects?

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>

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>

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

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>