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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
