Re: "set role" semantics

Hi,

Le mer. 9 nov. 2022, 19:55, Bryn Llewellyn <bryn(at)yugabyte(dot)com> a écrit :

> 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?
>

Nope. All you did was revoking all privileges on these database objects. It
didn't revoke privileges on objects of these databases. In other words, you
revoked CREATE, TEMP, VONNECT privileges on d1 and d2, you didn't revoke
privileges on the public schema.

> **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.
>

Using SET ROLE doesn't connect you as another role on the database. You can
see this by logging connections, you won't see any connection log lines
when using SET ROLE. It also doesn't check pg_hba.conf rules when using SET
ROLE.

SET ROLE only makes you impersonate another role. The only privilege you
need to do that is being a member of this role.

> **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()".
>

Because SET ROLE doesn't connect you as this role name.

> 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".
>

Because joe tried to connect on d1, and he doesn't have the privileges to
do so.

> 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()".
>
>