:hedgehog: PostHog is the leading platform for building self-driving products. Our developer tools – AI observability, analytics, session replay, flags, experiments, error tracking, logs, and more – capture all the context agents need to diagnose problems, uncover opportunities, and ship fixes. Steer it all from Slack, web, desktop, or the MCP.
Python ab-testingai-analyticsanalyticscdpdata-warehouseexperimentsfeature-flagsjavascriptproduct-analyticspythonreactsession-replaysurveystypescriptweb-analytics
from __future__ import annotations
import time
import logging
from collections.abc import Sequence
from typing import TYPE_CHECKING, Any
import psycopg
from psycopg import sql as psql
from psycopg.conninfo import make_conninfo
from posthog.hogql.database.s3_table import S3Table, parse_duckdb_s3_source
from products.managed_warehouse.backend.common import (
get_duckgres_config_for_org,
is_dev_mode,
sanitize_ducklake_identifier,
)
from products.managed_warehouse.backend.facade.contracts import (
DuckLakeCompiledQuery,
DuckLakeQueryResult,
DuckLakeS3Secret,
DuckLakeTableResult,
)
from products.managed_warehouse.backend.service_credentials import ServiceCredential
from products.managed_warehouse.backend.table_binding import bind_tables_to_ducklake
if TYPE_CHECKING:
from posthog.schema import HogQLQuery
from posthog.hogql.database.database import Database
from posthog.models import User
from posthog.models.team.team import Team
logger = logging.getLogger(__name__)
def make_duckgres_conninfo(
team_id: int,
*,
organization_id: str | None = None,
service_credential: ServiceCredential | None = None,
application_name: str = "posthog",
) -> str:
"""Build a psycopg conninfo for a team's duckgres server.
Default (no ``service_credential``): use the username/password from the
stored ``DuckgresServer`` row. Materialization and internal warehouse
helpers inherit the permissions of that login.
With ``service_credential``: connect with a CP-issued, org-scoped
per-credential grant (``svc_…`` credential_id + secret), short-lived and
disposable. This is what background jobs (dagster) should present; see
``products/managed_warehouse/backend/service_credentials.py``.
Host/port/database/sslmode come ENTIRELY from the credential's
CP-issued ``connect`` block, so this branch is independent of the stored
``DuckgresServer`` row.
Service credentials are only mintable for a provisioned production
warehouse, so dev-mode is rejected rather than silently using the
environment-configured login.
``application_name`` is forwarded to duckgres as a standard libpq startup
param and echoed into its analytics events, so callers should pass a
caller-identifying slug (e.g. ``"ducklake-register"``) instead of relying
on the ``"posthog"`` default — that default only distinguishes PostHog's
own connections in aggregate from customer clients (psql, their own
application_name).
"""
from products.managed_warehouse.backend.common import _duckgres_dev_config, _get_org_id_for_team
if service_credential is not None:
if is_dev_mode():
raise RuntimeError(
"service credentials are not available in dev mode (no CP to mint against); "
"omit service_credential to use the dev duckgres defaults"
)
if not service_credential.credential_secret:
raise RuntimeError("service_credential carries no secret; mint or refresh returned an invalid response")
connect = service_credential.connect
return make_conninfo(
host=connect.host,
port=connect.port,
dbname=connect.database,
user=service_credential.credential_id,
password=service_credential.credential_secret,
sslmode=connect.sslmode,
sslcert="/tmp/no.txt",
sslkey="/tmp/no.txt",
sslrootcert="/tmp/no.txt",
application_name=application_name,
)
if is_dev_mode():
config = _duckgres_dev_config()
else:
org_id = organization_id or _get_org_id_for_team(team_id)
config = get_duckgres_config_for_org(org_id)
return make_conninfo(
host=config["DUCKGRES_HOST"],
port=int(config["DUCKGRES_PORT"]),
dbname=config["DUCKGRES_DATABASE"],
user=config["DUCKGRES_USERNAME"],
password=config["DUCKGRES_PASSWORD"],
sslmode="require",
sslcert="/tmp/no.txt",
sslkey="/tmp/no.txt",
sslrootcert="/tmp/no.txt",
application_name=application_name,
)
# TODO: remove hardcoded schemas and derive the search path from the team's
# data warehouse sources / DAG configuration instead
_SEARCH_PATH_SCHEMAS = ["revenue", "stripe", "billing_public", "credit", "posthog"]
def _set_search_path(conn: psycopg.Connection[Any], extra_schemas: list[str] | None = None) -> None:
schemas = (extra_schemas or []) + _SEARCH_PATH_SCHEMAS
literal = psql.Literal(",".join(schemas))
sql = psql.SQL("SET search_path TO {}").format(literal)
conn.execute(sql)
def _s3_secrets_for_database(database: Database) -> tuple[DuckLakeS3Secret, ...]:
"""Collect the S3 secrets the self-managed tables of a built HogQL database need.
The table set comes from the database the query was compiled against, so warehouse access
control has already pruned anything the querier may not read. Reading it from the team's
tables instead would let a restricted table's credentials serve an allowed one: DuckDB
matches a secret on its ``s3://bucket/key`` scope alone, ignoring the endpoint, and breaks
a scope tie by picking the lexicographically smaller secret name.
"""
secrets: list[DuckLakeS3Secret] = []
for table_name in database.get_warehouse_table_names():
try:
table = database.get_table(table_name)
except Exception:
continue
# Connector-synced tables are rebound to their DuckLake copy and read no object storage.
if not isinstance(table, S3Table) or table.external_data_source_id or table.table_id is None:
continue
if table.format != "Parquet" or not table.access_key or not table.access_secret:
continue
source = parse_duckdb_s3_source(table.url)
if source is None:
continue
secrets.append(
DuckLakeS3Secret(
name=f"self_managed_{str(table.table_id).replace('-', '')}",
key_id=table.access_key,
secret=table.access_secret,
region=source.region,
scope=source.scope,
use_ssl=source.use_ssl,
url_style=source.url_style,
endpoint=source.endpoint,
)
)
return tuple(secrets)
def _configure_s3_secrets(conn: psycopg.Connection[Any], secrets: Sequence[DuckLakeS3Secret]) -> None:
if not secrets:
return
with conn.cursor() as cur:
for secret in secrets:
secret_options = [
psql.SQL("TYPE S3"),
psql.SQL("KEY_ID %s"),
psql.SQL("SECRET %s"),
psql.SQL("REGION %s"),
]
values: list[object] = [secret.key_id, secret.secret, secret.region]
if secret.endpoint is not None:
secret_options.append(psql.SQL("ENDPOINT %s"))
values.append(secret.endpoint)
secret_options.extend(
[
psql.SQL("USE_SSL %s"),
psql.SQL("URL_STYLE %s"),
psql.SQL("SCOPE %s"),
]
)
values.extend([secret.use_ssl, secret.url_style, secret.scope])
statement = psql.SQL("CREATE OR REPLACE TEMPORARY SECRET {} ({})").format(
psql.Identifier(secret.name),
psql.SQL(", ").join(secret_options),
)
try:
cur.execute(statement, values)
except Exception:
# A secret duckgres rejects only breaks the table it belongs to, so keep going
# and let the query fail on that table instead of on every other one too.
conn.rollback()
logger.warning("Could not configure DuckLake S3 access for %s", secret.name, exc_info=True)
def compile_hogql_to_ducklake_sql(
team_id: int,
query: HogQLQuery,
*,
team: Team | None = None,
user: User | None = None,
bypass_warehouse_access_control: bool = False,
) -> DuckLakeCompiledQuery:
"""Compile a HogQLQuery to DuckDB-dialect SQL for DuckLake.
The returned ``values`` hold parameter bindings for ``psycopg``'s ``%(name)s``
placeholders embedded in ``sql``; callers must pass them to
``cursor.execute(sql, values)`` or the query will fail with an
unbound-placeholder error. ``s3_secrets`` must be installed on the connection
before the query runs, or its self-managed tables cannot be read.
"""
from posthog.hogql.context import HogQLContext
from posthog.hogql.database.database import Database
from posthog.hogql.parser import parse_select
from posthog.hogql.printer.utils import prepare_and_print_ast
from posthog.hogql.variables import replace_variables
from posthog.models.team.team import Team
parsed = parse_select(query.query)
if query.variables:
team = team or Team.objects.get(pk=team_id)
parsed = replace_variables(parsed, list(query.variables.values()), team)
# Build the database up front so warehouse tables can be bound from the ClickHouse
# S3 table function to their DuckLake-materialized tables before printing.
database = Database.create_for(
team_id,
user=user,
bypass_warehouse_access_control=bypass_warehouse_access_control,
)
bind_tables_to_ducklake(database, team_id)
# Separate context for the DuckDB print — the HogQL round-trip below shouldn't
# contribute to ``postgres_context.values``.
postgres_context = HogQLContext(
team_id=team_id,
user=user,
enable_select_queries=True,
bypass_warehouse_access_control=bypass_warehouse_access_control,
database=database,
)
postgres_sql, _ = prepare_and_print_ast(parsed, postgres_context, dialect="duckdb")
hogql_context = HogQLContext(
team_id=team_id,
user=user,
enable_select_queries=True,
bypass_warehouse_access_control=bypass_warehouse_access_control,
)
hogql_pretty, _ = prepare_and_print_ast(parsed, hogql_context, dialect="hogql")
return DuckLakeCompiledQuery(
sql=postgres_sql,
values=dict(postgres_context.values),
hogql=hogql_pretty,
s3_secrets=_s3_secrets_for_database(database),
)
def execute_ducklake_query(
team_id: int,
*,
sql: str | None = None,
query: HogQLQuery | None = None,
organization_id: str | None = None,
team: Team | None = None,
user: User | None = None,
bypass_warehouse_access_control: bool = False,
) -> DuckLakeQueryResult:
"""Execute a query against a team's duckgres server.
Accepts either raw SQL or a HogQLQuery (which gets compiled to
DuckDB-dialect SQL). Exactly one of `sql` or `query` must be provided.
Pass organization_id to skip the Team→Organization lookup when org
context is already available from the caller. Pass team to skip the
Team lookup used for variable substitution. Pass user so HogQL compilation
resolves data warehouse access control the same way as normal query execution.
Set bypass_warehouse_access_control only from trusted internal callers that
must compile without a user, such as materialization.
Only the `query` form can read self-managed object storage — raw `sql` carries no
compiled schema, so no S3 secrets are installed for it.
"""
if sql and query:
raise ValueError("Provide either sql or query, not both")
if not sql and not query:
raise ValueError("Provide either sql or query")
hogql_pretty: str | None = None
values: dict[str, object] = {}
s3_secrets: Sequence[DuckLakeS3Secret] = ()
if query:
compiled = compile_hogql_to_ducklake_sql(
team_id,
query,
team=team,
user=user,
bypass_warehouse_access_control=bypass_warehouse_access_control,
)
sql = compiled.sql
values = compiled.values
hogql_pretty = compiled.hogql
s3_secrets = compiled.s3_secrets
assert sql is not None
conninfo = make_duckgres_conninfo(team_id, organization_id=organization_id, application_name="endpoints-shadow")
_connect_start = time.monotonic()
with psycopg.connect(conninfo) as conn:
connect_ms = (time.monotonic() - _connect_start) * 1000
_configure_s3_secrets(conn, s3_secrets)
_set_search_path(conn)
with conn.cursor() as cur:
_query_start = time.monotonic()
cur.execute(sql, values or None)
columns = [desc.name for desc in cur.description] if cur.description else []
types = [str(desc.type_code) for desc in cur.description] if cur.description else []
rows = cur.fetchall()
query_ms = (time.monotonic() - _query_start) * 1000
return DuckLakeQueryResult(
columns=columns,
types=types,
results=[list(r) for r in rows],
sql=sql,
hogql=hogql_pretty,
connect_ms=connect_ms,
query_ms=query_ms,
)
def _calculate_table_size(conninfo: str, safe_schema: str, safe_table: str) -> int:
try:
with psycopg.connect(conninfo) as conn:
with conn.cursor() as cur:
cur.execute(
"""
SELECT t.file_size_bytes
FROM ducklake_table_info('ducklake') t
JOIN __ducklake_metadata_ducklake.ducklake_schema s
ON t.schema_id = s.schema_id AND s.end_snapshot IS NULL
WHERE s.schema_name = %s AND t.table_name = %s
""",
(safe_schema, safe_table),
)
row = cur.fetchone()
return int(row[0]) if row and row[0] else 0
except Exception:
return 0
def execute_ducklake_create_table(
team_id: int,
sql: str,
schema_name: str,
table_name: str,
values: dict[str, object] | None = None,
*,
organization_id: str | None = None,
s3_secrets: Sequence[DuckLakeS3Secret] = (),
) -> DuckLakeTableResult:
"""Execute a query via duckgres and materialize the result as a DuckLake table.
Creates or replaces a table in the given schema using CREATE OR REPLACE TABLE ... AS.
The table is stored natively in DuckLake (Parquet on S3 + Postgres catalog metadata).
Pass organization_id to skip the Team→Organization lookup when org
context is already available from the caller.
``values`` carries parameter bindings for any ``%(name)s`` placeholders in ``sql``
(as produced by ``compile_hogql_to_ducklake_sql``). It is passed through to
``psycopg`` so the SELECT body is executed with safe parameter binding. Pass that
same compilation's ``s3_secrets`` so the SELECT can read self-managed object storage.
"""
safe_schema = sanitize_ducklake_identifier(schema_name, default_prefix="shadow")
safe_table = sanitize_ducklake_identifier(table_name, default_prefix="model")
qualified = psql.Identifier(safe_schema, safe_table)
conninfo = make_duckgres_conninfo(team_id, organization_id=organization_id, application_name="materialization")
# capture previous table size before replacing — best-effort, don't block materialization
previous_file_size_bytes = _calculate_table_size(conninfo, safe_schema, safe_table)
with psycopg.connect(conninfo) as conn:
_configure_s3_secrets(conn, s3_secrets)
conn.execute(psql.SQL("CREATE SCHEMA IF NOT EXISTS {}").format(psql.Identifier(safe_schema)))
# duckgres SET seems to only accept a single comma-separated string value with single quotes
_set_search_path(conn, extra_schemas=[safe_schema])
with conn.cursor() as cur:
cur.execute(
psql.SQL("""
CREATE OR REPLACE TABLE {} AS (
{}
)
""").format(qualified, psql.SQL(sql)),
values or None,
)
row_count = 0
try:
with psycopg.connect(conninfo) as conn:
_set_search_path(conn, extra_schemas=[safe_schema])
with conn.cursor() as cur:
cur.execute(psql.SQL("SELECT count(*) FROM {}").format(qualified))
row = cur.fetchone()
row_count = int(row[0]) if row else 0
except Exception:
pass
# capture new table size — best-effort, don't block materialization
file_size_bytes = _calculate_table_size(conninfo, safe_schema, safe_table)
return DuckLakeTableResult(
schema_name=safe_schema,
table_name=safe_table,
row_count=row_count,
file_size_bytes=file_size_bytes,
file_size_delta_bytes=file_size_bytes - previous_file_size_bytes,
)