使用存储过程创建新的数据库用户后出现身份验证错误

Question

我在Postgres中遇到了这个问题，我找不到出路。

为了使我们的主要用户在我们的Postgres数据库中创建用户帐户（Ubuntu 18.04.6 LTS（GNU/Linux 5.4.0-1074--azure x86_64）），我创建了一个存储过程，可以创建使用密码的用户。

这是存储过程的代码：

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;

如果我对其进行测试（我们有一个开发群集和ACC/Prod群集），则可以正常工作。

问题是，如果其中一位主要用户使用此过程创建新用户，则可以创建用户，但无法使用提供的密码登录。

以下建议在 pgadmin和terminal：fatal：致命的： 要使用对等登录登录，我尝试使用psql -u newuser命令在psql上登录，它告诉我致命：用户对用户的peer身份验证失败

，然后在 psql：fatal：fatal：对等身份验证的用户“ dev” 使用psql使用psql-u newuser -u newuser -d mydbname登录psql -h 12.345.678.910及其回复（在为用户创建的密码提供后：

用户newuser的密码： psql：错误：与服务器连接在“ 12.345.678.910”，端口8765失败：无法启动GSSAPI安全环境：未指定的GSS失败。 Minor code may provide more information: Server postgres/[email protected] not found在Kerberos数据库中 连接到服务器的“ 12.345.678.910”，端口8765失败：致命：用户“ newuser”的密码身份验证失败 连接到服务器的“ 12.345.678.910”，端口8765失败：致命：无主机“ 12.345.678.910”，用户“ newuser”，数据库“ mydbname”，

我确实有一些数据库体验， ，这种东西不在我的联盟中。有人对这里出了什么问题有任何想法吗？我的脚本中有明显的错误吗？或可能是安全问题（我们使用证书直接通过PSQL访问数据库服务器时访问数据库服务器，但通常我们使用DBEAVER设置了与数据库的连接，而DBEAVER无需使用SSL或SSH隧道或证书或证书或证书或证书或证书或证书所以）。

希望有人可以帮助我解决这个问题。 问候，头盔。

Answer 1

您的format（）函数是错误的，因为密码不是SQL标识符（这是i的价格）。至少在某些情况下，这将导致它被错误地逃脱。例如，如果密码中有一个空间或破折号（或我不知道的其他字符），则会导致整个密码被字面的双引号包围。然后，密码可以工作，但是您需要指定双引号，这肯定不是您期望的。

因此，它应该看起来像这样，使用％l非％i：

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

您的原始代码将适用于没有任何特殊符号的密码，因此也许这就是为什么测试良好的原因。