Skip site navigation (1) Skip section navigation (2)

Re: [pgsql-hackers] Allow GRANT/REVOKE permissions to be applied to all schema

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: [pgsql-hackers] Allow GRANT/REVOKE permissions to be applied to all schema
Date: 2005-01-28 20:49:42
Message-ID: 200501281249.42783.josh@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Matt,

> a) accept some sort of wildcard for the grant on table syntax:
>     GRANT ... ON TABLE schema.*
>
> b) use something like CASCADE for the grant on schema syntax:
>     GRANT ... ON SCHEMA CASCADE
>     In this case the grant on schema's need to swallow the permissions
>     (SELECT, INSERT, UPDATE ...) which are intended for TABLES. This
> seems to me
>     kind of strange.
>
> therefore I vote for Syntax a)
>
> What do you think?

Can't say I like either.     I'd prefer:

GRANT [PERM] ON ALL TABLES IN SCHEMA [schemaname] TO [user];

In fact, it would be good if you could multiplex this so that applicable 
grants could be performed on all objects, for example:

GRANT SELECT ON ALL TABLES, VIEWS IN SCHEMA public TO php-user;

Of course, if you enhanced this further, we'd be storing a "default 
permission" to each *new* table/view/function/etc. in the schema definition, 
which would be the ideal.  That way, this command:

GRANT SELECT, UPDATE, INSERT ON TABLES IN SCHEMA public TO php-user;

.. would set the defaults for any NEW tables created in public, and this 
command:

GRANT SELECT, UPDATE, INSERT ON TABLES IN SCHEMA public TO php-user CASCADE;

... would grant for existing tables as well.

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

Responses

pgsql-hackers by date

Next:From: Bruno Wolff IIIDate: 2005-01-28 21:03:19
Subject: Re: Allow GRANT/REVOKE permissions to be applied to all schema objects with one command
Previous:From: Josh BerkusDate: 2005-01-28 20:43:13
Subject: Re: [pgsql-hackers] Group-count estimation statistics

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group