David Legault wrote:
> 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.
REVOKE CONNECT ON DATABASE g FROM public;
Then you'll need to add "GRANT CONNECT" for each user/group.
Also check the section on database-specific roles in the manuals
(there's a setting in postgresql.conf that lets you have user@database).
Might be useful.
--
Richard Huxton
Archonet Ltd