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.