Message-ID: <18eab9b6-1f77-72b2-83d3-1856c17f3fcb@joeconway.com>
Date: Tue, 8 Feb 2022 06:59:41 -0500
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101
 Thunderbird/91.5.0
Content-Language: en-US
To: Robert Haas <robertmhaas@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>,
 Joshua Brindle <joshua.brindle@crunchydata.com>,
 "Bossart, Nathan" <bossartn@amazon.com>, Stephen Frost <sfrost@snowman.net>,
 PostgreSQL-development <pgsql-hackers@postgresql.org>
References: <CAGB+Vh4Zv_TvKt2tv3QNS6tUM_F_9icmuj0zjywwcgVi4PAhFA@mail.gmail.com>
 <CA+TgmobiMfUoZ42ZDz_6Ne4zMznOjxfEb22W+jdNX29yL88agw@mail.gmail.com>
 <CAGB+Vh4cCTey_TvE_O0CZu-D+6fVXC8a+C-UTzqkg8oN5YcOrQ@mail.gmail.com>
 <CAGB+Vh7fgOfLtCG+01D8ch3icG2aEd38dvy9uavtKWrpf1_XZw@mail.gmail.com>
 <20211108204449.GO20998@tamriel.snowman.net>
 <CAGB+Vh5RpzZroX-RKLXYWyTidpWX6=qcbMoVWERotAWh-_CeUQ@mail.gmail.com>
 <5F9388A2-35EA-4DAA-BE05-68D55EEA6DC3@amazon.com>
 <CAGB+Vh4Om+DFD2KChk5bjz+F2c+AJWDdPqiKPNxf5Ld3CvwwFw@mail.gmail.com>
 <0ED10807-9E7F-4BB5-9473-4668EE68AE2D@amazon.com>
 <4117557.1641329799@sss.pgh.pa.us>
 <CAGB+Vh4jWh-KuV6qt0FKC3M=HCZT1X=Kn5E1g68s1+0OSfoCdQ@mail.gmail.com>
 <c81ba78d-ae4f-02bc-9518-61cbe00c1f2e@joeconway.com>
 <730520.1644168290@sss.pgh.pa.us>
 <CA+TgmoYeNB1PxMO-TYv4_Ky7XR3-3Le+fpL87T7QMR=OWc7U1Q@mail.gmail.com>
 <80af9ba7-ec24-9134-8fd9-00bf13f5c494@joeconway.com>
 <CA+TgmoY7Hy2iiJ6SKPKjG9w4D+KiPYtZoXB=PpX2oQkwcK5rUw@mail.gmail.com>
From: Joe Conway <mail@joeconway.com>
Subject: Re: [PATCH v2] use has_privs_for_role for predefined roles
In-Reply-To: <CA+TgmoY7Hy2iiJ6SKPKjG9w4D+KiPYtZoXB=PpX2oQkwcK5rUw@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/18eab9b6-1f77-72b2-83d3-1856c17f3fcb%40joeconway.com>
Precedence: bulk

On 2/7/22 12:09, Robert Haas wrote:
> On Mon, Feb 7, 2022 at 11:13 AM Joe Conway <mail@joeconway.com> wrote:
>> It is confusing and IMHO dangerous that the predefined roles currently
>> work differently than regular roles eith respect to privilege inheritance.
> 
> I feel like that's kind of a conclusory statement, as opposed to
> making an argument. I mean that this tells me something about how you
> feel, but it doesn't really help me understand why you feel that way.


The argument is that we call these things "predefined roles", but they 
do not behave the same way normal "roles" behave.

Someone not intimately familiar with that fact could easily make bad 
assumptions, and therefore wind up with misconfigured security settings. 
In other words, it violates the principle of least astonishment (POLA).

As Joshua said nearby, it simply jumps out at me as a bug.

-------
After more thought, perhaps the real problem is that these things should 
not have been called "predefined roles" at all. I know, the horse has 
already left the barn on that, but in any case...

They are (to me at least) similar in concept to Linux capabilities in 
that they allow roles other than superusers to do a certain subset of 
the things historically reserved for superusers through a special 
mechanism (hardcoded) rather than through the normal privilege system 
(GRANTS/ACLs).

As an example, the predefined role pg_read_all_settings allows a 
non-superuser to read GUC normally reserved for superuser access only.

If I create a new user "bob" with the default INHERIT attribute, and I 
grant postgres to bob, bob must SET ROLE to postgres in order to access 
the capability to read superuser settings.

This is similar to bob's access to the default superuser privilege to 
read data in someone else's table (must SET ROLE to access that capability).

But it is different from bob's access to inherited privileges which are 
GRANTed:
8<--------------------------
psql nmx
psql (15devel)
Type "help" for help.

nmx=# create user bob;
CREATE ROLE

nmx=# grant postgres to bob;
GRANT ROLE

nmx=# \q
8<--------------------------
-and-
8<--------------------------
psql -U bob nmx
psql (15devel)
Type "help" for help.

nmx=> select current_user;
  current_user
--------------
  bob
(1 row)

nmx=> show stats_temp_directory;
ERROR:  must be superuser or have privileges of pg_read_all_settings to 
examine "stats_temp_directory"
nmx=> set role postgres;
SET
nmx=# show stats_temp_directory;
  stats_temp_directory
----------------------
  pg_stat_tmp
(1 row)

nmx=# select current_user;
  current_user
--------------
  postgres
(1 row)

nmx=# select * from foo;
  id
----
  42
(1 row)

nmx=# reset role;
RESET
nmx=> select current_user;
  current_user
--------------
  bob
(1 row)

nmx=> select * from foo;
ERROR:  permission denied for table foo
nmx=> set role postgres;
SET
nmx=# grant select on table foo to postgres;
GRANT
nmx=# reset role;
RESET
nmx=> select current_user;
  current_user
--------------
  bob
(1 row)

nmx=> select * from foo;
  id
----
  42
(1 row)
8<--------------------------

Joe

-- 
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development