Обсуждение: access privileges: grant select on (all current and future tables)?

how'd i "grant select on (all current and future tables inside a
private schema) to username" without turning that user into superuser?
"grant usage on..." doesn't do it.

or do i, everytime i batch/auto create the tables, do a "grant select
on (new table) to username"?

http://www.postgresql.org/docs/8.1/static/sql-grant.html
CREATE: For schemas, allows new objects to be created within the
schema. To rename an existing object, you must own the object and have
this privilege for the containing schema.
USAGE: For schemas, allows access to objects contained in the
specified schema (assuming that the objects' own privilege
requirements are also met). Essentially this allows the grantee to
"look up" objects within the schema.

using 8.1.4
cheers, jzs

On 9/27/07, John Smith <jayzee.smith@gmail.com> wrote:
> how'd i "grant select on (all current and future tables inside a
> private schema) to username" without turning that user into superuser?
> "grant usage on..." doesn't do it.
>
> or do i, everytime i batch/auto create the tables, do a "grant select
> on (new table) to username"?

ok did some more googling. seems like "grant" is not the strongest
feature of postgresql yet.

for those who follow:
"grant select on schemaname.tablename..." alone works. gotto
explicitly mention tablename via trigger/script since schema privilege
is separate from table privilige.

and "grant usage on new tables in schema..." doesn't exist yet. which
leads to my next question (see
http://svr5.postgresql.org/pgsql-hackers/2005-01/msg01070.php)- are we
there yet?

also how'd i find access privileges for a schema. something like "\z
schemaname" not "\dp schemaname."?
cheers, jzs

On 9/27/07, John Smith <jayzee.smith@gmail.com> wrote:
> On 9/27/07, John Smith <jayzee.smith@gmail.com> wrote:
> > how'd i "grant select on (all current and future tables inside a
> > private schema) to username" without turning that user into superuser?
> > "grant usage on..." doesn't do it.
> >
> > or do i, everytime i batch/auto create the tables, do a "grant select
> > on (new table) to username"?
>
> also how'd i find access privileges for a schema. something like "\z
> schemaname" not "\dp schemaname."?

ok let me ask this one other way:
when i "drop user username" which system tables does it access to then reply:
ERROR: role "username" cannot be dropped...
DETAIL: access to schema schemaname

where is this "access to schema..." info stored?

John Smith escribió:
> On 9/27/07, John Smith <jayzee.smith@gmail.com> wrote:
> > On 9/27/07, John Smith <jayzee.smith@gmail.com> wrote:
> > > how'd i "grant select on (all current and future tables inside a
> > > private schema) to username" without turning that user into superuser?
> > > "grant usage on..." doesn't do it.
> > >
> > > or do i, everytime i batch/auto create the tables, do a "grant select
> > > on (new table) to username"?
> >
> > also how'd i find access privileges for a schema. something like "\z
> > schemaname" not "\dp schemaname."?
>
> ok let me ask this one other way:
> when i "drop user username" which system tables does it access to then reply:
> ERROR: role "username" cannot be dropped...
> DETAIL: access to schema schemaname
>
> where is this "access to schema..." info stored?

pg_shdepend


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

John Smith wrote:

> and "grant usage on new tables in schema..." doesn't exist yet. which
> leads to my next question (see
> http://svr5.postgresql.org/pgsql-hackers/2005-01/msg01070.php)- are we
> there yet?

If I understand your question, you cannot grant "anticipatory"
privileges to tables which do not yet exist, which I discovered in
this thread:

   http://archives.postgresql.org/pgsql-general/2007-02/msg00911.php

If you follow the thread, you'll find that one reply pointed to some
existing functions for managing this stuff.  I found these useful.

- John D. Burger
   MITRE