Thread: Per-user schemas with inherited skeleton.
Hello. I am testing different security models to use on a new database, where I want different users to be restricted to their own data. Users would be able to connect to the database through an interface, but in particular cases of advanced users, they will be able to directly connect to the database and create their own tables and extend existing ones if needed. I have currently discarded two other models: (1) per-user databases, as getting the data from all users at once would be difficult and SELECTing from another user would be next to impossible, and (2) per-user schemas with "CREATE TABLE (LIKE parent_table)", as getting the data from all users at once would also be difficult and modifying the column definition on the user tables would be pretty much error-prone. The model I am testing right now is per-user schemas with inheritance. Non-user schemas are to be named with an underscore prefix. The model includes a "_skel" schema that defines each table the user should have on account creation, similar to /etc/skel for home directories. 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. I am aware that this can be done with views; it is my current fallback. However, modification of the underlying table structure is cumbersome as it implies updating the view and a number of rules and the view for each modification to the table. Furthermore, I can't use CREATE OR REPLACE VIEW to change the number of columns on a view. The following questions arose during the preparation of this test: Why is ownership needed on the parent table? Is there a way to let PostgreSQL to allow inherited tables to be owned by different roles? If PostgreSQL were to let inherited tables to be owned by different roles, what would be wrong on letting the parent table owner SELECT on the parent table, and having PostgreSQL return all data from the children tables as well, even if the owner of the children tables had revoked the permissions? This possibility is similar to how views work. Other than the previously mentioned, what other mechanisms are available to enhance database security this way? Thank you for your time, and best regards. Octavio.
alvarezp@alvarezp.ods.org wrote:
> (2) per-user schemas
> with "CREATE TABLE (LIKE parent_table)", as getting the data from all
> users at once would also be difficult and modifying the column
> definition on the user tables would be pretty much error-prone.
I'd think about this one, personally. You can get data from all users
with a UNION that selects only the common subset of fields. It'll be
somewhat ugly, but effective, and wouldn't be hard to build
programatically with PL/PgSQL using user/schema data obtained from
pg_catalog.
There is the issue that users can alter/drop fields from what's supposed
to be the shared subset of fields, though.
> 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.
What you CAN do is create the child table with the same owner as the
parent table, then GRANT appropriate rights to the user. This does not
give the user the ability to ALTER the child table, though, so they
can't add fields, constraints, etc.
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:
\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 to
-- see another's data.
INSERT INTO super.parent(x) VALUES (99);
-- Now, the user setup function:
CREATE OR REPLACE FUNCTION user_setup(newuser text) RETURNS void AS $$
BEGIN
EXECUTE 'CREATE SCHEMA ' || newuser;
EXECUTE 'GRANT ALL ON SCHEMA ' || newuser || ' TO ' || newuser;
EXECUTE 'ALTER USER ' || newuser ||
' SET search_path TO ''' || newuser || '''';
-- Now create newuser's inherited tables and grant them ownership.
EXECUTE 'CREATE TABLE ' || newuser || '.' ||
'tablename(y integer) inherits (super.parent)';
EXECUTE 'ALTER TABLE ' || newuser || '.' ||
'tablename OWNER TO ' || newuser;
END;
$$ LANGUAGE 'plpgsql';
-- Note that the above function could also be written with
-- SECURITY DEFINER rights, where it gets the user name to set
-- up from the `user' built-in sql variable instead of as a
-- parameter. That way the end user could run it to set their
-- account up. However, since you're going to have to
-- CREATE USER for them anyway, you may as well just
-- SELECT user_setup('fred'). Maybe even bundle the CREATE USER
-- into the user_setup function...
-- In this case, we just create a demo user:
CREATE USER test WITH PASSWORD 'test';
SELECT user_setup('test');
-- now the end user connects and has the ability to mess with
-- their tables, schema, etc.
\c test test 127.0.0.1
Password for user test:
You are now connected to database "test" on host "127.0.0.1" as user "test".
test=> \d
List of relations
Schema | Name | Type | Owner
--------+-----------+-------+-------
test | tablename | table | test
(1 row)
test=> select * from tablename;
x | y
---+---
(0 rows)
test=> insert into tablename (x,y) values (4,7);
INSERT 0 1
test=> select * from tablename;
x | y
---+---
4 | 7
(2 rows)
test=> select * from super.parent;
ERROR: permission denied for schema super
test=> ALTER TABLE tablename ADD COLUMN z integer;
ALTER TABLE
test=> \d tablename
Table "test.tablename"
Column | Type | Modifiers
--------+---------+-----------
x | integer |
y | integer |
z | integer |
Inherits: super.parent
test=> ALTER TABLE tablename DROP COLUMN x;
ERROR: cannot drop inherited column "x"
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.
> Is there a way to let PostgreSQL to allow inherited tables to be owned
> by different roles?
Not that I know of, and given the security implications I'd be a bit
nervous about it unless it was done via an explicitly GRANTed right.
> If PostgreSQL were to let inherited tables to be owned by different
> roles, what would be wrong on letting the parent table owner SELECT on
> the parent table, and having PostgreSQL return all data from the
> children tables as well, even if the owner of the children tables had
> revoked the permissions? This possibility is similar to how views work.
The problem is that if a newuser can create an table they own with an
inheritance relationship to a parent it doesn't own, the newuser can
"steal" rights to the parent table. Even if you require that the newuser
has select/insert/update/delete rights at time of relationship creation,
that doesn't help that much since revoking those rights later won't
delete the relationship.
The only right they can really steal, as such, is INSERT, since they can
only SELECT/UPDATE/DELETE those records they added via their own child
table. That's quite bad enough, though, and testing for INSERT rights
before permitting inherited table creation isn't good enough (as noted
above) because of issues with revocation.
> Other than the previously mentioned, what other mechanisms are available
> to enhance database security this way?
Doing all your access through a functional interface where you enforce
your security rules in PL/PgSQL in cases where the existing privelege
model is insufficient.
--
Craig Ringer
On Mon, 2009-01-05 at 13:05 +0900, Craig Ringer wrote: > alvarezp@alvarezp.ods.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.
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
On Mon, 2009-01-05 at 13:05 +0900, Craig Ringer wrote: > alvarezp@alvarezp.ods.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.
On Sun, Jan 4, 2009 at 9:05 PM, Craig Ringer <craig@postnewspapers.com.au> wrote: > alvarezp@alvarezp.ods.org wrote: > >> (2) per-user schemas >> with "CREATE TABLE (LIKE parent_table)", as getting the data from all >> users at once would also be difficult and modifying the column >> definition on the user tables would be pretty much error-prone. If you are just replacing a couple of tables, and they don't need most of the tables but can use the public schema this might be worthwhile for some projects. Everyone uses the public schema as the common one. I agree with everything you posted after this, and a nice example of doing it. >> Is there a way to let PostgreSQL to allow inherited tables to be owned >> by different roles? > > Not that I know of, and given the security implications I'd be a bit nervous > about it unless it was done via an explicitly GRANTed right. I hope here you're meaning to have tables that are inheritable by various non-role members. It works as long as everyone's in the same group role with the right permissions. Since you'd have to change ownership to the group role for the parent table, everyone would, in effect, own it now. But if you wanna do it... The admin creates a group called sharing, all users are granted sharing, so when they create a table they can share it or not. If they share it in the public schema then any user in the group can inherit it, and search_path can make it the "default" table so to speak. The child tables are automatically created with the role of the user not the group, so your user would have to remember to grant select to the sharing role when they created a table.
Scott Marlowe wrote: >>> Is there a way to let PostgreSQL to allow inherited tables to be owned >>> by different roles? >> Not that I know of, and given the security implications I'd be a bit nervous >> about it unless it was done via an explicitly GRANTed right. > > I hope here you're meaning to have tables that are inheritable by > various non-role members. It works as long as everyone's in the same > group role with the right permissions. Since you'd have to change > ownership to the group role for the parent table, everyone would, in > effect, own it now. But if you wanna do it... That's right - I refer to inheritance by a user that's not a member of the role that owns the table. If the inheriting users *are* a member of the owning role of the parent table, then they can select and update the shared-structure part of OTHER users' records via the parent table, as well as their own. IIRC they can delete other users records via the parent table, too. Not ideal if the various users are supposed to be blind to each others' data, as appears to be the case here. -- Craig Ringer
On Wed, Jan 7, 2009 at 11:39 PM, Craig Ringer <craig@postnewspapers.com.au> wrote: > Scott Marlowe wrote: > >>>> Is there a way to let PostgreSQL to allow inherited tables to be owned >>>> by different roles? >>> >>> Not that I know of, and given the security implications I'd be a bit >>> nervous >>> about it unless it was done via an explicitly GRANTed right. >> >> I hope here you're meaning to have tables that are inheritable by >> various non-role members. It works as long as everyone's in the same >> group role with the right permissions. Since you'd have to change >> ownership to the group role for the parent table, everyone would, in >> effect, own it now. But if you wanna do it... > > That's right - I refer to inheritance by a user that's not a member of the > role that owns the table. > > If the inheriting users *are* a member of the owning role of the parent > table, then they can select and update the shared-structure part of OTHER > users' records via the parent table, as well as their own. IIRC they can > delete other users records via the parent table, too. Not ideal if the > various users are supposed to be blind to each others' data, as appears to > be the case here. No, it doesn't work that way. Perms on the parent table are just that, perms on the parent table. I just tested it. Given a role called sharing, and two members stan and ted, and a parent table owned by sharing, the users have to grant select on their tables to the other users or the role they belong to or the other users can't see it or change it.
On Wed, Jan 7, 2009 at 11:39 PM, Craig Ringer <craig@postnewspapers.com.au> wrote: > Scott Marlowe wrote: > >>>> Is there a way to let PostgreSQL to allow inherited tables to be owned >>>> by different roles? >>> >>> Not that I know of, and given the security implications I'd be a bit >>> nervous >>> about it unless it was done via an explicitly GRANTed right. >> >> I hope here you're meaning to have tables that are inheritable by >> various non-role members. It works as long as everyone's in the same >> group role with the right permissions. Since you'd have to change >> ownership to the group role for the parent table, everyone would, in >> effect, own it now. But if you wanna do it... > > That's right - I refer to inheritance by a user that's not a member of the > role that owns the table. > > If the inheriting users *are* a member of the owning role of the parent > table, then they can select and update the shared-structure part of OTHER > users' records via the parent table, as well as their own. IIRC they can > delete other users records via the parent table, too. Not ideal if the > various users are supposed to be blind to each others' data, as appears to > be the case here. Also alter table gets locked by the child tables. If stan, a member of sharing, tries to change the inherited table top, which user ted has inherited, he gets an error saying he has to have alter perms on ted's table.