Re: "set role" semantics

On Tue, Nov 8, 2022 at 5:16 PM Bryn Llewellyn <bryn(at)yugabyte(dot)com> wrote:

>
> Is there anything that can be done to limit the scope of the ability to
> end up in a database like I'd thought would be possible? (A little test
> showed me that "set role" doesn't fire an event trigger.)
>
> I do see that, as far as I've taken this test, "d2$mgr" is entirely
> impotent when the "current_database()" is "d1". Is this, maybe, just as
> good as it gets. I suppose I can live with what seems to me to be very odd
> as long as no harm can come of it.
>

Yes. In short, you can prevent a person from connecting to specific
databases by ensuring the credentials they hold only resolve successfully
on those specific databases. Both via connect privilege and pg_hba.conf
can this be done. But that person, while connected to any databases, can
assume any roles the user name of the credentials they used are a member
of. Whether that is actually useful or not depends upon grants. But in no
case can you prevent them from, say, examining the database catalog tables,
or executing at least some limited SQL.

David J.