def _q_factory(query, omit_revoked, limit, offset):
conditions = []
if query:
conditions.append("AND user_id ILIKE $query")
if omit_revoked:
conditions.append("AND (revoked_on IS NULL OR revoked_on > $now)")
return Q(f"""
SELECT user_id
FROM user_
WHERE
organization = { q_organization_internal_id("$organization_id") }
{ " ".join(conditions) }
ORDER BY user_.user_id
LIMIT {limit} OFFSET {offset}
""")
def _q_count_total_human(query, omit_revoked, omit_non_human, in_find=False):
conditions = []
if query:
query_clean = query.translate({ord(c): None for c in "'%;<>\"="})
conditions.append(
f"AND (human.label ILIKE '%{query_clean}%' OR human.email ILIKE '%{query_clean}%')"
)
if in_find:
conditions = [f"AND user_id ILIKE '%{query_clean}%'"]
if omit_revoked:
conditions.append(
"AND (user_.revoked_on IS NULL OR user_.revoked_on > $now)")
if omit_non_human:
conditions.append("AND user_.human IS NOT NULL")
return Q(f"""
SELECT COUNT(*)
FROM user_ LEFT JOIN human ON user_.human=human._id
WHERE
user_.organization = { q_organization_internal_id("$organization_id") }
{ " ".join(conditions) }
""")
def _q_human_factory(query, omit_revoked, omit_non_human, limit, offset):
conditions = []
if query:
conditions.append(
"AND (human.label ILIKE $query OR human.email ILIKE $query)")
if omit_revoked:
conditions.append(
"AND (user_.revoked_on IS NULL OR user_.revoked_on > $now)")
if omit_non_human:
conditions.append("AND user_.human IS NOT NULL")
return Q(f"""
SELECT
user_.user_id,
human.email,
human.label,
user_.revoked_on IS NOT NULL AND user_.revoked_on <= $now
FROM user_ LEFT JOIN human ON user_.human=human._id
WHERE
user_.organization = { q_organization_internal_id("$organization_id") }
{ " ".join(conditions) }
ORDER BY human.label, user_.user_id
LIMIT {limit} OFFSET {offset}
""")
q_device_internal_id,
q_realm,
q_realm_internal_id,
q_block,
)
from backendService.postgresql.realm_queries.maintenance import (
get_realm_status,
RealmNotFoundError,
)
_q_get_realm_id_from_block_id = Q(
f"""
SELECT
{ q_realm(_id="block.realm", select="realm.realm_id") }
FROM block
WHERE
organization = { q_organization_internal_id("$organization_id") }
AND block_id = $block_id
"""
)
_q_get_block_meta = Q(
f"""
SELECT
deleted_on,
{
q_user_can_read_vlob(
user=q_user_internal_id(
organization_id="$organization_id",
user_id="$user_id"
elif expected_maintenance is True:
if not rep["maintenance_type"]:
raise VlobNotInMaintenanceError(
f"Realm `{realm_id}` not under maintenance")
if encryption_revision is not None and rep[
"encryption_revision"] != encryption_revision:
raise VlobEncryptionRevisionError()
_q_check_realm_access = Q(f"""
WITH cte_current_realm_roles AS (
SELECT DISTINCT ON(user_) user_, role
FROM realm_user_role
WHERE realm = { q_realm_internal_id(organization_id="$organization_id", realm_id="$realm_id") }
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 = { q_user_internal_id(organization_id="$organization_id", user_id="$user_id") }
""")
async def _check_realm_access(conn, organization_id, realm_id, author,
allowed_roles):
rep = await conn.fetchrow(
*_q_check_realm_access(organization_id=organization_id,
realm_id=realm_id,
user_id=author.user_id))
OrganizationInvalidBootstrapTokenError,
OrganizationAlreadyBootstrappedError,
OrganizationNotFoundError,
OrganizationFirstUserCreationError,
)
from backendService.postgresql.handler import PGHandler
from backendService.postgresql.user_queries.create import _create_user
from backendService.postgresql.utils import Q, q_organization_internal_id
_q_insert_organization = Q(
"""
INSERT INTO organization (organization_id, bootstrap_token, expiration_date)
VALUES ($organization_id, $bootstrap_token, $expiration_date)
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(
"""
SELECT bootstrap_token, root_verify_key, expiration_date
FROM organization
WHERE organization_id = $organization_id
"""
)
_q_maintenance_get_reencryption_batch = Q(f"""
WITH cte_to_encrypt AS (
SELECT vlob_id, version, blob
FROM vlob_atom
WHERE vlob_encryption_revision = {
q_vlob_encryption_revision_internal_id(
organization_id="$organization_id",
realm_id="$realm_id",
encryption_revision="$encryption_revision - 1"
)
}
),
cte_encrypted AS (
SELECT vlob_id, version
FROM vlob_atom
WHERE vlob_encryption_revision = {
q_vlob_encryption_revision_internal_id(
organization_id="$organization_id",
realm_id="$realm_id",
encryption_revision="$encryption_revision"
)
}
)
SELECT
cte_to_encrypt.vlob_id,
cte_to_encrypt.version,
blob
FROM cte_to_encrypt
LEFT JOIN cte_encrypted
ON cte_to_encrypt.vlob_id = cte_encrypted.vlob_id AND cte_to_encrypt.version = cte_encrypted.version
WHERE cte_encrypted.vlob_id IS NULL
LIMIT $size
""")
)
from backendService.postgresql.handler import send_signal
from backendService.postgresql.message import send_message
from backendService.postgresql.utils import (
Q,
query,
q_user,
q_user_internal_id,
q_device_internal_id,
q_realm,
q_realm_internal_id,
STR_TO_REALM_ROLE,
)
_q_get_user_profile = Q(
q_user(organization_id="$organization_id",
user_id="$user_id",
select="profile"))
_q_get_realm_status = Q(
q_realm(
organization_id="$organization_id",
realm_id="$realm_id",
select=
"encryption_revision, maintenance_started_by, maintenance_started_on, maintenance_type",
))
_q_get_roles = Q(f"""
SELECT
{ q_user_internal_id(organization_id="$organization_id", user_id="needle_user_id") },
(
SELECT role
from pendulum import now as pendulum_now
from guardata.api.protocol import OrganizationID
from backendService.user import User, Device, UserError, UserNotFoundError, UserAlreadyExistsError
from backendService.postgresql.utils import (
Q,
query,
q_organization_internal_id,
q_device_internal_id,
q_user_internal_id,
q_human_internal_id,
)
_q_get_user_devices = Q(f"""
SELECT device_id
FROM device
WHERE user_ = { q_user_internal_id(organization_id="$organization_id", user_id="$user_id") }
""")
_q_get_not_revoked_users_for_human = Q(f"""
SELECT user_id
FROM user_
WHERE
human = { q_human_internal_id(organization_id="$organization_id", email="$email") }
AND (
revoked_on IS NULL
OR revoked_on > $now
)
""")
_q_insert_human_if_not_exists = Q(f"""
UserAlreadyExistsError,
DeviceInvitation,
)
from backendService.postgresql.handler import send_signal
from backendService.postgresql.utils import (
Q,
query,
q_organization_internal_id,
q_device,
q_device_internal_id,
)
_q_device_exists = Q(f"""
SELECT true
FROM device
WHERE
organization = { q_organization_internal_id("$organization_id") }
AND device_id = $device_id
""")
async def _device_exists(conn, organization_id: OrganizationID,
device_id: DeviceID):
device_result = await conn.fetchrow(
*_q_device_exists(organization_id=organization_id, device_id=device_id)
)
return bool(device_result)
_q_insert_invitation = Q(f"""
INSERT INTO device_invitation (
# Parsec Cloud (https://parsec.cloud) Copyright (c) AGPLv3 2019 Scille SAS
from pendulum import now as pendulum_now
from functools import lru_cache
from typing import Tuple, List, Optional
from guardata.api.protocol import UserID, OrganizationID, HumanHandle
from backendService.user import HumanFindResultItem
from backendService.postgresql.utils import Q, q_organization_internal_id, query
_q_retrieve_active_human_by_email = Q(f"""
SELECT
user_.user_id
FROM user_ LEFT JOIN human ON user_.human=human._id
WHERE
user_.organization = { q_organization_internal_id("$organization_id") }
AND human.email = $email
AND (user_.revoked_on IS NULL OR user_.revoked_on > $now)
LIMIT 1
""")
@lru_cache()
def _q_factory(query, omit_revoked, limit, offset):
conditions = []
if query:
conditions.append("AND user_id ILIKE $query")
if omit_revoked:
conditions.append("AND (revoked_on IS NULL OR revoked_on > $now)")
return Q(f"""
SELECT user_id
FROM user_
q_organization_internal_id,
q_device,
q_user_internal_id,
q_human,
STR_TO_USER_PROFILE,
)
_q_get_user = Q(f"""
SELECT
{ q_human(_id="user_.human", select="email") } as human_email,
{ q_human(_id="user_.human", select="label") } as human_label,
profile,
user_certificate,
redacted_user_certificate,
{ q_device(select="device_id", _id="user_.user_certifier") } as user_certifier,
created_on,
revoked_on,
revoked_user_certificate,
{ q_device(select="device_id", _id="user_.revoked_user_certifier") } as revoked_user_certifier
FROM user_
WHERE
organization = { q_organization_internal_id("$organization_id") }
AND user_id = $user_id
""")
_q_get_device = Q(f"""
SELECT
device_label,
device_certificate,
redacted_device_certificate,
{ q_device(table_alias="d", select="d.device_id", _id="device.device_certifier") } as device_certifier,
q_user_internal_id,
q_user_can_read_vlob,
q_device,
q_realm,
q_realm_internal_id,
STR_TO_REALM_ROLE,
STR_TO_REALM_MAINTENANCE_TYPE,
)
from backendService.realm import RealmStats
_q_get_realm_status = Q(f"""
SELECT
{ q_user_can_read_vlob(organization_id="$organization_id", realm_id="$realm_id", user_id="$user_id") } has_access,
encryption_revision,
{ q_device(_id="maintenance_started_by", select="device_id") } maintenance_started_by,
maintenance_started_on,
maintenance_type
FROM realm
WHERE
organization = { q_organization_internal_id("$organization_id") }
AND realm_id = $realm_id
""")
_q_has_realm_access = Q(f"""
SELECT
{ q_user_can_read_vlob(organization_id="$organization_id", realm_id="$realm_id", user_id="$user_id") } has_access
FROM realm
WHERE
organization = { q_organization_internal_id("$organization_id") }
AND realm_id = $realm_id
""")
from backendService.backend_events import BackendEvent
from backendService.user import UserError, UserNotFoundError, UserAlreadyRevokedError
from backendService.postgresql.handler import send_signal
from backendService.postgresql.utils import (
Q,
query,
q_organization_internal_id,
q_device_internal_id,
q_user,
)
_q_revoke_user = Q(f"""
UPDATE user_ SET
revoked_user_certificate = $revoked_user_certificate,
revoked_user_certifier = { q_device_internal_id(organization_id="$organization_id", device_id="$revoked_user_certifier") },
revoked_on = $revoked_on
WHERE
organization = { q_organization_internal_id("$organization_id") }
AND user_id = $user_id
AND revoked_on IS NULL
""")
_q_revoke_user_error = Q(
q_user(organization_id="$organization_id",
user_id="$user_id",
select="revoked_on"))
@query(in_transaction=True)
async def query_revoke_user(
conn,
organization_id: OrganizationID,
from backendService.postgresql.vlob_queries.utils import (
_get_realm_id_from_vlob_id,
_check_realm,
_check_realm_access,
)
_q_read_data_without_timestamp = Q(f"""
SELECT
version,
blob,
{ q_device(_id="author", select="device_id") } as author,
created_on
FROM vlob_atom
WHERE
vlob_encryption_revision = {
q_vlob_encryption_revision_internal_id(
organization_id="$organization_id",
realm_id="$realm_id",
encryption_revision="$encryption_revision",
)
}
AND vlob_id = $vlob_id
ORDER BY version DESC
LIMIT 1
""")
_q_read_data_with_timestamp = Q(f"""
SELECT
version,
blob,
{ q_device(_id="author", select="device_id") } as author,
VlobAlreadyExistsError,
)
from backendService.postgresql.handler import send_signal
from backendService.postgresql.vlob_queries.utils import (
_get_realm_id_from_vlob_id,
_check_realm_and_write_access,
)
from backendService.backend_events import BackendEvent
q_vlob_updated = Q(f"""
INSERT INTO realm_vlob_update (
realm, index, vlob_atom
)
SELECT
{ q_realm_internal_id(organization_id="$organization_id", realm_id="$realm_id") },
(
SELECT COALESCE(MAX(index) + 1, 1)
FROM realm_vlob_update
WHERE realm = { q_realm_internal_id(organization_id="$organization_id", realm_id="$realm_id") }
),
$vlob_atom_internal_id
RETURNING index
""")
@query(in_transaction=True)
async def query_vlob_updated(conn,
vlob_atom_internal_id,
organization_id,
author,
realm_id,
src_id,
from backendService.postgresql.utils import (
Q,
query,
q_organization_internal_id,
q_user,
q_device_internal_id,
q_realm,
q_realm_internal_id,
STR_TO_REALM_ROLE,
)
_q_get_realm_status = Q(
q_realm(
organization_id="$organization_id",
realm_id="$realm_id",
select="encryption_revision, maintenance_started_by, maintenance_started_on, maintenance_type",
)
)
async def get_realm_status(conn, organization_id, realm_id):
rep = await conn.fetchrow(
*_q_get_realm_status(organization_id=organization_id, realm_id=realm_id)
)
if not rep:
raise RealmNotFoundError(f"Realm `{realm_id}` doesn't exist")
return rep
_q_get_realm_role_for_not_revoked_with_users = Q(
)
from backendService.postgresql.utils import (
Q,
q_organization_internal_id,
q_user,
q_user_internal_id,
STR_TO_INVITATION_CONDUIT_STATE,
)
from backendService.postgresql.user_queries.find import query_retrieve_active_human_by_email
_q_retrieve_compatible_user_invitation = Q(f"""
SELECT
token
FROM invitation
WHERE
organization = { q_organization_internal_id("$organization_id") }
AND type = $type
AND greeter = { q_user_internal_id(organization_id="$organization_id", user_id="$greeter_user_id") }
AND claimer_email = $claimer_email
AND deleted_on IS NULL
LIMIT 1
""")
_q_retrieve_compatible_device_invitation = Q(f"""
SELECT
token
FROM invitation
WHERE
organization = { q_organization_internal_id("$organization_id") }
AND type = $type
AND greeter = { q_user_internal_id(organization_id="$organization_id", user_id="$greeter_user_id") }
AND claimer_email IS NULL
from backendService.realm import RealmGrantedRole, RealmAlreadyExistsError
from backendService.postgresql.handler import send_signal
from backendService.postgresql.utils import (
Q,
query,
q_organization_internal_id,
q_user_internal_id,
q_device_internal_id,
)
_q_insert_realm = Q(f"""
INSERT INTO realm (
organization,
realm_id,
encryption_revision
) SELECT
{ q_organization_internal_id("$organization_id") },
$realm_id,
1
ON CONFLICT (organization, realm_id) DO NOTHING
RETURNING _id
""")
_q_insert_realm_role = Q(f"""
INSERT INTO realm_user_role(
realm,
user_,
role,
certificate,
certified_by,
certified_on
)
Q,
q_organization_internal_id,
q_user_internal_id,
q_device,
q_device_internal_id,
)
_q_insert_message = Q(f"""
INSERT INTO message (organization, recipient, timestamp, index, sender, body)
VALUES (
{ q_organization_internal_id("$organization_id") },
{ q_user_internal_id(organization_id="$organization_id", user_id="$recipient") },
$timestamp,
(
SELECT COUNT(*) + 1
FROM message
WHERE
recipient = { q_user_internal_id(organization_id="$organization_id", user_id="$recipient") }
),
{ q_device_internal_id(organization_id="$organization_id", device_id="$sender") },
$body
)
RETURNING index
""")
_q_get_messages = Q(f"""
SELECT
{ q_device(_id="message.sender", select="device_id") },
timestamp,
body
FROM message