First, let me say I was holding off replying/thanking everyone to have the time
to properly test this. Erik's quasi-question makes me break that silence.
On Tue, Apr 4, 2023 at 10:46 AM Erik Wienhold <ewie@xxxxxxxxx> wrote:
> On 04/04/2023 07:55 CEST walther@xxxxxxxxxxxxxxx wrote:
> For me, checking whether session_user has the privilege for claimed_role
> is not enough, so I add a DOMAIN to the mix:
> CREATE DOMAIN current_user_only AS NAME CHECK (VALUE = CURRENT_USER);
> CREATE FUNCTION f(calling_user current_user_only DEFAULT CURRENT_USER)
Nice. It's equivalent to my version without the domain if the client can
execute SET ROLE before calling f, thereby injecting any role for which
pg_has_role(session_user, calling_user, 'MEMBER') returns true.
First, thanks to Christoph, Erik, and Walther, for helping out.
Second, very nice indeed. I'll test it out soon.
Dominique did not say whether he controls the clients or not.
I would say I don't. The decision was made to go with a 2-tier architecture,
so the desktop apps connect to PostgreSQL using a typically per-OS-user
PostgreSQL LOGIN user, so even though it's our app's code that access
the DB, so we control that part, nothing prevents those users to connect
directly via psql or pgAdmin or any libpq or protocol-talking client, and try
to do mischiefs or worse. Thus the server-side permission model must be
as tight as it can be. Which means "regular" users don't have DDL privs,
of course; thus the need for a mid-tier "more privileged" service to "mediate"
the DDLs we sometimes need to do on behalf of "regular" users; thus the
need to properly authenticate those users with the mid-tier services, based
on the fact they can connect to the DB and its schema(s) and access/execute
that JWT returning function. Hopefully that's clear :)
