Обсуждение: "set role" semantics
I created a little test to demonstrate to myself how “set role” works. I ran it in a freshly-created PG 11.17 cluster on Ubuntu, installed and configured like I’ve recently discussed on this list. I copied my "pg-init.sh" script at the end.
I then did this test, after starting like this (as the system admin O/S user for my VM):
----------------------
source pg-init.sh
sudo -u postgres psql
sudo -u postgres psql
This is the SQL script:
create role clstr$mgr with
create role clstr$mgr with
nosuperuser
createrole
createdb
noreplication
nobypassrls
connection limit 0
login password null;
set role clstr$mgr;
create role d1$mgr
nosuperuser
nocreaterole
nocreatedb
noreplication
nobypassrls
connection limit 0
login password null;
create role d2$mgr
nosuperuser
nocreaterole
nocreatedb
noreplication
nobypassrls
connection limit 0
login password null;
create database d1;
revoke all on database d1 from public;
create database d2;
revoke all on database d2 from public;
\c d1 postgres
set role clstr$mgr;
grant create on database d1 to d1$mgr;
create schema s;
grant usage on schema s to d1$mgr;
grant create on schema s to d1$mgr;
set role d1$mgr;
select current_database()||' > '||session_user||' > '||current_user;
create table s.t(k int);
insert into s.t(k) values(17);
select * from s.t;
set role d2$mgr;
select current_database()||' > '||session_user||' > '||current_user;
-- permission denied...
select * from s.t;
create role clstr$mgr with
nosuperuser
createrole
createdb
noreplication
nobypassrls
connection limit 0
login password null;
set role clstr$mgr;
create role d1$mgr
nosuperuser
nocreaterole
nocreatedb
noreplication
nobypassrls
connection limit 0
login password null;
create role d2$mgr
nosuperuser
nocreaterole
nocreatedb
noreplication
nobypassrls
connection limit 0
login password null;
create database d1;
revoke all on database d1 from public;
create database d2;
revoke all on database d2 from public;
\c d1 postgres
set role clstr$mgr;
grant create on database d1 to d1$mgr;
create schema s;
grant usage on schema s to d1$mgr;
grant create on schema s to d1$mgr;
set role d1$mgr;
select current_database()||' > '||session_user||' > '||current_user;
create table s.t(k int);
insert into s.t(k) values(17);
select * from s.t;
set role d2$mgr;
select current_database()||' > '||session_user||' > '||current_user;
-- permission denied...
select * from s.t;
----------------------
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".
I couldn't find the doc that tells me what to expect. Where is it? I was a bit surprised that I could end up with the "current_database()" as "d1" or "d2" and the "current_user" as "clstr$mgr" when this role doesn't have "connect" on either of the databases. But I guessed that permission to do this was implied by the "createdb" attribute (as a special case of the general unstoppability of a superuser). However, I was very surprised that I could end up with the "current_database()" as "d1" or "d2" and the "current_user" as "d2$mgr" or because it is so far minimally privileged (and in particular doesn't have "connect" on "d1" or "d2").
I'd been hoping that "set role d2$mgr" would fail when "d2$mgr" doesn't have "connect" on the target database, "d1". My plan, then, had been to set up "d1$mgr" as the manager for "d1" by granting it "connect on "d1" but not on "d2". Then I'd've used a similar scheme for "d2$mgr".
Is there anything that can be done to limit the scope of the ability to end up in a database like I'd thought would be possible? (A little test showed me that "set role" doesn't fire an event trigger.)
I do see that, as far as I've taken this test, "d2$mgr" is entirely impotent when the "current_database()" is "d1". Is this, maybe, just as good as it gets. I suppose I can live with what seems to me to be very odd as long as no harm can come of it.
----------------------
# pg-init.sh
sudo pg_dropcluster --stop 11 main
sudo rm -Rf /etc/ybmt-generated/pg-logs/*
sudo pg_createcluster 11 main \
-e UTF8 --locale=C --lc-collate=C --lc-ctype=en_US.UTF-8 \
-d /var/lib/postgresql/11/main \
> /dev/null
sudo cp /etc/ybmt-code/pg-and-yb-config-files/*.conf \
/etc/postgresql/11/main
sudo chown postgres /etc/postgresql/11/main/*.conf
sudo chgrp postgres /etc/postgresql/11/main/*.conf
sudo chmod 644 /etc/postgresql/11/main/*.conf
sudo chmod 640 /etc/postgresql/11/main/pg_hba.conf
sudo chmod 640 /etc/postgresql/11/main/pg_ident.conf
sudo pg_ctlcluster start 11/main
sudo -u postgres psql -c " alter role postgres with superuser connection limit -1 login password 'x'; alter database postgres with allow_connections = true connection_limit = -1; "
sudo -u postgres psql -c " select name, setting from pg_settings where category = 'File Locations'; "
。
On Tue, Nov 8, 2022, 17:16 Bryn Llewellyn <bryn@yugabyte.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.
David J.
On 11/8/22 16:24, David G. Johnston wrote: > On Tue, Nov 8, 2022, 17:16 Bryn Llewellyn <bryn@yugabyte.com > <mailto:bryn@yugabyte.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 > <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=AQRQxJhFuJ3Qejq3E-PfiBjJ9CoHx_L_46BEgXQ@mail.gmail.com https://www.postgresql.org/message-id/CAKFQuwZVq-LerGMTN0E3_7MqhJwtuJuzf0GSnKG32mH_Qf24Zw@mail.gmail.com > > David J. -- Adrian Klaver adrian.klaver@aklaver.com
adrian.klaver@aklaver.com wrote:david.g.johnston@gmail.com wrote:bryn@yugabyte.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;
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()".
Hi,
Le mer. 9 nov. 2022, 19:55, Bryn Llewellyn <bryn@yugabyte.com> a écrit :
adrian.klaver@aklaver.com wrote:david.g.johnston@gmail.com wrote:bryn@yugabyte.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 joenosuperusernocreaterolenocreatedbnoreplicationnobypassrlsconnection limit -1login password 'p';create database d1;revoke all on database d1 from public;\c d1 postgresset 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 joeThe 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 maryset role joe;Here, too, I ended up with "Joe" as the "current_user" and "d1" (to which Joe cannot connect) as the "current_database()".
On Wed, Nov 9, 2022 at 11:55 AM Bryn Llewellyn <bryn@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 joenosuperusernocreaterolenocreatedbnoreplicationnobypassrlsconnection limit -1login 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 joeThe 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.
On 11/9/22 10:55 AM, Bryn Llewellyn wrote: >> adrian.klaver@aklaver.com <mailto:rian.klaver@aklaver.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.klaver@aklaver.com
On 11/9/22 10:55 AM, Bryn Llewellyn wrote: >> adrian.klaver@aklaver.com <mailto:rian.klaver@aklaver.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; > * Should have added to previous post I missed this on initial read of original post. > > Didn't I do exactly what you both said that I failed to do? -- Adrian Klaver adrian.klaver@aklaver.com
On Tue, Nov 8, 2022 at 5:16 PM Bryn Llewellyn <bryn@yugabyte.com> wrote:
Is there anything that can be done to limit the scope of the ability to end up in a database like I'd thought would be possible? (A little test showed me that "set role" doesn't fire an event trigger.)I do see that, as far as I've taken this test, "d2$mgr" is entirely impotent when the "current_database()" is "d1". Is this, maybe, just as good as it gets. I suppose I can live with what seems to me to be very odd as long as no harm can come of it.
Yes. In short, you can prevent a person from connecting to specific databases by ensuring the credentials they hold only resolve successfully on those specific databases. Both via connect privilege and pg_hba.conf can this be done. But that person, while connected to any databases, can assume any roles the user name of the credentials they used are a member of. Whether that is actually useful or not depends upon grants. But in no case can you prevent them from, say, examining the database catalog tables, or executing at least some limited SQL.
David J.
> david.g.johnston@gmail.com wrote: > >> bryn@yugabyte.com wrote: >> >> Is there anything that can be done to limit the scope of the ability to end up in a database like I'd thought would bepossible? (A little test showed me that "set role" doesn't fire an event trigger.) >> >> I do see that, as far as I've taken this test, "d2$mgr" is entirely impotent when the "current_database()" is "d1". Isthis, maybe, just as good as it gets. I suppose I can live with what seems to me to be very odd as long as no harm cancome of it. > > Yes. In short, you can prevent a person from connecting to specific databases by ensuring the credentials they hold onlyresolve successfully on those specific databases. Both via connect privilege and pg_hba.conf can this be done. But thatperson, while connected to any databases, can assume any roles the user name of the credentials they used are a memberof. Whether that is actually useful or not depends upon grants. But in no case can you prevent them from, say, examiningthe database catalog tables, or executing at least some limited SQL. Thanks. If nobody thinks that ending up as I showed is possible brings any kind of risk, then I’m happy to accept that. Moregenerally, I’m a huge fan of the principle of least privilege, and (as far as it concerns what I asked about in thisthread), its following aspect: If you consider these two alternatives: Alt. #1: Some ability that you do not need (and therefor don’t want) is simply not available. Alt. #2: That unwanted ability is available, but reasoning shows that it’s harmless. then Alt. #1 is preferable. But I do see that I can’t get Alt #1 here. In my actual use case, every role apart from "postgres", and its non-superuser deputy with "create role" and "createdb",will be confined at "connect time" to exactly one database. And those of these that will be able to "set role"will be able to do this only to other roles that are also confined (at "connect" time) to the same database. Moreover,I cannot worry about what sessions that authorize as "postgres" or its deputy can do because the former is unstoppable"and the latter is dangerously powerful in the wrong human hands. There's always a need for trusted humans who,if they betray that trust, can do untold harm. In this case, they must understand the design of the "multitenancy byconvention" scheme and must be committed to honoring it. So, sure enough, reasoning tells me that my plan is sound. Nevertheless, it does seem to be unfortunate to take the mechanics of "connect" as salient rather than the resulting stateof the session that either "connect" or "set role" can bring about. There could be (in a future PG version) a privilegethat governed "set role" in the present scenario. But I'm sure that there never will be. <aside> You mentioned access to the catalog tables. This, too, belongs to the discussion of the principle of least privilege. Thisaccess is not hard wired. Rather, it's just a manifestation of the default regime. I've prototyped a regime where theprivileges that you need to access these tables (and other things too) are revoked from public and (for convenience) aregranted to a single dedicated role. This means that it's easy to make it such that the role(s) that clients use to connectcan't query the catalog—or, if you prefer, can access exactly and only those catalog items that they need to. I'mpleased with how it worked out. And I'll pursue this regime further. </aside>
On 11/9/22 12:31, Bryn Llewellyn wrote: > Thanks. If nobody thinks that ending up as I showed is possible brings any kind of risk, then I’m happy to accept that.More generally, I’m a huge fan of the principle of least privilege, and (as far as it concerns what I asked about inthis thread), its following aspect: > > If you consider these two alternatives: > > Alt. #1: Some ability that you do not need (and therefor don’t want) is simply not available. > > Alt. #2: That unwanted ability is available, but reasoning shows that it’s harmless. > > then Alt. #1 is preferable. > > But I do see that I can’t get Alt #1 here. > > In my actual use case, every role apart from "postgres", and its non-superuser deputy with "create role" and "createdb",will be confined at "connect time" to exactly one database. And those of these that will be able to "set role"will be able to do this only to other roles that are also confined (at "connect" time) to the same database. Moreover,I cannot worry about what sessions that authorize as "postgres" or its deputy can do because the former is unstoppable"and the latter is dangerously powerful in the wrong human hands. There's always a need for trusted humans who,if they betray that trust, can do untold harm. In this case, they must understand the design of the "multitenancy byconvention" scheme and must be committed to honoring it. So, sure enough, reasoning tells me that my plan is sound. > > Nevertheless, it does seem to be unfortunate to take the mechanics of "connect" as salient rather than the resulting stateof the session that either "connect" or "set role" can bring about. There could be (in a future PG version) a privilegethat governed "set role" in the present scenario. But I'm sure that there never will be. Connecting to database and the role that is in play inside a session are two different things. Making them the same would make things like from here: https://www.postgresql.org/docs/current/sql-createfunction.html [EXTERNAL] SECURITY INVOKER [EXTERNAL] SECURITY DEFINER SECURITY INVOKER indicates that the function is to be executed with the privileges of the user that calls it. That is the default. SECURITY DEFINER specifies that the function is to be executed with the privileges of the user that owns it. The key word EXTERNAL is allowed for SQL conformance, but it is optional since, unlike in SQL, this feature applies to all functions not only external ones. go sideways. > > <aside> > You mentioned access to the catalog tables. This, too, belongs to the discussion of the principle of least privilege. Thisaccess is not hard wired. Rather, it's just a manifestation of the default regime. I've prototyped a regime where theprivileges that you need to access these tables (and other things too) are revoked from public and (for convenience) aregranted to a single dedicated role. This means that it's easy to make it such that the role(s) that clients use to connectcan't query the catalog—or, if you prefer, can access exactly and only those catalog items that they need to. I'mpleased with how it worked out. And I'll pursue this regime further. > </aside> Have you actually done that and tried to run SQL statements? They are called system catalogs because they are used by the system to get the information necessary to do things. Throwing restrictions on their access would be akin to pouring sand in a gearbox, lots of strange behavior and then nothing. -- Adrian Klaver adrian.klaver@aklaver.com
adrian.klaver@aklaver.com wrote: > >> bryn@yugabyte.com wrote: > > Connecting to database and the role that is in play inside a session are two different things. Making them the same wouldmake things [security define vs "security invoker"] go sideways. I said nothing to suggest that the role with which you connect to a database should be identical, always, to what "current_role"returns. I speculated only that an enhanced privilege scheme that limited the target of "set role" to thosethat have "connect" on the current database might be nice. I can't see that this would interfere with the "security"notion for a subprogram. After all, it's already possible for role "r1" to invoke a "security definer" subprogramowned by role "r2" when "r1" cannot "set role" to "r2". (This is probably the overwhelmingly common case.) I believe that I do understand the business of these two "security" kinds for user-defined functions and procedures well.(And, yes, I know that a "set role" attempt in a "security definer" context causes a run-time error.) But thanks formentioning the topic. There's a certain family resemblance between a "security definer" subprogram and "set role" in thateach brings the outcome that the value that "current_role" returns might differ from the value that "session_user" returns.And you can certainly arrange it so that a "security definer" subprogram is owned by a role that does not have "connect"on the database where the subprogram exists. There is, though, a difference between the two paradigms in that thesubprogram follows a stacked behavior so that when the subprogram that's first called exits, the "current_role" valueis back where it was when the call was made. In contrast "set role" makes a durable change that you can see at the "psql"prompt (mentioning this as an example of any client). And you can use "set role" to roam around, on demand, among anynumber of roles in the set that allows you do do this in any order. This feels different—at least to me. Anyway, all this is moot (except in that thinking about it helps me to enrich my mental model) because the privilege notionshere will never change. >> <aside> >> You mentioned access to the catalog tables. This, too, belongs to the discussion of the principle of least privilege.This access is not hard wired. Rather, it's just a manifestation of the default regime. I've prototyped a regimewhere the privileges that you need to access these tables (and other things too) are revoked from public and (for convenience)are granted to a single dedicated role. This means that it's easy to make it such that the role(s) that clientsuse to connect can't query the catalog—or, if you prefer, can access exactly and only those catalog items that theyneed to. I'm pleased with how it worked out. And I'll pursue this regime further. >> </aside> > > Have you actually done that and tried to run SQL statements? They are called system catalogs because they are used by thesystem to get the information necessary to do things. Throwing restrictions on their access would be akin to pouring sandin a gearbox, lots of strange behavior and then nothing. Yes I have actually done this. But rigorous testing remains to be done. I've implemented the scheme only within a disciplinedbigger picture. I've mentioned the thinking that I'll sketch now, before, in other contexts. It's not original.Many real-world applications follow it. I like to refer to it as the "hard shell" paradigm. Here, the ownershipof the various artifacts that implement an application's database backend is spread among as many roles as you please.For example, tables and their associated artifacts (like indexes, sequences, and so on) would have a different ownerfrom the user-defined subprograms that implement the business functions that access the tables. Significantly, client-sideaccess to this whole shooting match would be via one (or a few) dedicated "client" roles. Such a role has only"connect" on the database that houses the application's backend. And it owns no schema and no objects in other schemas.Rather, it's just the target for the "execute" privilege of those few of all the user-defined subprograms that jointlydefine the database's API. The point (conforming to the principle of least privilege) is that sessions that connectas "client" must not be allowed to do arbitrary SQL. Rather, they should be able to do only what has been explicitly"white-listed" in by the encapsulation provided by the API-defining subprograms. The "lazy" approach for the roles that own the application's implementation and that rely on (some of) the artifacts thatlive in "pg_catalog" is simply to revoke "all" from "public" for every one of these catalog items and, in the same breath,to grant "all" (or what is needed) on each to a dedicated role (say, "d0$developer" in database "d0"). Then "d0"developer"is granted to every role that owns any of the artifacts that jointly implement the application's database backend.But, significantly, "d0$client_1" for as many such "client" roles as there are would NOT have "d0$developer" grantedto it. A less lazy approach would be carefully to grant exactly and only what was needed to each artifact owning role to allow itto do what it's designed to do—and nothing else. Of course, this would take significant effort (and not least testing).And this might not be judged to be cost-effective.
On 11/9/22 15:23, Bryn Llewellyn wrote: > adrian.klaver@aklaver.com wrote: >> >>> bryn@yugabyte.com wrote: >> >> Connecting to database and the role that is in play inside a session are two different things. Making them the same wouldmake things [security define vs "security invoker"] go sideways. > > I said nothing to suggest that the role with which you connect to a database should be identical, always, to what "current_role"returns. I speculated only that an enhanced privilege scheme that limited the target of "set role" to thosethat have "connect" on the current database might be nice. I can't see that this would interfere with the "security"notion for a subprogram. After all, it's already possible for role "r1" to invoke a "security definer" subprogramowned by role "r2" when "r1" cannot "set role" to "r2". (This is probably the overwhelmingly common case.) > > I believe that I do understand the business of these two "security" kinds for user-defined functions and procedures well.(And, yes, I know that a "set role" attempt in a "security definer" context causes a run-time error.) But thanks formentioning the topic. There's a certain family resemblance between a "security definer" subprogram and "set role" in thateach brings the outcome that the value that "current_role" returns might differ from the value that "session_user" returns.And you can certainly arrange it so that a "security definer" subprogram is owned by a role that does not have "connect"on the database where the subprogram exists. There is, though, a difference between the two paradigms in that thesubprogram follows a stacked behavior so that when the subprogram that's first called exits, the "current_role" valueis back where it was when the call was made. In contrast "set role" makes a durable change that you can see at the "psql"prompt (mentioning this as an example of any client). And you can use "set role" to roam around, on demand, among anynumber of roles in the set that allows you do do this in any order. This feels different—at least to me. > > Anyway, all this is moot (except in that thinking about it helps me to enrich my mental model) because the privilege notionshere will never change. So, I want it but not really. > >>> <aside> >>> You mentioned access to the catalog tables. This, too, belongs to the discussion of the principle of least privilege.This access is not hard wired. Rather, it's just a manifestation of the default regime. I've prototyped a regimewhere the privileges that you need to access these tables (and other things too) are revoked from public and (for convenience)are granted to a single dedicated role. This means that it's easy to make it such that the role(s) that clientsuse to connect can't query the catalog—or, if you prefer, can access exactly and only those catalog items that theyneed to. I'm pleased with how it worked out. And I'll pursue this regime further. >>> </aside> >> >> Have you actually done that and tried to run SQL statements? They are called system catalogs because they are used bythe system to get the information necessary to do things. Throwing restrictions on their access would be akin to pouringsand in a gearbox, lots of strange behavior and then nothing. > > Yes I have actually done this. But rigorous testing remains to be done. I've implemented the scheme only within a disciplinedbigger picture. I've mentioned the thinking that I'll sketch now, before, in other contexts. It's not original.Many real-world applications follow it. I like to refer to it as the "hard shell" paradigm. Here, the ownershipof the various artifacts that implement an application's database backend is spread among as many roles as you please.For example, tables and their associated artifacts (like indexes, sequences, and so on) would have a different ownerfrom the user-defined subprograms that implement the business functions that access the tables. Significantly, client-sideaccess to this whole shooting match would be via one (or a few) dedicated "client" roles. Such a role has only"connect" on the database that houses the application's backend. And it owns no schema and no objects in other schemas.Rather, it's just the target for the "execute" privilege of those few of all the user-defined subprograms that jointlydefine the database's API. The point (conforming to the principle of least privilege) is that sessions that connectas "client" must not be allowed to do arbitrary SQL. Rather, they should be able to do only what has been explicitly"white-listed" in by the encapsulation provided by the API-defining subprograms. All right that I get. -- Adrian Klaver adrian.klaver@aklaver.com
> adrian.klaver@aklaver.com wrote: > >> bryn@yugabyte.com wrote: >> >> Anyway, all this is moot (except in that thinking about it helps me to enrich my mental model) because the privilege notionshere will never change. > > So, I want it but not really. I’d rather say “I’d very much prefer it if I had it. But, because I don’t, I will have to write a comment essay to explainwhat tests might show and why these outcomes that might seem worrisome at first sight can be seen, after an exerciseof reasoning, to be harmless. I’m not a fan of that kind of essay writing. But I’ll do it if I have to. >> Yes I have actually done this. But rigorous testing remains to be done... The point (conforming to the principle of leastprivilege) is that sessions that connect as "client" must not be allowed to do arbitrary SQL. Rather, they should beable to do only what has been explicitly "white-listed" in by the encapsulation provided by the API-defining subprograms. > > All right that I get. Good. I’m relieved that you haven’t (yet) spotted a flaw in my scheme.