如何使用Postgresql 9.5 SP中的变量更改新创建用户的密码

问题描述

我已经为我的postgresql 9.5数据库创建了一个存储过程(函数)以创建用户.下面的代码:

I have created a stored procedure (function) for my postgresql 9.5 database to create users. code below:

CREATE FUNCTION add_user (name text, cred text) RETURNS void AS $$
DECLARE
  name text := 'abc123';
BEGIN
  CREATE USER name WITH LOGIN NOSUPERUSER INHERIT NOCREATEDB
  NOCREATEROLE NOREPLICATION;
  GRANT CONNECT ON DATABASE test TO name;
  GRANT USAGE ON SCHEMA test_schema TO name;
  GRANT SELECT ON test_schema.test_table TO name;
  ALTER USER name WITH PASSWORD cred;
END;
$$ LANGUAGE plpgsql;

这无法更改用户密码.当我用硬编码的密码替换"cred"时，它就起作用了.

This fails for altering password for the user. The moment I replace "cred" with hardcoded password, it works.

因此，使用PASSWORD'userpassword'更改用户名即可，但不包含任何变量(信标).

So, ALTER USER name WITH PASSWORD 'userpassword' works,but NOT with any variable (cred).

无论如何，我们可以使用变量而不是对密码进行硬编码吗?

Is there anyway we can use variable instead of hard coding the password?

试图运行另一个功能，仅输入密码

Tried running another function, just for password

推荐答案

您必须使用动态SQL执行ALTER USER，例如:

You must execute the ALTER USER with dynamic SQL, for example:

EXECUTE 'ALTER USER || name || ' WITH PASSWORD '|| cred;

我认为您必须在CREATE和GRANT命令的整个代码中执行动态SQL，因为我认为总会创建一个名为名称"的用户

I believe that you must execute dynamic SQL in whole code of CREATE and GRANT commands, because I think always will create the user called "name"

这篇关于如何使用Postgresql 9.5 SP中的变量更改新创建用户的密码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！