virtual relationship..?

[Julien Cigar]

Dear SQLAlchemy users,

I have a (Pyramid) application (CMS-like) for which I'm adding
authorization.

The core consists of "users", "roles", and "permissions", where an
"user" can have many "roles", and a "role" can have many
"permissions" (see #1).

Nothing really new, but where it gets a bit complicated is that I have
two types of "roles": "classical" and "virtual" (which are all stored
in the database, in a "role" table, wether they are classical or
virtual).

How to know if a user "has a" role depends of the role type.

For the "classical" ones and entry should exist in the intermediary
table (many-to-many). However, "virtual" roles are assigned dynamically
by the application at the beginning of each request (and available in
some request.effective_principals property) and depends of some
context (if the user is logged, etc), so there is no entry in the
intermediary table.

For beauty and simplicity I'd like to have an User.roles property (which
in my current version fetches the intermediary table, so "classical"
roles only) which contains both types of roles (classical and virtual
ones).
Actually I have some wrapper function above the .roles property which
does that, but I don't like it too much.

The virtual ones should be excluded from any "state" management of
course (I have a trigger at the database level which forbids a link
between a virtual role and an account).

What would be a good way to do that in SQLAlchemy?

(1) https://gist.github.com/silenius/f7e4f4da9370e5db182e41d7ae93d324

Thank you,
Julien

--
Julien Cigar
Belgian Biodiversity Platform (http://www.biodiversity.be)
PGP fingerprint: EEF9 F697 4B68 D275 7B11 6A25 B2BB 3710 A204 23C0
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.

[Mike Bayer]

is the general idea, one attribute that is merging two totally
different relationships? You'd just build a @property that creates a
view of the two relationships when accessed. Kind of like an
association proxy, but less flexible and read-only.

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
> ---
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/20190509103817.GC39998%40home.lan.
> For more options, visit https://groups.google.com/d/optout.

[Julien Cigar]

On Thu, May 09, 2019 at 10:17:30PM -0400, Mike Bayer wrote:
> is the general idea, one attribute that is merging two totally
> different relationships? You'd just build a @property that creates a
> view of the two relationships when accessed. Kind of like an
> association proxy, but less flexible and read-only.

OK I'll make a separate property, I had some vague idea about using some
QueryEvents to manipulate the query, but it sounds a bit complicated and
hazardous

Thanks :)

> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CA%2BRjkXGUt1y1HPr0Vb9bRtiZi8Ge6u%2Byt6957mw8jbZOc4Cb3Q%40mail.gmail.com.

> For more options, visit https://groups.google.com/d/optout.