EXPLAIN Security Considerations

[ankane]

The EXPLAIN feature (unavoidably) exposes data to PgHero users to varying degrees.

EXPLAIN

EXPLAIN exposes statistical data through its output, since Postgres uses statistics about data in the database to generate the execution plan. A malicious user could learn these statistics in certain cases, as estimations will be different for similar queries.

You can view this data by querying the pg_stats view.

SELECT * FROM pg_stats;

EXPLAIN ANALYZE

EXPLAIN ANALYZE exposes data through timing. For instance, this query will take extra time if the given email exists.

EXPLAIN ANALYZE SELECT CASE WHEN email = '[email protected]' THEN pg_sleep(1) ELSE NULL END FROM users;

See this great write-up by Adam Derewecki.

Changes in 3.1

Starting with PgHero 3.1, teams now have control over this feature. There's a new explain option that allows the Explain page to be disabled.

explain: false

Also, EXPLAIN ANALYZE is now opt-in. You can enable it with:

explain: analyze

Please create a new issue if you have any questions, concerns, or feedback.