adrian.klaver@xxxxxxxxxxx wrote:david.g.johnston@xxxxxxxxx wrote:bryn@xxxxxxxxxxxx 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? *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()". |
