On Mon, 2009-01-05 at 13:05 +0900, Craig Ringer wrote: > alvarezp@xxxxxxxxxxxxxxxx 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
