Re: Per-user schemas with inherited skeleton.

Octavio Alvarez wrote:

> 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.

Beware here. IIRC PostgreSQL's inheritance support has some major
limitations when it comes to enforcing constraints on a parent table on
data inserted/updated via a child table. See the documentation for details.

> 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.

That would be interesting, though I don't think "owner user" would be
the way to do it. If anything could be obtained without significant
cost, it'd probably be the relid of the leaf table from which that
particular record came. You could get an owning role name etc from that
relatively easily, of course.

That seems like something that'd have a performance hit, though (as each
record would be wider by one relid) and, like the oid column, might
confuse apps that don't expect extra columns not present in the table
definition to be returned.

You can get vaguely the same effect yourself in any case, though
unfortunately not with any sort of guaranteed enforcement. Add an ON
INSERT ... FOR EACH ROW trigger to the child table that sets the value
of a "username" field to the value of the "user" SQL variable, ignoring
whatever the user may have supplied for that field. Since you're giving
your users ownership of the tables they can always just drop or replace
that trigger, so it's not a security measure or suitable for accounting,
but it's good enough for informational reporting.

> 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.

Yep .. which is why the example I posted didn't use it. If it can be
avoided it should be.

Note that I'm VERY far from an expert in Pg in general and Pg security
in particular, so of course you need to evaluate all this in detail
yourself.

> 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).

I'm not entirely sure I see where this would be useful. I'd be
interested in some examples. As I mentioned before, though,

--
Craig Ringer