Re: Raising the SCRAM iteration count

From: Daniel Gustafsson <daniel(at)yesql(dot)se>
To: Andres Freund <andres(at)anarazel(dot)de>
Cc: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: Raising the SCRAM iteration count
Date: 2022-12-10 23:46:23
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2022-12-11 00:00:13 Re: Error-safe user functions
Previous Message Maciek Sakrejda 2022-12-10 23:17:22 GetNewObjectId question