Authentication error after using stored procedure for creating new db user-CodePudding

I'm having this problem in postgres and I cannot find my way out.

For our key-users to create user accounts in our postgres database (Ubuntu 18.04.6 LTS (GNU/Linux 5.4.0-1074-azure x86_64)), I created a stored procedure that creates a user with a password.

This is the code for the stored procedure:

CREATE OR REPLACE PROCEDURE geodata_create_user(
   username CHARACTER VARYING,
   password CHARACTER VARYING
)
LANGUAGE PLPGSQL SECURITY DEFINER
AS 
$$
DECLARE
    user_to_create CHARACTER VARYING;
BEGIN
    -- Check name and create user
    IF (username like 'user%') THEN
      user_to_create := username;
    ELSE
      user_to_create := 'user_' || username;
    END IF;

    PERFORM create_role_if_not_exists (user_to_create);

    -- Set password and assign geodata_user role to user
    EXECUTE format('ALTER ROLE %I WITH LOGIN PASSWORD ''%I''', user_to_create, password);
    EXECUTE format('GRANT another_role TO %I', user_to_create);
end;
$$;

GRANT ALL ON PROCEDURE geodata_create_user TO some_group_role;

If I test it (we have a dev cluster and an acc/prod cluster), it works fine.

The problem is that if one of the key-users creates a new user using this procedure, the user is created yet cannot login using the supplied password.

Following suggestion in pgAdmin and terminal: FATAL: password authentication failed for user to login using peer logon, I tried logging in on psql using the psql -U newuser command it tells me FATAL: Peer authentication failed for user

Then, following suggestion in psql: FATAL: Peer authentication failed for user "dev" logging on the psql using psql -U newuser -d mydbname -h 12.345.678.910 and it replies (after feeding the password created for the user):

Password for user newuser: psql: error: connection to server at "12.345.678.910", port 8765 failed: could not initiate GSSAPI security context: Unspecified GSS failure. Minor code may provide more information: Server postgres/[email protected] not found in Kerberos database connection to server at "12.345.678.910", port 8765 failed: FATAL: password authentication failed for user "newuser" connection to server at "12.345.678.910", port 8765 failed: FATAL: no pg_hba.conf entry for host "12.345.678.910", user "newuser", database "mydbname", SSL off

I do have a little database experience, however, this kind of stuff is way out of my league. Any one any idea on what goes wrong here? Is there an obvious error in my script? Or might it be a security issue (we use certificates to access the database server when approaching the database server through psql directly, yet usually we set up connections to the database using DBeaver where there is no need to use SSL or SSH Tunnel or certificates or so).

Hope someone can help me out on this. Regards, Helmoet.

CodePudding user response：

Your format() function is wrong, because a password is not a SQL identifier (which is what %I is for). This will result in it being escaped incorrectly at least in some cases. For example, if the password has a space or a dash in it (or other characters I don't know off the top of my head), this will cause the whole password to be surrounded by literal double quotes. Then the password would work, but you need to specify the double quotes, which is surely not what you would be expecting.

So it should instead look like this, using %L not %I:

EXECUTE format('ALTER ROLE %I WITH LOGIN PASSWORD %L', user_to_create, password);

Your original code would work for passwords that didn't have any of the special symbols in them, so maybe that is why it tested fine.