pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Bryn Llewellyn <bryn@yugabyte.com>
To: pgsql-general list <pgsql-general@lists.postgresql.org>
Subject: "set role" semantics
Date: Tue, 8 Nov 2022 16:16:31 -0800
Message-ID: <FE0F7CE2-F1E8-40C8-A097-0CFDB0E14933@yugabyte.com> (raw)

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

view thread (14+ messages)  latest in thread

Message-ID: <FE0F7CE2-F1E8-40C8-A097-0CFDB0E14933@yugabyte.com>
Permalink:  ../FE0F7CE2-F1E8-40C8-A097-0CFDB0E14933@yugabyte.com/
Also on:    postgresql.org/message-id/FE0F7CE2-F1E8-40C8-A097-0CFDB0E14933@yugabyte.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: bryn@yugabyte.com
  Subject: Re: "set role" semantics
  In-Reply-To: <FE0F7CE2-F1E8-40C8-A097-0CFDB0E14933@yugabyte.com>
  References: <FE0F7CE2-F1E8-40C8-A097-0CFDB0E14933@yugabyte.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox