psql PGP_SYM_DECRYPT : HINT: No function matches the given name and argument types

Question

Since this morning :

psql PGP_SYM_DECRYPT : HINT:  No function matches the given name and argument types.

LINE 1: select login,PGP_SYM_DECRYPT(password,'*******') from pa...
                     ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Extenstion pg_crypto is present.

so I can't select any data from previously pgp_sym_encrypt queries... what goes wrong ? how to solve that ?

Answer 1

To follow investigations I build a copy of the table with less columns. And tried out the same password visual check :

perso=# select quoi,login,pgp_sym_decrypt(password::bytea,'someKEY') from tempo where quoi ilike '%somesite%' ;                                                                          
     quoi     |         login         | pgp_sym_decrypt                                                                                                                                      
--------------+-----------------------+-----------------
 somesite.com | [email protected] | foobar
(1 row)

perso=# select quoi,login,pgp_sym_decrypt(password,'someKEY') from tempo where quoi ilike '%somesite%' ;                                                                                 
     quoi     |         login         | pgp_sym_decrypt
--------------+-----------------------+-----------------
 somesite.com | [email protected] | foobar
(1 row)                                                                                                                                                                                      

perso=# \d+ tempo
                                    Table "public.tempo"
  Column  |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description
----------+---------+-----------+----------+---------+----------+--------------+-------------
 ref      | integer |           |          |         | plain    |              |
 quoi     | text    |           |          |         | extended |              |
 login    | text    |           |          |         | extended |              |
 password | bytea   |           |          |         | extended |              |

there is no more problems here so there is an issue on the table or on the data store-mode.

perso=# \d+ passwd                                                                                                                                                                  
                                                  Table "public.passwd"                                                                                                             
  Column  |  Type   | Collation | Nullable |               Default               | Storage  | Stats target | Description                                                            
----------+---------+-----------+----------+-------------------------------------+----------+--------------+-------------                                                           
 ref      | integer |           | not null | nextval('passwd_ref_seq'::regclass) | plain    |              |                                                                        
 quoi     | text    |           | not null |                                     | extended |              |                                                                        
 login    | text    |           | not null |                                     | extended |              |                                                                        
 password | text    |           | not null |                                     | extended |              |                                                                        
Indexes:                                                                                                                                                                             
    "passwd_pkey" PRIMARY KEY, btree (ref)                                                                                                                                            
    "passwd_password_key" UNIQUE CONSTRAINT, btree (password)                                                                                                                                 
perso=#

perso=# select quoi,login,pgp_sym_decrypt(password,'someKEY') from passwd where quoi ilike '%somesite%' ;
ERROR:  function pgp_sym_decrypt(text, unknown) does not exist
LINE 1: select quoi,login,pgp_sym_decrypt(password,'someKEY') fr...
                          ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
perso=# 

here we have back error & detect text on password column. So test :

• alter table => BIG FAIL it reencoded a second time all datas..
• drop test table passwd
• restore test table passwd
• copy in tempo table the datas
• delete datas in passwd table
• alter the passwd table
• copy data back in passwd table

as a procedure for my test.

so I did :

perso=# 
perso=# delete from passwd ;
DELETE 106
perso=# alter table passwd alter column password type bytea using PGP_SYM_ENCRYPT(password::text,'someKEY');
ALTER TABLE
perso=# \d+ passwd
                                                  Table "public.passwd"
  Column  |  Type   | Collation | Nullable |               Default               | Storage  | Stats target | Description 
----------+---------+-----------+----------+-------------------------------------+----------+--------------+-------------
 ref      | integer |           | not null | nextval('passwd_ref_seq'::regclass) | plain    |              | 
 quoi     | text    |           | not null |                                     | extended |              | 
 login    | text    |           | not null |                                     | extended |              | 
 password | bytea   |           | not null |                                     | extended |              | 
Indexes:
    "passwd_pkey" PRIMARY KEY, btree (ref)
    "passwd_password_key" UNIQUE CONSTRAINT, btree (password)

perso=# insert into passwd (ref,quoi,login,password) select ref,quoi,login,password::bytea from tempo ;  
INSERT 0 106
perso=# 
perso=# 
perso=# select quoi,login,pgp_sym_decrypt(password,'someKEY') from passwd where quoi ilike '%somesite%' ;
     quoi     |         login         | pgp_sym_decrypt 
--------------+-----------------------+-----------------
 somesite.com | [email protected] | foobar
(1 row)

perso=# 

Then I backup the database; and applied similar procedure to fix the issue with success. It might be a better way to do that but this way I understand the process.

both solution in there :

• using queries with column:bytea syntax
• fix the column type to be:bytea

Answer 2

If the extension was not updated and it was working yesterday, the issue is likely with the search_path. Make sure the path of the extension is set in the search_path.

So, to check where the extension is installed, type \dx and note the schema. Then, type show search_path; and make sure the extension's schema is listed there (it could be public). If not, add the extension's schema to the search path.