PostgreSQL pgp_sym_encrypt() broken in version 9.1

Question

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?

score 3 · Answer 1 · edited Oct 19 '18 at 17:06

3

Make sure you install the extension on the desired schema.

sudo -i -u postgres
psql $database
CREATE EXTENSION pgcrypto;

edited Oct 19 '18 at 17:06

pushkin

9,575
15
51
95

answered Oct 19 '18 at 16:45

Walter Schweitzer

354
2
7

Erwin Brandstetter · Accepted Answer · 2011-12-29T00:02:46.570

3

On 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*

edited Dec 29 '11 at 00:02

answered Dec 28 '11 at 23:45

Erwin Brandstetter

605,456
145
1,078
1,228

1

Your select statement returns nothing even though pgcrypto is installed in the postgres database. I tried to install pgcrypto in a different database but it failed with an error saying that it is already installed. I've dropped pgcrypto from the postgres db but when I run the following command it still fails because it thinks it is installed: psql -d VGDB -U postgres -c "create extension pgcrypto" I run this command with Python, but I don't know why that would matter. – Dean Schulze Dec 29 '11 at 03:09
1

@DeanSchulze Most every PostgreSQL extensions has to be installed **per database**. Installing `pgcrypto` in the `postgres` database is most certainly **not** what you want. Install it in the same database you want to use it in. If my query (executed in the same database!) does not find anything, the extension is not installed correctly. You need to understand the concepts of `database cluster`, `database` and `schema` before you proceed. You can start by searching for those terms in the [excellent manual](http://www.postgresql.org/docs/current/interactive/index.html). – Erwin Brandstetter Dec 29 '11 at 04:02

score 0 · Answer 3 · answered Jul 06 '21 at 09:56

Just mention de schema where is installed pgcrypto like this:

@ColumnTransformer(forColumn = "TEST",
        read =  "public.pgp_sym_decrypt(TEST, 'password')",
        write = "public.pgp_sym_encrypt(?, 'password')")
@Column(name = "TEST", columnDefinition = "bytea", nullable = false)
private String test;

score 0 · Answer 4 · answered Dec 29 '11 at 03:57

OK, problem solved.

I was creating the pgcrypto extension as the first operation in the script. Then I dropped and added the VGDB database. That's why pgcrypto was there immediately after creating it, but didn't exist when running the sql later in the script or when I opened pgadmin.

This script is meant for setting up new databases and if I had tried it on a new database the create extension would have failed right away.

My bad. Thanks for the help, Erwin.

score -1 · Answer 5 · answered Dec 29 '11 at 03:37

I ran my (python) script again and the CREATE EXTENSION ran without error. The script also executes this command

psql -d VGDB -U postgres -c "select * from pg_available_extensions order by name"

which includes the following in the result set:

pgcrypto           | 1.0             | 1.0               | cryptographic functions

So psql believes that it has installed pgcrypto.

Later in the same script when I execute

psql -d VGDB -U postgres -f sql/Create.Credentials.table.sql

where sql/Create.Credentials.table.sql includes this

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

I get this

psql:sql/Create.Credentials.table.sql:31: 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.

When I open pgadmin it does not show pgcrypto in either the VGDB or postgres databases even though the query above called by psql shows that pgcrypto is installed.

Could there be an issue with needing to commit after using psql to execute the "create extension ..." command? None of my other DDL or SQL statements require a commit when they get executed with psql.

It's starting to look like psql is just flakey. Is there another way to call "create extension pgcrypto" - e.g. with Python's database support classes - or does that have to be run through psql?

PostgreSQL pgp_sym_encrypt() broken in version 9.1

5 Answers5