代码之家 › 专栏 › 技术社区 › Joey Adams

在PostgreSQL中使用视图进行访问控制

sql-view access-control postgresql

Joey Adams · 技术社区 · 15 年前

我有一个表的模式,其内容基本上可以归结为:

一组用户
一组对象组
访问控制列表(ACL),指示哪些用户可以访问哪些组
一组对象,每个对象恰好属于一个组。

我想创建一个支持访问控制的简单应用程序。我认为观点是一个很好的方法。

假设我进行了以下数据库初始化:

/* Database definition */

BEGIN;

CREATE SCHEMA foo;

CREATE TABLE foo.users (
    id SERIAL PRIMARY KEY,
    name TEXT
);

CREATE TABLE foo.groups (
    id SERIAL PRIMARY KEY,
    name TEXT
);

CREATE TABLE foo.acl (
    user_ INT REFERENCES foo.users,
    group_ INT REFERENCES foo.groups
);

CREATE TABLE foo.objects (
    id SERIAL PRIMARY KEY,
    group_ INT REFERENCES foo.groups,
    name TEXT,
    data TEXT
);

/* Sample data */

-- Create groups A and B
INSERT INTO foo.groups VALUES (1, 'A');
INSERT INTO foo.groups VALUES (2, 'B');

-- Create objects belonging to group A
INSERT INTO foo.objects VALUES (1, 1, 'object in A', 'apples');
INSERT INTO foo.objects VALUES (2, 1, 'another object in A', 'asparagus');

-- Create objects belonging to group B
INSERT INTO foo.objects VALUES (3, 2, 'object in B', 'bananas');
INSERT INTO foo.objects VALUES (4, 2, 'object in B', 'blueberries');

-- Create users
INSERT INTO foo.users VALUES (1, 'alice');
INSERT INTO foo.users VALUES (2, 'amy');
INSERT INTO foo.users VALUES (3, 'billy');
INSERT INTO foo.users VALUES (4, 'bob');
INSERT INTO foo.users VALUES (5, 'caitlin');
INSERT INTO foo.users VALUES (6, 'charlie');

-- alice and amy can access group A
INSERT INTO foo.acl VALUES (1, 1);
INSERT INTO foo.acl VALUES (2, 1);

-- billy and bob can access group B
INSERT INTO foo.acl VALUES (3, 2);
INSERT INTO foo.acl VALUES (4, 2);

-- caitlin and charlie can access groups A and B
INSERT INTO foo.acl VALUES (5, 1);
INSERT INTO foo.acl VALUES (5, 2);
INSERT INTO foo.acl VALUES (6, 1);
INSERT INTO foo.acl VALUES (6, 2);

COMMIT;

我的想法是使用镜像数据库的视图,但仅限于当前用户(由我的PHP脚本确定)可以访问的内容(这里我只使用用户“bob”)。假设我在每一个PostgreSQL会话的开头运行这个(意味着每次有人访问我的站点上的页面时):

BEGIN;

CREATE TEMPORARY VIEW users AS
SELECT * FROM foo.users
WHERE name='bob';

CREATE TEMPORARY VIEW acl AS
SELECT acl.* FROM foo.acl, users
WHERE acl.user_=users.id;

CREATE TEMPORARY VIEW groups AS
SELECT groups.* FROM foo.groups, acl
WHERE groups.id=acl.group_;

CREATE TEMPORARY VIEW objects AS
SELECT objects.* FROM foo.objects, groups
WHERE objects.group_=groups.id;

COMMIT;

我的问题是,这是一个好方法吗?这些创建临时视图语句是否会产生很大的开销,特别是与几个简单查询相比?

另外,是否有一种方法可以使这些视图在我的数据库定义中保持永久性,然后在每个会话中将一个值绑定到用户名?这样,就不必每次用户加载页面时都创建所有这些视图。

1 回复 | 直到 15 年前

richardtallent 15 年前

这种方法的几个问题:

一用户 网状物 会话与会话不同 数据库 会话。具有某种设置的多个用户将立即失败。
创建/销毁视图的管理开销。

相反,我建议使用以下视图:

CREATE VIEW AllowedObjects
SELECT objects.*, users.name AS alloweduser
FROM objects
   INNER JOIN groups ON groups.id = objects.group_
   INNER JOIN acl ON acl.group_ = groups.id
   INNER JOIN users ON users.id = acl.user_

然后,在选择对象的任何位置:

SELECT * FROM AllowedObjects
WHERE alloweduser='Bob'

这假设Bob只能有一个ACL将他连接到一个特定的组,否则就需要一个distinct。

这可以抽象为一个稍微不复杂的视图,用于更容易检查更新和删除的权限:

CREATE VIEW AllowedUserGroup
SELECT groups.id AS allowedgroup, users.name AS alloweduser
FROM groups
   INNER JOIN acl ON acl.group_ = groups.id
   INNER JOIN users ON users.id = acl.user_

这提供了一个扁平视图,其中用户在哪些组中,您可以在更新/删除期间对照对象表进行检查:

UPDATE objects SET foo='bar' WHERE id=42 AND EXISTS
(SELECT NULL FROM AllowedUserGroup 
 WHERE alloweduser='Bob' AND allowedgroup = objects.group_)