Permission template

Hi all,

Reading up on the recent (and recurring) discussion about granting
privileges on multiple tables I suddenly wondered whether the CREATE
TABLE LIKE approach could be used to copy privileges to a to-be-
created table. From the documentation it doesn't look like it, but
wouldn't that be neat? It's pretty much like using a database as a
template, but for tables instead of databases.

Here's an example of what I mean:
CREATE ROLE normal_user;

-- Create our template table
CREATE TABLE user_template ();
REVOKE ALL ON user_template TO normal_user;
GRANT SELECT ON user_template TO normal_user;

-- Use our template table to create other tables with the same
privileges
CREATE TABLE accounts (
account_id serial PRIMARY KEY,
name text NOT NULL,
LIKE user_template INCLUDING PRIVILEGES
);

etc.

After which accounts would have only SELECT privileges for normal_users.

Of course with this approach you'd still have to alter privileges for
each table if you change your mind on who can access what, but it
should make setting up privileges in a consistent way easier. Some
way to copy privileges from another object might be a nice addition
to this... Comments?

Regards,

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,47dba99b233093511810745!