pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedSchema security
7+ messages / 5 participants
[nested] [flat]
* Schema security
@ 2007-12-13 01:52 Paul Lambert <paul.lambert@reynolds.com.au>
0 siblings, 2 replies; 7+ messages in thread
From: Paul Lambert @ 2007-12-13 01:52 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Schema security
@ 2007-12-13 03:25 Rodrigo De León <rdeleonp@gmail.com>
parent: Paul Lambert <paul.lambert@reynolds.com.au>
1 sibling, 1 reply; 7+ messages in thread
From: Rodrigo De León @ 2007-12-13 03:25 UTC (permalink / raw)
To: Paul Lambert <paul.lambert@reynolds.com.au>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Schema security
@ 2007-12-13 03:28 Paul Lambert <paul.lambert@reynolds.com.au>
parent: Rodrigo De León <rdeleonp@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Paul Lambert @ 2007-12-13 03:28 UTC (permalink / raw)
To: pgsql-admin
Rodrigo De León wrote:
> You could use pgAdmin's Grant Wizard.
That doesn't help in scripting...
--
Paul Lambert
Database Administrator
AutoLedgers
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Schema security
@ 2007-12-13 05:35 Tom Lane <tgl@sss.pgh.pa.us>
parent: Paul Lambert <paul.lambert@reynolds.com.au>
1 sibling, 1 reply; 7+ messages in thread
From: Tom Lane @ 2007-12-13 05:35 UTC (permalink / raw)
To: Paul Lambert <paul.lambert@reynolds.com.au>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Schema security
@ 2007-12-13 05:55 Paul Lambert <paul.lambert@reynolds.com.au>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 7+ messages in thread
From: Paul Lambert @ 2007-12-13 05:55 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Schema security
@ 2007-12-13 13:01 Alvaro Herrera <alvherre@alvh.no-ip.org>
parent: Paul Lambert <paul.lambert@reynolds.com.au>
1 sibling, 0 replies; 7+ messages in thread
From: Alvaro Herrera @ 2007-12-13 13:01 UTC (permalink / raw)
To: Paul Lambert <paul.lambert@reynolds.com.au>; +Cc: pgsql-admin
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)
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Schema security
@ 2007-12-13 18:49 Joshua D. Drake <jd@commandprompt.com>
parent: Paul Lambert <paul.lambert@reynolds.com.au>
1 sibling, 0 replies; 7+ messages in thread
From: Joshua D. Drake @ 2007-12-13 18:49 UTC (permalink / raw)
To: Paul Lambert <paul.lambert@reynolds.com.au>; +Cc: pgsql-admin
-----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-----
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2007-12-13 18:49 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-12-13 01:52 Schema security Paul Lambert <paul.lambert@reynolds.com.au>
2007-12-13 03:25 ` Rodrigo De León <rdeleonp@gmail.com>
2007-12-13 03:28 ` Paul Lambert <paul.lambert@reynolds.com.au>
2007-12-13 05:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-12-13 05:55 ` Paul Lambert <paul.lambert@reynolds.com.au>
2007-12-13 13:01 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2007-12-13 18:49 ` Joshua D. Drake <jd@commandprompt.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox