On Wed, Nov 9, 2022 at 11:55 AM Bryn Llewellyn <b...@yugabyte.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.
