async def _check_realm_access(conn, organization_id, realm_id, author, allowed_roles):
query = """
WITH cte_current_realm_roles AS (
SELECT DISTINCT ON(user_) user_, role
FROM realm_user_role
WHERE realm = ({})
ORDER BY user_, certified_on DESC
)
SELECT role
FROM user_
LEFT JOIN cte_current_realm_roles
ON user_._id = cte_current_realm_roles.user_
WHERE user_._id = ({})
""".format(
q_realm_internal_id(organization_id=Parameter("$1"), realm_id=Parameter("$2")),
q_user_internal_id(organization_id=Parameter("$1"), user_id=Parameter("$3")),
)
rep = await conn.fetchrow(query, organization_id, realm_id, author.user_id)
if not rep:
raise VlobNotFoundError(f"User `{author.user_id}` doesn't exist")
if STR_TO_REALM_ROLE.get(rep[0]) not in allowed_roles:
raise VlobAccessError()
from pypika import Parameter
from parsec.api.protocol import OrganizationID
from parsec.backend.user import User, Device, UserError, UserNotFoundError, UserAlreadyExistsError
from parsec.backend.postgresql.handler import send_signal
from parsec.backend.postgresql.utils import Query, query
from parsec.backend.postgresql.tables import (
t_device,
t_user,
q_organization_internal_id,
q_user_internal_id,
q_device_internal_id,
)
_q_get_user_devices = (Query.from_(t_device).select(
t_device.device_id).where(t_device.user_ == q_user_internal_id(
organization_id=Parameter("$1"), user_id=Parameter("$2"))).get_sql())
_q_insert_user = (Query.into(t_user).columns(
"organization", "user_id", "is_admin", "user_certificate",
"user_certifier", "created_on").insert(
q_organization_internal_id(Parameter("$1")),
Parameter("$2"),
Parameter("$3"),
Parameter("$4"),
q_device_internal_id(organization_id=Parameter("$1"),
device_id=Parameter("$5")),
Parameter("$6"),
).get_sql())
_q_insert_device = (Query.into(t_device).columns(
"organization", "user_", "device_id", "device_certificate",
WHERE realm = ({})
ORDER BY certified_on ASC
""".format(
q_user(_id=Parameter("user_")).select("user_id"),
q_realm_internal_id(organization_id=Parameter("$1"),
realm_id=Parameter("$2")),
)
_q_get_realms_for_user = """
SELECT DISTINCT ON(realm) ({}) as realm_id, role
FROM realm_user_role
WHERE user_ = ({})
ORDER BY realm, certified_on DESC
""".format(
q_realm(_id=Parameter("realm")).select("realm_id"),
q_user_internal_id(organization_id=Parameter("$1"),
user_id=Parameter("$2")),
)
@query()
async def query_get_status(conn, organization_id: OrganizationID,
author: DeviceID, realm_id: UUID) -> RealmStatus:
ret = await conn.fetchrow(_q_get_realm_status, organization_id, realm_id,
author.user_id)
if not ret:
raise RealmNotFoundError(f"Realm `{realm_id}` doesn't exist")
if not ret["has_access"]:
raise RealmAccessError()
return RealmStatus(
"redacted_device_certificate",
q_device(_id=_t_d1.device_certifier,
table=_t_d2).select(_t_d2.device_id).as_("device_certifier"),
"created_on",
).where((_t_d1.organization == q_organization_internal_id(Parameter("$1")))
& (_t_d1.device_id == Parameter("$2"))).get_sql())
_q_get_user_devices = (Query.from_(_t_d1).select(
"device_id",
"device_label",
"device_certificate",
"redacted_device_certificate",
q_device(_id=_t_d1.device_certifier,
table=_t_d2).select(_t_d2.device_id).as_("device_certifier"),
"created_on",
).where(_t_d1.user_ == q_user_internal_id(organization_id=Parameter("$1"),
user_id=Parameter("$2"))).get_sql())
_q_get_trustchain = """
WITH RECURSIVE cte2 (
_uid, _did, user_id, device_id,
user_certifier, revoked_user_certifier, device_certifier,
user_certificate, redacted_user_certificate, revoked_user_certificate, device_certificate, redacted_device_certificate
) AS (
WITH cte (
_uid, _did, user_id, device_id,
user_certifier, revoked_user_certifier, device_certifier,
user_certificate, redacted_user_certificate, revoked_user_certificate, device_certificate, redacted_device_certificate
) AS (
SELECT user_._id AS _uid, device._id AS _did, user_id, device_id,
user_certifier, revoked_user_certifier, device_certifier,
from parsec.backend.message import BaseMessageComponent
from parsec.backend.postgresql.handler import send_signal, PGHandler
from parsec.backend.postgresql.utils import Query
from parsec.backend.postgresql.tables import (
t_message,
q_user_internal_id,
q_device_internal_id,
q_device,
q_organization_internal_id,
)
_q_insert_message = (Query.into(t_message).columns(
"organization", "recipient", "timestamp", "index", "sender",
"body").insert(
q_organization_internal_id(Parameter("$1")),
q_user_internal_id(organization_id=Parameter("$1"),
user_id=Parameter("$2")),
Parameter("$3"),
Query.from_(t_message).select(fn.Count("*") + 1).where(
t_message.recipient == q_user_internal_id(
organization_id=Parameter("$1"), user_id=Parameter("$2"))),
q_device_internal_id(organization_id=Parameter("$1"),
device_id=Parameter("$4")),
Parameter("$5"),
).returning("index").get_sql())
_q_get_messages = (Query.from_(t_message).select(
q_device(_id=t_message.sender).select("device_id"), t_message.timestamp,
t_message.body).where(t_message.recipient == q_user_internal_id(
organization_id=Parameter("$1"), user_id=Parameter("$2"))).orderby(
"_id", order=Order.asc).offset(Parameter("$3")).get_sql())
q_realm(organization_id=Parameter("$1"), realm_id=Parameter("$2")).select(
"encryption_revision",
"maintenance_started_by",
"maintenance_started_on",
"maintenance_type",
)
).get_sql()
_q_get_roles = """
SELECT
({}),
({})
FROM UNNEST($3::VARCHAR[]) AS needle_user_id
""".format(
q_user_internal_id(organization_id=Parameter("$1"), user_id=Parameter("needle_user_id")),
q_realm_user_role(
organization_id=Parameter("$1"),
user_id=Parameter("needle_user_id"),
realm_id=Parameter("$2"),
)
.select("role")
.orderby("certified_on", order=Order.desc)
.limit(1),
)
_q_insert_realm_user_role = """
INSERT INTO realm_user_role(
realm,
user_,
