from parsec.backend.realm import RealmGrantedRole, RealmAlreadyExistsError
from parsec.backend.postgresql.handler import send_signal
from parsec.backend.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
)
from parsec.backend.postgresql.message import send_message
from parsec.backend.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(f"""
WITH cte_current_realm_roles AS (
# 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 parsec.api.protocol import UserID, OrganizationID, HumanHandle
from parsec.backend.user import HumanFindResultItem
from parsec.backend.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(with_query: bool, omit_revoked: bool) -> Q:
conditions = []
if with_query:
conditions.append("AND user_id ~* $query")
if omit_revoked:
conditions.append("AND (revoked_on IS NULL OR revoked_on > $now)")
return Q(
from parsec.backend.backend_events import BackendEvent
from parsec.backend.user import UserError, UserNotFoundError, UserAlreadyRevokedError
from parsec.backend.postgresql.handler import send_signal
from parsec.backend.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,
VlobAlreadyExistsError,
)
from parsec.backend.postgresql.handler import send_signal
from parsec.backend.postgresql.vlob_queries.utils import (
_get_realm_id_from_vlob_id,
_check_realm_and_write_access,
)
from parsec.backend.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,
_q_insert_organization = Q("""
INSERT INTO organization (
organization_id,
bootstrap_token,
active_users_limit,
user_profile_outsider_allowed,
_created_on,
_bootstrapped_on,
is_expired,
_expired_on
)
VALUES (
$organization_id,
$bootstrap_token,
$active_users_limit,
$user_profile_outsider_allowed,
NOW(),
NULL,
FALSE,
NULL
)
ON CONFLICT (organization_id) DO
UPDATE SET
bootstrap_token = EXCLUDED.bootstrap_token,
active_users_limit = EXCLUDED.active_users_limit,
user_profile_outsider_allowed = EXCLUDED.user_profile_outsider_allowed,
_created_on = EXCLUDED._created_on,
is_expired = EXCLUDED.is_expired,
_expired_on = EXCLUDED._expired_on
WHERE organization.root_verify_key is NULL
""")
RealmRoleRequireGreaterTimestampError,
)
from parsec.backend.postgresql.handler import send_signal
from parsec.backend.postgresql.message import send_message
from parsec.backend.postgresql.utils import (
Q,
query,
q_user,
q_user_internal_id,
q_device_internal_id,
q_realm,
q_realm_internal_id,
)
_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 ROW(role::text, certified_on)
q_user_internal_id,
q_user_can_read_vlob,
q_user_can_write_vlob,
q_device_internal_id,
q_realm,
q_realm_internal_id,
q_block,
)
from parsec.backend.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"
_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 parsec.backend.postgresql.vlob_queries.utils import (
_get_realm_id_from_vlob_id,
_check_realm_and_read_access,
_get_last_role_granted_on,
)
_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,
)
from parsec.backend.postgresql import PGHandler
from parsec.backend.postgresql.utils import Q, q_organization_internal_id, q_device_internal_id
from parsec.backend.postgresql.user_queries.create import (
q_create_user,
q_take_user_device_write_lock,
)
_q_get_last_pki_enrollment_from_certificate_sha1_for_update = Q(f"""
SELECT
enrollment_id,
enrollment_state,
submitted_on,
submitter_accepted_device,
accepter
FROM
pki_enrollment
WHERE (
organization = { q_organization_internal_id("$organization_id") }
AND submitter_der_x509_certificate_sha1=$submitter_der_x509_certificate_sha1
)
ORDER BY _id DESC LIMIT 1
FOR UPDATE
""")
_q_get_pki_enrollment_from_enrollment_id = Q(f"""
SELECT
enrollment_id,
enrollment_state,
submitted_on,
info_cancelled,
info_accepted,
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
)
from parsec.backend.postgresql.utils import (
Q,
q_organization_internal_id,
q_user,
q_user_internal_id,
STR_TO_INVITATION_CONDUIT_STATE,
)
from parsec.backend.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
query,
q_organization_internal_id,
q_device,
q_user_internal_id,
q_human,
)
_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,
OrganizationAlreadyExistsError,
OrganizationInvalidBootstrapTokenError,
OrganizationAlreadyBootstrappedError,
OrganizationNotFoundError,
OrganizationFirstUserCreationError,
)
from parsec.backend.postgresql.handler import PGHandler
from parsec.backend.postgresql.user_queries.create import _create_user
from parsec.backend.postgresql.utils import Q, q_organization_internal_id
from parsec.backend.postgresql.handler import send_signal
_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_bootstrap_organization = Q("""
UPDATE organization
SET root_verify_key = $root_verify_key
WHERE
from parsec.api.protocol import OrganizationID, UserID, DeviceID
from parsec.backend.backend_events import BackendEvent
from parsec.backend.user import UserError, UserNotFoundError, UserAlreadyExistsError, UserInvitation
from parsec.backend.postgresql.handler import send_signal
from parsec.backend.postgresql.utils import (
Q,
query,
q_organization_internal_id,
q_device,
q_device_internal_id,
q_user,
)
_q_user_exists = Q(
q_user(organization_id="$organization_id",
user_id="$user_id",
select="TRUE"))
async def _user_exists(conn, organization_id: OrganizationID, user_id: UserID):
user_result = await conn.fetchrow(
*_q_user_exists(organization_id=organization_id, user_id=user_id))
return bool(user_result)
_q_insert_invitation = Q(f"""
INSERT INTO user_invitation (
organization,
creator,
user_id,
created_on
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))
q_organization_internal_id,
q_user,
q_user_internal_id,
q_user_can_read_vlob,
q_device,
q_realm,
q_realm_internal_id,
)
from parsec.backend.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 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 (
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"""
)
from parsec.backend.postgresql.handler import send_signal
from parsec.backend.postgresql.message import send_message
from parsec.backend.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
VlobAlreadyExistsError,
)
from parsec.backend.postgresql.handler import send_signal
from parsec.backend.postgresql.vlob_queries.utils import (
_get_realm_id_from_vlob_id,
_check_realm_and_write_access,
)
from parsec.backend.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
""")
_q_set_last_vlob_update = Q(f"""
INSERT INTO realm_user_change(realm, user_, last_role_change, last_vlob_update)
VALUES (
{ q_realm_internal_id(organization_id="$organization_id", realm_id="$realm_id") },
{ q_user_internal_id(organization_id="$organization_id", user_id="$user_id") },
NULL,
$timestamp
)
