Re: Raising the SCRAM iteration count

> On 10 Dec 2022, at 01:15, Andres Freund <andres(at)anarazel(dot)de> wrote:
> On 2022-12-09 11:55:07 +0100, Daniel Gustafsson wrote:

>> The attached introduces a scram_iteration_count GUC with a default of 15000
>> (still conservative, from RFC7677) and a minimum of 4096. Since the iterations
>> are stored per secret it can be altered with backwards compatibility.
>
> I am extremely doubtful it's a good idea to increase the default (if anything
> the opposite). 0.1 seconds is many times the connection establishment
> overhead, even over network. I've seen users complain about postgres
> connection establishment overhead being high, and it just turned out to be due
> to scram - yes, they ended up switching to md5, because that was the only
> viable alternative.

That's a fair point. For the record I don't think we should raise the default
to match 0.1 seconds, but we should make the option available to those who want
it. If we provide a GUC for the iteration count which has a lower limit than
todays hardcoded value, then maybe we can help workloads with long-lived
connections who want increased on-disk safety as well as workloads where low
connection establishment is critical (or where the env is constrained like in
Heikki's example).

> PGPASSWORD=passme pgbench -n -C -f ~/tmp/select.sql -h 127.0.0.1 -T10 -U passme
>
> md5: tps = 158.577609
> scram: tps = 38.196362

Lowering the minimum for scram_iteration_count I tried out the patch on a set
of iteration counts of interest. Values are averaged over three runs, using
the same pgbench setup you had above with basically a noop select.sql. The
relative difference between the values are way off from your results, but I
haven't done much digging to figure that out yet (different OpenSSL versions
might be one factor).

md5: tps = 154.052690
scram 1: tps = 150.060285
scram 1024: tps = 138.191224
scram 4096: tps = 115.197533
scram 15000: tps = 75.156399

For the fun of it, 100000 iterations yields tps = 20.822393.

SCRAM with an iteration count of 1 still provides a lot of benefits over md5,
so if we can make those comparable in performance then that could be a way
forward (with the tradeoffs properly documented).

--
Daniel Gustafsson https://vmware.com/