Concerning the pg_hba.conf file, I don't want to prevent external connections to the DB as I need all my web apps to connect to them. I was referring the fact that ROLE A "belongs" to DB G so that I don't want him to access anything in DB H for example.
I'd like to be able to create roles that can't connect (not the pg_hba.conf user configs) to any database except the ones for which they have been granted the privilege to do so.
So when creating ROLE A, he wouldn't be able to connect [through a PHP call pg_connect(user, pass, db)] until I explicitly grant him access to the DB for connexion via GRANT ON DATABASE G TO A.
As for the REVOKE and checking of privileges, haven't found anything for that on pgfoundry, will look on google.
Thanks
David
On 2/21/07, Richard Huxton <dev@archonet.com> wrote: David Legault wrote:
> In which table pg_* are stored the GRANT options? As I can do a cross-check
> with a SELECT to see if the user has any grants on functions using the
> pg_proc table. At the same time, I need to know exactly the names of the
> functions to be able to REVOKE them which in my opinion, there should be a
> wildcard which enables you to REVOKE everything at once without prior
> knowing the names of the functions.
Check pgfoundry / google for some functions to do just that - there are
plenty out there.
If you want to write your own, the permissions are stored in "proacl" in
pg_proc (and similarly named columns in other tables for other objects).
You may find the information_schema.routine* views simpler to work with
for part of your effort though.
--
Richard Huxton
Archonet Ltd