Re: Regrading TODO item alerting pg_hba.conf from SQL

Thank you very much :) :)

On Sun, 2006-04-16 at 17:08 -0400, Tom Lane wrote:
> Gevik Babakhani <pgdev(at)xs4all(dot)nl> writes:
> > 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.
>
> > Tom, could you please provide more insight of how you see this taking
> > shape.
>
> It doesn't seem particularly complicated: inside the connection-startup
> transaction done by InitPostgres, you could check to make sure the
> selected user has the CONNECT privilege on the selected database.
> [ looks at code... ] Actually ReverifyMyDatabase is the right place,
> since it already has its hands on the pg_database row. You don't want
> this to cost an extra pg_database search during startup.
>
> If you use the normal definition of privilege checking, superusers
> would always pass the test, which seems fine to me. (Without that,
> you'd need some special exception for standalone mode, to provide
> a recovery path from DBA mistakes like revoking connect privilege
> from everyone on all databases. autovacuum needs to be immune
> from the check too.)
>
> > How would you suggest the SQL syntax be like for example.
>
> Just another privilege name in the existing GRANT/REVOKE ON DATABASE
> syntax.
>
> regards, tom lane
>