Обсуждение: Schema security
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
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.
Rodrigo De León wrote: > You could use pgAdmin's Grant Wizard. That doesn't help in scripting... -- Paul Lambert Database Administrator AutoLedgers
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
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
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)
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Thu, 13 Dec 2007 14:55:53 +0900 Paul Lambert <paul.lambert@reynolds.com.au> wrote: > > 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? Yes and it has been oft requested. However :), nobody has coded a patch or submitted a proposal on how it would be done in a maintainable manner. > > 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. Yes *ix can do it to. Sincerely, Joshua D. Drake - -- The PostgreSQL Company: Since 1997, http://www.commandprompt.com/ Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD' -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHYX60ATb/zqfZUUQRAkK1AKCY8i5bHTUChaUp2LcovnSdgrwq+wCdHlCW TdBpE7HUUVyr2OmzSnNQUKw= =Ci4R -----END PGP SIGNATURE-----