Backing up Profile Management Functions v12

A profile may include a PASSWORD_VERIFY_FUNCTION clause that refers to a user-defined function that specifies the behavior enforced by Advanced Server. Profiles are global objects; they are shared by all of the databases within a cluster. While profiles are global objects, user-defined functions are database objects.

Invoking pg_dumpall with the –g or –r option will create a script that recreates the definition of any existing profiles, but that does not recreate the user-defined functions that are referred to by the PASSWORD_VERIFY_FUNCTION clause. You should use the pg_dump utility to explicitly dump (and later restore) the database in which those functions reside.

The script created by pg_dump will contain a command that includes the clause and function name:

ALTER PROFILE… LIMIT PASSWORD_VERIFY_FUNCTION <function_name>

to associate the restored function with the profile with which it was previously associated.

If the PASSWORD_VERIFY_FUNCTION clause is set to DEFAULT or NULL, the behavior will be replicated by the script generated by the pg_dumpall –g or pg_dumpall –r command.