Re: Restricting user to see schema structure

On Mon, May 16, 2022 at 2:04 PM Bryn Llewellyn <bryn(at)yugabyte(dot)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?

David J.