Anticipatory privileges

If I am reading the (7.4) docs correctly, privileges can be granted
only with respect to tables that exist at the time the GRANT command
is given - there is no

GRANT ALL ON * TO PUBLIC

or some such that would result in subsequently created tables having
public privileges.

Is this so?

Thanks.

- John D. Burger
MITRE

John D. Burger wrote:
> If I am reading the (7.4) docs correctly, privileges can be granted
> only with respect to tables that exist at the time the GRANT command
> is given - there is no
>
> GRANT ALL ON * TO PUBLIC
>
> or some such that would result in subsequently created tables having
> public privileges.
>
> Is this so?

Yes.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera wrote:

>> If I am reading the (7.4) docs correctly, privileges can be granted
>> only with respect to tables that exist at the time the GRANT command
>> is given

> Yes.

In fact, I have to individually grant access to each table, and any
associated sequences, yes? How dangerous is it to UPDATE pg_class
directly, perhaps copying the relacl column for a table that I've
done by hand with GRANT. I'm thinking something like this:

=> grant all on annotations to public;
=> update pg_class set relacl = (select relacl from pg_class where
relname = 'annotations')
where relnamespace = (select oid from pg_namespace where nspname =
'public');

This will "grant" access to indexes and other stuff that may be
unnecessary, but is this a sound approach? (By the way, are there in
fact any other kinds of objects that I may need to allow access to,
other than tables and sequences?)

Another solution to my access control issues is to change the owner
of the tables and sequences. Can I safely do this with an UPDATE on
pg_class?

Thanks, and sorry if these are dumb questions, but I haven't been
able to glean the answers directly from the docs.

- John Burger
MITRE

"John D. Burger" <john(at)mitre(dot)org> writes:
> How dangerous is it to UPDATE pg_class
> directly, perhaps copying the relacl column for a table that I've
> done by hand with GRANT.

You can do it, and it will seem to work. However, unless you also make
entries in pg_shdepend, bad things will happen if you later drop any of
the users mentioned in the ACL. Your code will also be vulnerable to
breakage in future releases if we change any of these details.

A better approach is to write a plpgsql function that assembles and
EXECUTEs the required GRANT commands.

regards, tom lane

Tom Lane wrote:

>> How dangerous is it to UPDATE pg_class
>> directly, perhaps copying the relacl column for a table that I've
>> done by hand with GRANT.
>
> You can do it, and it will seem to work. However, unless you also
> make
> entries in pg_shdepend, bad things will happen if you later drop
> any of
> the users mentioned in the ACL. Your code will also be vulnerable to
> breakage in future releases if we change any of these details.
>
> A better approach is to write a plpgsql function that assembles and
> EXECUTEs the required GRANT commands.

Okay, thanks - guess it's time to learn some real plpgsql control
structures.

- John Burger
MITRE

On Feb 17, 2007, at 12:12 PM, John D. Burger wrote:

>>
>> A better approach is to write a plpgsql function that assembles and
>> EXECUTEs the required GRANT commands.
>
> Okay, thanks - guess it's time to learn some real plpgsql control
> structures.

You can find some help here:

http://pgedit.com/tip/postgresql/access_control_functions

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL