From: | Bryn Llewellyn <bryn(at)yugabyte(dot)com> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>, "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | pgsql-general list <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: "set role" semantics |
Date: | 2022-11-09 18:55:35 |
Message-ID: | E3224C71-F868-49C2-9C86-EB8AA67637E1@yugabyte.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> adrian(dot)klaver(at)aklaver(dot)com wrote:
>
>> david(dot)g(dot)johnston(at)gmail(dot)com wrote:
>>
>>> bryn(at)yugabyte(dot)com wrote:
>>>
>>> Notice that I didn't grant "connect" on either of the databases, "d1" or "d2", to any of the roles, "clstr$mgr, "d1$mgr", or "d2$mgr".
>>
>> You didn't have to since PUBLIC gets that privilege and you didn't revoke it.
>>
>> https://www.postgresql.org/docs/current/ddl-priv.html
>
> Revoking PUBLIC has been explained before to you (Bryn Llewellyn).
>
> A quick search:
>
> https://www.postgresql.org/message-id/2176817.1644613186@sss.pgh.pa.us
>
> https://www.postgresql.org/message-id/CAKFQuwayij%3DAQRQxJhFuJ3Qejq3E-PfiBjJ9CoHx_L_46BEgXQ@mail.gmail.com
>
> https://www.postgresql.org/message-id/CAKFQuwZVq-LerGMTN0E3_7MqhJwtuJuzf0GSnKG32mH_Qf24Zw@mail.gmail.com
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?
*Summary*
My experiments (especially below) show that "set role" has special semantics that differ from starting a session from cold:
"set role" allows a role that lacks "connect" on some database to end up so that the "current_database()" shows that forbidden database.
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.
*Detail*
I suppose that the script that I first showed you conflated too many separable notions. (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
set role joe;
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()".
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".
I wondered if the fact that the "session_user" was "postgres" in my tests was significant. So I did a new test. (As ever, I started with a freshly created cluster to be sure that no earlier tests had left a trace.)
create role mary
nosuperuser
noinherit
nocreaterole
nocreatedb
noreplication
nobypassrls
connection limit -1
login password 'p';
create role joe
nosuperuser
noinherit
nocreaterole
nocreatedb
noreplication
nobypassrls
connection limit -1
login password 'p';
create database d1;
revoke all on database d1 from public;
grant connect on database d1 to mary;
grant joe to mary;
Then I did this on the client machine:
psql -h u -p 5432 -d d1 -U mary
set role joe;
Here, too, I ended up with "Joe" as the "current_user" and "d1" (to which Joe cannot connect) as the "current_database()".
From | Date | Subject | |
---|---|---|---|
Next Message | Ron | 2022-11-09 18:57:23 | Re: copy file from a client app to remote postgres isntance |
Previous Message | David Rowley | 2022-11-09 18:29:52 | Re: Unnecessary locks for partitioned tables |