On Mon, May 16, 2022 at 2:04 PM Bryn Llewellyn <bryn@yugabyte.com> wrote:
«
To connect to a particular database, a user must not only pass the pg_hba.conf checks, but must have the CONNECT privilege for the database. If you wish to restrict which users can connect to which databases, it's usually easier to control this by granting/revoking CONNECT privilege than to put the rules in pg_hba.conf entries.
»
I'd like to do what this tip says. But the regime that I have allows any non-super user to connect to any database.
I just re-tested this with a brand-new user "joe"—and after doing "revoke connect on database postgres from joe".
I'm obviously missing critical "pg_hba.conf" line(s). But I can't see what to add from the section that I mentioned. There must be some keyword, like "none", meaning the opposite of "all" for users.
You are failing to grasp the concept of "additive permissions" (ignoring auth-method reject for now). The idea of a literal "none" makes no sense - the absence of something is nothing, you do not say "none" explicitly.
But this goes against what the tip says. Anyway, after "revoke connect on database postgres from joe", my "\c postgres joe" succeeded.
See my answer, with link, from Friday. Joe's ability to connect is inherited through PUBLIC. There is no privilege directly on Joe to revoke.
I don't quite know how to address your random experimentation with pg_hba.conf. None of the things you showed are surprising though - were you expecting different?
