Use of pgcrypto, can't call crypt?

[pidu]

I'm attempting to use pgcrypto, specifically this: select crypt('password', gen_salt('bf')) and I'm unable to make it work. I've tried with prepared statements and formatted statements alike trying to replace 'bf' with a parameter $2 and trying to just leave it inline with the single quotes escaped like so: gen_salt(''bf''). I thought this would be straightforward but I'm a bit stumped.

[svenvc]

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')"

[pidu]

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

[svenvc]

What is the exact Pharo code that you are running ?

[pidu]

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

[pidu]

Other queries succeed, just no luck with crypt

[svenvc]

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

select * from pg_available_extensions;

[pidu]

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

[svenvc]

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).

[pidu]

I get just pgcrypto:

[astares]

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

[svenvc]

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 ?

[svenvc]

@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

[pidu]

Same issue, with plpgsql extension installed:

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

[pidu]

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

[svenvc]

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')" 

[pidu]

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

[svenvc]

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

Are you sure you made no such error ?

[pidu]

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.

[svenvc]

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.

[svenvc]

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.

[pidu]

We seem to have mostly an identical setup:

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

[svenvc]

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.

[pidu]

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!

[svenvc]

OK, thx for the feedback.

I guess we should have done a

select current_schema()

on the connection to figure this out.