使用 Access ODBC 链接表的 PostgreSQL 授权

Question

对于不耐烦的人——我可以将这个问题总结为:

What practical approach can be used to leverage role-based privileges in PostgreSQL when using an Access Front End that employs ODBC linked-tables?

现在是更长的版本:

我接手了将 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中的条件。但是，如果用户可以进入导航 Pane - 他们可以直接 Access 链接表并绕过所有安全限制。在升级此数据库时，我想看看是否可以收紧它。

我可以在 PostgreSQL 上为每个用户设置他们自己的登录角色，但这意味着(从我的角度来看)对数据库进行大量重组。我不想对生产数据库进行如此大的更改 - 更需要增量更改。

查看数据库的安全需求 - 我可以想到只需要五个角色。

• 订单输入
• 客户输入
• 订单和客户输入
• 只读
• 未授权 - 无权 Access

我可以在 PGSQL 中将这些设置为组角色，并且每个表都为每个角色设置必要的 ACL。

我缺少的是如何从单一登录角色 (application_user) 转变为上述所有角色？

我最初的想法是将 application_user(登录角色)设置为没有组角色(基本上导致 “未授权 - 无 Access 权”)，然后调用 PL/pgSQL 函数 authorize(Username, MD5PassWord) 来授权和提升角色。该函数将检查提供的 MD5 哈希值是否与存储在用户表中的 MD5 哈希值匹配 - 如果匹配 - 它会为适当的组角色发出 SET SESSION ROLE。
如果这可行，它会让我跟踪登录的用户名，然后使用 pg_backend_pid() 函数，我可以将它与用户关联起来业务逻辑或日志记录或其他任何东西。这也意味着如果某些用户进入链接表，我不必担心 - 因为他们的 Access 将受到他们当前在该数据库 session 中被授权的任何角色的限制。

所以我创建了一个 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

所以我仔细阅读了这个 - 据我所知，你曾经能够做到这一点，但无论出于何种原因它被删除了。除了在每个用户级别使用内置角色外，我还没有找到替代方案。

所以我要问的是..我缺少什么来使我的方法(一个简单的解决方案)起作用，或者是否有更好的方法来做到这一点这不会涉及撕裂现有的 Access 数据库吗？

Answer 1

如果您想限制直接连接对数据库的 Access ，那么无论如何您都需要在后端进行一定数量的“重组”。最好的方法几乎总是让每个用户使用他们自己的凭据连接，然后根据他们在数据库中所属的组(有时称为“角色”)限制该用户可以执行的操作。

如果您想避免为每个网络用户设置单独的数据库用户 ID/密码，那么您应该研究使用集成 Windows 身份验证 (SSPI)，如另一个问题中所述 here .您仍然需要在数据库级别定义用户(除了组/角色)，但无论如何您都必须完成大部分工作。