public inbox for [email protected]help / color / mirror / Atom feed
[PATCH v8 6/7] Row pattern recognition patch (tests). 5+ messages / 4 participants [nested] [flat]
* [PATCH v8 6/7] Row pattern recognition patch (tests). @ 2023-09-25 05:01 Tatsuo Ishii <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: Tatsuo Ishii @ 2023-09-25 05:01 UTC (permalink / raw) --- src/test/regress/expected/rpr.out | 594 +++++++++++++++++++++++++++++ src/test/regress/parallel_schedule | 2 +- src/test/regress/sql/rpr.sql | 292 ++++++++++++++ 3 files changed, 887 insertions(+), 1 deletion(-) create mode 100644 src/test/regress/expected/rpr.out create mode 100644 src/test/regress/sql/rpr.sql diff --git a/src/test/regress/expected/rpr.out b/src/test/regress/expected/rpr.out new file mode 100644 index 0000000000..3e7ad943c9 --- /dev/null +++ b/src/test/regress/expected/rpr.out @@ -0,0 +1,594 @@ +-- +-- Test for row pattern definition clause +-- +CREATE TEMP TABLE stock ( + company TEXT, + tdate DATE, + price INTEGER +); +INSERT INTO stock VALUES ('company1', '2023-07-01', 100); +INSERT INTO stock VALUES ('company1', '2023-07-02', 200); +INSERT INTO stock VALUES ('company1', '2023-07-03', 150); +INSERT INTO stock VALUES ('company1', '2023-07-04', 140); +INSERT INTO stock VALUES ('company1', '2023-07-05', 150); +INSERT INTO stock VALUES ('company1', '2023-07-06', 90); +INSERT INTO stock VALUES ('company1', '2023-07-07', 110); +INSERT INTO stock VALUES ('company1', '2023-07-08', 130); +INSERT INTO stock VALUES ('company1', '2023-07-09', 120); +INSERT INTO stock VALUES ('company1', '2023-07-10', 130); +INSERT INTO stock VALUES ('company2', '2023-07-01', 50); +INSERT INTO stock VALUES ('company2', '2023-07-02', 2000); +INSERT INTO stock VALUES ('company2', '2023-07-03', 1500); +INSERT INTO stock VALUES ('company2', '2023-07-04', 1400); +INSERT INTO stock VALUES ('company2', '2023-07-05', 1500); +INSERT INTO stock VALUES ('company2', '2023-07-06', 60); +INSERT INTO stock VALUES ('company2', '2023-07-07', 1100); +INSERT INTO stock VALUES ('company2', '2023-07-08', 1300); +INSERT INTO stock VALUES ('company2', '2023-07-09', 1200); +INSERT INTO stock VALUES ('company2', '2023-07-10', 1300); +SELECT * FROM stock; + company | tdate | price +----------+------------+------- + company1 | 07-01-2023 | 100 + company1 | 07-02-2023 | 200 + company1 | 07-03-2023 | 150 + company1 | 07-04-2023 | 140 + company1 | 07-05-2023 | 150 + company1 | 07-06-2023 | 90 + company1 | 07-07-2023 | 110 + company1 | 07-08-2023 | 130 + company1 | 07-09-2023 | 120 + company1 | 07-10-2023 | 130 + company2 | 07-01-2023 | 50 + company2 | 07-02-2023 | 2000 + company2 | 07-03-2023 | 1500 + company2 | 07-04-2023 | 1400 + company2 | 07-05-2023 | 1500 + company2 | 07-06-2023 | 60 + company2 | 07-07-2023 | 1100 + company2 | 07-08-2023 | 1300 + company2 | 07-09-2023 | 1200 + company2 | 07-10-2023 | 1300 +(20 rows) + +-- basic test using PREV +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w, + nth_value(tdate, 2) OVER w AS nth_second + FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (START UP+ DOWN+) + DEFINE + START AS TRUE, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); + company | tdate | price | first_value | last_value | nth_second +----------+------------+-------+-------------+------------+------------ + company1 | 07-01-2023 | 100 | 100 | 140 | 07-02-2023 + company1 | 07-02-2023 | 200 | | | + company1 | 07-03-2023 | 150 | | | + company1 | 07-04-2023 | 140 | | | + company1 | 07-05-2023 | 150 | | | + company1 | 07-06-2023 | 90 | 90 | 120 | 07-07-2023 + company1 | 07-07-2023 | 110 | | | + company1 | 07-08-2023 | 130 | | | + company1 | 07-09-2023 | 120 | | | + company1 | 07-10-2023 | 130 | | | + company2 | 07-01-2023 | 50 | 50 | 1400 | 07-02-2023 + company2 | 07-02-2023 | 2000 | | | + company2 | 07-03-2023 | 1500 | | | + company2 | 07-04-2023 | 1400 | | | + company2 | 07-05-2023 | 1500 | | | + company2 | 07-06-2023 | 60 | 60 | 1200 | 07-07-2023 + company2 | 07-07-2023 | 1100 | | | + company2 | 07-08-2023 | 1300 | | | + company2 | 07-09-2023 | 1200 | | | + company2 | 07-10-2023 | 1300 | | | +(20 rows) + +-- last_value() should remain consistent +SELECT company, tdate, price, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (START UP+ DOWN+) + DEFINE + START AS TRUE, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); + company | tdate | price | last_value +----------+------------+-------+------------ + company1 | 07-01-2023 | 100 | 140 + company1 | 07-02-2023 | 200 | + company1 | 07-03-2023 | 150 | + company1 | 07-04-2023 | 140 | + company1 | 07-05-2023 | 150 | + company1 | 07-06-2023 | 90 | 120 + company1 | 07-07-2023 | 110 | + company1 | 07-08-2023 | 130 | + company1 | 07-09-2023 | 120 | + company1 | 07-10-2023 | 130 | + company2 | 07-01-2023 | 50 | 1400 + company2 | 07-02-2023 | 2000 | + company2 | 07-03-2023 | 1500 | + company2 | 07-04-2023 | 1400 | + company2 | 07-05-2023 | 1500 | + company2 | 07-06-2023 | 60 | 1200 + company2 | 07-07-2023 | 1100 | + company2 | 07-08-2023 | 1300 | + company2 | 07-09-2023 | 1200 | + company2 | 07-10-2023 | 1300 | +(20 rows) + +-- omit "START" in DEFINE but it is ok because "START AS TRUE" is +-- implicitly defined. per spec. +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w, + nth_value(tdate, 2) OVER w AS nth_second + FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (START UP+ DOWN+) + DEFINE + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); + company | tdate | price | first_value | last_value | nth_second +----------+------------+-------+-------------+------------+------------ + company1 | 07-01-2023 | 100 | 100 | 140 | 07-02-2023 + company1 | 07-02-2023 | 200 | | | + company1 | 07-03-2023 | 150 | | | + company1 | 07-04-2023 | 140 | | | + company1 | 07-05-2023 | 150 | | | + company1 | 07-06-2023 | 90 | 90 | 120 | 07-07-2023 + company1 | 07-07-2023 | 110 | | | + company1 | 07-08-2023 | 130 | | | + company1 | 07-09-2023 | 120 | | | + company1 | 07-10-2023 | 130 | | | + company2 | 07-01-2023 | 50 | 50 | 1400 | 07-02-2023 + company2 | 07-02-2023 | 2000 | | | + company2 | 07-03-2023 | 1500 | | | + company2 | 07-04-2023 | 1400 | | | + company2 | 07-05-2023 | 1500 | | | + company2 | 07-06-2023 | 60 | 60 | 1200 | 07-07-2023 + company2 | 07-07-2023 | 1100 | | | + company2 | 07-08-2023 | 1300 | | | + company2 | 07-09-2023 | 1200 | | | + company2 | 07-10-2023 | 1300 | | | +(20 rows) + +-- the first row start with less than or equal to 100 +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (LOWPRICE UP+ DOWN+) + DEFINE + LOWPRICE AS price <= 100, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); + company | tdate | price | first_value | last_value +----------+------------+-------+-------------+------------ + company1 | 07-01-2023 | 100 | 100 | 140 + company1 | 07-02-2023 | 200 | | + company1 | 07-03-2023 | 150 | | + company1 | 07-04-2023 | 140 | | + company1 | 07-05-2023 | 150 | | + company1 | 07-06-2023 | 90 | 90 | 120 + company1 | 07-07-2023 | 110 | | + company1 | 07-08-2023 | 130 | | + company1 | 07-09-2023 | 120 | | + company1 | 07-10-2023 | 130 | | + company2 | 07-01-2023 | 50 | 50 | 1400 + company2 | 07-02-2023 | 2000 | | + company2 | 07-03-2023 | 1500 | | + company2 | 07-04-2023 | 1400 | | + company2 | 07-05-2023 | 1500 | | + company2 | 07-06-2023 | 60 | 60 | 1200 + company2 | 07-07-2023 | 1100 | | + company2 | 07-08-2023 | 1300 | | + company2 | 07-09-2023 | 1200 | | + company2 | 07-10-2023 | 1300 | | +(20 rows) + +-- second row raises 120% +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (LOWPRICE UP+ DOWN+) + DEFINE + LOWPRICE AS price <= 100, + UP AS price > PREV(price) * 1.2, + DOWN AS price < PREV(price) +); + company | tdate | price | first_value | last_value +----------+------------+-------+-------------+------------ + company1 | 07-01-2023 | 100 | 100 | 140 + company1 | 07-02-2023 | 200 | | + company1 | 07-03-2023 | 150 | | + company1 | 07-04-2023 | 140 | | + company1 | 07-05-2023 | 150 | | + company1 | 07-06-2023 | 90 | | + company1 | 07-07-2023 | 110 | | + company1 | 07-08-2023 | 130 | | + company1 | 07-09-2023 | 120 | | + company1 | 07-10-2023 | 130 | | + company2 | 07-01-2023 | 50 | 50 | 1400 + company2 | 07-02-2023 | 2000 | | + company2 | 07-03-2023 | 1500 | | + company2 | 07-04-2023 | 1400 | | + company2 | 07-05-2023 | 1500 | | + company2 | 07-06-2023 | 60 | | + company2 | 07-07-2023 | 1100 | | + company2 | 07-08-2023 | 1300 | | + company2 | 07-09-2023 | 1200 | | + company2 | 07-10-2023 | 1300 | | +(20 rows) + +-- using NEXT +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (START UPDOWN) + DEFINE + START AS TRUE, + UPDOWN AS price > PREV(price) AND price > NEXT(price) +); + company | tdate | price | first_value | last_value +----------+------------+-------+-------------+------------ + company1 | 07-01-2023 | 100 | 100 | 200 + company1 | 07-02-2023 | 200 | | + company1 | 07-03-2023 | 150 | | + company1 | 07-04-2023 | 140 | 140 | 150 + company1 | 07-05-2023 | 150 | | + company1 | 07-06-2023 | 90 | | + company1 | 07-07-2023 | 110 | 110 | 130 + company1 | 07-08-2023 | 130 | | + company1 | 07-09-2023 | 120 | | + company1 | 07-10-2023 | 130 | | + company2 | 07-01-2023 | 50 | 50 | 2000 + company2 | 07-02-2023 | 2000 | | + company2 | 07-03-2023 | 1500 | | + company2 | 07-04-2023 | 1400 | 1400 | 1500 + company2 | 07-05-2023 | 1500 | | + company2 | 07-06-2023 | 60 | | + company2 | 07-07-2023 | 1100 | 1100 | 1300 + company2 | 07-08-2023 | 1300 | | + company2 | 07-09-2023 | 1200 | | + company2 | 07-10-2023 | 1300 | | +(20 rows) + +-- using AFTER MATCH SKIP TO NEXT ROW +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP TO NEXT ROW + INITIAL + PATTERN (START UPDOWN) + DEFINE + START AS TRUE, + UPDOWN AS price > PREV(price) AND price > NEXT(price) +); + company | tdate | price | first_value | last_value +----------+------------+-------+-------------+------------ + company1 | 07-01-2023 | 100 | 100 | 200 + company1 | 07-02-2023 | 200 | | + company1 | 07-03-2023 | 150 | | + company1 | 07-04-2023 | 140 | 140 | 150 + company1 | 07-05-2023 | 150 | | + company1 | 07-06-2023 | 90 | | + company1 | 07-07-2023 | 110 | 110 | 130 + company1 | 07-08-2023 | 130 | | + company1 | 07-09-2023 | 120 | | + company1 | 07-10-2023 | 130 | | + company2 | 07-01-2023 | 50 | 50 | 2000 + company2 | 07-02-2023 | 2000 | | + company2 | 07-03-2023 | 1500 | | + company2 | 07-04-2023 | 1400 | 1400 | 1500 + company2 | 07-05-2023 | 1500 | | + company2 | 07-06-2023 | 60 | | + company2 | 07-07-2023 | 1100 | 1100 | 1300 + company2 | 07-08-2023 | 1300 | | + company2 | 07-09-2023 | 1200 | | + company2 | 07-10-2023 | 1300 | | +(20 rows) + +-- match everything +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (A+) + DEFINE + A AS TRUE +); + company | tdate | price | first_value | last_value +----------+------------+-------+-------------+------------ + company1 | 07-01-2023 | 100 | 100 | 130 + company1 | 07-02-2023 | 200 | | + company1 | 07-03-2023 | 150 | | + company1 | 07-04-2023 | 140 | | + company1 | 07-05-2023 | 150 | | + company1 | 07-06-2023 | 90 | | + company1 | 07-07-2023 | 110 | | + company1 | 07-08-2023 | 130 | | + company1 | 07-09-2023 | 120 | | + company1 | 07-10-2023 | 130 | | + company2 | 07-01-2023 | 50 | 50 | 1300 + company2 | 07-02-2023 | 2000 | | + company2 | 07-03-2023 | 1500 | | + company2 | 07-04-2023 | 1400 | | + company2 | 07-05-2023 | 1500 | | + company2 | 07-06-2023 | 60 | | + company2 | 07-07-2023 | 1100 | | + company2 | 07-08-2023 | 1300 | | + company2 | 07-09-2023 | 1200 | | + company2 | 07-10-2023 | 1300 | | +(20 rows) + +-- backtracking with reclassification of rows +-- using AFTER MATCH SKIP PAST LAST ROW +SELECT company, tdate, price, first_value(tdate) OVER w, last_value(tdate) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (A+ B+) + DEFINE + A AS price > 100, + B AS price > 100 +); + company | tdate | price | first_value | last_value +----------+------------+-------+-------------+------------ + company1 | 07-01-2023 | 100 | | + company1 | 07-02-2023 | 200 | 07-02-2023 | 07-05-2023 + company1 | 07-03-2023 | 150 | | + company1 | 07-04-2023 | 140 | | + company1 | 07-05-2023 | 150 | | + company1 | 07-06-2023 | 90 | | + company1 | 07-07-2023 | 110 | 07-07-2023 | 07-10-2023 + company1 | 07-08-2023 | 130 | | + company1 | 07-09-2023 | 120 | | + company1 | 07-10-2023 | 130 | | + company2 | 07-01-2023 | 50 | | + company2 | 07-02-2023 | 2000 | 07-02-2023 | 07-05-2023 + company2 | 07-03-2023 | 1500 | | + company2 | 07-04-2023 | 1400 | | + company2 | 07-05-2023 | 1500 | | + company2 | 07-06-2023 | 60 | | + company2 | 07-07-2023 | 1100 | 07-07-2023 | 07-10-2023 + company2 | 07-08-2023 | 1300 | | + company2 | 07-09-2023 | 1200 | | + company2 | 07-10-2023 | 1300 | | +(20 rows) + +-- backtracking with reclassification of rows +-- using AFTER MATCH SKIP TO NEXT ROW +SELECT company, tdate, price, first_value(tdate) OVER w, last_value(tdate) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP TO NEXT ROW + INITIAL + PATTERN (A+ B+) + DEFINE + A AS price > 100, + B AS price > 100 +); + company | tdate | price | first_value | last_value +----------+------------+-------+-------------+------------ + company1 | 07-01-2023 | 100 | | + company1 | 07-02-2023 | 200 | 07-02-2023 | 07-05-2023 + company1 | 07-03-2023 | 150 | 07-03-2023 | 07-05-2023 + company1 | 07-04-2023 | 140 | 07-04-2023 | 07-05-2023 + company1 | 07-05-2023 | 150 | | + company1 | 07-06-2023 | 90 | | + company1 | 07-07-2023 | 110 | 07-07-2023 | 07-10-2023 + company1 | 07-08-2023 | 130 | 07-08-2023 | 07-10-2023 + company1 | 07-09-2023 | 120 | 07-09-2023 | 07-10-2023 + company1 | 07-10-2023 | 130 | | + company2 | 07-01-2023 | 50 | | + company2 | 07-02-2023 | 2000 | 07-02-2023 | 07-05-2023 + company2 | 07-03-2023 | 1500 | 07-03-2023 | 07-05-2023 + company2 | 07-04-2023 | 1400 | 07-04-2023 | 07-05-2023 + company2 | 07-05-2023 | 1500 | | + company2 | 07-06-2023 | 60 | | + company2 | 07-07-2023 | 1100 | 07-07-2023 | 07-10-2023 + company2 | 07-08-2023 | 1300 | 07-08-2023 | 07-10-2023 + company2 | 07-09-2023 | 1200 | 07-09-2023 | 07-10-2023 + company2 | 07-10-2023 | 1300 | | +(20 rows) + +-- +-- Aggregates +-- +-- using AFTER MATCH SKIP PAST LAST ROW +SELECT company, tdate, price, + first_value(price) OVER w, + last_value(price) OVER w, + max(price) OVER w, + min(price) OVER w, + sum(price) OVER w, + avg(price) OVER w, + count(price) OVER w +FROM stock +WINDOW w AS ( +PARTITION BY company +ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING +AFTER MATCH SKIP PAST LAST ROW +INITIAL +PATTERN (START UP+ DOWN+) +DEFINE +START AS TRUE, +UP AS price > PREV(price), +DOWN AS price < PREV(price) +); + company | tdate | price | first_value | last_value | max | min | sum | avg | count +----------+------------+-------+-------------+------------+------+-----+------+-----------------------+------- + company1 | 07-01-2023 | 100 | 100 | 140 | 200 | 100 | 590 | 147.5000000000000000 | 4 + company1 | 07-02-2023 | 200 | | | | | | | + company1 | 07-03-2023 | 150 | | | | | | | + company1 | 07-04-2023 | 140 | | | | | | | + company1 | 07-05-2023 | 150 | | | | | | | 0 + company1 | 07-06-2023 | 90 | 90 | 120 | 130 | 90 | 450 | 112.5000000000000000 | 4 + company1 | 07-07-2023 | 110 | | | | | | | + company1 | 07-08-2023 | 130 | | | | | | | + company1 | 07-09-2023 | 120 | | | | | | | + company1 | 07-10-2023 | 130 | | | | | | | 0 + company2 | 07-01-2023 | 50 | 50 | 1400 | 2000 | 50 | 4950 | 1237.5000000000000000 | 4 + company2 | 07-02-2023 | 2000 | | | | | | | + company2 | 07-03-2023 | 1500 | | | | | | | + company2 | 07-04-2023 | 1400 | | | | | | | + company2 | 07-05-2023 | 1500 | | | | | | | 0 + company2 | 07-06-2023 | 60 | 60 | 1200 | 1300 | 60 | 3660 | 915.0000000000000000 | 4 + company2 | 07-07-2023 | 1100 | | | | | | | + company2 | 07-08-2023 | 1300 | | | | | | | + company2 | 07-09-2023 | 1200 | | | | | | | + company2 | 07-10-2023 | 1300 | | | | | | | 0 +(20 rows) + +-- using AFTER MATCH SKIP TO NEXT ROW +SELECT company, tdate, price, + first_value(price) OVER w, + last_value(price) OVER w, + max(price) OVER w, + min(price) OVER w, + sum(price) OVER w, + avg(price) OVER w, + count(price) OVER w +FROM stock +WINDOW w AS ( +PARTITION BY company +ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING +AFTER MATCH SKIP TO NEXT ROW +INITIAL +PATTERN (START UP+ DOWN+) +DEFINE +START AS TRUE, +UP AS price > PREV(price), +DOWN AS price < PREV(price) +); + company | tdate | price | first_value | last_value | max | min | sum | avg | count +----------+------------+-------+-------------+------------+------+------+------+-----------------------+------- + company1 | 07-01-2023 | 100 | 100 | 140 | 200 | 100 | 590 | 147.5000000000000000 | 4 + company1 | 07-02-2023 | 200 | | | | | | | 0 + company1 | 07-03-2023 | 150 | | | | | | | 0 + company1 | 07-04-2023 | 140 | 140 | 90 | 150 | 90 | 380 | 126.6666666666666667 | 3 + company1 | 07-05-2023 | 150 | | | | | | | 0 + company1 | 07-06-2023 | 90 | 90 | 120 | 130 | 90 | 450 | 112.5000000000000000 | 4 + company1 | 07-07-2023 | 110 | 110 | 120 | 130 | 110 | 360 | 120.0000000000000000 | 3 + company1 | 07-08-2023 | 130 | | | | | | | 0 + company1 | 07-09-2023 | 120 | | | | | | | 0 + company1 | 07-10-2023 | 130 | | | | | | | 0 + company2 | 07-01-2023 | 50 | 50 | 1400 | 2000 | 50 | 4950 | 1237.5000000000000000 | 4 + company2 | 07-02-2023 | 2000 | | | | | | | 0 + company2 | 07-03-2023 | 1500 | | | | | | | 0 + company2 | 07-04-2023 | 1400 | 1400 | 60 | 1500 | 60 | 2960 | 986.6666666666666667 | 3 + company2 | 07-05-2023 | 1500 | | | | | | | 0 + company2 | 07-06-2023 | 60 | 60 | 1200 | 1300 | 60 | 3660 | 915.0000000000000000 | 4 + company2 | 07-07-2023 | 1100 | 1100 | 1200 | 1300 | 1100 | 3600 | 1200.0000000000000000 | 3 + company2 | 07-08-2023 | 1300 | | | | | | | 0 + company2 | 07-09-2023 | 1200 | | | | | | | 0 + company2 | 07-10-2023 | 1300 | | | | | | | 0 +(20 rows) + +-- +-- Error cases +-- +-- row pattern definition variable name must not appear more than once +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + ORDER BY tdate + INITIAL + PATTERN (START UP+ DOWN+) + DEFINE + START AS TRUE, + UP AS price > PREV(price), + DOWN AS price < PREV(price), + UP AS price > PREV(price) +); +ERROR: syntax error at or near "ORDER" +LINE 6: ORDER BY tdate + ^ +-- pattern variable name must appear in DEFINE +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (START UP+ DOWN+ END) + DEFINE + START AS TRUE, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); +ERROR: syntax error at or near "END" +LINE 8: PATTERN (START UP+ DOWN+ END) + ^ +-- FRAME must start at current row when row patttern recognition is used +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (START UP+ DOWN+) + DEFINE + START AS TRUE, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); +ERROR: FRAME must start at current row when row patttern recognition is used +-- SEEK is not supported +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP TO NEXT ROW + SEEK + PATTERN (START UP+ DOWN+) + DEFINE + START AS TRUE, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); +ERROR: SEEK is not supported +LINE 8: SEEK + ^ +HINT: Use INITIAL. diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 4df9d8503b..896531002b 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -98,7 +98,7 @@ test: publication subscription # Another group of parallel tests # select_views depends on create_view # ---------- -test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combocid tsearch tsdicts foreign_data window xmlmap functional_deps advisory_lock indirect_toast equivclass +test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combocid tsearch tsdicts foreign_data window xmlmap functional_deps advisory_lock indirect_toast equivclass rpr # ---------- # Another group of parallel tests (JSON related) diff --git a/src/test/regress/sql/rpr.sql b/src/test/regress/sql/rpr.sql new file mode 100644 index 0000000000..846e1af2d7 --- /dev/null +++ b/src/test/regress/sql/rpr.sql @@ -0,0 +1,292 @@ +-- +-- Test for row pattern definition clause +-- + +CREATE TEMP TABLE stock ( + company TEXT, + tdate DATE, + price INTEGER +); +INSERT INTO stock VALUES ('company1', '2023-07-01', 100); +INSERT INTO stock VALUES ('company1', '2023-07-02', 200); +INSERT INTO stock VALUES ('company1', '2023-07-03', 150); +INSERT INTO stock VALUES ('company1', '2023-07-04', 140); +INSERT INTO stock VALUES ('company1', '2023-07-05', 150); +INSERT INTO stock VALUES ('company1', '2023-07-06', 90); +INSERT INTO stock VALUES ('company1', '2023-07-07', 110); +INSERT INTO stock VALUES ('company1', '2023-07-08', 130); +INSERT INTO stock VALUES ('company1', '2023-07-09', 120); +INSERT INTO stock VALUES ('company1', '2023-07-10', 130); +INSERT INTO stock VALUES ('company2', '2023-07-01', 50); +INSERT INTO stock VALUES ('company2', '2023-07-02', 2000); +INSERT INTO stock VALUES ('company2', '2023-07-03', 1500); +INSERT INTO stock VALUES ('company2', '2023-07-04', 1400); +INSERT INTO stock VALUES ('company2', '2023-07-05', 1500); +INSERT INTO stock VALUES ('company2', '2023-07-06', 60); +INSERT INTO stock VALUES ('company2', '2023-07-07', 1100); +INSERT INTO stock VALUES ('company2', '2023-07-08', 1300); +INSERT INTO stock VALUES ('company2', '2023-07-09', 1200); +INSERT INTO stock VALUES ('company2', '2023-07-10', 1300); + +SELECT * FROM stock; + +-- basic test using PREV +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w, + nth_value(tdate, 2) OVER w AS nth_second + FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (START UP+ DOWN+) + DEFINE + START AS TRUE, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); + +-- last_value() should remain consistent +SELECT company, tdate, price, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (START UP+ DOWN+) + DEFINE + START AS TRUE, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); + +-- omit "START" in DEFINE but it is ok because "START AS TRUE" is +-- implicitly defined. per spec. +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w, + nth_value(tdate, 2) OVER w AS nth_second + FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (START UP+ DOWN+) + DEFINE + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); + +-- the first row start with less than or equal to 100 +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (LOWPRICE UP+ DOWN+) + DEFINE + LOWPRICE AS price <= 100, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); + +-- second row raises 120% +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (LOWPRICE UP+ DOWN+) + DEFINE + LOWPRICE AS price <= 100, + UP AS price > PREV(price) * 1.2, + DOWN AS price < PREV(price) +); + +-- using NEXT +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (START UPDOWN) + DEFINE + START AS TRUE, + UPDOWN AS price > PREV(price) AND price > NEXT(price) +); + +-- using AFTER MATCH SKIP TO NEXT ROW +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP TO NEXT ROW + INITIAL + PATTERN (START UPDOWN) + DEFINE + START AS TRUE, + UPDOWN AS price > PREV(price) AND price > NEXT(price) +); + +-- match everything + +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (A+) + DEFINE + A AS TRUE +); + +-- backtracking with reclassification of rows +-- using AFTER MATCH SKIP PAST LAST ROW +SELECT company, tdate, price, first_value(tdate) OVER w, last_value(tdate) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (A+ B+) + DEFINE + A AS price > 100, + B AS price > 100 +); + +-- backtracking with reclassification of rows +-- using AFTER MATCH SKIP TO NEXT ROW +SELECT company, tdate, price, first_value(tdate) OVER w, last_value(tdate) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP TO NEXT ROW + INITIAL + PATTERN (A+ B+) + DEFINE + A AS price > 100, + B AS price > 100 +); + +-- +-- Aggregates +-- + +-- using AFTER MATCH SKIP PAST LAST ROW +SELECT company, tdate, price, + first_value(price) OVER w, + last_value(price) OVER w, + max(price) OVER w, + min(price) OVER w, + sum(price) OVER w, + avg(price) OVER w, + count(price) OVER w +FROM stock +WINDOW w AS ( +PARTITION BY company +ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING +AFTER MATCH SKIP PAST LAST ROW +INITIAL +PATTERN (START UP+ DOWN+) +DEFINE +START AS TRUE, +UP AS price > PREV(price), +DOWN AS price < PREV(price) +); + +-- using AFTER MATCH SKIP TO NEXT ROW +SELECT company, tdate, price, + first_value(price) OVER w, + last_value(price) OVER w, + max(price) OVER w, + min(price) OVER w, + sum(price) OVER w, + avg(price) OVER w, + count(price) OVER w +FROM stock +WINDOW w AS ( +PARTITION BY company +ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING +AFTER MATCH SKIP TO NEXT ROW +INITIAL +PATTERN (START UP+ DOWN+) +DEFINE +START AS TRUE, +UP AS price > PREV(price), +DOWN AS price < PREV(price) +); + +-- +-- Error cases +-- + +-- row pattern definition variable name must not appear more than once +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + ORDER BY tdate + INITIAL + PATTERN (START UP+ DOWN+) + DEFINE + START AS TRUE, + UP AS price > PREV(price), + DOWN AS price < PREV(price), + UP AS price > PREV(price) +); + +-- pattern variable name must appear in DEFINE +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (START UP+ DOWN+ END) + DEFINE + START AS TRUE, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); + +-- FRAME must start at current row when row patttern recognition is used +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (START UP+ DOWN+) + DEFINE + START AS TRUE, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); + +-- SEEK is not supported +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP TO NEXT ROW + SEEK + PATTERN (START UP+ DOWN+) + DEFINE + START AS TRUE, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); -- 2.25.1 ----Next_Part(Mon_Sep_25_14_26_30_2023_752)-- Content-Type: Text/X-Patch; charset=us-ascii Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="v8-0007-Allow-to-print-raw-parse-tree.patch" ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Modern SHA2- based password hashes for pgcrypto @ 2025-02-07 08:17 Japin Li <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Japin Li @ 2025-02-07 08:17 UTC (permalink / raw) To: Alvaro Herrera <[email protected]>; +Cc: Bernd Helmle <[email protected]>; pgsql-hackers On Thu, 06 Feb 2025 at 11:20, Alvaro Herrera <[email protected]> wrote: > On 2025-Jan-28, Bernd Helmle wrote: > >> Python's passlib is very strict when it comes to supported characters >> within a salt string. It rejects everything thats not matching '[./0- >> 9A-Za-z]'. So when you provide the example above you get > > The reason it uses these chars is that in their scheme the salt bytes > are base64-encoded. > > The passlib docs has this page about the "modular crypt format": > https://passlib.readthedocs.io/en/stable/modular_crypt_format.html > > and they point this other page as a "modern, non-ambiguous standard": > https://github.com/P-H-C/phc-string-format/blob/master/phc-sf-spec.md > About the salt, this last document says: > > The role of salts is to achieve uniqueness. A random salt is fine for > that as long as its length is sufficient; a 16-byte salt would work > well (by definition, UUID are very good salts, and they encode over > exactly 16 bytes). 16 bytes encode as 22 characters in B64. Functions > should disallow salt values that are too small for security (4 bytes > should be viewed as an absolute minimum). > > This "Password Hashing Competition" organization hardly seems an > authority though. It'd be great to have an IETF standard about this ... Yeah. Since there is no standard, how do we handle this? I prefer to use the strict mode like passlib. -- Regrads, Japin Li ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Modern SHA2- based password hashes for pgcrypto @ 2025-02-07 09:31 Alvaro Herrera <[email protected]> parent: Japin Li <[email protected]> 0 siblings, 2 replies; 5+ messages in thread From: Alvaro Herrera @ 2025-02-07 09:31 UTC (permalink / raw) To: Japin Li <[email protected]>; +Cc: Bernd Helmle <[email protected]>; pgsql-hackers On 2025-Feb-07, Japin Li wrote: > Since there is no standard, how do we handle this? I prefer to use > the strict mode like passlib. I definitely like that passlib have documented their thought process thoroughly. I think using their strict mode is good on principle, but if we're going to do that, then the salt string should not be used verbatim, but instead base64-decoded first to get the actual salt bytes, like they do. Does this break compabitibility with other systems? Are passlib-generated password hashes incompatible with, say, "openssl passwd" which you (Bernd) mentioned at the beginning of the thread? Maybe if the password hashes are no longer compatible, then we should ditch the idea of restricting salts to base64 chars and accept the whole range of bytes, like Drepper. But in any case ISTM we should reject, as they suggest, the use of less than 4 bytes of salt (and should perhaps settle for a default of 16, as passlib suggests). I suppose this is why passlib returns NULL with empty salt. What we should do in that case IMO is ereport(ERROR). -- Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/ "Porque Kim no hacía nada, pero, eso sí, con extraordinario éxito" ("Kim", Kipling) ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Modern SHA2- based password hashes for pgcrypto @ 2025-02-08 02:16 Japin Li <[email protected]> parent: Alvaro Herrera <[email protected]> 1 sibling, 0 replies; 5+ messages in thread From: Japin Li @ 2025-02-08 02:16 UTC (permalink / raw) To: Alvaro Herrera <[email protected]>; +Cc: Bernd Helmle <[email protected]>; pgsql-hackers On Fri, 07 Feb 2025 at 10:31, Alvaro Herrera <[email protected]> wrote: > On 2025-Feb-07, Japin Li wrote: > >> Since there is no standard, how do we handle this? I prefer to use >> the strict mode like passlib. > > I definitely like that passlib have documented their thought process > thoroughly. > > I think using their strict mode is good on principle, but if we're going > to do that, then the salt string should not be used verbatim, but > instead base64-decoded first to get the actual salt bytes, like they do. > Does this break compabitibility with other systems? Are > passlib-generated password hashes incompatible with, say, "openssl > passwd" which you (Bernd) mentioned at the beginning of the thread? > Maybe if the password hashes are no longer compatible, then we should > ditch the idea of restricting salts to base64 chars and accept the whole > range of bytes, like Drepper. Thinking about compatibility, the Drepper's behavior is a good choice. Or we can accept the whole range of bytes except the $ character since it is a separator. Of course, it is also not compatible with other systems. > > But in any case ISTM we should reject, as they suggest, the use of less > than 4 bytes of salt (and should perhaps settle for a default of 16, as > passlib suggests). I suppose this is why passlib returns NULL with > empty salt. What we should do in that case IMO is ereport(ERROR). +1 -- Regrads, Japin Li ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Modern SHA2- based password hashes for pgcrypto @ 2025-03-11 16:58 Bernd Helmle <[email protected]> parent: Alvaro Herrera <[email protected]> 1 sibling, 0 replies; 5+ messages in thread From: Bernd Helmle @ 2025-03-11 16:58 UTC (permalink / raw) To: Alvaro Herrera <[email protected]>; Japin Li <[email protected]>; +Cc: pgsql-hackers Hi, > I definitely like that passlib have documented their thought process > thoroughly. > Please find attached v4 of this patch. I added the following changes: - Check for non-supported characters in the salt like passlib does. - Check for reserved tokens when parsing the salt string (i find this very strict, but it covers the cases Japin Li pointed out). I didn't implement being more strict when it comes to the salt length: the salt might be provided from external sources and the password hash is just used for validating within the database. When hashing the password within postgres, users always should use gen_salt(), which generates a reasonable salt string. Maybe, in case of empty salts, we should issue a WARNING instead of erroring out and put additional documentation on how to use it right. > I think using their strict mode is good on principle, but if we're > going > to do that, then the salt string should not be used verbatim, but > instead base64-decoded first to get the actual salt bytes, like they > do. > Does this break compabitibility with other systems? Are > passlib-generated password hashes incompatible with, say, "openssl > passwd" which you (Bernd) mentioned at the beginning of the thread? > Maybe if the password hashes are no longer compatible, then we should > ditch the idea of restricting salts to base64 chars and accept the > whole > range of bytes, like Drepper. > > But in any case ISTM we should reject, as they suggest, the use of > less > than 4 bytes of salt (and should perhaps settle for a default of 16, > as > passlib suggests). I suppose this is why passlib returns NULL with > empty salt. What we should do in that case IMO is ereport(ERROR). > Hmm, i didn't understand that passlib does decode them first, i thought they use it encoded... at least, in our current form we're pretty much compatible with Drepper, passlib and OpenSSL, as far as i tested: Empty salt ---------- # Drepper reference code shacrypt password "" sha512crypt $6$$bLTg4cpho8PIUrjfsE7qlU08Qx2UEfw..xOc6I1wpGVtyVYToGrr7BzRdAAnEr5lYFr 1Z9WcCf1xNZ1HG9qFW1 # Patch SELECT crypt('password', '$6$$'); crypt ─────────────────────────────────────────────────────────────────────── ───────────────────── $6$$bLTg4cpho8PIUrjfsE7qlU08Qx2UEfw..xOc6I1wpGVtyVYToGrr7BzRdAAnEr5lYFr1 Z9WcCf1xNZ1HG9qFW1 (1 row) # Passlib from passlib.hash import sha256_crypt,sha512_crypt hash = sha512_crypt.using(salt='').using(rounds=5000).hash("password") print(hash) $6$$bLTg4cpho8PIUrjfsE7qlU08Qx2UEfw..xOc6I1wpGVtyVYToGrr7BzRdAAnEr5lYFr 1Z9WcCf1xNZ1HG9qFW1 # OpenSSL echo password | openssl passwd -6 -stdin -salt '' <NULL> Short salt ---------- # Drepper reference code ./shacrypt password abcdef sha512crypt $6$abcdef$2yYFwtar3.rlLovG0bXbxXMjZe7Z/1EhQ0gEs0nXalsCW04p2iy6unkvbrBFH 1SXexabbdNXXOO11F7sdyurk/ # Patch SELECT crypt('password', '$6$abcdef$'); crypt ─────────────────────────────────────────────────────────────────────── ─────────────────────────── $6$abcdef$2yYFwtar3.rlLovG0bXbxXMjZe7Z/1EhQ0gEs0nXalsCW04p2iy6unkvbrBFH1 SXexabbdNXXOO11F7sdyurk/ (1 row) # Passlib from passlib.hash import sha256_crypt,sha512_crypt hash = sha512_crypt.using(salt='abcdef').using(rounds=5000).hash("password") print(hash) $6$abcdef$2yYFwtar3.rlLovG0bXbxXMjZe7Z/1EhQ0gEs0nXalsCW04p2iy6unkvbrBFH 1SXexabbdNXXOO11F7sdyurk/ # OpenSSL echo password | openssl passwd -6 -stdin -salt 'abcdef' $6$abcdef$2yYFwtar3.rlLovG0bXbxXMjZe7Z/1EhQ0gEs0nXalsCW04p2iy6unkvbrBFH 1SXexabbdNXXOO11F7sdyurk/ Salt > MAXLEN(16 Bytes) ----------------------- # Drepper reference code (truncates salt) shacrypt password abcdefghijklmnopqrstuvwxyz sha512crypt $6$abcdefghijklmnop$0aenUFHf897F9u0tURIHOeACWajSuVGa7jgJGyq.DKZm/WXl/IZ FvPbneFydBjomEOgM.Sh1m0L3KsS1.H5b// # Patch (truncates salt) SELECT crypt('password', '$6$abcdefghijklmnopqrstuvwxyz$'); crypt ─────────────────────────────────────────────────────────────────────── ───────────────────────────────────── $6$abcdefghijklmnop$0aenUFHf897F9u0tURIHOeACWajSuVGa7jgJGyq.DKZm/WXl/IZF vPbneFydBjomEOgM.Sh1m0L3KsS1.H5b// (1 row) # Passlib Errors out with ValueError: salt too large (sha512_crypt requires <= 16 chars) # OpenSSL (truncates salt) echo password | openssl passwd -6 -stdin -salt 'abcdefghijklmnopqrstuvwxyz' $6$abcdefghijklmnop$0aenUFHf897F9u0tURIHOeACWajSuVGa7jgJGyq.DKZm/WXl/IZ FvPbneFydBjomEOgM.Sh1m0L3KsS1.H5b// Salt with "invalid" character ----------------------------- # Drepper reference code ./shacrypt password abcdefghi%jklmno sha512crypt $6$abcdefghi%jklmno$LMN/V1pW97IoK0rWSDVqCo9EYd6zpqP0TdTX9.cxFAFqsdSMWQM jehkmMtDzL36VBKeG6dg.kFAQKoFvZpK0G. # Patch current version V4 (attached) Errors out: SELECT crypt('password', '$6$abcdefghi%jklmno$'); FEHLER: invalid character in salt string: "%" Time: 0,217 ms # Passlib Errors out ValueError: invalid characters in sha512_crypt salt # OpenSSL echo password | openssl passwd -6 -stdin -salt 'abcdefghi%jklmno' $6$abcdefghi%jklmno$LMN/V1pW97IoK0rWSDVqCo9EYd6zpqP0TdTX9.cxFAFqsdSMWQM jehkmMtDzL36VBKeG6dg.kFAQKoFvZpK0G. So the hashes we produce are identical, but with being more strict we differ in the handling of the provided salt. Bernd Attachments: [text/x-patch] 0001-Add-modern-SHA-2-based-password-hashes-to-pgcrypto-v4.patch (43.3K, ../../[email protected]/2-0001-Add-modern-SHA-2-based-password-hashes-to-pgcrypto-v4.patch) download | inline diff: From dccaa0c9cea257edb7350d0ff3ab7f1752383278 Mon Sep 17 00:00:00 2001 From: Bernd Helmle <Bernd Helmle [email protected]> Date: Tue, 11 Mar 2025 17:31:39 +0100 Subject: [PATCH] Add modern SHA-2 based password hashes to pgcrypto. This adapts the publicly available reference implementation on https://www.akkadia.org/drepper/SHA-crypt.txt and adds the new hash algorithms sha256crypt and sha512crypt to crypt() and gen_salt() respectively. --- contrib/pgcrypto/Makefile | 3 +- contrib/pgcrypto/crypt-gensalt.c | 84 +++ contrib/pgcrypto/crypt-sha.c | 694 +++++++++++++++++++ contrib/pgcrypto/expected/crypt-shacrypt.out | 196 ++++++ contrib/pgcrypto/meson.build | 2 + contrib/pgcrypto/px-crypt.c | 16 + contrib/pgcrypto/px-crypt.h | 31 + contrib/pgcrypto/sql/crypt-shacrypt.sql | 99 +++ doc/src/sgml/pgcrypto.sgml | 41 +- 9 files changed, 1164 insertions(+), 2 deletions(-) create mode 100644 contrib/pgcrypto/crypt-sha.c create mode 100644 contrib/pgcrypto/expected/crypt-shacrypt.out create mode 100644 contrib/pgcrypto/sql/crypt-shacrypt.sql diff --git a/contrib/pgcrypto/Makefile b/contrib/pgcrypto/Makefile index 11c4455dd05..69afa375011 100644 --- a/contrib/pgcrypto/Makefile +++ b/contrib/pgcrypto/Makefile @@ -11,6 +11,7 @@ OBJS = \ crypt-des.o \ crypt-gensalt.o \ crypt-md5.o \ + crypt-sha.o \ mbuf.o \ openssl.o \ pgcrypto.o \ @@ -43,7 +44,7 @@ REGRESS = init md5 sha1 hmac-md5 hmac-sha1 blowfish rijndael \ sha2 des 3des cast5 \ crypt-des crypt-md5 crypt-blowfish crypt-xdes \ pgp-armor pgp-decrypt pgp-encrypt pgp-encrypt-md5 $(CF_PGP_TESTS) \ - pgp-pubkey-decrypt pgp-pubkey-encrypt pgp-info + pgp-pubkey-decrypt pgp-pubkey-encrypt pgp-info crypt-shacrypt ifdef USE_PGXS PG_CONFIG = pg_config diff --git a/contrib/pgcrypto/crypt-gensalt.c b/contrib/pgcrypto/crypt-gensalt.c index 740f3612532..b9aded897d1 100644 --- a/contrib/pgcrypto/crypt-gensalt.c +++ b/contrib/pgcrypto/crypt-gensalt.c @@ -185,3 +185,87 @@ _crypt_gensalt_blowfish_rn(unsigned long count, return output; } + +static char * +_crypt_gensalt_sha(unsigned long count, + const char *input, int size, char *output, int output_size) +{ + char * s_ptr = output; + unsigned int result_bufsize = PX_SHACRYPT_SALT_BUF_LEN; + int rc; + + /* output buffer must be allocated with PX_MAX_SALT_LEN bytes */ + if (PX_MAX_SALT_LEN < result_bufsize) + { + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("invalid size of salt"))); + } + + /* + * Care must be taken to not exceed the buffer size allocated for + * the input character buffer. + */ + if ((PX_SHACRYPT_SALT_MAX_LEN != size) + || (output_size < size)) + { + ereport(ERROR, + (errcode(ERRCODE_INTERNAL_ERROR), + errmsg("invalid length of salt buffer"))); + } + + /* Skip magic bytes, set by callers */ + s_ptr += 3; + if ((rc = pg_snprintf(s_ptr, 18, "rounds=%ld$", count)) <= 0) + { + ereport(ERROR, + (errcode(ERRCODE_INTERNAL_ERROR), + errmsg("cannot format salt string"))); + } + + /* s_ptr should now be positioned at the start of the salt string */ + s_ptr += rc; + + /* + * Normalize salt string + * + * size of input buffer was checked above to + * not exceed PX_SHACRYPT_SALT_LEN_MAX. + */ + for (int i = 0; i < size; i++) + { + *s_ptr = _crypt_itoa64[input[i] & 0x3f]; + s_ptr++; + } + + /* We're done */ + return output; +} + +char * +_crypt_gensalt_sha512_rn(unsigned long count, + char const *input, int size, + char *output, int output_size) +{ + memset(output, 0, output_size); + /* set magic byte for sha512crypt */ + output[0] = '$'; + output[1] = '6'; + output[2] = '$'; + + return _crypt_gensalt_sha(count, input, size, output, output_size); +} + +char * +_crypt_gensalt_sha256_rn(unsigned long count, + const char *input, int size, + char *output, int output_size) +{ + memset(output, 0, output_size); + /* set magic byte for sha256crypt */ + output[0] = '$'; + output[1] = '5'; + output[2] = '$'; + + return _crypt_gensalt_sha(count, input, size, output, output_size); +} \ No newline at end of file diff --git a/contrib/pgcrypto/crypt-sha.c b/contrib/pgcrypto/crypt-sha.c new file mode 100644 index 00000000000..ec51e865a5e --- /dev/null +++ b/contrib/pgcrypto/crypt-sha.c @@ -0,0 +1,694 @@ +/* + * contrib/pgcrypto/crypt-sha.c + * + * This implements shacrypt password hash functions and follows the + * public available reference implementation from + * + * https://www.akkadia.org/drepper/SHA-crypt.txt + * + * This code is public domain. + * + * Please see the inline comments for details about the algorithm. + * + * Basically the following code implements password hashing with sha256 and + * sha512 digest via OpenSSL. Additionally, an extended salt generation (see + * crypt-gensalt.c for details) is provided, which generates a salt suitable + * for either sha256crypt and sha512crypt password hash generation. + * + * Official identifiers for suitable password hashes used in salts are + * 5 : sha256crypt and + * 6 : sha512crypt + * + * The hashing code below supports and uses salt length up to 16 bytes. Longer + * input is possible, but any additional byte of the input is disregarded. + * gen_salt(), when called with a sha256crypt or sha512crypt identifier will + * always generate a 16 byte long salt string. + * + * Output is compatible with any sha256crypt and sha512crypt output + * generated by e.g. OpenSSL or libc crypt(). + * + * The described algorithm uses default computing rounds of 5000. Currently, + * even when no specific rounds specification is used, we always explicitly + * print out the rounds option flag with the final hash password string. + * + * The length of the specific password hash (without magic bytes and salt + * string) is: + * + * sha256crypt: 43 bytes and + * sha512crypt: 86 bytes. + * + * Overall hashed password length is: + * + * sha256crypt: 80 bytes and + * sha512crypt: 123 bytes + * + */ +#include "postgres.h" +#include "miscadmin.h" + +#include "px-crypt.h" +#include "px.h" + +typedef enum +{ + PGCRYPTO_SHA256CRYPT = 0, + PGCRYPTO_SHA512CRYPT = 1, + PGCRYPTO_SHA_UNKOWN +} PGCRYPTO_SHA_t; + +static unsigned char _crypt_itoa64[64 + 1] = +"./0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"; + +/* + * Modern UNIX password, based on SHA crypt hashes + */ +char * +px_crypt_shacrypt(const char *pw, const char *salt, char *passwd, unsigned dstlen) +{ + static const char rounds_prefix[] = "rounds="; + static char *magic_bytes[2] = {"$5$", "$6$"}; + + /* Used to create the password hash string */ + StringInfo out_buf = NULL; + + PGCRYPTO_SHA_t type = PGCRYPTO_SHA_UNKOWN; + PX_MD *digestA = NULL; + PX_MD *digestB = NULL; + int err; + + const char *dec_salt_binary; /* pointer into the real salt string */ + StringInfo decoded_salt = NULL; /* decoded salt string */ + + unsigned char sha_buf[PX_SHACRYPT_DIGEST_MAX_LEN]; + unsigned char sha_buf_tmp[PX_SHACRYPT_DIGEST_MAX_LEN]; /* temporary buffer for + * digests */ + + char rounds_custom = 0; + char *p_bytes = NULL; + char *s_bytes = NULL; + char *cp = NULL; + const char *fp = NULL; /* intermediate pointer within salt string */ + const char *ep = NULL; /* holds pointer to the end of the salt string */ + + size_t buf_size = 0; /* buffer size for sha256crypt/sha512crypt */ + unsigned int block; /* number of bytes processed */ + unsigned long rounds = PX_SHACRYPT_ROUNDS_DEFAULT; + + unsigned len, salt_len = 0; + + /* Init result buffer */ + out_buf = makeStringInfoExt(PX_SHACRYPT_BUF_LEN); + decoded_salt = makeStringInfoExt(PX_SHACRYPT_SALT_MAX_LEN); + + /* Sanity checks */ + if (!passwd) + return NULL; + + if (pw == NULL) + { + elog(ERROR, "null value for password rejected"); + } + + if (salt == NULL) + { + elog(ERROR, "null value for salt rejected"); + } + + /* + * Make sure result buffers are large enough. + */ + if (dstlen < PX_SHACRYPT_BUF_LEN) + { + elog(ERROR, "insufficient result buffer size to encrypt password"); + } + + /* Init contents of buffers properly */ + memset(&sha_buf, '\0', sizeof(sha_buf)); + memset(&sha_buf_tmp, '\0', sizeof(sha_buf_tmp)); + + /* + * Decode the salt string. We need to know how many rounds and which + * digest we have to use to hash the password. + */ + len = strlen(pw); + dec_salt_binary = salt; + + /* + * Analyze and prepare the salt string + * + * The magic string should be specified in the first three bytes of the + * salt string. But do some sanity checks before. + */ + if (strlen(dec_salt_binary) < 3) + { + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid salt"))); + } + + /* + * Check format of magic bytes. These should define either 5=sha256crypt + * or 6=sha512crypt in the second byte, enclosed by ascii dollar signs. + */ + if ((dec_salt_binary[0] != '$') + && (dec_salt_binary[2] != '$')) + { + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid format of salt")), + errhint("magic byte format for shacrypt is either \"$5$\" or \"$6$\"")); + } + + /* + * Check magic byte for supported shacrypt digest. + * + * We're just interested in the very first 3 bytes of the salt string, + * since this defines the digest length to use. + */ + if (strncmp(dec_salt_binary, magic_bytes[0], strlen(magic_bytes[0])) == 0) + { + type = PGCRYPTO_SHA256CRYPT; + dec_salt_binary += strlen(magic_bytes[0]); + } + else if (strncmp(dec_salt_binary, magic_bytes[1], strlen(magic_bytes[1])) == 0) + { + type = PGCRYPTO_SHA512CRYPT; + dec_salt_binary += strlen(magic_bytes[1]); + } + + /* + * dec_salt_binary pointer is positioned after the magic bytes now + * + * We extract any options in the following code branch. The only optional + * setting we need to take care of is the "rounds" option. Note that the + * salt generator already checked for invalid settings before, but we need + * to do it here again to protect against injection of wrong values when + * called without the generator. + * + * If there is any garbage added after the magic byte and the options/salt + * string, we don't treat this special: This is just absorbed as part of + * the salt with up to PX_SHACRYPT_SALT_LEN_MAX. + * + * Unknown magic byte is handled later below + */ + if (strncmp(dec_salt_binary, + rounds_prefix, sizeof(rounds_prefix) - 1) == 0) + { + + const char *num = dec_salt_binary + sizeof(rounds_prefix) - 1; + char *endp; + long srounds = strtoul(num, &endp, 10); + + if (*endp == '$') + { + dec_salt_binary = endp + 1; + + /* + * We violate supported lower or upper bound of rounds, but in + * this case we change this value to the supported lower or upper + * value. We don't do this silently and print a NOTICE in such a + * case. + * + * Note that a salt string generated with gen_salt() would never + * generated such a salt string, since it would error out. + * + * But Drepper's upstream reference implementation supports this + * when passing the salt string directly, so we maintain + * compatibility here. + */ + if (srounds > PX_SHACRYPT_ROUNDS_MAX) + { + ereport(NOTICE, + errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE), + errmsg("rounds=%ld exceeds maximum supported value (%ld), using %ld instead", + srounds, PX_SHACRYPT_ROUNDS_MAX, + PX_SHACRYPT_ROUNDS_MAX)); + srounds = PX_SHACRYPT_ROUNDS_MAX; + } + else if (srounds < PX_SHACRYPT_ROUNDS_MIN) + { + ereport(NOTICE, + errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE), + errmsg("rounds=%ld is below supported value (%ld), using %ld instead", + srounds, PX_SHACRYPT_ROUNDS_MIN, + PX_SHACRYPT_ROUNDS_MIN)); + srounds = PX_SHACRYPT_ROUNDS_MIN; + } + + rounds = (unsigned long) srounds; + rounds_custom = 1; + } + else + { + ereport(ERROR, + errcode(ERRCODE_SYNTAX_ERROR), + errmsg("could not parse salt options")); + } + + } + + /* + * Choose the correct digest length and add the magic bytes to the result + * buffer. Also handle possible invalid magic byte we've extracted above. + */ + switch (type) + { + case PGCRYPTO_SHA256CRYPT: + { + /* Two PX_MD objects required */ + err = px_find_digest("sha256", &digestA); + if (err) + goto error; + + err = px_find_digest("sha256", &digestB); + if (err) + goto error; + + /* digest buffer length is 32 for sha256 */ + buf_size = 32; + + appendStringInfoString(out_buf, magic_bytes[0]); + break; + } + + case PGCRYPTO_SHA512CRYPT: + { + /* Two PX_MD objects required */ + err = px_find_digest("sha512", &digestA); + if (err) + goto error; + + err = px_find_digest("sha512", &digestB); + if (err) + goto error; + + buf_size = PX_SHACRYPT_DIGEST_MAX_LEN; + + appendStringInfoString(out_buf, magic_bytes[1]); + break; + } + + case PGCRYPTO_SHA_UNKOWN: + elog(ERROR, "unknown crypt identifier \"%c\"", salt[1]); + } + + if (rounds_custom > 0) + { + appendStringInfo(out_buf, "rounds=%lu$", rounds); + } + + /* + * We need the real decoded salt string from salt input, this is every + * character before the last '$' in the preamble. Append every + * compatible character up to PX_SHACRYPT_SALT_MAX_LEN to the result buffer. + * Note that depending on the input, there might be no '$' marker after + * the salt, when there is no password hash attached at the end. + * + * We try hard to recognize mistakes, but since we might get an input + * string which might also have the password hash after the salt string + * section we give up as soon we reach the end of the input or if there + * are any bytes consumed for the salt string until we reach the + * first '$' marker thereafter. + */ + for (ep = dec_salt_binary; + *ep && ep < (dec_salt_binary + PX_SHACRYPT_SALT_MAX_LEN); + ep++) + { + /* + * Filter out any string which shouldn't be here. + * + * First check for accidentally embedded magic strings here. We don't + * support '$' in salt strings anyways and seeing a magic byte trying + * to identify shacrypt hashes might indicate that something went + * wrong when generating this salt string. Note that we later check for + * non-supported literals anyways, but any '$' here confuses us + * at this point. + */ + fp = strstr(dec_salt_binary, magic_bytes[0]); + + if (fp != NULL) + { + elog(ERROR, "bogus magic byte found in salt string"); + } + + fp = strstr(dec_salt_binary, magic_bytes[1]); + + if (fp != NULL) + { + elog(ERROR, "bogus magic byte found in salt string"); + } + + /* + * This looks very strict, but we assume the caller did something + * wrong when we see a "rounds=" option here. + */ + fp = strstr(dec_salt_binary, rounds_prefix); + + if (fp != NULL) + { + elog(ERROR, "invalid rounds option specified in salt string"); + } + + if (*ep != '$') + { + if (isalpha(*ep) || isdigit(*ep) || (*ep == '.') || (*ep == '/')) + { + appendStringInfoCharMacro(decoded_salt, *ep); + } + else + { + elog(ERROR, "invalid character in salt string: \"%c\"", *ep); + } + } + else + { + /* We encountered a '$' marker. Check if we already absorbed + * some bytes from input. If true, we are optimistic and + * terminate at this stage. Of not, we try further. + * + * If we already consumed enough bytes for the salt string, everything + * that is after this marker is considered to be part of an + * optionally specified password hash and ignored. + */ + if (decoded_salt->len > 0) + break; + } + + } + + salt_len = decoded_salt->len; + appendStringInfoString(out_buf, decoded_salt->data); + elog(DEBUG1, "using salt \"%s\", salt len = %d, rounds = %lu", + decoded_salt->data, decoded_salt->len, rounds); + + /* + * Sanity check: + * + * At this point the salt string buffer must not exceed expected size + */ + if (out_buf->len > (3 + 17 * rounds_custom + salt_len)) + { + elog(ERROR, "unexpected length of salt string"); + } + + /*- + * 1. Start digest A + * 2. Add the password string to digest A + * 3. Add the salt to digest A + */ + px_md_update(digestA, (const unsigned char *) pw, len); + px_md_update(digestA, (const unsigned char *) decoded_salt->data, salt_len); + + /*- + * 4. Create digest B + * 5. Add password to digest B + * 6. Add the salt string to digest B + * 7. Add the password again to digest B + * 8. Finalize digest B + */ + px_md_update(digestB, (const unsigned char *) pw, len); + px_md_update(digestB, (const unsigned char *) dec_salt_binary, salt_len); + px_md_update(digestB, (const unsigned char *) pw, len); + px_md_finish(digestB, sha_buf); + + /*- + * 9. For each block of (excluding the NULL byte), add + * digest B to digest A. + */ + for (block = len; block > buf_size; block -= buf_size) + { + px_md_update(digestA, sha_buf, buf_size); + } + + /*- + * 10 For the remaining N bytes of the password string, add + * the first N bytes of digest B to A */ + px_md_update(digestA, sha_buf, block); + + /*- + * 11 For each bit of the binary representation of the length of the + * password string up to and including the highest 1-digit, starting + * from to lowest bit position (numeric value 1) + * + * a) for a 1-digit add digest B (sha_buf) to digest A + * b) for a 0-digit add the password string + */ + + block = len; + while (block) + { + px_md_update(digestA, + (block & 1) ? sha_buf : (const unsigned char *) pw, + (block & 1) ? buf_size : len); + + /* right shift to next byte */ + block >>= 1; + } + + /* 12 Finalize digest A */ + px_md_finish(digestA, sha_buf); + + /* 13 Start digest DP */ + px_md_reset(digestB); + + /*- + * 14 Add every byte of the password string (excluding trailing NULL) + * to the digest DP + */ + for (block = len; block > 0; block--) + { + px_md_update(digestB, (const unsigned char *) pw, len); + } + + /* 15 Finalize digest DP */ + px_md_finish(digestB, sha_buf_tmp); + + /*- + * 16 produce byte sequence P with same length as password. + * + * a) for each block of 32 or 64 bytes of length of the password + * string the entire digest DP is used + * b) for the remaining N (up to 31 or 63) bytes use the + * first N bytes of digest DP + */ + if ((p_bytes = palloc0(len)) == NULL) + { + goto error; + } + + /* N step of 16, copy over the bytes from password */ + for (cp = p_bytes, block = len; block > buf_size; block -= buf_size, cp += buf_size) + memcpy(cp, sha_buf_tmp, buf_size); + memcpy(cp, sha_buf_tmp, block); + + /* + * 17 Start digest DS + */ + px_md_reset(digestB); + + /*- + * 18 Repeat the following 16+A[0] times, where A[0] represents the first + * byte in digest A interpreted as an 8-bit unsigned value + * add the salt to digest DS + */ + for (block = 16 + sha_buf[0]; block > 0; block--) + { + px_md_update(digestB, (const unsigned char *) dec_salt_binary, salt_len); + } + + /* + * 19 Finalize digest DS + */ + px_md_finish(digestB, sha_buf_tmp); + + /*- + * 20 Produce byte sequence S of the same length as the salt string where + * + * a) for each block of 32 or 64 bytes of length of the salt string the + * entire digest DS is used + * + * b) for the remaining N (up to 31 or 63) bytes use the first N + * bytes of digest DS + */ + if ((s_bytes = palloc0(salt_len)) == NULL) + goto error; + + for (cp = s_bytes, block = salt_len; block > buf_size; block -= buf_size, cp += buf_size) + { + memcpy(cp, sha_buf_tmp, buf_size); + } + memcpy(cp, sha_buf_tmp, block); + + /* Make sure we don't leave something important behind */ + px_memset(&sha_buf_tmp, 0, sizeof sha_buf); + + /*- + * 21 Repeat a loop according to the number specified in the rounds=<N> + * specification in the salt (or the default value if none is + * present). Each round is numbered, starting with 0 and up to N-1. + * + * The loop uses a digest as input. In the first round it is the + * digest produced in step 12. In the latter steps it is the digest + * produced in step 21.h of the previous round. The following text + * uses the notation "digest A/B" to describe this behavior. + */ + for (block = 0; block < rounds; block++) + { + + /* + * Make it possible to abort in case large values for "rounds" are + * specified. + */ + CHECK_FOR_INTERRUPTS(); + + /* a) start digest B */ + px_md_reset(digestB); + + /* + * b) for odd round numbers add the byte sequense P to digest B c) for + * even round numbers add digest A/B + */ + px_md_update(digestB, + (block & 1) ? (const unsigned char *) p_bytes : sha_buf, + (block & 1) ? len : buf_size); + + /* d) for all round numbers not divisible by 3 add the byte sequence S */ + if (block % 3) + { + px_md_update(digestB, (const unsigned char *) s_bytes, salt_len); + } + + /* e) for all round numbers not divisible by 7 add the byte sequence P */ + if (block % 7) + { + px_md_update(digestB, (const unsigned char *) p_bytes, len); + } + + /* + * f) for odd round numbers add digest A/C g) for even round numbers + * add the byte sequence P + */ + px_md_update(digestB, + (block & 1) ? sha_buf : (const unsigned char *) p_bytes, + (block & 1) ? buf_size : len); + + /* h) finish digest C. */ + px_md_finish(digestB, sha_buf); + + } + + px_md_free(digestA); + px_md_free(digestB); + + digestA = NULL; + digestB = NULL; + + pfree(s_bytes); + pfree(p_bytes); + + s_bytes = NULL; + p_bytes = NULL; + + /* prepare final result buffer */ + appendStringInfoCharMacro(out_buf, '$'); + +#define b64_from_24bit(B2, B1, B0, N) \ + do { \ + unsigned int w = ((B2) << 16) | ((B1) << 8) | (B0); \ + int i = (N); \ + while (i-- > 0) \ + { \ + appendStringInfoCharMacro(out_buf, _crypt_itoa64[w & 0x3f]); \ + w >>= 6; \ + } \ + } while (0) + + switch (type) + { + case PGCRYPTO_SHA256CRYPT: + { + b64_from_24bit(sha_buf[0], sha_buf[10], sha_buf[20], 4); + b64_from_24bit(sha_buf[21], sha_buf[1], sha_buf[11], 4); + b64_from_24bit(sha_buf[12], sha_buf[22], sha_buf[2], 4); + b64_from_24bit(sha_buf[3], sha_buf[13], sha_buf[23], 4); + b64_from_24bit(sha_buf[24], sha_buf[4], sha_buf[14], 4); + b64_from_24bit(sha_buf[15], sha_buf[25], sha_buf[5], 4); + b64_from_24bit(sha_buf[6], sha_buf[16], sha_buf[26], 4); + b64_from_24bit(sha_buf[27], sha_buf[7], sha_buf[17], 4); + b64_from_24bit(sha_buf[18], sha_buf[28], sha_buf[8], 4); + b64_from_24bit(sha_buf[9], sha_buf[19], sha_buf[29], 4); + b64_from_24bit(0, sha_buf[31], sha_buf[30], 3); + + break; + } + + case PGCRYPTO_SHA512CRYPT: + { + b64_from_24bit(sha_buf[0], sha_buf[21], sha_buf[42], 4); + b64_from_24bit(sha_buf[22], sha_buf[43], sha_buf[1], 4); + b64_from_24bit(sha_buf[44], sha_buf[2], sha_buf[23], 4); + b64_from_24bit(sha_buf[3], sha_buf[24], sha_buf[45], 4); + b64_from_24bit(sha_buf[25], sha_buf[46], sha_buf[4], 4); + b64_from_24bit(sha_buf[47], sha_buf[5], sha_buf[26], 4); + b64_from_24bit(sha_buf[6], sha_buf[27], sha_buf[48], 4); + b64_from_24bit(sha_buf[28], sha_buf[49], sha_buf[7], 4); + b64_from_24bit(sha_buf[50], sha_buf[8], sha_buf[29], 4); + b64_from_24bit(sha_buf[9], sha_buf[30], sha_buf[51], 4); + b64_from_24bit(sha_buf[31], sha_buf[52], sha_buf[10], 4); + b64_from_24bit(sha_buf[53], sha_buf[11], sha_buf[32], 4); + b64_from_24bit(sha_buf[12], sha_buf[33], sha_buf[54], 4); + b64_from_24bit(sha_buf[34], sha_buf[55], sha_buf[13], 4); + b64_from_24bit(sha_buf[56], sha_buf[14], sha_buf[35], 4); + b64_from_24bit(sha_buf[15], sha_buf[36], sha_buf[57], 4); + b64_from_24bit(sha_buf[37], sha_buf[58], sha_buf[16], 4); + b64_from_24bit(sha_buf[59], sha_buf[17], sha_buf[38], 4); + b64_from_24bit(sha_buf[18], sha_buf[39], sha_buf[60], 4); + b64_from_24bit(sha_buf[40], sha_buf[61], sha_buf[19], 4); + b64_from_24bit(sha_buf[62], sha_buf[20], sha_buf[41], 4); + b64_from_24bit(0, 0, sha_buf[63], 2); + + break; + } + + case PGCRYPTO_SHA_UNKOWN: + /* we shouldn't land here ... */ + elog(ERROR, "unsupported digest length"); + + } + + *cp = '\0'; + + /* + * Copy over result to specified buffer. + * + * The passwd character buffer should have at least PX_SHACRYPT_BUF_LEN + * allocated, since we checked above if dstlen is smaller than + * PX_SHACRYPT_BUF_LEN (which also includes the NULL byte). + * + * In that case we would have failed above already. + */ + memcpy(passwd, out_buf->data, out_buf->len); + + /* make sure nothing important is left behind */ + px_memset(&sha_buf, 0, sizeof sha_buf); + destroyStringInfo(out_buf); + destroyStringInfo(decoded_salt); + + /* ...and we're done */ + return passwd; + +error: + if (digestA != NULL) + px_md_free(digestA); + + if (digestB != NULL) + px_md_free(digestB); + + if (out_buf != NULL) + destroyStringInfo(out_buf); + + ereport(ERROR, + (errcode(ERRCODE_INTERNAL_ERROR), + errmsg("cannot create encrypted password"))); + return NULL; /* keep compiler quiet */ +} diff --git a/contrib/pgcrypto/expected/crypt-shacrypt.out b/contrib/pgcrypto/expected/crypt-shacrypt.out new file mode 100644 index 00000000000..f3b76d5e913 --- /dev/null +++ b/contrib/pgcrypto/expected/crypt-shacrypt.out @@ -0,0 +1,196 @@ +-- +-- crypt() and gensalt: sha256crypt, sha512crypt +-- +-- $5$ is sha256crypt +SELECT crypt('', '$5$Szzz0yzz'); + crypt +--------------------------------------------------------- + $5$Szzz0yzz$cA.ZFZKqblRYjdsbrWtVTYa/qSwPQnt2uh0LBtyYAAD +(1 row) + +SELECT crypt('foox', '$5$Szzz0yzz'); + crypt +--------------------------------------------------------- + $5$Szzz0yzz$7hI0rUWkO2QdBkzamh.vP.MIPlbZiwSvu2smhSi6064 +(1 row) + +CREATE TABLE ctest (data text, res text, salt text); +INSERT INTO ctest VALUES ('password', '', ''); +-- generate a salt for sha256crypt, default rounds +UPDATE ctest SET salt = gen_salt('sha256crypt'); +UPDATE ctest SET res = crypt(data, salt); +SELECT res = crypt(data, res) AS "worked" +FROM ctest; + worked +-------- + t +(1 row) + +-- generate a salt for sha256crypt, rounds 9999 +UPDATE ctest SET salt = gen_salt('sha256crypt', 9999); +UPDATE ctest SET res = crypt(data, salt); +SELECT res = crypt(data, res) AS "worked" +FROM ctest; + worked +-------- + t +(1 row) + +-- should fail, below supported minimum rounds value +UPDATE ctest SET salt = gen_salt('sha256crypt', 10); +ERROR: gen_salt: Incorrect number of rounds +-- should fail, exceeds supported maximum rounds value +UPDATE ctest SET salt = gen_salt('sha256crypt', 1000000000); +ERROR: gen_salt: Incorrect number of rounds +TRUNCATE ctest; +-- $6$ is sha512crypt +SELECT crypt('', '$6$Szzz0yzz'); + crypt +---------------------------------------------------------------------------------------------------- + $6$Szzz0yzz$EGj.JLAovFyAtCJx3YD1DXD1yTXoO9gv4qgLyHBsJJ1lkpnLB8ZPHekm1qXjJCOBc/8thCuHpxNN8Y5xzRYU5. +(1 row) + +SELECT crypt('foox', '$6$Szzz0yzz'); + crypt +---------------------------------------------------------------------------------------------------- + $6$Szzz0yzz$KqDw1Y8kze.VFapkvTc9Y5fbqzltjeRz1aPGC/pkHRhFQZ2aM6PmZpXQjcD7AOH88Bq0CSD.VlmymQzcBMEUl0 +(1 row) + +INSERT INTO ctest VALUES ('password', '', ''); +-- generate a salt for sha512crypt, default rounds +UPDATE ctest SET salt = gen_salt('sha512crypt'); +UPDATE ctest SET res = crypt(data, salt); +SELECT res = crypt(data, res) AS "worked" +FROM ctest; + worked +-------- + t +(1 row) + +-- generate a salt for sha512crypt, rounds 9999 +UPDATE ctest SET salt = gen_salt('sha512crypt', 9999); +UPDATE ctest SET res = crypt(data, salt); +SELECT res = crypt(data, res) AS "worked" +FROM ctest; + worked +-------- + t +(1 row) + +-- should fail, below supported minimum rounds value +UPDATE ctest SET salt = gen_salt('sha512crypt', 10); +ERROR: gen_salt: Incorrect number of rounds +-- should fail, exceeds supported maximum rounds value +UPDATE ctest SET salt = gen_salt('sha512crypt', 1000000000); +ERROR: gen_salt: Incorrect number of rounds +-- Extended tests taken from public domain code at +-- https://www.akkadia.org/drepper/SHA-crypt.txt +-- +-- We adapt the tests defined there to make sure we are compatible with the reference +-- implementation. +-- This tests sha256crypt (magic byte $5$ with salt and rounds) +SELECT crypt('Hello world!', '$5$saltstring') + = '$5$saltstring$5B8vYYiY.CVt1RlTTf8KbXBH3hsxY/GNooZaBBGWEc5' AS result; + result +-------- + t +(1 row) + +SELECT crypt('Hello world!', '$5$rounds=10000$saltstringsaltstring') + = '$5$rounds=10000$saltstringsaltst$3xv.VbSHBb41AL9AvLeujZkZRBAwqFMz2.opqey6IcA' AS result; + result +-------- + t +(1 row) + +SELECT crypt('This is just a test', '$5$rounds=5000$toolongsaltstring') + = '$5$rounds=5000$toolongsaltstrin$Un/5jzAHMgOGZ5.mWJpuVolil07guHPvOW8mGRcvxa5' AS result; + result +-------- + t +(1 row) + + SELECT crypt('a very much longer text to encrypt. This one even stretches over more' + 'than one line.', '$5$rounds=1400$anotherlongsaltstring') + = '$5$rounds=1400$anotherlongsalts$Rx.j8H.h8HjEDGomFU8bDkXm3XIUnzyxf12oP84Bnq1' AS result; + result +-------- + t +(1 row) + +SELECT crypt('we have a short salt string but not a short password', '$5$rounds=77777$short') + = '$5$rounds=77777$short$JiO1O3ZpDAxGJeaDIuqCoEFysAe1mZNJRs3pw0KQRd/' AS result; + result +-------- + t +(1 row) + +SELECT crypt('a short string', '$5$rounds=123456$asaltof16chars..') + = '$5$rounds=123456$asaltof16chars..$gP3VQ/6X7UUEW3HkBn2w1/Ptq2jxPyzV/cZKmF/wJvD' AS result; + result +-------- + t +(1 row) + +SELECT crypt('the minimum number is still observed', '$5$rounds=10$roundstoolow') + = '$5$rounds=1000$roundstoolow$yfvwcWrQ8l/K0DAWyuPMDNHpIVlTQebY9l/gL972bIC' AS result; +NOTICE: rounds=10 is below supported value (1000), using 1000 instead + result +-------- + t +(1 row) + +-- The following tests sha512crypt (magic byte $6$ with salt and rounds) +SELECT crypt('Hello world!', '$6$saltstring') + = '$6$saltstring$svn8UoSVapNtMuq1ukKS4tPQd8iKwSMHWjl/O817G3uBnIFNjnQJuesI68u4OTLiBFdcbYEdFCoEOfaS35inz1' AS result; + result +-------- + t +(1 row) + +SELECT crypt('Hello world!', '$6$rounds=10000$saltstringsaltstring') + = '$6$rounds=10000$saltstringsaltst$OW1/O6BYHV6BcXZu8QVeXbDWra3Oeqh0sbHbbMCVNSnCM/UrjmM0Dp8vOuZeHBy/YTBmSK6H9qs/y3RnOaw5v.' AS result; + result +-------- + t +(1 row) + +SELECT crypt('This is just a test', '$6$rounds=5000$toolongsaltstring') + = '$6$rounds=5000$toolongsaltstrin$lQ8jolhgVRVhY4b5pZKaysCLi0QBxGoNeKQzQ3glMhwllF7oGDZxUhx1yxdYcz/e1JSbq3y6JMxxl8audkUEm0' AS result; + result +-------- + t +(1 row) + +SELECT crypt('a very much longer text to encrypt. This one even stretches over more' + 'than one line.', '$6$rounds=1400$anotherlongsaltstring') + = '$6$rounds=1400$anotherlongsalts$POfYwTEok97VWcjxIiSOjiykti.o/pQs.wPvMxQ6Fm7I6IoYN3CmLs66x9t0oSwbtEW7o7UmJEiDwGqd8p4ur1' AS result; + result +-------- + t +(1 row) + +SELECT crypt('we have a short salt string but not a short password', '$6$rounds=77777$short') + = '$6$rounds=77777$short$WuQyW2YR.hBNpjjRhpYD/ifIw05xdfeEyQoMxIXbkvr0gge1a1x3yRULJ5CCaUeOxFmtlcGZelFl5CxtgfiAc0' AS result; + result +-------- + t +(1 row) + +SELECT crypt('a short string', '$6$rounds=123456$asaltof16chars..') + = '$6$rounds=123456$asaltof16chars..$BtCwjqMJGx5hrJhZywWvt0RLE8uZ4oPwcelCjmw2kSYu.Ec6ycULevoBK25fs2xXgMNrCzIMVcgEJAstJeonj1' AS result; + result +-------- + t +(1 row) + +SELECT crypt('the minimum number is still observed', '$6$rounds=10$roundstoolow') + = '$6$rounds=1000$roundstoolow$kUMsbe306n21p9R.FRkW3IGn.S9NPN0x50YhH1xhLsPuWGsUSklZt58jaTfF4ZEQpyUNGc0dqbpBYYBaHHrsX.' AS result; +NOTICE: rounds=10 is below supported value (1000), using 1000 instead + result +-------- + t +(1 row) + +-- cleanup +DROP TABLE ctest; diff --git a/contrib/pgcrypto/meson.build b/contrib/pgcrypto/meson.build index 7a4e8e76d64..7d5ef9b6d32 100644 --- a/contrib/pgcrypto/meson.build +++ b/contrib/pgcrypto/meson.build @@ -9,6 +9,7 @@ pgcrypto_sources = files( 'crypt-des.c', 'crypt-gensalt.c', 'crypt-md5.c', + 'crypt-sha.c', 'mbuf.c', 'pgcrypto.c', 'pgp-armor.c', @@ -52,6 +53,7 @@ pgcrypto_regress = [ 'pgp-pubkey-decrypt', 'pgp-pubkey-encrypt', 'pgp-info', + 'crypt-shacrypt' ] pgcrypto_openssl_sources = files( diff --git a/contrib/pgcrypto/px-crypt.c b/contrib/pgcrypto/px-crypt.c index 96ce9384aff..df9875b9433 100644 --- a/contrib/pgcrypto/px-crypt.c +++ b/contrib/pgcrypto/px-crypt.c @@ -67,6 +67,16 @@ run_crypt_bf(const char *psw, const char *salt, return res; } +static char * +run_crypt_sha(const char *psw, const char *salt, + char *buf, unsigned len) +{ + char *res; + + res = px_crypt_shacrypt(psw, salt, buf, len); + return res; +} + struct px_crypt_algo { char *id; @@ -81,6 +91,8 @@ static const struct px_crypt_algo {"$2x$", 4, run_crypt_bf}, {"$2$", 3, NULL}, /* N/A */ {"$1$", 3, run_crypt_md5}, + {"$5$", 3, run_crypt_sha}, + {"$6$", 3, run_crypt_sha}, {"_", 1, run_crypt_des}, {"", 0, run_crypt_des}, {NULL, 0, NULL} @@ -127,6 +139,10 @@ static struct generator gen_list[] = { {"md5", _crypt_gensalt_md5_rn, 6, 0, 0, 0}, {"xdes", _crypt_gensalt_extended_rn, 3, PX_XDES_ROUNDS, 1, 0xFFFFFF}, {"bf", _crypt_gensalt_blowfish_rn, 16, PX_BF_ROUNDS, 4, 31}, + {"sha256crypt", _crypt_gensalt_sha256_rn, PX_SHACRYPT_SALT_MAX_LEN, + PX_SHACRYPT_ROUNDS_DEFAULT, PX_SHACRYPT_ROUNDS_MIN, PX_SHACRYPT_ROUNDS_MAX}, + {"sha512crypt", _crypt_gensalt_sha512_rn, PX_SHACRYPT_SALT_MAX_LEN, + PX_SHACRYPT_ROUNDS_DEFAULT, PX_SHACRYPT_ROUNDS_MIN, PX_SHACRYPT_ROUNDS_MAX}, {NULL, NULL, 0, 0, 0, 0} }; diff --git a/contrib/pgcrypto/px-crypt.h b/contrib/pgcrypto/px-crypt.h index 54de8069655..9287c33efe1 100644 --- a/contrib/pgcrypto/px-crypt.h +++ b/contrib/pgcrypto/px-crypt.h @@ -45,6 +45,30 @@ /* default for blowfish salt */ #define PX_BF_ROUNDS 6 +/* Maximum salt string length of shacrypt. */ +#define PX_SHACRYPT_SALT_MAX_LEN 16 + +/* SHA buffer length */ +#define PX_SHACRYPT_DIGEST_MAX_LEN 64 + +/* calculated buffer size of a buffer to store a shacrypt salt string */ +#define PX_SHACRYPT_SALT_BUF_LEN (3 + 7 + 10 + PX_SHACRYPT_SALT_MAX_LEN + 1) + +/* + * calculated buffer size of a buffer to store complete result of a shacrypt + * digest including salt + */ +#define PX_SHACRYPT_BUF_LEN (PX_SHACRYPT_SALT_BUF_LEN + 86 + 1) + +/* Default number of rounds of shacrypt if not explicitly specified. */ +#define PX_SHACRYPT_ROUNDS_DEFAULT 5000l + +/* Minimum number of rounds of shacrypt. */ +#define PX_SHACRYPT_ROUNDS_MIN 1000l + +/* Maximum number of rounds of shacrypt. */ +#define PX_SHACRYPT_ROUNDS_MAX 999999999l + /* * main interface */ @@ -64,6 +88,10 @@ char *_crypt_gensalt_md5_rn(unsigned long count, const char *input, int size, char *output, int output_size); char *_crypt_gensalt_blowfish_rn(unsigned long count, const char *input, int size, char *output, int output_size); +char *_crypt_gensalt_sha256_rn(unsigned long count, + const char *input, int size, char *output, int output_size); +char *_crypt_gensalt_sha512_rn(unsigned long count, + const char *input, int size, char *output, int output_size); /* disable 'extended DES crypt' */ /* #define DISABLE_XDES */ @@ -79,4 +107,7 @@ char *px_crypt_des(const char *key, const char *setting); char *px_crypt_md5(const char *pw, const char *salt, char *passwd, unsigned dstlen); +/* crypt-sha.c */ +char *px_crypt_shacrypt(const char *pw, const char *salt, char *passwd, unsigned dstlen); + #endif /* _PX_CRYPT_H */ diff --git a/contrib/pgcrypto/sql/crypt-shacrypt.sql b/contrib/pgcrypto/sql/crypt-shacrypt.sql new file mode 100644 index 00000000000..3ee826f61aa --- /dev/null +++ b/contrib/pgcrypto/sql/crypt-shacrypt.sql @@ -0,0 +1,99 @@ +-- +-- crypt() and gensalt: sha256crypt, sha512crypt +-- + +-- $5$ is sha256crypt +SELECT crypt('', '$5$Szzz0yzz'); + +SELECT crypt('foox', '$5$Szzz0yzz'); + +CREATE TABLE ctest (data text, res text, salt text); +INSERT INTO ctest VALUES ('password', '', ''); + +-- generate a salt for sha256crypt, default rounds +UPDATE ctest SET salt = gen_salt('sha256crypt'); +UPDATE ctest SET res = crypt(data, salt); +SELECT res = crypt(data, res) AS "worked" +FROM ctest; + +-- generate a salt for sha256crypt, rounds 9999 +UPDATE ctest SET salt = gen_salt('sha256crypt', 9999); +UPDATE ctest SET res = crypt(data, salt); +SELECT res = crypt(data, res) AS "worked" +FROM ctest; + +-- should fail, below supported minimum rounds value +UPDATE ctest SET salt = gen_salt('sha256crypt', 10); + +-- should fail, exceeds supported maximum rounds value +UPDATE ctest SET salt = gen_salt('sha256crypt', 1000000000); + +TRUNCATE ctest; + +-- $6$ is sha512crypt +SELECT crypt('', '$6$Szzz0yzz'); + +SELECT crypt('foox', '$6$Szzz0yzz'); + +INSERT INTO ctest VALUES ('password', '', ''); + +-- generate a salt for sha512crypt, default rounds +UPDATE ctest SET salt = gen_salt('sha512crypt'); +UPDATE ctest SET res = crypt(data, salt); +SELECT res = crypt(data, res) AS "worked" +FROM ctest; + +-- generate a salt for sha512crypt, rounds 9999 +UPDATE ctest SET salt = gen_salt('sha512crypt', 9999); +UPDATE ctest SET res = crypt(data, salt); +SELECT res = crypt(data, res) AS "worked" +FROM ctest; + +-- should fail, below supported minimum rounds value +UPDATE ctest SET salt = gen_salt('sha512crypt', 10); + +-- should fail, exceeds supported maximum rounds value +UPDATE ctest SET salt = gen_salt('sha512crypt', 1000000000); + +-- Extended tests taken from public domain code at +-- https://www.akkadia.org/drepper/SHA-crypt.txt +-- +-- We adapt the tests defined there to make sure we are compatible with the reference +-- implementation. + +-- This tests sha256crypt (magic byte $5$ with salt and rounds) +SELECT crypt('Hello world!', '$5$saltstring') + = '$5$saltstring$5B8vYYiY.CVt1RlTTf8KbXBH3hsxY/GNooZaBBGWEc5' AS result; +SELECT crypt('Hello world!', '$5$rounds=10000$saltstringsaltstring') + = '$5$rounds=10000$saltstringsaltst$3xv.VbSHBb41AL9AvLeujZkZRBAwqFMz2.opqey6IcA' AS result; +SELECT crypt('This is just a test', '$5$rounds=5000$toolongsaltstring') + = '$5$rounds=5000$toolongsaltstrin$Un/5jzAHMgOGZ5.mWJpuVolil07guHPvOW8mGRcvxa5' AS result; + SELECT crypt('a very much longer text to encrypt. This one even stretches over more' + 'than one line.', '$5$rounds=1400$anotherlongsaltstring') + = '$5$rounds=1400$anotherlongsalts$Rx.j8H.h8HjEDGomFU8bDkXm3XIUnzyxf12oP84Bnq1' AS result; +SELECT crypt('we have a short salt string but not a short password', '$5$rounds=77777$short') + = '$5$rounds=77777$short$JiO1O3ZpDAxGJeaDIuqCoEFysAe1mZNJRs3pw0KQRd/' AS result; +SELECT crypt('a short string', '$5$rounds=123456$asaltof16chars..') + = '$5$rounds=123456$asaltof16chars..$gP3VQ/6X7UUEW3HkBn2w1/Ptq2jxPyzV/cZKmF/wJvD' AS result; +SELECT crypt('the minimum number is still observed', '$5$rounds=10$roundstoolow') + = '$5$rounds=1000$roundstoolow$yfvwcWrQ8l/K0DAWyuPMDNHpIVlTQebY9l/gL972bIC' AS result; + +-- The following tests sha512crypt (magic byte $6$ with salt and rounds) +SELECT crypt('Hello world!', '$6$saltstring') + = '$6$saltstring$svn8UoSVapNtMuq1ukKS4tPQd8iKwSMHWjl/O817G3uBnIFNjnQJuesI68u4OTLiBFdcbYEdFCoEOfaS35inz1' AS result; +SELECT crypt('Hello world!', '$6$rounds=10000$saltstringsaltstring') + = '$6$rounds=10000$saltstringsaltst$OW1/O6BYHV6BcXZu8QVeXbDWra3Oeqh0sbHbbMCVNSnCM/UrjmM0Dp8vOuZeHBy/YTBmSK6H9qs/y3RnOaw5v.' AS result; +SELECT crypt('This is just a test', '$6$rounds=5000$toolongsaltstring') + = '$6$rounds=5000$toolongsaltstrin$lQ8jolhgVRVhY4b5pZKaysCLi0QBxGoNeKQzQ3glMhwllF7oGDZxUhx1yxdYcz/e1JSbq3y6JMxxl8audkUEm0' AS result; +SELECT crypt('a very much longer text to encrypt. This one even stretches over more' + 'than one line.', '$6$rounds=1400$anotherlongsaltstring') + = '$6$rounds=1400$anotherlongsalts$POfYwTEok97VWcjxIiSOjiykti.o/pQs.wPvMxQ6Fm7I6IoYN3CmLs66x9t0oSwbtEW7o7UmJEiDwGqd8p4ur1' AS result; +SELECT crypt('we have a short salt string but not a short password', '$6$rounds=77777$short') + = '$6$rounds=77777$short$WuQyW2YR.hBNpjjRhpYD/ifIw05xdfeEyQoMxIXbkvr0gge1a1x3yRULJ5CCaUeOxFmtlcGZelFl5CxtgfiAc0' AS result; +SELECT crypt('a short string', '$6$rounds=123456$asaltof16chars..') + = '$6$rounds=123456$asaltof16chars..$BtCwjqMJGx5hrJhZywWvt0RLE8uZ4oPwcelCjmw2kSYu.Ec6ycULevoBK25fs2xXgMNrCzIMVcgEJAstJeonj1' AS result; +SELECT crypt('the minimum number is still observed', '$6$rounds=10$roundstoolow') + = '$6$rounds=1000$roundstoolow$kUMsbe306n21p9R.FRkW3IGn.S9NPN0x50YhH1xhLsPuWGsUSklZt58jaTfF4ZEQpyUNGc0dqbpBYYBaHHrsX.' AS result; + +-- cleanup +DROP TABLE ctest; diff --git a/doc/src/sgml/pgcrypto.sgml b/doc/src/sgml/pgcrypto.sgml index f87668dfaed..b567b5c16e8 100644 --- a/doc/src/sgml/pgcrypto.sgml +++ b/doc/src/sgml/pgcrypto.sgml @@ -189,6 +189,29 @@ hmac(data bytea, key bytea, type text) returns bytea <entry>13</entry> <entry>Original UNIX crypt</entry> </row> + <row> + <entry><literal>sha256crypt</literal></entry> + <entry>unlimited</entry> + <entry>yes</entry> + <entry>up to 32</entry> + <entry>80</entry> + <entry>Adapted from publicly available reference implementation + <ulink url="https://www.akkadia.org/drepper/SHA-crypt.txt">Unix crypt using SHA-256 and SHA-512 + </ulink> + </entry> + </row> + <row> + <entry><literal>sha512crypt</literal></entry> + <entry>unlimited</entry> + <entry>yes</entry> + <entry>up to 32</entry> + <entry>123</entry> + <entry>Adapted from publicly available reference implementation + <ulink url="https://www.akkadia.org/drepper/SHA-crypt.txt">Unix crypt using SHA-256 and SHA-512 + </ulink> + </entry> + </row> + </tbody> </tgroup> </table> @@ -245,7 +268,9 @@ gen_salt(type text [, iter_count integer ]) returns text <para> The <parameter>type</parameter> parameter specifies the hashing algorithm. The accepted types are: <literal>des</literal>, <literal>xdes</literal>, - <literal>md5</literal> and <literal>bf</literal>. + <literal>md5</literal>, <literal>bf</literal>, <literal>sha256crypt</literal> and + <literal>sha512crypt</literal>. The last two, <literal>sha256crypt</literal> and + <literal>sha512crypt</literal> are modern <literal>SHA-2</literal> based password hashes. </para> <para> @@ -284,6 +309,12 @@ gen_salt(type text [, iter_count integer ]) returns text <entry>4</entry> <entry>31</entry> </row> + <row> + <entry><literal>sha256crypt, sha512crypt</literal></entry> + <entry>5000</entry> + <entry>1000</entry> + <entry>999999999</entry> + </row> </tbody> </tgroup> </table> @@ -313,6 +344,14 @@ gen_salt(type text [, iter_count integer ]) returns text <function>gen_salt</function>. </para> + <para> + The default <parameter>iter_count</parameter> for <literal>sha256crypt</literal> and + <literal>sha512crypt</literal> of <literal>5000</literal> is considered too low for modern + hardware, but can be adjusted to generate stronger password hashes. + Otherwise both hashes, <literal>sha256crypt</literal> and <literal>sha512crypt</literal> are + considered safe. + </para> + <table id="pgcrypto-hash-speed-table"> <title>Hash Algorithm Speeds</title> <tgroup cols="5"> -- 2.48.1 ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-03-11 16:58 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2023-09-25 05:01 [PATCH v8 6/7] Row pattern recognition patch (tests). Tatsuo Ishii <[email protected]> 2025-02-07 08:17 Re: Modern SHA2- based password hashes for pgcrypto Japin Li <[email protected]> 2025-02-07 09:31 ` Re: Modern SHA2- based password hashes for pgcrypto Alvaro Herrera <[email protected]> 2025-02-08 02:16 ` Re: Modern SHA2- based password hashes for pgcrypto Japin Li <[email protected]> 2025-03-11 16:58 ` Re: Modern SHA2- based password hashes for pgcrypto Bernd Helmle <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox