Function accepts only certain passwords in postgreSQL

Question

I have created a function in a postgreSQL(v10.10) database to create new users or update existing ones.

As parameters you pass the username, password, role memberships, first name and last name. In my case the function is called by an external program with connection to this database. Basically it works, but strangely only passwords are taken over which contain only lower case letters and do not start with a number. As soon as the password contains uppercase letters, special characters or the password begins with a number, the user is created, but the login data is supposed to be wrong when trying to connect afterwards.

I have already written the password in that function into a logging table. There the password is also identical with the given password parameter(e.g. uppercase, numbers, etc.), but a login still does not work. Thats my function I am using:

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;

Do you have any ideas what's going wrong in there?

Answer 1

That's because you used the wrong format specifier. %I is for identifiers, but the password is a string literal, so you should use %L (and not include the single quotes).

Compare these:

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)

So there were unexpected double quotes included in the password.

But there are bigger problems:

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

Ha! I can abuse your function to become superuser, because you didn't use proper quoting.