Re: Regrading TODO item alerting pg_hba.conf from SQL

On Sun, Apr 16, 2006 at 08:34:10PM +0200, Gevik Babakhani wrote:
> On Sun, 2006-04-16 at 11:48 -0400, Tom Lane wrote:
>
> > I don't think there would be any objection to adding a database-level
> > CONNECT privilege that's checked inside the database, *after* the
> > existing pg_hba.conf mechanism. That requires no new concepts: we
> > already have databases and privilege bits for them. If the default is
> > to grant CONNECT to PUBLIC then the behavior is backward-compatible, and
> > people can use the privilege, pg_hba.conf, or a combination to control
> > access. (Might be best to call it USAGE so we don't need to create a
> > new reserved word, but that's a minor detail.)
>
> Tom, could you please provide more insight of how you see this taking
> shape. I am sure your vote counts heavy on this. How would you suggest
> the SQL syntax be like for example.

I think we went over this last thread. The basic idea would be:

REVOKE CONNECT ON DATABASE foo FROM PUBLIC;
GRANT CONNECT ON DATABASE foo TO user1, user2, user3;

The default would be to grant CONNECT to public to retain backwards
compatability. So you revoke that then GRANT the priveledge to the
users and/or roles who can connect.

For simple systems then you could have a short pg_hba.conf to limit the
IP addresses users can connect on, and the DB stores what databases
they have access to...
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.