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_,