Re: Regrading TODO item alerting pg_hba.conf from SQL

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