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 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; ---------------------- 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'; " 。 |
