PostgreSQL使用函数创建用户

Question

我目前无法在函数内创建postgresql数据库用户。 背景: 我有一个Java Swing应用程序，我的目标是开发一个菜单来创建、修改和删除数据库用户。为了增加安全性，我创建了一个名为“usermanagement”的角色，只有该角色的成员才被允许使用创建用户的功能。该角色还包含了“createuser”权限。

查询运行没有任何问题，但它并没有创建新用户... 所以我不知道哪里出了问题。

以下是我尝试使用该函数的方法:

SELECT create_databaseuser(v_username := 'thisname' ,v_password := 'pwpwpw');

有人能帮忙吗？

这是我的代码：

-- Function: public.create_databaseuser(text, text)

-- DROP FUNCTION public.create_databaseuser(text, text);

CREATE OR REPLACE FUNCTION public.create_databaseuser(
    v_username text,
    v_password text)
  RETURNS numeric AS
$BODY$
DECLARE
    r_id numeric;
BEGIN
    --CREATE ROLE v_username LOGIN
    --PASSWORD 'v_password' NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

    EXECUTE 'CREATE USER ' || v_username || ' WITH PASSWORD ' || v_password;
-- Alternative:CREATE ROLE v_username LOGIN PASSWORD v_password NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

    return 1;
    -- Simple Exception
EXCEPTION
    WHEN others THEN
        RETURN 0;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  COST 100;
ALTER FUNCTION public.create_databaseuser(text, text)
  OWNER TO postgres;

- Beginner

---

这可能更适合发布到兄弟站点http://dba.StackExchange.com/。 - Basil Bourque

Answer 1

请注意STRICT（在输入为NULL时返回NULL），使用FORMAT()以防止SQL注入问题，并正确引用输入。输入参数“v_username”已修订为NAME类型，以匹配pg_catalog.pg_roles中的类型。

DROP FUNCTION IF EXISTS public.create_databaseuser(NAME, TEXT);

CREATE OR REPLACE FUNCTION public.create_databaseuser(
    v_username NAME,
    v_password TEXT)
RETURNS smallint AS
$BODY$
DECLARE
BEGIN
    EXECUTE FORMAT('CREATE ROLE "%I" LOGIN PASSWORD ''%L''', v_username, v_password);
    RETURN 1;
    -- Simple Exception
EXCEPTION
    WHEN others THEN
        RETURN 0;
END;
$BODY$
LANGUAGE plpgsql STRICT VOLATILE SECURITY DEFINER
COST 100;

ALTER FUNCTION public.create_databaseuser(NAME, TEXT) OWNER TO postgres;


select rolname from pg_catalog.pg_roles order by 1;
SELECT create_databaseuser(v_username := 'thisname' ,v_password := 'pwpwpw');
select rolname from pg_catalog.pg_roles order by 1;

- bma

---

非常感谢。它正在工作。同时，我只是在我的应用程序中使用了普通的SQL，但现在我可以更改并使用函数，这样会更好 :) - Beginner

你有没有建议，如果我想使用纯数字作为密码？ 我用'pw123'调用函数是可以的，但'12345'不行。实际上，我发现它是可以工作的。但是之后密码将不会是12345。我检查了md5哈希值，因为我用pgadmin和我的函数更改了密码。 之后，密码被存储为“12345”。所以我可以在函数内部使用正则表达式来解决这个问题。现在，如果我想进一步使用像$这样的特殊字符，也无法工作。我该如何解决这个问题？ - Beginner

密码应该使用%L（字面量）进行替换，而不是%I（标识符）。我已经编辑了函数体以进行修复。 - kgrittn