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.