Skip site navigation (1) Skip section navigation (2)

Re: How do I grant access to entire database at

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Sam Barnett-Cormack <s(dot)barnett-cormack(at)lancaster(dot)ac(dot)uk>
Cc: Randall Perry <rgp(at)systame(dot)com>, olly(at)lfix(dot)co(dot)uk,pgsql-admin(at)postgresql(dot)org
Subject: Re: How do I grant access to entire database at
Date: 2004-07-26 20:46:27
Message-ID: 9472.1090874787@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-admin
Sam Barnett-Cormack <s(dot)barnett-cormack(at)lancaster(dot)ac(dot)uk> writes:
> GRANT out to have a varying degree of granularity, IMO. So you can
> specify individual sequences or tables, a glob (handy for
> table+sequence), possibly an intelligent way of including anything
> requisite for a given table, a whole DB, a whole namespace, a whole
> cluster (although then you could just make the user a superuser), all of
> the members of a DB/namespace that are of a certain type (say,
> tables+sequences, or functions-only, or whatever).

All but the last are nonstarters for the simple reason that different
kinds of objects have different sets of possible GRANT rights.  Only
"GRANT ALL" could possibly be common across different object kinds, and
it doesn't seem to me that wholesale GRANT ALL would be a particularly
common thing to want to do.

It is perhaps interesting to do something like
	GRANT SELECT ON TABLE foo.* TO user;
but I'm not sure this is so useful as to be worth enshrining in the
syntax.  You could also argue that it's a potential security hole since
it'd be mighty easy to grant rights you didn't intend to on objects you
didn't realize would match the wildcard.  (And that'd be true in spades
if the effect of the command were to automatically grant the same rights
on matching objects created in the future, which is what I think some of
the people asking for this sort of thing wanted.  But I'm outright
scared of that idea.)

As long as you're not after the implicit-effects-on-future-objects
behavior, it's easy enough to write custom functions that do exactly
what you want in this line.  I'm inclined to leave it at that for the
moment.  But perhaps we could put some examples of such functions on
techdocs, or set up a pgfoundry project for them.  If your long-term
goal is to get this functionality migrated into the core server, having
a popular pgfoundry project that embodies a specific set of features
would go a long way towards convincing people that those particular
features were right and useful.  Without any pre-existing standard
to follow, it'll be hard to get consensus on "the right thing"
otherwise.

			regards, tom lane

In response to

Responses

pgsql-admin by date

Next:From: Daniel StruckDate: 2004-07-27 11:41:33
Subject: Re: [ADMIN] Secure DB Systems - How to
Previous:From: alvaroDate: 2004-07-26 20:39:34
Subject: Re: SOS ---- Could you help me with postgresql.....????

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group