外部执行Postgres的jsonb_set命令解析JSONB值失败问题排查

问题场景 #

尝试通过podman exec在容器内执行PostgreSQL命令，更新JSONB类型字段search_settings中的密码值，但始终遇到JSON解析错误：

ERROR: invalid input syntax for type json
LINE 1: ...h_settings, '{application,local,user,admin,password}', 'password1...
^
DETAIL: Token "password123" is invalid.
CONTEXT: JSON data, line 1: password123

此前已解决直接执行psql命令的引号转义问题，但嵌套到podman exec+su postgres -c的多层命令结构后，问题复现。

尝试过的命令 #

• 直接执行psql可正常运行的命令：

  /opt/application/usr/postgresql/bin/psql -U postgres -d database1 -c "UPDATE system_settings SET search_settings = jsonb_set(search_settings, '{application,local,user,admin,password}', '\"password123\"');"
  

• 嵌套到容器命令后报错的版本：

  podman exec application su postgres -c "/opt/application/usr/postgresql/bin/psql -U postgres -d database1 -c \"UPDATE system_settings SET search_settings = jsonb_set(search_settings, '{application,local,user,admin,password}', '\"password123\"');\""
  

问题原因 #

多层shell嵌套时，每一层都会解析转义字符：podman exec启动的shell、su postgres -c启动的postgres用户shell、psql的命令解析层，会逐步吃掉转义符号，最终传递给PostgreSQL的JSON字符串丢失了必要的双引号，导致解析失败。

解决方案 #

方案1：增加转义层级 #

针对su的shell再增加一层转义，把\"替换为\\\"，确保最终到达PostgreSQL时保留双引号：

podman exec application su postgres -c "/opt/application/usr/postgresql/bin/psql -U postgres -d database1 -c \"UPDATE system_settings SET search_settings = jsonb_set(search_settings, '{application,local,user,admin,password}', '\\\"password123\\\"');\""

方案2：使用单引号嵌套简化转义 #

利用shell中单引号不解析转义的特性，重新组织命令结构，减少转义复杂度：

podman exec application su postgres -c '/opt/application/usr/postgresql/bin/psql -U postgres -d database1 -c '\''UPDATE system_settings SET search_settings = jsonb_set(search_settings, '\''{application,local,user,admin,password}'\'', '\''"password123"'\'');'\'''

注：'\''是shell中在单引号包裹的字符串内插入单引号的标准写法（关闭外层单引号 → 插入转义单引号 → 重新打开外层单引号）。

方案3：通过SQL文件执行（推荐） #

完全避免转义问题，将SQL写入文件后在容器内执行：

1. 本地创建update_password.sql文件：

   UPDATE system_settings SET search_settings = jsonb_set(search_settings, '{application,local,user,admin,password}', '"password123"');
   

2. 复制文件到容器内：

   podman cp update_password.sql application:/tmp/
   

3. 容器内执行SQL文件：

   podman exec application su postgres -c "/opt/application/usr/postgresql/bin/psql -U postgres -d database1 -f /tmp/update_password.sql"
   

内容的提问来源于stack exchange，提问作者Chirag