Re: Limiting the operations that client-side code can perform upon its database backend's artifacts

On 2022-09-30 17:59:01 -0700, Bryn Llewellyn wrote:
>     hjp-pgsql@hjp.at wrote:
>         bryn@yugabyte.com wrote:
>         Paraphrasing Peter, the design of the application's RDBMS backend has
>         to implement its own notions of roles and privileges as a new layer on
>         top of whatever the native RDBMS mechanisms provide. Some RDBMSs have
>         native primitives that help the implementation of this next,
>         application-specific, roles-and-privileges regime.
>
>     Can you provide a link to documentation (hopefully high-level and concise)
>     of such a system? I'm having a hard time imagining one which I wouldn't
>     either classify as "database roles by another name" or "just data".
>
>
> I fear that we might be about to start another round of mutual
> misunderstanding. I’m not confident that I understand the question.
>
> I believe that you want x-refs to accounts of native RDBMS features that let
> you implement application-level specific notions of security on top of the
> basic roles and privileges features and that are oriented to the case where a
> single RDBMS role, "client", must expose differentiated functionality to
> different human end-users—where these principals are identified by, and
> authorized by, system(s) outside of the RDBMS in question.

I the context of this thread, I don't "want" anything (what I actually
want may change from project to project). I am not familiar with the
"native primitives" you mentioned, so I would like to read up on them.
So if you can just drop a few names I can feed them to my favourite
search engine. An overview article which explains the concept and how
the primitives are used would be better of course, but if you don't have
any at hand, no problem.

Background: I have used Oracle, MySQL/MariaDB and PostgreSQL enough that
I consider myself to be quite familiar with their capabilities (my
Oracle skills are getting rusty, though). I have occasionally used
and/or read about other RDBMSs, but my knowledge of those is very
spotty.


> Franck's PG RLS  policies are created like this:
>
> create policy... on ... for... using(tenant_id=current_setting
> ('rls.tenant_id')::int);

So, I think the intermediate concept here which is neither role nor data
is the use of a run-time parameter.

He's not using a database role and he's not using a parameter which has
to be supplied to every query by the application programmer, but a
run-time parameter which would presumably be set once at the beginning
of a session or transaction (depending on whether you use connection
pooling). That's clever. Not sure if I would actually use it but it's
certainly something I'll add to my bag of tools.

> I'm afraid that I don't know what you mean by « classify as "database roles by
> another name" or "just data" ». For me, the RLS scheme is simply the native PG
> feature that you use to get the result that you want.

I meant what do you use to identify the user. The mechanism in
PostgreSQL intended for this are roles. You can use roles in RLS (in
fact I don't think I've ever seen an example which didn't use roles).

In contrast to this many systems (e.g. Django, Typo3, several I've
designed myself) use a table to keep their own list of users (and
possibly groups), and then check for every access whether the user
logged into the application has access by joining with that table. That
join may be explicit in the application code or it may be hidden behind
a view or a function. But the database doesn't know about that user.
It's just another parameter passed in by the application - "just data".

Using a run-time parameter is somewhere in between. The database still
doesn't know what it means (so in some sense it's still "just data"),
but it will keep the value for the duration of the transaction or
session, so the application gets sort of an "authorize once, then forget
about it" abstraction which puts it closer to the "roles by another
name" camp.


> Platform Multitenant Architecture
> https://architect.salesforce.com/fundamentals/platform-multitenant-architecture

That's *too* high-level for me. There's any number of techniques which
could be used to implement something like that and I don't see how they
actually did it (maybe I missed it - I admit I only skimmed the
article).

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"