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.