def q_organization(organization_id=None, _id=None):
assert organization_id is not None or _id is not None
q = Query.from_(t_organization)
if _id is not None:
return q.where(t_organization._id == _id)
else:
return q.where(t_organization.organization_id == organization_id)
def q_vlob_atom_internal_id(vlob_id, organization_id=None, organization=None, table=t_vlob_atom):
q = Query.from_(t_vlob_atom).select("_id")
assert organization_id is not None or organization is not None
_q_organization = (
organization if organization is not None else q_organization_internal_id(organization_id)
)
return q.where((t_vlob_atom.organization == _q_organization) & (t_vlob_atom.vlob_id == vlob_id))
def q_insert_block(
block_id,
size,
created_on,
organization=None,
organization_id=None,
realm=None,
realm_id=None,
author=None,
author_id=None,
):
assert organization is not None or organization_id is not None
assert realm is not None or realm_id is not None
assert author is not None or author_id is not None
_q_organization = (
organization if organization is not None else q_organization_internal_id(organization_id)
)
_q_realm = (
realm
if realm is not None
else q_realm_internal_id(organization=_q_organization, realm_id=realm_id)
)
_q_author = (
author
if author is not None
else q_device_internal_id(organization=_q_organization, device_id=author_id)
)
return (
Query.into(t_block)
.columns("organization", "block_id", "realm", "author", "size", "created_on")
.insert(_q_organization, block_id, _q_realm, _q_author, size, created_on)
)
def q_insert_user_invitation(
user_id, created_on, organization=None, organization_id=None, creator=None, creator_id=None
):
assert organization is not None or organization_id is not None
assert creator is not None or creator_id is not None
_q_organization = (
organization if organization is not None else q_organization_internal_id(organization_id)
)
_q_creator = (
creator
if creator is not None
else q_device_internal_id(organization=_q_organization, device_id=creator_id)
)
return (
Query.into(t_user_invitation)
.columns("organization", "creator", "user_id", "created_on")
.insert(_q_organization, _q_creator, user_id, created_on)
# .on_conflict(Raw("organization, user_id"))
.on_conflict(t_user_invitation.organization)
.do_update("organization", "excluded.organization")
# .do_update("creator", "excluded.creator")
# .do_update("created_on", "excluded.created_on")
)
def q_user_can_read_vlob(user=None, user_id=None, realm=None, realm_id=None, organization_id=None):
if user is None:
assert organization_id is not None and user_id is not None
_q_user = (
user
if user is not None
else q_user_internal_id(organization_id=organization_id, user_id=user_id)
)
else:
_q_user = user
if realm is None:
assert organization_id is not None and realm_id is not None
_q_realm = (
realm
if realm is not None
else q_realm_internal_id(organization_id=organization_id, realm_id=realm_id)
)
else:
_q_realm = realm
return fn_exists(
Query.from_(t_realm_user_role)
.where((t_realm_user_role.realm == _q_realm) & (t_realm_user_role.user_ == _q_user))
.limit(1)
)
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_user_can_write_vlob(user, realm):
return fn_exists(
Query.from_(t_realm_user_role)
.where(
(t_realm_user_role.realm == realm)
& (t_realm_user_role.user_ == user)
& (t_realm_user_role.role != "READER")
)
.limit(1)
)
def q_block(organization_id=None, organization=None, block_id=None, _id=None):
q = Query.from_(t_block)
if _id is not None:
return q.where(t_block._id == _id)
else:
assert block_id is not None
assert organization_id is not None or organization is not None
_q_organization = (organization if organization is not None else
q_organization_internal_id(organization_id))
return q.where((t_block.organization == _q_organization)
& (t_block.block_id == block_id))
def q_realm_in_maintenance(realm=None, realm_id=None, organization_id=None, table=t_realm):
q = Query.from_(table).select(table.maintenance_type.notnull())
if realm:
q = q.where(table._id == realm)
else:
assert organization_id is not None and realm_id is not None
q = q.where(
(table.organization == q_organization_internal_id(organization_id))
& (table.realm_id == realm_id)
)
return q
def q_user(organization_id=None, organization=None, user_id=None, _id=None, table=t_user):
q = Query.from_(table)
if _id is not None:
return q.where(table._id == _id)
else:
assert user_id is not None
assert organization_id is not None or organization is not None
_q_organization = (
organization
if organization is not None
else q_organization_internal_id(organization_id)
)
return q.where((table.organization == _q_organization) & (table.user_id == user_id))
def q_device(organization_id=None, organization=None, device_id=None, _id=None, table=t_device):
q = Query.from_(table)
if _id is not None:
return q.where(table._id == _id)
else:
assert device_id is not None
assert organization_id is not None or organization is not None
_q_organization = (
organization
if organization is not None
else q_organization_internal_id(organization_id)
)
return q.where((table.organization == _q_organization) & (table.device_id == device_id))
def q_realm(organization_id=None, organization=None, realm_id=None, _id=None, table=t_realm):
q = Query.from_(table)
if _id is not None:
return q.where(table._id == _id)
else:
assert realm_id is not None
assert organization_id is not None or organization is not None
_q_organization = (
organization
if organization is not None
else q_organization_internal_id(organization_id)
)
return q.where((table.organization == _q_organization) & (table.realm_id == realm_id))
def q_human(organization_id=None, organization=None, email=None, _id=None, table=t_human):
q = Query.from_(table)
if _id is not None:
return q.where(table._id == _id)
else:
assert email is not None
assert organization_id is not None or organization is not None
_q_organization = (
organization
if organization is not None
else q_organization_internal_id(organization_id)
)
return q.where((table.organization == _q_organization) & (table.email == email))
def q_vlob_atom(organization_id=None,
organization=None,
vlob_id=None,
_id=None):
q = Query.from_(t_vlob_atom)
if _id is not None:
return q.where(t_vlob_atom._id == _id)
else:
assert vlob_id is not None
assert organization_id is not None or organization is not None
_q_organization = (organization if organization is not None else
q_organization_internal_id(organization_id))
return q.where((t_vlob_atom.organization == _q_organization)
& (t_vlob_atom.vlob_id == vlob_id))
def q_vlob_encryption_revision_internal_id(
encryption_revision,
organization_id=None,
organization=None,
realm_id=None,
realm=None,
table=t_vlob_encryption_revision,
):
q = Query.from_(table).select("_id")
if realm is None:
assert realm_id is not None
assert organization_id is not None or organization is not None
if organization is None:
_q_realm = q_realm_internal_id(organization_id=organization_id, realm_id=realm_id)
else:
_q_realm = q_realm_internal_id(organization=organization, realm_id=realm_id)
else:
_q_realm = realm
return q.where((table.realm == _q_realm) & (table.encryption_revision == encryption_revision))
def q_realm_user_role(
organization_id=None,
organization=None,
realm_id=None,
realm=None,
user_id=None,
user=None,
_id=None,
table=t_realm_user_role,
):
q = Query.from_(table)
if _id is not None:
return q.where(table._id == _id)
if organization is None:
assert organization_id is not None
_q_organization = q_organization_internal_id(organization_id)
else:
_q_organization = organization
if realm is not None:
_q_realm = realm
else:
assert realm_id is not None
assert _q_organization is not None
_q_realm = q_realm_internal_id(organization=_q_organization,
realm_id=realm_id)
if user is not None:
_q_user = user
else:
assert user_id is not None
assert _q_organization is not None
_q_user = q_user_internal_id(organization=_q_organization,
user_id=user_id)
return q.where((table.realm == _q_realm) & (table.user_ == _q_user))
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())
async def send_message(conn, organization_id, sender, recipient, timestamp,
q_block(organization_id=Parameter("$1"), block_id=Parameter("$2"))
.select(
"deleted_on",
q_user_can_read_vlob(
user=q_user_internal_id(organization_id=Parameter("$1"), user_id=Parameter("$3")),
realm=t_block.realm,
),
)
.get_sql()
)
_q_get_block_write_right_and_unicity = Query.select(
q_user_can_write_vlob(
user=q_user_internal_id(organization_id=Parameter("$1"), user_id=Parameter("$2")),
realm=q_realm_internal_id(organization_id=Parameter("$1"), realm_id=Parameter("$3")),
),
fn_exists(q_block(organization_id=Parameter("$1"), block_id=Parameter("$4"))),
).get_sql()
_q_insert_block = (
Query.into(t_block)
.columns("organization", "block_id", "realm", "author", "size", "created_on")
.insert(
q_organization_internal_id(Parameter("$1")),
Parameter("$2"),
q_realm_internal_id(organization_id=Parameter("$1"), realm_id=Parameter("$3")),
q_device_internal_id(organization_id=Parameter("$1"), device_id=Parameter("$4")),
Parameter("$5"),
Parameter("$6"),
OrganizationFirstUserCreationError,
)
from parsec.backend.postgresql.handler import PGHandler
from parsec.backend.postgresql.utils import Query
from parsec.backend.postgresql.tables import (
t_organization,
q_organization,
q_organization_internal_id,
t_user,
t_vlob_atom,
t_block,
)
from parsec.backend.postgresql.user_queries.create import _create_user
_q_insert_organization = ((Query.into(t_organization).columns(
"organization_id", "bootstrap_token", "expiration_date").insert(
Parameter("$1"), Parameter("$2"), Parameter("$3")).get_sql()) + """
ON CONFLICT (organization_id) DO
UPDATE SET
bootstrap_token = EXCLUDED.bootstrap_token,
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(
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
)
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",
"redacted_device_certificate",
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,
t_human,
q_organization_internal_id,
q_human_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_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(
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(
from parsec.backend.postgresql.tables import (
t_device,
t_user,
t_human,
q_organization_internal_id,
q_human_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_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"))