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