Data API Advisors

The Data API Advisors analyze your database schema and configuration to detect security and performance issues for tables and objects exposed by the Data API feature. They run a set of checks against your database and report issues with severity levels and recommended fixes.

Because the Data API exposes your database schema directly over HTTP, security misconfigurations that would normally be hidden behind an application server become directly exploitable. Missing RLS policies and overly permissive views are especially dangerous in this context. The advisors also check for common performance issues like unindexed foreign keys and table bloat, helping you catch issues before they reach production.

• Your compute must be active (not suspended) when running a scan.
• If the Data API is not yet enabled, the Advisors screen will show a "Data API not enabled" message. Enable the Data API to run scans.

In the Neon Console, go to Monitoring > Data API Advisors for your branch. The advisor scans your database and displays any issues found, grouped by category.

Each issue includes a severity level, a description, and a recommended fix. Clicking an issue opens a detail view with an Ask Assistant option that analyzes the risk and suggests a specific fix (such as a SQL query tailored to your schema), along with a Read our docs link for general guidance on that type of issue.

You can also retrieve advisor issues via the Neon API:

GET /projects/{project_id}/advisors

This endpoint requires Data API to be enabled for the target project and branch.

Optional query parameters:

• branch_id: Target branch (defaults to the primary branch)
• database_name: Target database (required if the branch has multiple databases)
• category: Filter by SECURITY or PERFORMANCE
• min_severity: Minimum severity to include: INFO, WARN, or ERROR. For example, WARN returns WARN and ERROR issues but not INFO.

{
  "issues": [
    {
      "name": "rls_disabled_in_public",
      "title": "RLS Disabled in Public",
      "level": "ERROR",
      "facing": "EXTERNAL",
      "categories": ["SECURITY"],
      "description": "Detects cases where row level security (RLS) has not been enabled on tables in schemas exposed to Data-API",
      "detail": "Table `public.users` is public, but RLS has not been enabled.",
      "remediation": "https://neon.com/docs/data-api/database-advisor#rls-disabled-in-public",
      "metadata": {}
    }
  ]
}

Severity: ERROR

Tables in schemas exposed through the Data API are accessible to anyone with your project's API URL if RLS is not enabled. Without RLS, all rows are fully readable and writable via the API.

See Data API access control and RLS tutorial.

ALTER TABLE <schema>.<table> ENABLE ROW LEVEL SECURITY;

Severity: ERROR

RLS policies have been created on a table, but RLS itself is not enabled. The policies have no effect until RLS is turned on, which may give a false sense of security.

ALTER TABLE <schema>.<table> ENABLE ROW LEVEL SECURITY;

Severity: ERROR

Tables exposed via the Data API that contain columns with potentially sensitive data (credentials, personal identifiers, financial info) are at risk if RLS is not enabled. Without RLS, any API user can read all rows.

ALTER TABLE <schema>.<table> ENABLE ROW LEVEL SECURITY;

CREATE POLICY restrict_access ON <schema>.<table>
  FOR SELECT
  USING ((SELECT auth.uid()) = user_id);

Severity: ERROR

Views default to SECURITY DEFINER in PostgreSQL, meaning they execute with the permissions of the view creator rather than the querying user. This bypasses RLS policies on the underlying tables and can expose more data than intended through the Data API.

See PostgreSQL views for background.

CREATE OR REPLACE VIEW <schema>.<view_name>
  WITH (security_invoker=on)
AS
SELECT ...
FROM ...;

Severity: ERROR

Views or materialized views that reference neon_auth.user can expose sensitive user data to API roles. Views in PostgreSQL default to SECURITY DEFINER mode, which means they bypass row-level security policies on the underlying tables.

See Managed Better Auth overview and Data API access control for background.

CREATE TABLE public.profiles (
  id uuid NOT NULL REFERENCES neon_auth.user ON DELETE CASCADE,
  display_name text,
  PRIMARY KEY (id)
);

CREATE FUNCTION public.handle_new_user()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER SET search_path = ''
AS $$
BEGIN
  INSERT INTO public.profiles (id)
  VALUES (new.id);
  RETURN new;
END;
$$;

CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON neon_auth.user
  FOR EACH ROW EXECUTE PROCEDURE public.handle_new_user();

CREATE VIEW public.members
  WITH (security_invoker=on)
AS
SELECT id, created_at
FROM neon_auth.user;

Severity: ERROR

RLS policies that reference user-editable fields from Managed Better Auth (such as user.role or organization.metadata) are insecure. Users can modify these fields, allowing them to bypass security checks.

See Managed Better Auth overview.

-- Insecure: references neon_auth.user.role which users can modify
CREATE POLICY role_based_access ON public.documents
  FOR SELECT
  USING (
    (SELECT role FROM neon_auth.user WHERE id = (SELECT auth.uid())) = 'admin'
  );

CREATE POLICY owner_access ON public.documents
  FOR SELECT
  USING ((SELECT auth.uid()) = user_id);

Severity: ERROR

Foreign keys referencing unique constraints (rather than primary keys) in the neon_auth schema are not supported. These unique constraints may change in future updates, which would break your foreign key references and block migrations.

ALTER TABLE <schema>.<table> DROP CONSTRAINT <foreign_key_name>;

ALTER TABLE <schema>.<table>
  ADD CONSTRAINT <foreign_key_name>
  FOREIGN KEY (<column>) REFERENCES neon_auth.user(id);

Severity: WARN

RLS policies that use always-true expressions like USING (true) or WITH CHECK (true) for write operations (INSERT, UPDATE, DELETE) effectively bypass row-level security. While the table appears to have RLS enabled, these policies grant unrestricted access.

Note: USING (true) on SELECT-only policies is excluded from this check, as it is a common pattern for intentional public read access.

DROP POLICY <policy_name> ON <schema>.<table>;

CREATE POLICY <new_policy_name> ON <schema>.<table>
  FOR <action>
  USING ((SELECT auth.uid()) = user_id);

CREATE POLICY restrict_writes ON <schema>.<table>
  AS RESTRICTIVE
  FOR UPDATE
  USING ((SELECT auth.uid()) = user_id);

Severity: WARN

Functions without an explicit search_path inherit the calling user's session search_path. This can lead to inconsistent behavior and potential security issues if a user manipulates their search_path to resolve unqualified names to unexpected objects.

See PostgreSQL schemas and search_path for background.

CREATE OR REPLACE FUNCTION <schema>.<function_name>()
  RETURNS void
  LANGUAGE sql
  SET search_path = ''
AS $$
  -- Use fully qualified names, e.g. public.my_table
$$;

Severity: WARN

Extensions installed in the public schema make their functions, tables, and views appear as part of your API surface. This is usually unintentional and can expose internal functionality.

See Neon extensions for more on managing extensions.

ALTER EXTENSION <extension_name> SET SCHEMA <non_exposed_schema>;

ALTER EXTENSION ltree SET SCHEMA extensions;

Severity: WARN

Running an outdated extension version may expose your database to known security vulnerabilities and prevent you from benefiting from bug fixes and performance improvements.

See managing extensions for background.

SELECT name, installed_version, default_version
FROM pg_catalog.pg_available_extensions
WHERE name = '<extension_name>';

ALTER EXTENSION <extension_name> UPDATE;

Severity: WARN

Materialized views cannot be protected with RLS. If a materialized view is accessible to API roles (anonymous or authenticated), all rows are visible to any API user.

See PostgreSQL materialized views.

REVOKE SELECT ON <schema>.<materialized_view> FROM public, anonymous, authenticated;

SELECT pg_catalog.has_table_privilege('anonymous', '<schema>.<materialized_view>'::regclass::oid, 'select');
-- Should return: false

Severity: WARN

Foreign tables do not support RLS. If a foreign table is accessible over the Data API, all rows are visible to any API user.

REVOKE SELECT ON <schema>.<foreign_table> FROM public, anonymous, authenticated;

CREATE SCHEMA private;
ALTER FOREIGN TABLE public.<foreign_table> SET SCHEMA private;

-- Ensure API roles can still read from the foreign table
GRANT SELECT ON private.<foreign_table> TO anonymous, authenticated;

-- Create a function with security rules, accessible via RPC
CREATE OR REPLACE FUNCTION public.secure_foreign_read()
  RETURNS TABLE (id integer, data text, author_id uuid)
  LANGUAGE sql
  SET search_path = ''
AS $$
  SELECT id, data, author_id
  FROM private.<foreign_table>
  WHERE author_id = (SELECT auth.uid());
$$;

Severity: WARN

Columns using certain reg* types that reference PostgreSQL internals are not supported by pg_upgrade, the standard tool for upgrading between PostgreSQL versions. Using these types can block future database upgrades. The flagged types are: regcollation, regconfig, regdictionary, regnamespace, regoper, regoperator, regproc, and regprocedure.

-- Instead of:
CREATE TABLE public.bad_table (
  id int PRIMARY KEY,
  my_collation regcollation
);

-- Use:
CREATE TABLE public.good_table (
  id int PRIMARY KEY,
  my_collation_name text
);

Severity: INFO

RLS is enabled on a table but no policies have been created. This means no data is accessible via the Data API for this table, which may or may not be intentional.

CREATE POLICY select_own_rows ON <schema>.<table>
  FOR SELECT
  USING ((SELECT auth.uid()) = user_id);

CREATE POLICY deny_all ON <schema>.<table>
  FOR SELECT
  USING (false);

Severity: WARN

When RLS policies call functions like auth.uid() or current_setting() directly, PostgreSQL may re-evaluate them for every row the query touches. At scale, this adds significant overhead. Wrapping the call in a subquery caches the result for the duration of the query.

See RLS performance with subqueries for more context.

-- Before (re-evaluated per row):
CREATE POLICY access_policy ON documents
  USING (auth.uid() = user_id);

-- After (evaluated once per query):
CREATE POLICY access_policy ON documents
  USING ((SELECT auth.uid()) = user_id);

Severity: WARN

When multiple permissive RLS policies apply to the same table for the same role and action, PostgreSQL must evaluate all of them (they combine with OR logic). This increases query overhead and can also lead to unintentionally broad access.

See row-level security for background on policy types.

-- Instead of two separate policies:
DROP POLICY policy_a ON <table>;
DROP POLICY policy_b ON <table>;

-- Combine into one:
CREATE POLICY consolidated_policy ON <table>
  FOR SELECT
  USING (
    <condition_a> OR <condition_b>
  );

Severity: WARN

Two or more identical indexes on the same table add write overhead with no performance benefit. Each duplicate must be updated on every insert, update, and delete operation.

DROP INDEX <schema_name>.<duplicate_index_name>;

Severity: INFO

Foreign key columns without a covering index can lead to slow joins and increased resource usage, especially as tables grow. Adding an index on foreign key columns improves join performance.

See PostgreSQL foreign keys and index types for background.

CREATE INDEX ON <table>(<foreign_key_column>);

CREATE INDEX ix_order_item_customer_id ON order_item(customer_id);

Severity: INFO

Indexes that have never been used by any query still add overhead on every write operation (insert, update, delete) and consume storage. Removing unused indexes can improve write performance. This check only flags non-unique, non-primary-key indexes.

Important: PostgreSQL tracks index usage in pg_stat_user_indexes, but these statistics are local to the compute and do not persist across compute restarts (including suspend/resume cycles and failovers). An index showing zero scans may simply not have been used since the last compute start, not since it was created. Allow enough time for representative traffic before treating an index as truly unused.

See PostgreSQL indexes for background.

DROP INDEX <schema_name>.<index_name>;

Severity: INFO

Tables without a primary key lack a guaranteed unique row identifier. This can degrade query performance and prevents establishing foreign key relationships with other tables.

ALTER TABLE <schema>.<table> ADD PRIMARY KEY (<column>);

ALTER TABLE <schema>.<table> ADD PRIMARY KEY (<column1>, <column2>);

Severity: INFO

Tables with excessive bloat contain unused space from deleted or updated rows. This increases storage usage, slows down sequential scans, and increases I/O overhead. PostgreSQL's autovacuum normally reclaims this space, but it may not keep up with high-churn tables.

See pg_repack and pgstattuple for tools to analyze and resolve bloat.

VACUUM <schema>.<table>;

VACUUM FULL <schema>.<table>;

SELECT pg_size_pretty(pg_table_size('<schema>.<table>'));

Database lints originally based on open-source lints from the Supabase project, Apache 2.0.