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 ?

The error message should contain more lines than that, including a description of what argument types it believes you to be using. — jjanes, Aug 31 '20 at 13:00
no the line above is the query Itself that's why I didn't copy it — francois P, Aug 31 '20 at 13:05
just as mentionned in the error message `select login,PGP_SYM_DECRYPT(password,'*******') from passwd where login ilike '%foobar%" and date = current_date ; ` the where clause here has no sense for helpers @jjanes I think ;-) — francois P, Aug 31 '20 at 13:46

JGH · Answer 1 · 2020-08-31T12:16:42.507

1

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.

edited Aug 31 '20 at 12:16

answered Aug 31 '20 at 11:55

JGH

15,928
4
31
48

I don't understand what to do with that. I do not want to apply it to the whole shemas :) – francois P Aug 31 '20 at 12:12
ok it it the case I have : "$user", public as search_path variable . – francois P Aug 31 '20 at 12:24
I have no more extensions juste pl/sql & pgcrypto – francois P Aug 31 '20 at 12:31

score 0 · Accepted Answer · edited Sep 07 '20 at 20:59

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 | somename@somewhere.fr | 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 | somename@somewhere.fr | 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 | somename@somewhere.fr | 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

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

2 Answers2