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}
def _q_factory(query, omit_revoked):
_param_count = itertools.count(1)
def _next_param():
return Parameter(f"${next(_param_count)}")
q = (Query.from_(t_user).select(t_user.user_id).where(
(t_user.organization == q_organization_internal_id(
_next_param()))).orderby(t_user.user_id))
if query:
q = q.where(IRegex(t_user.user_id, _next_param()))
if omit_revoked:
q = q.where(
t_user.revoked_on.isnull() | t_user.revoked_on > _next_param())
return q
async def group_check(
self, organization_id: OrganizationID, author: DeviceID, to_check: List[dict]
) -> List[dict]:
changed = []
to_check_dict = {}
for x in to_check:
if x["version"] == 0:
changed.append({"vlob_id": x["vlob_id"], "version": 0})
else:
to_check_dict[x["vlob_id"]] = x
async with self.dbh.pool.acquire() as conn:
query = """
SELECT DISTINCT ON (vlob_id) vlob_id, version
FROM vlob_atom
WHERE
organization = ({})
AND vlob_id = any($3::uuid[])
AND ({})
AND NOT ({})
ORDER BY vlob_id, version DESC
""".format(
q_organization_internal_id(Parameter("$1")),
q_user_can_read_vlob(
organization_id=Parameter("$1"),
user_id=Parameter("$2"),
realm=Query.from_(t_vlob_encryption_revision)
.select("realm")
.where(t_vlob_encryption_revision._id == Parameter("vlob_encryption_revision")),
),
q_realm_in_maintenance(
realm=Query.from_(t_vlob_encryption_revision)
.select("realm")
.where(t_vlob_encryption_revision._id == Parameter("vlob_encryption_revision"))
),
)
rows = await conn.fetch(query, organization_id, author.user_id, to_check_dict.keys())
for vlob_id, version in rows:
if version != to_check_dict[vlob_id]["version"]:
changed.append({"vlob_id": vlob_id, "version": version})
return changed
def _q_human_factory(query, omit_revoked, omit_non_human):
_param_count = itertools.count(1)
def _next_param():
return Parameter(f"${next(_param_count)}")
q_revoked = t_user.revoked_on.notnull() & (t_user.revoked_on <=
_next_param())
q = (Query.from_(t_user).left_join(t_human).on(
t_user.human == t_human._id).select(
t_user.user_id, t_human.email, t_human.label,
q_revoked.as_("revoked")).where(
(t_user.organization == q_organization_internal_id(
_next_param()))))
if query:
q = q.where(
IRegex(Concat(t_human.label, t_human.email, t_user.user_id),
_next_param()))
if omit_revoked:
q = q.where(q_revoked.negate())
if omit_non_human:
q = q.where(t_user.human.notnull())
return q.get_sql()
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",
"device_certifier", "created_on").insert(
q_organization_internal_id(Parameter("$1")),
q_user_internal_id(organization_id=Parameter("$1"),
user_id=Parameter("$2")),
Parameter("$3"),
q_device_internal_id,
)
_q_insert_realm = """
INSERT INTO realm (
organization,
realm_id,
encryption_revision
) SELECT
({}),
({}),
1
ON CONFLICT (organization, realm_id) DO NOTHING
RETURNING
_id
""".format(q_organization_internal_id(Parameter("$1")), Parameter("$2"))
_q_insert_realm_role = """
INSERT INTO realm_user_role(
realm,
user_,
role,
certificate,
certified_by,
certified_on
)
SELECT
({}), ({}), ({}), ({}), ({}), ({})
""".format(
Parameter("$1"),
q_user_internal_id(organization_id=Parameter("$2"),
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",
"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())
async def get_realm_role_for_not_revoked(conn,
organization_id,
realm_id,
users=None):
now = pendulum.now()
def _cook_role(row):
if row["revoked_on"] and row["revoked_on"] <= now:
return None
if row["role"] is None:
return None
return STR_TO_REALM_ROLE[row["role"]]
if users:
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 user_.user_id as user_id, user_.revoked_on as revoked_on, role
FROM user_
LEFT JOIN cte_current_realm_roles
ON user_._id = cte_current_realm_roles.user_
WHERE
organization = ({})
AND user_.user_id = ANY({}::VARCHAR[])
""".format(
q_realm_internal_id(organization_id=Parameter("$1"),
realm_id=Parameter("$2")),
q_organization_internal_id(Parameter("$1")),
Parameter("$3"),
)
rep = await conn.fetch(query, organization_id, realm_id, users)
roles = {row["user_id"]: _cook_role(row) for row in rep}
for user in users or ():
if user not in roles:
raise RealmNotFoundError(f"User `{user}` doesn't exist")
return roles
else:
query = """
SELECT DISTINCT ON(user_) ({}) as user_id, ({}) as revoked_on, role
FROM realm_user_role
WHERE realm = ({})
ORDER BY user_, certified_on DESC
""".format(
q_user(_id=Parameter("realm_user_role.user_")).select("user_id"),
q_user(
_id=Parameter("realm_user_role.user_")).select("revoked_on"),
q_realm_internal_id(organization_id=Parameter("$1"),
realm_id=Parameter("$2")),
)
rep = await conn.fetch(query, organization_id, realm_id)
return {
row["user_id"]: _cook_role(row)
for row in rep if _cook_role(row) is not None
}
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_get_not_revoked_users_for_human = (Query.from_(t_user).select(
t_user.user_id).where((t_user.human == q_human_internal_id(
organization_id=Parameter("$1"), email=Parameter("$2")))
& (t_user.revoked_on.isnull()
| t_user.revoked_on > Parameter("$3"))).get_sql())
_q_insert_human_if_not_exists = (Query.into(t_human).columns(
"organization", "email",
"label").insert(q_organization_internal_id(Parameter("$1")),
Parameter("$2"),
Parameter("$3")).on_conflict().do_nothing().get_sql())
_q_insert_user = (Query.into(t_user).columns(
"organization",
"user_id",
"profile",
"user_certificate",
"redacted_user_certificate",
"user_certifier",
"created_on",
).insert(
q_organization_internal_id(Parameter("$1")),
Parameter("$2"),
Parameter("$3"),
async def maintenance_save_reencryption_batch(
self,
organization_id: OrganizationID,
author: DeviceID,
realm_id: UUID,
encryption_revision: int,
batch: List[Tuple[UUID, int, bytes]],
) -> Tuple[int, int]:
async with self.dbh.pool.acquire() as conn, conn.transaction():
await _check_realm_and_maintenance_access(
conn, organization_id, author, realm_id, encryption_revision
)
for vlob_id, version, blob in batch:
query = """
INSERT INTO vlob_atom(
organization,
vlob_encryption_revision,
vlob_id,
version,
blob,
size,
author,
created_on,
deleted_on
)
SELECT
organization,
({}),
$3,
$4,
$6,
$7,
author,
created_on,
deleted_on
FROM vlob_atom
WHERE
organization = ({})
AND vlob_id = $3
AND version = $4
ON CONFLICT DO NOTHING
""".format(
q_vlob_encryption_revision_internal_id(
organization_id=Parameter("$1"),
realm_id=Parameter("$2"),
encryption_revision=Parameter("$5"),
),
q_organization_internal_id(Parameter("$1")),
)
await conn.execute(
query,
organization_id,
realm_id,
vlob_id,
version,
encryption_revision,
blob,
len(blob),
)
query = """
SELECT (
SELECT COUNT(*)
FROM vlob_atom
WHERE vlob_encryption_revision = ({})
),
(
SELECT COUNT(*)
FROM vlob_atom
WHERE vlob_encryption_revision = ({})
)
""".format(
q_vlob_encryption_revision_internal_id(
organization_id=Parameter("$1"),
realm_id=Parameter("$2"),
encryption_revision=Parameter("$3") - 1,
),
q_vlob_encryption_revision_internal_id(
organization_id=Parameter("$1"),
realm_id=Parameter("$2"),
encryption_revision=Parameter("$3"),
),
)
rep = await conn.fetchrow(query, organization_id, realm_id, encryption_revision)
return rep[0], rep[1]
async def update(
self,
organization_id: OrganizationID,
author: DeviceID,
encryption_revision: int,
vlob_id: UUID,
version: int,
timestamp: pendulum.Pendulum,
blob: bytes,
) -> None:
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_write_access(
conn, organization_id, author, realm_id, encryption_revision
)
query = """
SELECT
version,
created_on
FROM vlob_atom
WHERE
organization = ({})
AND vlob_id = $2
ORDER BY version DESC LIMIT 1
""".format(
q_organization_internal_id(Parameter("$1"))
)
previous = await conn.fetchrow(query, organization_id, vlob_id)
if not previous:
raise VlobNotFoundError(f"Vlob `{vlob_id}` doesn't exist")
elif previous["version"] != version - 1:
raise VlobVersionError()
elif previous["created_on"] > timestamp:
raise VlobTimestampError()
query = """
INSERT INTO vlob_atom (
organization,
vlob_encryption_revision,
vlob_id,
version,
blob,
size,
author,
created_on
)
SELECT
({}),
({}),
$5,
$9,
$6,
$7,
({}),
$8
RETURNING _id
""".format(
q_organization_internal_id(Parameter("$1")),
q_vlob_encryption_revision_internal_id(
organization_id=Parameter("$1"),
realm_id=Parameter("$3"),
encryption_revision=Parameter("$4"),
),
q_device_internal_id(organization_id=Parameter("$1"), device_id=Parameter("$2")),
)
try:
vlob_atom_internal_id = await conn.fetchval(
query,
organization_id,
author,
realm_id,
encryption_revision,
vlob_id,
blob,
len(blob),
timestamp,
version,
)
except UniqueViolationError:
# Should not occurs in theory given we are in a transaction
raise VlobVersionError()
await _vlob_updated(
conn, vlob_atom_internal_id, organization_id, author, realm_id, vlob_id, version
)
async def create(
self,
organization_id: OrganizationID,
author: DeviceID,
realm_id: UUID,
encryption_revision: int,
vlob_id: UUID,
timestamp: pendulum.Pendulum,
blob: bytes,
) -> None:
async with self.dbh.pool.acquire() as conn, conn.transaction():
await _check_realm_and_write_access(
conn, organization_id, author, realm_id, encryption_revision
)
# Actually create the vlob
try:
query = """
INSERT INTO vlob_atom (
organization,
vlob_encryption_revision,
vlob_id,
version,
blob,
size,
author,
created_on
)
SELECT
({}),
({}),
$5,
1,
$6,
$7,
({}),
$8
RETURNING _id
""".format(
q_organization_internal_id(organization_id=Parameter("$1")),
Query.from_(t_vlob_encryption_revision)
.where(
(
t_vlob_encryption_revision.realm
== q_realm_internal_id(
organization_id=Parameter("$1"), realm_id=Parameter("$3")
)
)
& (t_vlob_encryption_revision.encryption_revision == Parameter("$4"))
)
.select("_id"),
q_device_internal_id(
organization_id=Parameter("$1"), device_id=Parameter("$2")
),
)
vlob_atom_internal_id = await conn.fetchval(
query,
organization_id,
author,
realm_id,
encryption_revision,
vlob_id,
blob,
len(blob),
timestamp,
)
except UniqueViolationError:
raise VlobAlreadyExistsError()
await _vlob_updated(
conn, vlob_atom_internal_id, organization_id, author, realm_id, vlob_id
)
expiration_date = EXCLUDED.expiration_date
WHERE organization.root_verify_key is NULL
""")
_q_get_organization = (q_organization(Parameter("$1")).select(
"bootstrap_token", "root_verify_key", "expiration_date").get_sql())
_q_bootstrap_organization = (Query.update(t_organization).where(
(t_organization.organization_id == Parameter("$1"))
& (t_organization.bootstrap_token == Parameter("$2"))
& (t_organization.root_verify_key.isnull())).set(
t_organization.root_verify_key, Parameter("$3")).get_sql())
_q_get_stats = Query.select(
Query.from_(t_user).where(
t_user.organization == q_organization_internal_id(Parameter(
"$1"))).select(fn.Count("*")).as_("users"),
Query.from_(t_vlob_atom).where(
t_vlob_atom.organization == q_organization_internal_id(Parameter(
"$1"))).select(fn.Coalesce(fn.Sum(t_vlob_atom.size),
0)).as_("metadata_size"),
Query.from_(t_block).where(
t_block.organization == q_organization_internal_id(Parameter(
"$1"))).select(fn.Coalesce(fn.Sum(t_block.size),
0)).as_("data_size"),
).get_sql()
_q_update_organisation_expiration_date = (Query.update(t_organization).where(
(t_organization.organization_id == Parameter("$1"))).set(
t_organization.expiration_date, Parameter("$2")).get_sql())
.select(t_user.user_id)
.where(
(
t_user.human
== q_human_internal_id(organization_id=Parameter("$1"), email=Parameter("$2"))
)
& (t_user.revoked_on.isnull() | t_user.revoked_on > Parameter("$3"))
)
.get_sql()
)
_q_insert_human_if_not_exists = (
Query.into(t_human)
.columns("organization", "email", "label")
.insert(q_organization_internal_id(Parameter("$1")), Parameter("$2"), Parameter("$3"))
.on_conflict()
.do_nothing()
.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"),
