pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Schema security
7+ messages / 5 participants
[nested] [flat]

* Schema security
@ 2007-12-13 01:52 Paul Lambert <paul.lambert@reynolds.com.au>
  2007-12-13 03:25 ` Re: Schema security Rodrigo De León <rdeleonp@gmail.com>
  2007-12-13 05:35 ` Re: Schema security Tom Lane <tgl@sss.pgh.pa.us>
  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 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   ` Re: Schema security 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 01:52 Schema security Paul Lambert <paul.lambert@reynolds.com.au>
  2007-12-13 03:25 ` Re: Schema security Rodrigo De León <rdeleonp@gmail.com>
@ 2007-12-13 03:28   ` Paul Lambert <paul.lambert@reynolds.com.au>
  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 01:52 Schema security Paul Lambert <paul.lambert@reynolds.com.au>
@ 2007-12-13 05:35 ` Tom Lane <tgl@sss.pgh.pa.us>
  2007-12-13 05:55   ` Re: Schema security 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 01:52 Schema security Paul Lambert <paul.lambert@reynolds.com.au>
  2007-12-13 05:35 ` Re: Schema security Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-12-13 05:55   ` Paul Lambert <paul.lambert@reynolds.com.au>
  2007-12-13 13:01     ` Re: Schema security Alvaro Herrera <alvherre@alvh.no-ip.org>
  2007-12-13 18:49     ` Re: Schema security Joshua D. Drake <jd@commandprompt.com>
  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 01:52 Schema security Paul Lambert <paul.lambert@reynolds.com.au>
  2007-12-13 05:35 ` Re: Schema security Tom Lane <tgl@sss.pgh.pa.us>
  2007-12-13 05:55   ` Re: Schema security Paul Lambert <paul.lambert@reynolds.com.au>
@ 2007-12-13 13:01     ` Alvaro Herrera <alvherre@alvh.no-ip.org>
  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 01:52 Schema security Paul Lambert <paul.lambert@reynolds.com.au>
  2007-12-13 05:35 ` Re: Schema security Tom Lane <tgl@sss.pgh.pa.us>
  2007-12-13 05:55   ` Re: Schema security Paul Lambert <paul.lambert@reynolds.com.au>
@ 2007-12-13 18:49     ` Joshua D. Drake <jd@commandprompt.com>
  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