Re: "set role" semantics

On Wed, Nov 9, 2022 at 11:55 AM Bryn Llewellyn <bryn(at)yugabyte(dot)com> wrote:

>
> Here's an extract from the script that I copied in my first email:
>
>
>
>
>
>
> *create database d1;revoke all on database d1 from public;create database
> d2;revoke all on database d2 from public;*
>
> Didn't I do exactly what you both said that I failed to do?
>

I'll admit that I didn't spend enough time thoroughly reading your email
and indeed missed some salient points.

*"set role" allows a role that lacks "connect" on some database to end up
> so that the "current_database()" shows that forbidden database.*
>

Just because you cannot connect to a database using a specific role doesn't
mean you cannot connect to said database using some other role and then
assume the role that doesn't have connect privileges. SET ROLE does not
equate to connecting (in particular, role-level settings are not applied,
in addition to not performing the connection check).

>
> My question still stands: where can I read the account of this? I'm also
> interested to know _why_ it was decided not to test for the "connect"
> privilege when "set role" is used.
>

Why should "connect privilege" be tested in a situation where one is not
connecting?

>
> I suppose that the script that I first showed you conflated too many
> separable notions.
>

Yes, I would rationalize away my mistake as being a consequence of your
tendency to do this.

> (My aim was to you show what my overall aim was). Here's a drastically cut
> down version. It still demonstrates the behavior that I asked about.
>
> *create role joe*
> * nosuperuser*
> * nocreaterole*
> * nocreatedb*
> * noreplication*
> * nobypassrls*
> * connection limit -1*
> * login password 'p';*
>
> *create database d1;*
> *revoke all on database d1 from public;*
>
> *\c d1 postgres*
>

You are connect as postgres which is superuser and can always connect
(pg_hba.conf permitting)

> *set role joe;*
>

You've assumed the role of joe but have not connected as them

> *select current_database()||' > '||session_user||' > '||current_user*
> *;*
> I'm still able to end up with "Joe" as the "current_user" and "d1" (to
> which Joe cannot connect) as the "current_database()".
>

Yes, that is the meaning of "current_user", the role you are presently
assuming. session_user exists in order to keep track of the user you
actually connected with.

>
> I then did the sanity test that I should have shown you at the outset.
> (Sorry that I didn't do that.) I started a session from cold, running
> "psql" on a client machine where the server machine is called "u" (for
> Ubuntu) in my "/etc/hosts", thus:
>
>
> *psql -h u -p 5432 -d d1 -U joe*
> The connect attempt was rejected with the error that I expected: "User
> does not have CONNECT privilege".
>

Yep
We did not document that "set role" doesn't obey "connect" privilege
because, frankly, it didn't seem like one of the many things the system
does not do that warrants documenting. I still agree with that position.

That said, I'm kinda seeing the design choice that is contrary to your
assumptions. There are only three privileges on a database: create,
connect, and temporary - none of which speak to whether a given role is
allowed to be assumed while already connected to a specific database.
Roles are global, not per-database, and the system does not consider it an
issue for a role to be active in any database. You can make such a role be
incapable of doing anything useful by revoking all default privileges its
mere presence produces no harm. If you do remove connect, then the only
people who could assume that role would be members who themselves have
connect privilege. It is seemingly pointless to prohibit them from
assuming any of the roles they are a member of on the basis of which
database they are in. In short, yes, the permissions model could be made
more nuanced than its present design. But it isn't, it isn't documented to
be, and your assuming that connect implies non-assumability doesn't seem to
stem from anything the documentation actually says.

David J.