async def list_versions(
self, organization_id: OrganizationID, author: DeviceID, vlob_id: UUID
) -> Dict[int, Tuple[pendulum.Pendulum, DeviceID]]:
async with self.dbh.pool.acquire() as conn:
async with conn.transaction():
realm_id = await _get_realm_id_from_vlob_id(conn, organization_id, vlob_id)
await _check_realm_and_read_access(conn, organization_id, author, realm_id, None)
query = """
SELECT
version,
({}) as author,
created_on
FROM vlob_atom
WHERE
organization = ({})
AND vlob_id = $2
ORDER BY version DESC
""".format(
q_device(_id=Parameter("author")).select("device_id"),
q_organization_internal_id(Parameter("$1")),
)
rows = await conn.fetch(query, organization_id, vlob_id)
assert rows
if not rows:
raise VlobNotFoundError(f"Vlob `{vlob_id}` doesn't exist")
return {row["version"]: (row["created_on"], row["author"]) for row in rows}
STR_TO_REALM_MAINTENANCE_TYPE,
q_user,
q_user_internal_id,
q_user_can_read_vlob,
q_device,
q_realm_internal_id,
q_realm,
)
_q_get_realm_status = (q_realm(
organization_id=Parameter("$1"), realm_id=Parameter("$2")).select(
q_user_can_read_vlob(organization_id=Parameter("$1"),
realm_id=Parameter("$2"),
user_id=Parameter("$3")).as_("has_access"),
"encryption_revision",
q_device(_id=Parameter("maintenance_started_by")).select(
"device_id").as_("maintenance_started_by"),
"maintenance_started_on",
"maintenance_type",
)).get_sql()
_q_get_current_roles = """
SELECT DISTINCT ON(user_) ({}), role
FROM realm_user_role
WHERE realm = ({})
ORDER BY user_, certified_on DESC
""".format(
q_user(_id=Parameter("user_")).select("user_id"),
q_realm_internal_id(organization_id=Parameter("$1"),
realm_id=Parameter("$2")),
)
from parsec.backend.user import User, Device, Trustchain, UserNotFoundError, GetUserAndDevicesResult
from parsec.backend.postgresql.utils import Query, query
from parsec.backend.postgresql.tables import (
STR_TO_USER_PROFILE,
t_user,
t_device,
q_device,
q_organization_internal_id,
q_user_internal_id,
)
_q_get_user = (Query.from_(t_user).select(
"profile",
"user_certificate",
"redacted_user_certificate",
q_device(_id=t_user.user_certifier).select(
t_device.device_id).as_("user_certifier"),
"created_on",
"revoked_on",
"revoked_user_certificate",
q_device(_id=t_user.revoked_user_certifier).select(
t_device.device_id).as_("revoked_user_certifier"),
).where((t_user.organization == q_organization_internal_id(Parameter("$1")))
& (t_user.user_id == Parameter("$2"))).get_sql())
_t_d1 = t_device.as_("d1")
_t_d2 = t_device.as_("d2")
_t_d3 = t_device.as_("d3")
_q_get_device = (Query.from_(_t_d1).select(
"device_label",
"device_certificate",
"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())
async def send_message(conn, organization_id, sender, recipient, timestamp,
body):
index = await conn.fetchval(_q_insert_message, organization_id, recipient,
timestamp, sender, body)
await send_signal(
conn,
"message.received",
organization_id=organization_id,
author=sender,
SET
organization = excluded.organization,
creator = excluded.creator,
created_on = excluded.created_on
""".format(
q_organization_internal_id(organization_id=Parameter("$1")),
q_device_internal_id(organization_id=Parameter("$1"),
device_id=Parameter("$2")),
Parameter("$3"),
Parameter("$4"),
)
_q_get_invitation = (q_user_invitation(
organization_id=Parameter("$1"), user_id=Parameter("$2")).select(
"user_id",
q_device(_id=Parameter("creator")).select("device_id"),
"created_on").get_sql())
_q_delete_invitation = (q_user_invitation(
organization_id=Parameter("$1"),
user_id=Parameter("$2")).delete().get_sql())
async def _get_user_invitation(conn, organization_id: OrganizationID,
user_id: UserID):
if await _user_exists(conn, organization_id, user_id):
raise UserAlreadyExistsError(f"User `{user_id}` already exists")
result = await conn.fetchrow(_q_get_invitation, organization_id, user_id)
if not result:
raise UserNotFoundError(user_id)
async def read(
self,
organization_id: OrganizationID,
author: DeviceID,
encryption_revision: int,
vlob_id: UUID,
version: Optional[int] = None,
timestamp: Optional[pendulum.Pendulum] = None,
) -> Tuple[int, bytes, DeviceID, pendulum.Pendulum]:
async with self.dbh.pool.acquire() as conn, conn.transaction():
realm_id = await _get_realm_id_from_vlob_id(conn, organization_id, vlob_id)
await _check_realm_and_read_access(
conn, organization_id, author, realm_id, encryption_revision
)
if version is None:
if timestamp is None:
query = """
SELECT
version,
blob,
({}) as author,
created_on
FROM vlob_atom
WHERE
vlob_encryption_revision = ({})
AND vlob_id = $4
ORDER BY version DESC
LIMIT 1
""".format(
q_device(_id=Parameter("author")).select("device_id"),
q_vlob_encryption_revision_internal_id(
organization_id=Parameter("$1"),
realm_id=Parameter("$2"),
encryption_revision=Parameter("$3"),
),
)
data = await conn.fetchrow(
query, organization_id, realm_id, encryption_revision, vlob_id
)
assert data # _get_realm_id_from_vlob_id checks vlob presence
else:
query = """
SELECT
version,
blob,
({}) as author,
created_on
FROM vlob_atom
WHERE
vlob_encryption_revision = ({})
AND vlob_id = $4
AND created_on <= $5
ORDER BY version DESC
LIMIT 1
""".format(
q_device(_id=Parameter("author")).select("device_id"),
q_vlob_encryption_revision_internal_id(
organization_id=Parameter("$1"),
realm_id=Parameter("$2"),
encryption_revision=Parameter("$3"),
),
)
data = await conn.fetchrow(
query, organization_id, realm_id, encryption_revision, vlob_id, timestamp
)
if not data:
raise VlobVersionError()
else:
query = """
SELECT
version,
blob,
({}) as author,
created_on
FROM vlob_atom
WHERE
vlob_encryption_revision = ({})
AND vlob_id = $4
AND version = $5
""".format(
q_device(_id=Parameter("author")).select("device_id"),
q_vlob_encryption_revision_internal_id(
organization_id=Parameter("$1"),
realm_id=Parameter("$2"),
encryption_revision=Parameter("$3"),
),
)
data = await conn.fetchrow(
query, organization_id, realm_id, encryption_revision, vlob_id, version
)
if not data:
raise VlobVersionError()
return list(data)
from parsec.backend.user import (
UserError,
UserNotFoundError,
UserAlreadyExistsError,
DeviceInvitation,
)
from parsec.backend.postgresql.handler import send_signal
from parsec.backend.postgresql.utils import query
from parsec.backend.postgresql.tables import (
q_device,
q_device_invitation,
q_organization_internal_id,
q_device_internal_id,
)
_q_device_exists = (q_device(organization_id=Parameter("$1"),
device_id=Parameter("$2")).select(True).get_sql())
async def _device_exists(conn, organization_id: OrganizationID,
device_id: DeviceID):
device_result = await conn.fetchrow(_q_device_exists, organization_id,
device_id)
return bool(device_result)
_q_insert_invitation = """
INSERT INTO device_invitation (
organization,
creator,
device_id,
created_on