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"