Re: How do I grant access to entire database at - Mailing list pgsql-admin

From Tom Lane
Subject Re: How do I grant access to entire database at
Date
Msg-id 9472.1090874787@sss.pgh.pa.us
Whole thread Raw
In response to Re: How do I grant access to entire database at  (Sam Barnett-Cormack <s.barnett-cormack@lancaster.ac.uk>)
Responses Re: How do I grant access to entire database at
List pgsql-admin
Sam Barnett-Cormack <s.barnett-cormack@lancaster.ac.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

pgsql-admin by date:

Previous
From: Sam Barnett-Cormack
Date:
Subject: Re: How do I grant access to entire database at
Next
From: "Chris Hoover"
Date:
Subject: Max varchar size