I have a schema for example called f65, and the public schema of course,
in a database.

I've created a user f65 to access only the f65 schema using the following:

CREATE ROLE f65 LOGIN
ENCRYPTED PASSWORD 'md52a630d68054defeed4b4c27cb6413ece'
NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE;
REVOKE ALL ON SCHEMA public FROM public;
REVOKE ALL ON SCHEMA f65 FROM public;
GRANT ALL ON SCHEMA f65 TO f65;

Which gives the f65 user access to the schema, but they cannot access
any of the objects within it (i.e. permission denied when trying a
select from a table)

testdb=> select * from f65.billing;
ERROR: permission denied for relation billing

I would have thought giving the user all privileges on a schema would by
default add them to all objects within it, but clearly not. Do I need to
explicitly go through every object within the schema and grant that user
access to them or is there an easier way of doing it? Something like a
"GRANT CASCADE" option?

Cheers,
P.

--
Paul Lambert
Database Administrator
AutoLedgers


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On 12/12/07, Paul Lambert <paul.lambert@reynolds.com.au> wrote:
> I would have thought giving the user all privileges on a schema would by
> default add them to all objects within it, but clearly not. Do I need to
> explicitly go through every object within the schema and grant that user
> access to them or is there an easier way of doing it? Something like a
> "GRANT CASCADE" option?

You could use pgAdmin's Grant Wizard.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Rodrigo De León wrote:
> You could use pgAdmin's Grant Wizard.

That doesn't help in scripting...

--
Paul Lambert
Database Administrator
AutoLedgers


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Paul Lambert <paul.lambert@reynolds.com.au> writes:
> I would have thought giving the user all privileges on a schema would by
> default add them to all objects within it,

Why would you think that?

The analogy to think about is that usage privilege on a schema is
comparable to read access on a directory. That doesn't necessarily give
you access to any single file in the directory --- but lack of it does
ensure you cannot get to those files.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Tom Lane wrote:
> Paul Lambert <paul.lambert@reynolds.com.au> writes:
>> I would have thought giving the user all privileges on a schema would by
>> default add them to all objects within it,
>
> Why would you think that?
>
> The analogy to think about is that usage privilege on a schema is
> comparable to read access on a directory. That doesn't necessarily give
> you access to any single file in the directory --- but lack of it does
> ensure you cannot get to those files.
>
> regards, tom lane
>
>

Point taken and yes, I would agree that default behavior should be to
not give priviledges to anything other than the explicitly defined
object - but would it not be a good idea to provide some sort of
cascade/recurse option to granting/revoking privileges so that doing so
on a container object results in the priviledges being propogated down
the line for the cases where such is desired?

Taking your example of file permissions - although it is not default
behavior, it is possible to recursively apply a priviledge change to a
directory onto files/subdirectories within it. Certainly it can be done
on OpenVMS and Windows that I work with primarily and I'm 99% sure it
can be done on *ix systems too.

I.e.
GRANT ALL ON SCHEMA <blah> TO <role> CASCADE;
NOTICE: GRANT ALL cascades to table "billings"
NOTICE: GRANT ALL cascades to table "customers"
NOTICE: GRANT ALL cascades to function "calculate_daily_balance()"
etc...

Much the same way that truncate or drop and so forth can have a cascade
option to propogate down to dependant objects.

--
Paul Lambert
Database Administrator
AutoLedgers

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Paul Lambert wrote:

> Taking your example of file permissions - although it is not default
> behavior, it is possible to recursively apply a priviledge change to a
> directory onto files/subdirectories within it. Certainly it can be done on
> OpenVMS and Windows that I work with primarily and I'm 99% sure it can be
> done on *ix systems too.
>
> I.e.
> GRANT ALL ON SCHEMA <blah> TO <role> CASCADE;
> NOTICE: GRANT ALL cascades to table "billings"
> NOTICE: GRANT ALL cascades to table "customers"
> NOTICE: GRANT ALL cascades to function "calculate_daily_balance()"
> etc...

Yes, it can be done at least with GNU chmod by using chmod -R.

The problem is that it doesn't work too well for schemas -> tables,
because the set of acceptable privileges is completely different. So
the only case that would work is GRANT ALL. Perhaps what could work is
to be able to specify wildcards in GRANT, for example

GRANT SELECT, INSERT ON TABLE schema.* TO <role>

The problem with this idea is what happens if you create a new table in
that schema? Is the role given access to that table? (The other
problem is whether this new command conforms to the SQL standard, or is
it in conflict with it.)

--
Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4
"Estoy de acuerdo contigo en que la verdad absoluta no existe...
El problema es que la mentira sí existe y tu estás mintiendo" (G. Lama)

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings