函式在 postgreSQL 中只接受某些密碼

我在 postgreSQL(v10.10) 數據庫中創建了一個函式來創建新使用者或更新現有使用者。

作為參數，您傳遞使用者名、密碼、角色成員資格、名字和姓氏。在我的情況下，該函式由連接到該數據庫的外部程序呼叫。基本上它可以工作，但奇怪的是只有密碼只包含小寫字母並且不以數字開頭。只要密碼包含大寫字母、特殊字元或密碼以數字開頭，就會創建使用者，但之後嘗試連接時，登錄數據應該是錯誤的。

我已經將該函式中的密碼寫入了日誌記錄表。那裡的密碼也與給定的密碼參數相同（例如大寫、數字等），但登錄仍然無效。這就是我正在使用的功能：

DECLARE
v_role TEXT;
v_timestamp TIMESTAMP;
v_status TEXT;

BEGIN
   v_timestamp := NOW();
   --check ob Nutzer bereits angelegt, dann create oder alter
   IF NOT EXISTS (
     SELECT FROM pg_catalog.pg_roles  
     WHERE  rolname = v_username) 
     THEN --Neuanlage
       --lege Nutzer an
       EXECUTE FORMAT('CREATE USER %I WITH PASSWORD ''%I''', v_username, v_password);
       EXECUTE FORMAT(E'COMMENT ON ROLE %I IS ''%s %s \nAngelegt am:\n%s''', v_username, v_vorname, v_nachname, to_char(v_timestamp, 'DD.MM.YYYY HH24:MI:SS'));
       v_status := 'neuanlage';
       
     ELSE --Änderung
     --ändere bestehenden Nutzer
       EXECUTE FORMAT('ALTER USER %I WITH PASSWORD ''%I''', v_username, v_password);
       EXECUTE FORMAT(E'COMMENT ON ROLE %I IS ''%s %s \nUpdate am:\n%s''', v_username, v_vorname, v_nachname, to_char(v_timestamp, 'DD.MM.YYYY HH24:MI:SS'));
       --entferne Mitgliedschaft aus bestehenden Gruppen
       FOR v_role IN
       SELECT rolname FROM pg_roles WHERE pg_has_role( v_username, oid, 'member')
       LOOP
           IF v_role != v_username THEN
               EXECUTE(FORMAT('revoke %I from %I', v_role, v_username));
           END IF;
       END LOOP;
       v_status := 'update';
   END IF;
   --füge zu Gruppen hinzu
   FOREACH v_role IN ARRAY v_roles LOOP
       EXECUTE FORMAT('GRANT %I TO %I', v_role, v_username);
   END loop;
   
   --Protokollierung
   INSERT INTO verwaltung.roles_protokoll (roles, status, timestamp, username, password) VALUES (v_roles, v_status, v_timestamp, v_username, v_password);
   
   RETURN 1;
   -- Simple Exception
EXCEPTION
   WHEN others THEN
       RETURN 0;
END;

你知道那裡出了什麼問題嗎？

那是因為您使用了錯誤的格式說明符。%I用於標識符，但密碼是字元串文字，因此您應該使用%L（並且不包括單引號）。

比較這些：

SELECT format('PASSWORD %L', 'gut'), format('PASSWORD ''%I''', 'gut');
    format     |     format     
----------------+----------------
PASSWORD 'gut' | PASSWORD 'gut'
(1 row)

test=> SELECT format('PASSWORD %L', 'Böse'), format('PASSWORD ''%I''', 'Böse');
    format      |      format       
-----------------+-------------------
PASSWORD 'Böse' | PASSWORD '"Böse"'
(1 row)

所以密碼中包含了意外的雙引號。

但還有更大的問題：

SELECT format('ALTER USER me PASSWORD ''%I''', 'inject'' SUPERUSER --');
                    format                      
-------------------------------------------------
ALTER USER me PASSWORD '"inject' SUPERUSER --"'
(1 row)

哈！我可以濫用你的功能成為超級使用者，因為你沒有使用正確的引用。

引用自：https://dba.stackexchange.com/questions/269246