Discovering and Interpreting API Errors in the Logs

为 Postgres 日志制作了一个免费的指南

浏览 API 日志： #

🌐 Navigating the API logs:

数据库 API 由 PostgREST 网络服务器驱动，会将每一次请求记录到 API Edge 网络日志中。要精确查看它们，可以使用日志浏览器。这些日志由 Logflare 管理，并且可以使用部分 BigQuery SQL 语法进行查询。

🌐 The Database API is powered by a PostgREST web-server, recording every request to the API Edge Network logs. To precisely navigate them, use the Log Explorer. These logs are managed through Logflare and can be queried with a subset of BigQuery SQL syntax.

包含 API 请求的日志表是 edge_logs。

🌐 The log table that contains API requests is edge_logs.

值得注意的是，它包含：

字段	描述
event_message	日志的消息
timestamp	事件记录的时间
request metadata	关于 REST 请求的元数据
response metadata	关于 REST 响应的元数据

🌐 Notably, it contains:

field	description
event_message	the log's message
timestamp	time event was recorded
request metadata	metadata about the REST request
response metadata	metadata about the REST response

请求和响应列是元数据字段中的数组，必须展开。这可以通过 cross join 完成。

🌐 The request and response columns are arrays in the metadata field and must be unnested. This is done with a cross join.

展开嵌套示例

1
select
2
  -- the event message does not require unnesting
3
  event_message,
4
  -- unnested status_code column from metadata.response field
5
  status_code
6
from
7
  edge_logs
8
  -- Unpack data stored in the 'metadata' field
9
  cross join unnest(metadata) as metadata
10
  -- After unpacking the 'metadata' field, extract the 'response' field from it
11
  cross join unnest(response) as response;

最有用来调试的字段有：

🌐 The most useful fields for debugging are:

注意：下面并未包含每一个字段。要查看完整列表，请在日志浏览器中查阅 API Edge 字段参考。

请求对象 #

🌐 Request object

Cloudflare 地理数据： #

🌐 Cloudflare geographic data:

建议使用场景：

检测来自特定地区的滥用行为
检测某些区域的活动高峰

列	描述	示例值
request.cf.city	请求者的城市	慕尼黑
request.cf.country	请求者的国家	德国
request.cf.continent	请求者的洲	欧洲
request.cf.region	请求者的地区	巴伐利亚
request.cf.latitudex	请求者的纬度	48.10840
request.cf.longitude	请求者的经度	11.61020
request.cf.timezone	请求者的时区	欧洲/柏林

展开示例：

1
select
2
  city
3
from
4
  edge_logs
5
-- Unpack 'metadata' field
6
cross join unnest(metadata) AS metadata
7
-- unpack 'request' from 'metadata'
8
cross join unnest(request) AS request;
9
-- unpack 'cf' from 'request'
10
cross join unnest(cf) AS cf;

IP 和浏览器/环境数据： #

🌐 IP and browser/environment data:

建议使用场景：

从IP检测请求行为
通过IP检测滥用
通过用户代理检测错误

列	描述	示例值
request.headers.cf_connecting_ip	请求者的 IP	80.81.18.138
request.headers.user_agent	请求者的浏览器或应用环境	Mozilla/5.0 (Linux; Android 11; K) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/123.0.0.0 Mobile Safari/537.36

展开示例：

1
select
2
  cf_connecting_ip
3
from
4
  edge_logs
5
-- Unpack 'metadata' field
6
cross join unnest(metadata) AS metadata
7
-- unpack 'request' from 'metadata'
8
cross join unnest(request) AS request;
9
-- unpack 'headers' from 'request'
10
cross join unnest(headers) AS headers;

查询类型和格式化数据： #

🌐 Query type and formatting data:

建议使用场景：

找出有问题的查询
识别已认证用户的异常行为

列	描述	示例值
request.method	请求方法 (PATCH, GET, PUT...)	GET
request.url	请求 URL，包含 PostgREST 格式的查询	https://yuhplfrsdxxxtldakizi.supabase.co/rest/v1/users?select=username&id=eq.63b6190e-214f-4b8a-b72d-3af6e1921411&limit=1
request.sb.auth_users	已认证用户的 ID	63b6190e-214f-4b8a-b72d-3af6e1921411

展开示例：

1
select
2
  method,
3
  url,
4
  auth_users
5
from
6
  edge_logs
7
-- Unpack 'metadata' field
8
cross join unnest(metadata) AS metadata
9
-- unpack 'request' from 'metadata'
10
cross join unnest(request) AS request;
11
-- unpack 'sb' from 'request'
12
cross join unnest(sb) AS sb;

响应对象 #

🌐 Response object

状态码： #

🌐 Status code:

建议使用场景：

检测成功/错误

列	描述	示例值
response.status_code	响应状态码 (200, 404, 500...)	404

展开示例：

1
select
2
  status_code
3
from
4
  edge_logs
5
  -- Unpack 'metadata' field
6
  cross join unnest(metadata) as metadata
7
  -- unpack 'response' from 'metadata'
8
  cross join unnest(response) as response;

查找错误 #

🌐 Finding errors

API级别错误 #

🌐 API level errors

metadata.request.url 包含 PostgREST 格式的查询。

🌐 The metadata.request.url contains PostgREST formatted queries.

例如，下面对 JS 客户端的调用：

🌐 For example, the following call to the JS client:

1
let { data: countries, error } = await supabase.from('countries').select('name')

对应调用以下端点：

🌐 translates to calling the following endpoint:

1
https://<project ref>.supabase.co/rest/v1/countries?select=name

你可以使用正则表达式 (高级正则指南) 来查找与你的查询相关的对象。尝试按以下方式隔离：

🌐 You can use regex (Advanced Regex Guide) to find the objects related to your query. Try isolating by:

函数名
列名
表名
查询方法（选择、插入、...）

示例：

🌐 Example:

1
select
2
  cast(timestamp as datetime) as timestamp,
3
  status_code,
4
  url,
5
  event_message
6
from edge_logs
7
cross join unnest(metadata) as metadata
8
cross join unnest(response) AS request;
9
cross join unnest(response) AS response;
10
where
11
  -- find all errors
12
  status_code >= 400
13
    and
14
  -- find queries featuring the a specific <table_name> and <column_name>
15
  (
16
    regexp_contains(url, '<table_name>')
17
    and
18
    regexp_contains(event_message, '<column_name1>|<column_name2>')
19
  )

PostgREST 有一个错误参考表，你可以用它来解读状态码。

🌐 PostgREST has an error reference table that you can use to interpret status codes.

数据库级错误 #

🌐 Database-level errors

不过，通过数据库 API 报告的一些错误是在 Postgres 层发生的。如果不清楚发生了哪种错误，你应该参考错误的时间戳，并尝试查看是否可以在 Postgres 日志中找到它。

🌐 However, some errors that are reported through the Database API occur at the Postgres level. If it is not clear which error occurred you should reference the timestamp of the error and try to see if you can find it in the Postgres logs.

1
select
2
  cast(postgres_logs.timestamp as datetime) as timestamp,
3
  error_severity,
4
  user_name,
5
  query,
6
  detail,
7
  sql_state_code,
8
  event_message
9
from postgres_logs
10
  cross join unnest(metadata) as metadata
11
  cross join unnest(metadata.parsed) as parsed
12
where
13
  -- filter only for error events
14
  regexp_contains(parsed.error_severity, 'ERROR|FATAL|PANIC')
15
    and
16
  -- All DB API requests are registered as the authenticator role
17
  parsed.user_name = 'authenticator'
18
    and
19
  -- find failed queries featuring the function <function_name>
20
  regexp_contains(parsed.query, '<function_name>')
21
    and
22
  -- limit the time of the search to be around the time of the failed API request
23
postgres_logs.timestamp between '2024-04-15 10:50:00' AND '2024-04-15 10:50:27'
24
order by
25
 timestamp desc
26
limit 100;

像 PostgREST 一样，Postgres 也有一个参考表用来解释错误代码。

🌐 Like PostgREST, Postgres has a reference table for interpreting error codes.

PostgREST 服务器和 Cloudflare 错误 #

🌐 PostgREST server and Cloudflare errors

在某些情况下，错误可能是由于 Cloudflare 或 PostgREST 服务器错误引起的。对于 500 及以上的错误，你可能需要检查你的 PostgREST 日志以及 Cloudflare 文档。

🌐 In some cases, errors may emerge because of Cloudflare or PostgREST server errors. For 500 and above errors, you may want to check your PostgREST logs and the Cloudflare docs.)

实际例子： #

🌐 Practical examples:

查找所有错误：

1
select
2
  cast(timestamp as datetime) as timestamp,
3
  status_code,
4
  event_message,
5
  path
6
from
7
  edge_logs
8
  cross join unnest(metadata) as metadata
9
  cross join unnest(response) as response
10
  cross join unnest(request) as request
11
where
12
  -- find all errors
13
  status_code >= 400
14
  and regexp_contains(path, '^/rest/v1/');
15
-- only look at DB API

按路径和代码分组错误:

1
select
2
  status_code,
3
  path,
4
  count(path) as reoccurrence_per_path
5
from
6
  edge_logs
7
  cross join unnest(metadata) as metadata
8
  cross join unnest(response) as response
9
  cross join unnest(request) as request
10
where
11
  -- find all errors
12
  status_code >= 400
13
  and regexp_contains(path, '^/rest/v1/') -- only look at DB API
14
group by path, status_code
15
order by reoccurrence_per_path;

按地区查找请求：

1
select
2
  path,
3
  region,
4
  count(region) as region_count
5
from
6
  edge_logs
7
  cross join unnest(metadata) as metadata
8
  cross join unnest(request) as request
9
  cross join unnest(cf) as cf
10
where
11
  -- only look at DB API
12
  regexp_contains(path, '^/rest/v1/')
13
group by region, path
14
order by requester_region_count;

按IP统计总请求数：

1
select
2
  cf_connecting_ip as ip,
3
  count(cf_connecting_ip) as ip_count
4
from
5
  edge_logs
6
  cross join unnest(metadata) as metadata
7
  cross join unnest(request) as request
8
  cross join unnest(headers) as headers
9
  cross join unnest(cf) as cf
10
  cross join unnest(response) as response
11
where regexp_contains(path, '^/auth/v1/')
12
group by ip
13
order by ip_count;

已认证用户常用的搜索查询路径：

1
select
2
  -- only available for front-end clients
3
  auth_users,
4
  path,
5
  count(auth_users) as ip_count
6
from
7
  edge_logs
8
  cross join unnest(metadata) as metadata
9
  cross join unnest(request) as request
10
  cross join unnest(sb) as sb
11
where
12
  -- only look at DB API
13
  regexp_contains(path, '^/rest/v1/')
14
group by auth_users, path;