6

I run a daily backup of my database using pg_dump and pg_restore that recently stopped working after I pushed an update.

I have a function validate_id that's a Case/When statement just as a quick check for some the data that has integrity issues. Looks something like this:

CREATE OR REPLACE FUNCTION validate_id(
    _string text,
    _type type
) RETURNS boolean AS
$$
SELECT
    CASE WHEN (stuff) THEN TRUE
    WHEN (other stuff) THEN TRUE
    When (more stuff) THEN raise_err('Not an accepted type, the accepted types are: x y z')
ELSE FALSE
$$
LANGUAGE SQL;

Since I added this function, when I dump using this command:

pg_dump -U postgres -h ipaddress -p 5432 -w -F t databaseName > backupsfolder/databaseName.tar

When I use this command:

pg_restore -U postgres -h localhost -p 5432 -d postgres -C "backupsfolder/databaseName.tar"

As of two days ago, this now throws an error:

pg_restore: error: could not execute query: ERROR: function raise_err(unknown) does not exist

I'm pretty lost on what to do. I think what might be going on is that it's trying to restore this function before it restores the raise_err function. Which I thought was built-in to postgres (I can SELECT raise_err('Hello, World');). Is this possible? Is it my CASE statement because I need to return only Booleans? All of the permissions seem correct and restoring with previous backups works fine.

HelpMeExitVim
  • 105
  • 6
  • 2
    Log into your source database with `psql`, and do `\df+ raise_err`. That is not a standard function so far as I know. You should not need another function to raise an error since you can just do `raise error` – Mike Organek Aug 20 '20 at 21:25
  • Does that work in a function written in `sql` or does it have to be `plpgsql`? – HelpMeExitVim Aug 20 '20 at 21:26
  • `\df+ raise_err` does...return the function. It's not something I ever remember explicitly creating but I mean, stuff happens. – HelpMeExitVim Aug 20 '20 at 21:29
  • 1
    Sorry I did not think of that. `language sql` will not support it, and that probably explains why you wrote it as a function. Also, it is `raise exception`, not `raise error` as I erroneously said in my earlier comment. It looks like this sometimes happens: https://stackoverflow.com/q/30707533/13808319 – Mike Organek Aug 20 '20 at 21:34
  • Awesome! This was it, I feel super stupid. Thank you for your help! – HelpMeExitVim Aug 20 '20 at 21:42
  • Are you using this as a ```CHECK``` function? If so that is a no-no [createtable](https://www.postgresql.org/docs/current/sql-createtable.html): "Currently, CHECK expressions cannot contain subqueries nor refer to variables other than columns of the current row (see Section 5.4.1). The system column tableoid may be referenced, but not any other system column." – Adrian Klaver Aug 20 '20 at 21:46
  • 1
    Don't feel stupid, creating that function makes perfect sense IMO. I suspect that the fact that this is a `language sql` function confounded the algo's trying to find dependencies during pg_backup. – Mike Organek Aug 20 '20 at 22:11

2 Answers2

6

The problem is that raise_err is not schema qualified in your function code.

This is potentially dangerous: a malicious user could create his own function raise_err and set search_path so that the wrong function is called.

Since pg_restore is typically run by a superuser, this can be a security problem. Imagine such a function being used in an index definition!

For these reasons pg_dump and pg_restore set an empty search_pathin current versions of PostgreSQL.

The solution to your problem is to explicitly use the function's schema in your SQL statement.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
1

I ended up solving this issue by explicitly setting the search paths for both functions, raise_err() and validate_id() to public:

ALTER FUNCTION validate_id(text,text) SET search_path=public;
ALTER FUNCTION raise_err(text,text) SET search_path=public;
HelpMeExitVim
  • 105
  • 6