hjp-pgsql@xxxxxx wrote: 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. In such a world, the RDBMS gets a suitable ID for the human (by all means suitably encrypted) and can use this to advantage by consulting its own representation of the current human's identity. If my guess about your question is wrong, then I'm wasting my time. But I'll carry on anyway. This is the canonical example: —A manager must be able to see the salaries of all employees in the reporting hierarchy under her/him—but not be able to see salaries outside of that subtree. Imagine the usual "employees" table with the pig's ear "manager_id" FK. But add an extra column for each employees external-to-the-database unique ID (unless this is already used as the table's PK). PG has a native feature for this: row level security (hereinafter RLS). You can Google for blog posts about this use case. Here's one by a colleague of mine, Franck: It uses the multi-tenant use case rather than my "managers-and-salaries" one. But the principles are what matter. Never mind the detail. The critical table(s) are striped with "tenant_id". And this is passed from the application tier to the PG tier with: set rls.tenant_id=42; Using the "hard shell" paradigm, "rls.tenant_id" could be a formal argument to an API subprogram. And its within-database representation could be a one column, one row temporary table with "on commit delete rows". Franck's PG RLS policies are created like this: create policy... on ... for... using(tenant_id=current_setting('rls.tenant_id')::int); The argument of "using()" is just an arbitrary SQL _expression_ that evaluates to a boolean. So in the "managers-and-salaries" use case, it could be "employee_id in (<a suitable query that uses a recursive CTE>)". 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. But the context of these examples is very definitely a connection pooling regime that uses the single role "client" on behalf of (very) many different human users. Finally, in case your question sought an account of some real-world scheme in this general use case space, a bit of Googling for « Salesforce multi-tenancy » got me this: Platform Multitenant Architecture It's public knowledge that they use Oracle Database (or at least did—I haven't thought about them recently). But the general principles have their equivalents in PG too. Salesforce engineers have also talked at Oracle User group conferences about how they use PL/SQL. Their main multi-tenant implementation (at least at the time of these talks) followed the "hard shell" paradigm. |
