Re: Per-user schemas with inherited skeleton.

On Mon, 2009-01-05 at 13:05 +0900, Craig Ringer wrote:
> alvarezp(at)alvarezp(dot)ods(dot)org wrote:
>
> > On user account creation, the schema gets created and the interface
> > tries to do a "CREATE TABLE my_relation () INHERITS
> > (_skel.my_relation);" as the new role, but PostgreSQL returns the error
> > "must be owner of relation my_relations". I am assuming it refers to
> > _skel.my_relation.
>
> > The following questions arose during the preparation of this test:
> >
> > Why is ownership needed on the parent table?
>
> I don't know for sure, but I'd say it's a security issue. Granting a
> user ownership of a table that inherits another table gives them, as the
> owner of the child table, the ability to (via the child table) INSERT
> into the parent table, as well as DELETE/UPDATE/SELECT rows they've
> previously inserted themselves.

I see. So, in other words, inserting a record in a table they don't have
INSERT privileges from would be just a matter of inheriting that table.

For the tables given in the _skel schema from this model, this is be the
way to go. The _skel tables are empty, and have foreign keys to other
secured-tables that prevent two users from having the same value in the
primary keys.

It is also possible to list the records from all users with a simple
SELECT directly from the the parent table in the _skel schema.

> You can also have the owner of the parent table CREATE the child
> table
> with the inheritance relationship, then ALTER TABLE ... OWNER TO to
> give ownership of the child table away. You can wrap this within a
> SECURITY DEFINER Pl/PgSQL function if you want the eventual owning user
> to be able to do it. eg, assuming for the sake of the example that
> you're on a machine with `trust' authentication set to the db:

This is what bewildered me. So if parent and child tables CAN have
different owners, this is perfect for the model. Users can extend their
use of the database with an app of their own connecting with their own
role and permissions, it is still secure (or so it looks), and
administration is relatively simple.

> \c - super
> CREATE SCHEMA super;
> CREATE TABLE super.parent (x integer);
>
> -- insert a dummy row, too. No end user should ever be able to see it,
> -- and it's just here to demonstrate that one user shouldn't be able
> << snipped rest of SQL demonstration >>

Thank you for this detailed example. It proves to be secure enough.

CHECK constraints or FOREIGN keys to secured tables are present so the
users don't fill up the tables with dummy rows to perform a DoS. This
can or can not be ultimately desired, though.

> I guess it might be handy for a new right might be created called say
> 'INHERIT'. This would let a table owner delegate the right to inherit
> from the table to other users. Such a right does not presently exist.
> There might be good reasons for it that I don't know about, or it might
> simply be that nobody has wanted it - or at least, wanted it enough to
> bother implementing it.
>
> Do you? To me, it seems pretty easy to just create the table with the
> same ownership as the parent then ALTER TABLE ... OWNER TO it away.

What *would* be even nicer is for PostgreSQL to expose the owner of the
row as a special column (say, "__owner_user") in a SELECT statement to
the parent table.

An INHERITS privilege is a very nice proposal for extension, though
through the use of SECURITY DEFINER we can achieve the same result and
it is good enough for this model. SECURITY DEFINER is comparable to
having a SUID-root program in a Unix system, with everything it implies.

Also, if this privilege would exist, PostgreSQL would need to provide
the owner of the parent table a way to use the before-mentioned special
__owner_user column as part of primary and unique keys.

This would be a good on the way of having a native way of setting up
row-level security based on user ownership (a la virtual private
database with some shared data).

Octavio.