async def reset_db_schema():
# Now drop the database clean...
async with triopg.connect(postgresql_url) as conn:
rep = await conn.execute("DROP SCHEMA public CASCADE")
assert rep == "DROP SCHEMA"
rep = await conn.execute("CREATE SCHEMA public")
assert rep == "CREATE SCHEMA"
async def apply_migrations(url: str, migrations: Iterable[MigrationItem],
dry_run: bool) -> MigrationResult:
"""
Returns: MigrationResult
"""
async with triopg.connect(url) as conn:
return await _apply_migrations(conn, migrations, dry_run)
async def test_triopg_connection(asyncio_loop, asyncpg_conn,
postgresql_connection_specs):
async with triopg.connect(**postgresql_connection_specs) as conn:
await execute_queries(conn, asyncpg_conn)
with pytest.raises(triopg.InterfaceError):
await conn.execute("SELECT * FROM users")
async def init_db(url: str, force: bool = False) -> bool:
"""
Raises:
triopg.exceptions.PostgresError
"""
async with triopg.connect(url) as conn:
return await _init_db(conn, force)
async def init_db(url: str) -> None:
"""
Returns: if the database was already initialized
Raises:
triopg.exceptions.PostgresError
"""
async with triopg.connect(url) as conn:
return await _init_db(conn)
async def _retryable(url,
migrations,
dry_run,
postgres_initial_connect_failed=False):
async with triopg.connect(url) as conn:
if postgres_initial_connect_failed:
logger.warning(
"db connection established after initial failure")
return await _apply_migrations(conn, migrations, dry_run)
async def triopg_conn(request, asyncio_loop, postgresql_connection_specs):
if request.param == "from_connect":
async with triopg.connect(**postgresql_connection_specs) as conn:
yield conn
else:
async with triopg.create_pool(**postgresql_connection_specs) as pool:
async with pool.acquire() as conn:
yield conn
async def _run_connections(self, task_status=trio.TASK_STATUS_IGNORED):
async with triopg.create_pool(
self.url, min_size=self.min_connections, max_size=self.max_connections
) as self.pool:
# This connection is dedicated to the notifications listening, so it
# would only complicate stuff to include it into the connection pool
async with triopg.connect(self.url) as self.notification_conn:
await self.notification_conn.add_listener("app_notification", self._on_notification)
task_status.started()
await trio.sleep_forever()
async def test_concurrency_create_user_with_limit_reached(
postgresql_url,
backend_factory,
backend_data_binder_factory,
coolorg,
alice,
local_device_factory,
):
results = []
async def _concurrent_create(backend, user):
backend_user, backend_first_device = local_device_to_backend_user(
user, alice)
try:
await backend.user.create_user(coolorg.organization_id,
backend_user, backend_first_device)
results.append(None)
except Exception as exc:
results.append(exc)
async with backend_factory(config={
"db_url": postgresql_url,
"db_max_connections": 10
},
populated=False) as backend:
# Create&bootstrap the organization
binder = backend_data_binder_factory(backend)
await binder.bind_organization(coolorg, alice)
# Set a limit that will be soon reached
await backend.organization.update(alice.organization_id,
active_users_limit=3)
# Concurrent user creation
with ensure_pg_transaction_concurrency_barrier(concurrency=10):
async with trio.open_nursery() as nursery:
for _ in range(10):
nursery.start_soon(_concurrent_create, backend,
local_device_factory(org=coolorg))
assert len(results) == 10
assert len([
r for r in results if isinstance(r, UserActiveUsersLimitReached)
]) == 8
assert len([r for r in results if r is None]) == 2
async with triopg.connect(postgresql_url) as conn:
res = await conn.fetchrow("SELECT count(*) FROM organization")
assert res["count"] == 1
res = await conn.fetchrow("SELECT count(*) FROM user_")
assert res["count"] == 3
res = await conn.fetchrow("SELECT count(*) FROM device")
assert res["count"] == 3
async def _run_connections(self, started_cb):
async with triopg.create_pool(self.url) as self.pool:
async with self.pool.acquire() as conn:
if not await _is_db_initialized(conn):
raise RuntimeError("Database not initialized !")
# This connection is dedicated to the notifications listening, so it
# would only complicate stuff to include it into the connection pool
async with triopg.connect(self.url) as self.notification_conn:
await self.notification_conn.add_listener(
"app_notification", self._on_notification)
await started_cb()
async def test_concurrency_bootstrap_organization(postgresql_url,
backend_factory, coolorg,
alice):
results = []
backend_user, backend_first_device = local_device_to_backend_user(
alice, coolorg)
async def _concurrent_boostrap(backend):
try:
await backend.organization.bootstrap(
coolorg.organization_id,
backend_user,
backend_first_device,
coolorg.bootstrap_token,
coolorg.root_verify_key,
)
results.append(None)
except Exception as exc:
results.append(exc)
async with backend_factory(config={
"db_url": postgresql_url,
"db_max_connections": 10
},
populated=False) as backend:
# Create the organization
await backend.organization.create(coolorg.organization_id,
coolorg.bootstrap_token)
# Concurrent bootstrap
with ensure_pg_transaction_concurrency_barrier(concurrency=10):
async with trio.open_nursery() as nursery:
for _ in range(10):
nursery.start_soon(_concurrent_boostrap, backend)
assert len(results) == 10
assert len([
r for r in results
if isinstance(r, OrganizationAlreadyBootstrappedError)
]) == 9
async with triopg.connect(postgresql_url) as conn:
res = await conn.fetchrow("SELECT count(*) FROM organization")
assert res["count"] == 1
res = await conn.fetchrow("SELECT count(*) FROM user_")
assert res["count"] == 1
res = await conn.fetchrow("SELECT count(*) FROM device")
assert res["count"] == 1
async def test_postgresql_notification_listener_terminated(
postgresql_url, backend_factory):
async with triopg.connect(postgresql_url) as conn:
with pytest.raises(ConnectionError):
async with backend_factory(config={"db_url": postgresql_url}):
pid, = await wait_for_listeners(conn)
value, = await conn.fetchrow("SELECT pg_terminate_backend($1)",
pid)
assert value
# Wait to get cancelled by the backend app
with trio.fail_after(3):
await trio.sleep_forever()
async def test_retry_policy_allow_retry(postgresql_url, unused_tcp_port,
asyncio_loop):
host = "localhost"
port = unused_tcp_port
app_config = BackendConfig(
administration_token="s3cr3t",
db_min_connections=1,
db_max_connections=5,
debug=False,
blockstore_config=PostgreSQLBlockStoreConfig(),
email_config=None,
backend_addr=None,
forward_proto_enforce_https=None,
ssl_context=False,
spontaneous_organization_bootstrap=False,
organization_bootstrap_webhook_url=None,
db_url=postgresql_url,
)
# Allow to retry once
retry_policy = RetryPolicy(maximum_attempts=1, pause_before_retry=0)
async with trio.open_nursery() as nursery:
# Run backend in the background
nursery.start_soon(lambda: _run_backend(host,
port,
ssl_context=False,
retry_policy=retry_policy,
app_config=app_config))
# Connect to PostgreSQL database
async with triopg.connect(postgresql_url) as conn:
# Test for 10 cycles
pid = None
for _ in range(10):
# Wait for the backend to be connected
new_pid, = await wait_for_listeners(conn)
# Make sure a new connection has been created
assert new_pid != pid
pid = new_pid
# Terminate the backend listener connection
value, = await conn.fetchrow("SELECT pg_terminate_backend($1)",
pid)
assert value
# Wait for the listener to terminate
await wait_for_listeners(conn, to_terminate=True)
# Cancel the backend nursery
nursery.cancel_scope.cancel()
async def test_concurrency_create_user(postgresql_url, backend_factory,
backend_data_binder_factory, coolorg,
alice, bob):
results = []
backend_user, backend_first_device = local_device_to_backend_user(
bob, alice)
async def _concurrent_create(backend):
try:
await backend.user.create_user(coolorg.organization_id,
backend_user, backend_first_device)
results.append(None)
except Exception as exc:
results.append(exc)
async with backend_factory(config={
"db_url": postgresql_url,
"db_max_connections": 10
},
populated=False) as backend:
# Create&bootstrap the organization
binder = backend_data_binder_factory(backend)
await binder.bind_organization(coolorg, alice)
# Concurrent user creation
with ensure_pg_transaction_concurrency_barrier(concurrency=10):
async with trio.open_nursery() as nursery:
for _ in range(10):
nursery.start_soon(_concurrent_create, backend)
assert len(results) == 10
assert len([r for r in results
if isinstance(r, UserAlreadyExistsError)]) == 9
async with triopg.connect(postgresql_url) as conn:
res = await conn.fetchrow("SELECT count(*) FROM organization")
assert res["count"] == 1
res = await conn.fetchrow(
"SELECT count(*) FROM user_ WHERE user_id = 'bob'")
assert res["count"] == 1
res = await conn.fetchrow(
"SELECT count(*) FROM device WHERE user_ = (SELECT _id FROM user_ WHERE user_id = 'bob')"
)
assert res["count"] == 1
async def _retryable(self,
task_status,
postgres_initial_connect_failed=False):
async with triopg.create_pool(
self.url,
min_size=self.min_connections,
max_size=self.max_connections) as self.pool:
# This connection is dedicated to the notifications listening, so it
# would only complicate stuff to include it into the connection pool
async with triopg.connect(self.url) as self.notification_conn:
await self.notification_conn.add_listener(
"app_notification", self._on_notification)
task_status.started()
if postgres_initial_connect_failed:
logger.warning(
"db connection established after initial failure")
await trio.sleep_forever()
async def test_connection_closed(asyncio_loop, postgresql_connection_specs):
termination_listener_called = False
def _termination_listener(connection):
nonlocal termination_listener_called
termination_listener_called = True
async with triopg.connect(**postgresql_connection_specs) as conn:
assert not conn.is_closed()
pid = conn.get_server_pid()
assert isinstance(pid, int)
conn.add_termination_listener(_termination_listener)
assert conn.is_closed()
assert termination_listener_called
with pytest.raises(triopg.InterfaceError):
await conn.execute("VALUES (1)")
async def test_retry_policy_no_retry(postgresql_url, unused_tcp_port,
asyncio_loop):
host = "localhost"
port = unused_tcp_port
app_config = BackendConfig(
administration_token="s3cr3t",
db_min_connections=1,
db_max_connections=5,
debug=False,
blockstore_config=PostgreSQLBlockStoreConfig(),
email_config=None,
backend_addr=None,
forward_proto_enforce_https=None,
ssl_context=False,
spontaneous_organization_bootstrap=False,
organization_bootstrap_webhook_url=None,
db_url=postgresql_url,
)
# No retry
retry_policy = RetryPolicy(maximum_attempts=0, pause_before_retry=0)
# Expect a connection error
with pytest.raises(ConnectionError):
async with trio.open_nursery() as nursery:
# Run backend in the background
nursery.start_soon(lambda: _run_backend(host,
port,
ssl_context=False,
retry_policy=retry_policy,
app_config=app_config))
# Connect to PostgreSQL database
async with triopg.connect(postgresql_url) as conn:
# Wait for the backend to be connected
pid, = await wait_for_listeners(conn)
# Terminate the backend listener connection
value, = await conn.fetchrow("SELECT pg_terminate_backend($1)",
pid)
assert value
# Wait to get cancelled by the connection error `_run_backend`
with trio.fail_after(3):
await trio.sleep_forever()
async def _run_connections(self, task_status=trio.TASK_STATUS_IGNORED):
async with triopg.create_pool(
self.url,
min_size=self.min_connections,
max_size=self.max_connections) as self.pool:
# This connection is dedicated to the notifications listening, so it
# would only complicate stuff to include it into the connection pool
async with triopg.connect(self.url) as self.notification_conn:
self.notification_conn.add_termination_listener(
self._on_notification_conn_termination)
await self.notification_conn.add_listener(
"app_notification", self._on_notification)
task_status.started()
try:
await trio.sleep_forever()
finally:
if self._connection_lost:
raise ConnectionError(
"PostgreSQL notification query has been lost")
async def _trio_test_migration(postgresql_url, pg_dump, psql):
async def reset_db_schema():
# Now drop the database clean...
async with triopg.connect(postgresql_url) as conn:
rep = await conn.execute("DROP SCHEMA public CASCADE")
assert rep == "DROP SCHEMA"
rep = await conn.execute("CREATE SCHEMA public")
assert rep == "CREATE SCHEMA"
async def dump_schema() -> str:
cmd = [pg_dump, "--schema=public", "--schema-only", postgresql_url]
print(f"run: {' '.join(cmd)}")
process = await trio.run_process(cmd, capture_stdout=True)
return process.stdout.decode()
async def dump_data() -> str:
cmd = [pg_dump, "--schema=public", "--data-only", postgresql_url]
print(f"run: {' '.join(cmd)}")
process = await trio.run_process(cmd, capture_stdout=True)
return process.stdout.decode()
async def restore_data(data: str) -> None:
cmd = [
psql, "--no-psqlrc", "--set", "ON_ERROR_STOP=on", postgresql_url
]
print(f"run: {' '.join(cmd)}")
process = await trio.run_process(cmd,
capture_stdout=True,
stdin=data.encode())
return process.stdout.decode()
# The schema may start with an automatic comment, something like:
# `COMMENT ON SCHEMA public IS 'standard public schema';`
# So we clean everything first
await reset_db_schema()
# Now we apply migrations one after another and also insert the provided data
migrations = retrieve_migrations()
patches = collect_data_patches()
async with triopg.connect(postgresql_url) as conn:
for migration in migrations:
result = await apply_migrations(postgresql_url, [migration],
dry_run=False)
assert not result.error
patch_sql = patches.get(migration.idx, "")
if patch_sql:
await conn.execute(patch_sql)
# Save the final state of the database schema
schema_from_migrations = await dump_schema()
# Also save the final data
data_from_migrations = await dump_data()
# Now drop the database clean...
await reset_db_schema()
# ...and reinitialize it with the current datamodel script
sql = importlib_resources.files(migrations_module).joinpath(
"datamodel.sql").read_text()
async with triopg.connect(postgresql_url) as conn:
await conn.execute(sql)
# The resulting database schema should be equivalent to what we add after
# all the migrations
schema_from_init = await dump_schema()
assert schema_from_init == schema_from_migrations
# Final check is to re-import all the data, this requires some cooking first:
# Remove the migration related data given their should already be in the database
data_from_migrations = re.sub(r"COPY public.migration[^\\]*\\.",
"",
data_from_migrations,
flags=re.DOTALL)
# Modify the foreign key constraint between `user_` and `device` to be
# checked at the end of the transaction. This is needed because `user_`
# table is entirely populated before switching to `device`. So the
# constraint should break as soon as an `user_` row references a device_id.
data_from_migrations = f"""
ALTER TABLE public."user_" ALTER CONSTRAINT fk_user_device_user_certifier DEFERRABLE;
ALTER TABLE public."user_" ALTER CONSTRAINT fk_user_device_revoked_user_certifier DEFERRABLE;
BEGIN;
SET CONSTRAINTS fk_user_device_user_certifier DEFERRED;
SET CONSTRAINTS fk_user_device_revoked_user_certifier DEFERRED;
{data_from_migrations}
COMMIT;
"""
await restore_data(data_from_migrations)
async def migrate_db(url: str, migrations: List[str], dry_run: bool) -> MigrationResult:
"""
Returns: MigrationResult
"""
async with triopg.connect(url) as conn:
return await _migrate_db(conn, migrations, dry_run)
async def test_concurrency_pki_enrollment_accept(postgresql_url,
backend_factory,
backend_data_binder_factory,
coolorg, alice, bob):
results = []
enrollment_id = uuid4()
backend_user, backend_first_device = local_device_to_backend_user(
bob, alice)
async def _concurrent_enrollment_accept(backend):
try:
await backend.pki.accept(
organization_id=coolorg.organization_id,
enrollment_id=enrollment_id,
accepter_der_x509_certificate=b"whatever",
accept_payload_signature=b"whatever",
accept_payload=b"whatever",
accepted_on=pendulum_now(),
user=backend_user,
first_device=backend_first_device,
)
results.append(None)
except AssertionError:
# Improve pytest --pdb behavior
raise
except Exception as exc:
results.append(exc)
async with backend_factory(config={
"db_url": postgresql_url,
"db_max_connections": 10
},
populated=False) as backend:
# Create&bootstrap the organization
binder = backend_data_binder_factory(backend)
await binder.bind_organization(coolorg, alice)
# Create the PKI enrollment
await backend.pki.submit(
organization_id=coolorg.organization_id,
enrollment_id=enrollment_id,
force=False,
submitter_der_x509_certificate=b"whatever",
submitter_der_x509_certificate_email="whatever",
submit_payload_signature=b"whatever",
submit_payload=b"whatever",
submitted_on=pendulum_now(),
)
# Concurrent PKI enrollement accept
with ensure_pg_transaction_concurrency_barrier(concurrency=10):
async with trio.open_nursery() as nursery:
for _ in range(10):
nursery.start_soon(_concurrent_enrollment_accept, backend)
assert len(results) == 10
assert len([
r for r in results
if isinstance(r, PkiEnrollmentNoLongerAvailableError)
]) == 9
async with triopg.connect(postgresql_url) as conn:
res = await conn.fetchrow("SELECT count(*) FROM organization")
assert res["count"] == 1
res = await conn.fetchrow(
"SELECT count(*) FROM user_ WHERE user_id = 'bob'")
assert res["count"] == 1
res = await conn.fetchrow(
"SELECT count(*) FROM device WHERE user_ = (SELECT _id FROM user_ WHERE user_id = 'bob')"
)
assert res["count"] == 1
res = await conn.fetchrow("SELECT count(*) FROM pki_enrollment")
assert res["count"] == 1
res = await conn.fetchrow("SELECT enrollment_state FROM pki_enrollment"
)
res["enrollment_state"] == "ACCEPTED"
