postgresql - 如何使用 postgres 角色和密码进行网站身份验证

Question

我希望 future 的 Web 应用程序的用户能够使用他们的网站用户名和密码登录到 postgres 数据库。

该网站将使用特殊用户名(例如“web”)连接到 postgres，该用户名将无法用作网站用户名。 (就像“postgres”等一样)

尚无用户或密码存在，因此不存在迁移问题。所有网站用户都将是 postgres 用户。该网站可以散列密码，而 postgres 可以。

以最少的数据重复实现这一目标的最明智方法是什么？

我可以使用/扩展 postgres 的内部角色表作为用户名/密码身份验证的一般来源吗？即我需要能够运行一个查询，该查询将告诉我给定的用户名/密码组合是否有效。

或者单独存储网站用户名和密码并以某种方式让 postgres 使用它会更好吗？

Answer 1

您确实可以使用 select 检查身份验证

t=# select 'md5'||md5('somePass'||usename) = passwd from pg_shadow where usename = 'web';
 ?column?
----------
 t
(1 row)

但是你不应该直接操作 pg_authid，而是使用 SQL 语句，比如:

t=# create user web password 'somePass';
CREATE ROLE

或

t=# alter user web password 'anotherP';
ALTER ROLE
t=# select 'md5'||md5('somePass'||usename) = passwd from pg_shadow where usename = 'web';
 ?column?
----------
 f
(1 row)

t=# select 'md5'||md5('anotherP'||usename) = passwd from pg_shadow where usename = 'web';
 ?column?
----------
 t
(1 row)

和:

t=# drop user web;
DROP ROLE
t=# select 'md5'||md5('anotherP'||usename) = passwd from pg_shadow where usename = 'web';
 ?column?
----------
(0 rows)

为了避免在应用程序中扮演 SU 角色，只需为帐户操作创建包装，例如:

t=# create or replace function adduser(_u text,_p text) returns boolean as $$
begin
 execute format('create user %I password %L',_u,_p);
 return true;
end;
$$ language plpgsql security definer;
CREATE FUNCTION

安全定义器将允许应用用户创建新角色:

t=# select adduser('web','newP');
 adduser
---------
 t
(1 row)

t=# select 'md5'||md5('newP'||usename) = passwd from pg_shadow where usename = 'web';
 ?column?
----------
 t
(1 row)

不要忘记添加一些逻辑和异常，这样应用程序就不会干扰现有的 SU 角色(不删除，不更改密码)。

最后我认为这样的想法可能很危险。在实现之前计划...