Postgres Foreign Data Wrapper Program gets "child process exited with exit code 1"

Question

I am using Postgres 14 and Foreign Data Wrappers (file_fdw and the CREATE SERVER statement) to call some system commands and get the results. But some system commands fail with a "child process exited with exit code 1" error. Can someone help explain why the second command fails? Both commands run fine from a command line as the postgres user. I am on linux.

Here is an example of one that works:

CREATE FOREIGN TABLE IF NOT EXISTS rawdata.system_ps (
  pid text,
  started text,
  cpu_pcnt text,
  mem_pcnt text,
  command text
) 
SERVER "import" options (
  PROGRAM 'ps --no-headers -e -o %p, -o lstart -o ,%C, -o %mem -o ,%c',
  FORMAT 'csv',
  HEADER 'true'
);
-- select * from rawdata.system_ps;

Here is an example of one that fails:

CREATE FOREIGN TABLE IF NOT EXISTS rawdata.logs (
  result text
) 
SERVER "import" options (
  PROGRAM 'sudo journalctl -u postgrest.service --since "5 min ago" -o short-iso  --no-pager',
  FORMAT 'text', --text, csv, or binary
  HEADER 'off'
);
select * from rawdata.logs;

Answer 1

The sudo manual says:

Upon successful execution of a command, the exit status from sudo will be the exit status of the program that was executed.

[...]

If there is an authentication failure, a configuration/permission problem, or if the given command cannot be executed, sudo exits with a value of 1. In the latter case, the error string is printed to the standard error. If sudo cannot stat(2) one or more entries in the user's PATH, an error is printed to the standard error.

So you should look into the PostgreSQL stderr log for helpful messages.