Re: "set role" semantics

From: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
To: Bryn Llewellyn <bryn(at)yugabyte(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 19:22:50
Message-ID: ad63eda6-ec31-3923-24b2-97dffe4a1912@aklaver.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On 11/9/22 10:55 AM, Bryn Llewellyn wrote:
>> adrian(dot)klaver(at)aklaver(dot)com <mailto:rian(dot)klaver(at)aklaver(dot)com> wrote:

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

You need to go here:

https://www.postgresql.org/docs/current/ddl-priv.html

To see what revoke all on database actually does:

CREATE

For databases, allows new schemas and publications to be created
within the database, and allows trusted extensions to be installed
within the database. ...

CONNECT

Allows the grantee to connect to the database. This privilege is
checked at connection startup (in addition to checking any restrictions
imposed by pg_hba.conf).

TEMPORARY

Allows temporary tables to be created while using the database.

Also look at Table 5.2. Summary of Access Privileges

None of the above stops a role from looking up information in the
system catalogs which is what:

select current_database()||' > '||session_user||' > '||current_user;

is doing.

More comments below.
>
> **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.*nn

From below, you started the session with postgres(superuser) db user
and it can set role to whatever it wants. The system catalog information
is basically available to all and the functions(current_database,
session_user, current_user) also are. Revoking connect on a database is
that just denying the connection. Once a role has connected it can
change the current_user to any role it is allowed to that does not count
as a connection vs:

\c - joe
connection to server at "localhost" (::1), port 5432 failed: FATAL:
permission denied for database "d1"
DETAIL: User does not have CONNECT privilege.

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

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2022-11-09 19:27:23 Re: "set role" semantics
Previous Message David G. Johnston 2022-11-09 19:22:44 Re: "set role" semantics