使用Postgresql作为中间层。需要意见
我需要一些意见。
我要为朋友开发 POS 和库存软件。这是一个单人小规模项目，所以我想让架构尽可能简单。
我正在使用 Winform 开发 GUI（Web 界面对于 POS 软件没有意义）。对于数据库，我使用 Postgresql。
该程序将根据用户角色控制访问，因此我要么必须使用 Web 服务器开发中间层来控制用户访问，要么我可以直接在 Postgresql 中设置用户权限。
开发中间层会比较耗时，而且维护也会比较复杂。所以我更喜欢直接在数据库中设置访问控制。
现在看来，用数据库来控制用户访问是很麻烦的。我必须为每个角色设置权限。更不用说对于某些表来说，权限是列级别的。这使得关于安全性的推理变得非常困难。
所以我现在要做的就是将所有表设置为除超级用户之外都无法访问。该程序将使用公共角色连接到数据库。由于这些表无法被公众访问，因此我将使用 SECURITY DEFINER（具有超级用户角色）创建可公开访问的存储函数。访问表的唯一方法是使用这些函数。
我将把用户角色和密码放在表中。由于非超级用户无法访问用户表本身，因此我将创建一个登录函数，我们将其命名为fn_login(username,password)。如果登录成功，fn_login 将返回会话密钥。
要调用其他函数，我们需要为用户提供会话密钥，例如：fn_purchase_list(session_key)、fn_purchase_new(session_key,purchase_id, ...)。
这样，我将存储的函数视为 API。添加新用户会更容易，因为我只需要在用户表中添加新行，而不是添加新的 Postgresql 角色。我不需要在列级别设置权限。所有控制都将以编程方式完成。
那么你觉得怎么样？这种方法是否可行且可扩展？有更好的方法吗？
谢谢！
I need some opinions.
I'm going to develop a POS and inventory software for a friend. This is a one man small scale project so I want to make the architecture as simple as possible.
I'm using Winform to develop the GUI (web interface doesn't make sense for POS software). For the database, I am using Postgresql.
The program will control access based on user roles, so either I have to develop a middle tier, using a web server, to control user access or I can just set user priveleges directly in Postgresql.
Developing a middle tier will be time consuming, and the maintenance will be more complex. So I prefer to set access control directly in the database.
Now it appears that using database to control user access is troublesome. I have to set priveleges for each role. Not to mention that for some tables, the priveleges are at column level. This makes reasoning about the security very hard.
So what I'm doing now is to set all the tables to be inaccessible except by superusers. The program will connect to the database using public role. Because the tables are inaccessible by public, I'm going to make publicly accessible stored functions with SECURITY DEFINER (with superuser role). The only way to access the tables is by using these functions.
I'll put the user roles and passwords in a table. Because the user table itself is inaccessible by non-superuser, I'll make a login function, let's call it fn_login(username, password). fn_login will return a session key if login is successful.
To call other functions, we need to supply session key for the user, e.g.: fn_purchase_list(session_key), fn_purchase_new(session_key, purchase_id, ...).
That way, I'm treating the stored functions as APIs. Adding new user will be easier as I only need to add new rows in the user table rather than adding new Postgresql roles. I won't need to set priveleges at column level. All controls will be done programmatically.
So what do you think? Is this approach feasible and scalable? Is there a better way to do it?
Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
我相信有更好的方法来做到这一点。但由于您还没有讨论您需要什么类型的安全性，因此我无法详细说明具体细节。
由于您是在 .NET 中开发应用程序代码，因此需要信任该代码（与 Web 应用程序不同）。因此，为什么不在应用程序代码中而不是在数据库中简单地实现角色和权限呢？
我对您所说的方法的担忧是存储过程的人力开销。更愿意看到您用 C# 编写所述函数，而不是在 PostgreSQL 中。然后，可以应用标准版本控制和软件开发技术。
I believe there is a better way to do it. But since you haven't discussed what type of security you need, I cannot elaborate on specifics.
Since you are developing the application code in .NET, that code needs to be trusted (unlike a web application). Therefore, why don't you simply implement your roles and permissions in the application code, rather than the database?
My concern with your stated approach is the human overhead of stored procedures. Would much rather see you write the stated functions in C#, rather than in PostgreSQL. Then, standard version control and software development techniques could apply.
如果你等到有人访问你的数据库来检查安全性，我认为你就太晚了。这是 20 世纪 90 年代末的客户端/服务器思维模式。这是 n 层架构流行的部分原因。客户端/服务器无法像 n 层解决方案那样水平扩展。
我建议您更好地利用中间层。安全性应该是一个跨领域的问题，比持久层更重要。
If you wait until somebody has at your database to check security, I think you'll be too late. That's a client/server mentality that went out at the end of the 90s. It's part of the reason why n-tier architectures came into vogue. Client/server can't scale horizontally as well as an n-tier solution.
I'd advise that you take better advantage of the middle tier. Security should be a cross-cutting concern that's further up the stack than your persistence layer.
如果数据库安全的管理是问题，那么您应该添加自动化该管理的任务。这意味着您可以使用数据库表存储更高级别的数据，然后您的应用程序可以将该数据转换为数据库所需的适当详细信息和工件。
听起来数据库有您需要的详细信息，您只需要促进该详细信息的管理，并将其滚动到您的应用程序中。
If the MANAGEMENT of the database security is the issue, then you should add the task of automating that management. That means that you can store higher level data with the database tables, and then your application can convert that data in to the appropriate details and artifacts that the database requires.
It sounds like the database has the detail that you need, you just need to facilitate the management of that detail, and roll that in to your app.
我诚实的建议：不要发明 POS 和库存软件。采用现有项目之一并使其变得更好。
My honest advice: Do not invent POS and inventory software. Take one of existing projects and make it better.