postgresqlpgppostgresql-9.1

PostgreSQL pgp_sym_encrypt() broken in version 9.1


The following works in PostgreSQL 8.4:

insert into credentials values('demo', pgp_sym_encrypt('password', 'longpassword'));

When I try it in version 9.1 I get this:

ERROR: function pgp_sym_encrypt(unknown, unknown) does not exist LINE 1: insert into credentials values('demo', pgp_sym_encrypt('pass... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.

*** Error ***

ERROR: function pgp_sym_encrypt(unknown, unknown) does not exist SQL state: 42883 Hint: No function matches the given name and argument types. You might need to add explicit type casts. Character: 40

If I try some explicit casts like this

insert into credentials values('demo', pgp_sym_encrypt(cast('password' as text), cast('longpassword' as text)))

I get a slightly different error message:

ERROR: function pgp_sym_encrypt(text, text) does not exist

I have pgcrypto installed. Does anyone have pgp_sym_encrypt() working in PostgreSQL 9.1?


Solution

  • One explanation could be that the module was installed into a schema that is not in your search path - or to the wrong database.
    Diagnose your problem with this query and report back the output:

    SELECT n.nspname, p.proname, pg_catalog.pg_get_function_arguments(p.oid) as params
    FROM   pg_catalog.pg_proc p
    JOIN   pg_catalog.pg_namespace n ON n.oid = p.pronamespace
    WHERE  p.proname ~~* '%pgp_sym_encrypt%'
    AND    pg_catalog.pg_function_is_visible(p.oid);
    

    Finds functions in all schemas in your database. Similar to the psql meta-command

    \df *pgp_sym_encrypt*