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 ?
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 :
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 :