Use of pgcrypto, can't call crypt? about p3 HOT 24 CLOSED

Hmm, it works for me (after installing the extension).
First command line psql:

sven=# create extension pgcrypto;
CREATE EXTENSION
sven=# select crypt('password', gen_salt('bf'));                                                                                           
                            crypt                             
--------------------------------------------------------------
 $2a$06$JFIB27woeCPgFr204HAbGeYtXFaGF7KiY8RJkE0QceycxRHottEPO
(1 row)

Now via P3:

"a P3Client(psql://sven@localhost:5432/)"
self isWorking.
 "true"
(self query: 'select crypt(''password'', gen_salt(''bf''))') firstColumnData.   
 "#('$2a$06$rWtbCpNkNDPXzNLwGABY0uYWeGEiR3rjzrXbaa0TlKSQtqPsv7Mz6')"
((self format: 'select crypt($1, gen_salt($2))') query: #('password' 'bf')) firstColumnData. "#('$2a$06$i1F0NmhodqeufqZM134mUutBq6L1hIN0FbebXCrsKUfaj4.ANsFSS')"

from p3.

This is what I get:

I can run this sql directly outside pharo as well and it succeeds. My postgresql version is 9.6.5

from p3.

What is the exact Pharo code that you are running ?

from p3.

(P3Client new url: 'psql://postgres@localhost/testdb') in: [ :client |
   [ client query: 'select crypt(''password'', gen_salt(''bf''))' ] 
	ensure: [ client close ] ].

from p3.

Other queries succeed, just no luck with crypt

from p3.

I think you did not install the extension, you can query the available/installed extensions with

select * from pg_available_extensions;

from p3.

No I do have the extension installed, because it works from a psql command line.

from p3.

The error that you get is the same one I got command line psql before I installed the extension.

What do you get from

self query: 'select * from pg_available_extensions where installed_version is not null'.

I get two installed extensions (plpgsql & pgcrypto).

from p3.

I get just pgcrypto:

from p3.

Maybe both extensions are required. At least it might not work if plpgsql is not there.

https://dba.stackexchange.com/questions/66195/why-does-plpgsql-not-appear-in-the-pg-extension-table

from p3.

Then I don't know.

We have the same version (more or less).

self serverVersion  "'9.6.13'"

Also, your error says 'gen_salt' does not exist, not 'crypto'.

Do all unit tests pass ? In particular, #testSqlFunction ?

from p3.

@astares that does seem quite strange indeed

but still, he says command line psql works ...

maybe extensions are defined per database ? hopefully not by session

from p3.

Same issue, with plpgsql extension installed:

And it does work outside of pharo. All test pass except: testChronolgy

from p3.

One more datapoint: trying to create pgcrypto extension from within pharo complains that it already exists (as one would expect). I am clueless...

from p3.

What if you inspect the function via the system catalog ?

(self query: 'select oid, proname, prosrc,probin from pg_proc where proname=''gen_salt''') firstRecord.
"#(35325 'gen_salt' 'pg_gen_salt' '$libdir/pgcrypto')" 

from p3.

#(539626 'gen_salt' 'pg_gen_salt' '$libdir/pgcrypto')

from p3.

The thing is, if I mistype the function name, I get your error (see attached screenshot).

Are you sure you made no such error ?

from p3.

I've copied your example and it still won't run for me, I'll retry in a clean image to make sure there is nothing else interfering.

Same thing in stock pharo image (7, 64bit) with just P3 added. My postgresql runs in docker, but I don't see how that would be a problem.

from p3.

Yes, maybe, restarting postgres might be good too.

I really don't understand what is going on.

Maybe someone else should also try this.

I am on macOS using 64-bit Pharo 7.0.3 BTW.

from p3.

A screenshot like the following might be useful too, to compare the properties:

Not that it is related, but I would like to know why #testChronolgy does not work for you.

from p3.

We seem to have mostly an identical setup:

And I'm also on macOS (Mojave), same version pharo, both 64 bit.

from p3.

Yes, I see no major differences either, strange.

You are running as user postgres instead of as a local user, but that is probably not important.

The #testChronology failure is probably related to your database being in UTC.

from p3.

I finally figured it out. My database had an additional schema (apart from public) and I created the extension in this schema by accident. Therefore when running crypt() in the console it worked because the schema was in effect. But running through P3/pharo I accessed the public schema and there the crypt and gen_salt functions where undefined.

Lesson learned, beware in what schema to run create extension. Incidentally when creating the extension in the public schema there is no need to qualify schema when using crypt() in the non-public schema.

Thanks for all the help, and sorry about wasting your time!

from p3.

OK, thx for the feedback.

I guess we should have done a

select current_schema()

on the connection to figure this out.

from p3.