Re: Per-user schemas with inherited skeleton.

alvarezp(at)alvarezp(dot)ods(dot)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