对于没有耐心的人——我可以把这个问题总结为:
可以使用什么实用的方法来利用基于角色的权限在PostgreSQL使用访问前端采用ODBC链接表?
现在是更长的版本:
我继承了一项令人讨厌的任务，将Access 2000/PG 7应用程序升级到Access 2013/PG 9。我是PostgreSQL的新手，但使用过Oracle和Microsoft Access相当多。编辑:生产服务器在Mac OS X Lion上运行PostgreSQL。我的测试机器在Oracle Linux 7上运行PostgreSQL。
这个Access DB通过ODBC连接到PG数据库中的表，使用单个PG登录角色( application_user )进行连接。每个用户都连接到这个登录角色，只有Forms/VBA中的条件限制了用户的权限。但是，如果用户可以进入导航窗格，那么他们可以直接访问链接表并绕过所有安全限制。在升级这个数据库的时候，我想看看我能不能把它收紧。
我可以在PostgreSQL上为每个用户设置自己的登录角色，但这将意味着(从我的角度来看)大量的数据库重组。我不希望在生产数据库上做这么大的改变——增量式的改变更可取。
查看数据库的安全需求——我只能想到五个需要的角色。
- 订单输入 <
- 客户条目/gh>
- 订单和客户输入 只读>
- 未授权-禁止访问
我可以将这些设置为PGSQL中的组角色，并为每个角色设置必要的ACL。
我缺少的是如何从单个登录角色( application_user )转换到上述所有角色?
我最初的想法是将 application_user (登录角色)设置为没有组角色(本质上导致"Not Authorized - no Access")，然后使用调用PL/pgSQL函数 authorize(Username, MD5PassWord) 来授权和提升角色。该函数将检查提供的MD5哈希是否与存储在users表中的MD5哈希匹配，如果匹配，它将为相应的组角色发出SET SESSION ROLE。
如果这可以工作，它将允许我跟踪正在登录的用户名，然后使用 pg_backend_pid() 函数，我可以将其与用户关联起来，用于业务逻辑或日志记录或其他。这也意味着我不需要担心某些用户是否进入了链接表——因为他们的访问将受到他们当前在该数据库会话中被授权的任何角色的限制。
于是我编写了一个plpgsql脚本，将其所有者设置为OrderCustomerEntryGroup，并赋予其SECURITY DEFINER权限。
DECLARE
v_Status integer;
BEGIN
v_Status := 0;
IF pin_username = 'username' AND MD5('foo') = pin_pwmd5 THEN
SET SESSION AUTHORIZATION OrderEntryGroup;
v_Status := 1;
END IF;
RETURN v_Status;
END;
然而，我实现的唯一问题是
SELECT authenticate('username',MD5('foo'));
给:
ERROR: cannot set parameter "session_authorization" within security-definer function
SQL state: 42501
Context: SQL statement "SET SESSION AUTHORIZATION OrderEntryGroup"
PL/pgSQL function authenticate(character varying,text) line 7 at SQL statement
所以我仔细研究了一下——据我所知，你以前可以这样做，但由于某种原因它被删除了。除了在每个用户级别上使用内置角色之外，我还没能找到其他选择。
所以我要问的是…我错过了什么使我的方法(一个简单的解决方案)工作，或者有一个更好的方法来做到这一点，不会涉及撕裂现有的访问数据库?
如果您想限制直接连接对数据库的访问，那么无论如何都需要在后端进行一定数量的"重组"。最好的方法几乎总是让每个用户使用自己的凭据连接，然后根据用户在数据库中所属的组(有时称为"角色")限制该用户可以执行的操作。
如果你想避免为每个网络用户设置单独的数据库用户名/密码，那么你应该研究使用集成的Windows身份验证(SSPI)，就像这里的另一个问题中讨论的那样。您仍然需要在数据库级别定义用户(除了组/角色之外)，但是无论如何您都必须完成大部分工作。