允许安全执行任意sql所需的权限
我想创建一个 SQL 沙箱，允许用户在 PostGIS 数据库中执行任意 SELECT 查询。本质上，我想允许用户访问 psql 来玩。
显然，如果允许写访问，这将是一场安全灾难。如何创建一个允许查询数据的系统，但用户不存在合理的可能性：
- 损害数据库中的数据
- 获得对服务器更广泛的访问权限
- 使用像 SELECT * from long_table 这样的查询来破坏系统， long_table, long_table, long_table, long_table, long_table, long_table 需要一生时间才能执行
请在回答中尽可能具体。
I want to create an SQL sandbox that will allow users to execute arbitrary SELECT queries at a PostGIS database. Essentially, I want to allow users access to psql to play with.
Obviously this is a security disaster if write access is allowed. How can I create a system such that querying data is allowed, but there is no reasonable possibility of a user:
- Compromising the data in the database
- Gaining broader access to the server
- Crippling the system with a query like
SELECT * from long_table, long_table, long_table, long_table, long_table, long_table, long_tablethat will take a life-time to execute
Please be as specific as possible in your answer.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
正如其他人指出的那样，问题 #1 和问题 #2 是通过显式授予和撤销权限来处理的。
至于#3，
对我来说一直很有效。这将终止任何执行时间超过 1 分钟（60000 毫秒）的查询。我们在 phpPgAdmin 中的几个用户编写的查询导致问题后发现了这一点......
Problems #1 and #2 are handled via explicit GRANTs and REVOKEs of permissions as others have noted.
As for #3,
has always worked well for me. This terminates any query that executes for longer than 1 minute (60000 ms). We discovered this after having a couple of user-written queries in phpPgAdmin cause problems...
好吧，你真的需要创建一个角色然后< a href="http://www.postgresql.org/docs/current/static/sql-grant.html" rel="nofollow noreferrer">授予对您想要允许的内容的只读访问权限。任何您不授予的操作，他们都无法执行（只要他们不是超级用户）。
如果您授予只读访问权限并且他们不是超级用户，他们应该无法访问底层系统。这并不是说您不应该以非特权用户身份安装 postgres，您应该这样做 - 只是不需要完成您列出的内容。
好的，您编辑了您的帖子以包括发出疯狂的查询。我不认为 postgres 目前有办法限制每个用户的查询资源。
Well you just really need to CREATE a ROLE and then GRANT read only access explicitly to the things you want to allow. Anything you don't grant, they can't do (as long as they're not a superuser).
If you've granted readonly access and they're not a superuser, they shouldn't be able to gain access to the underlying system. That is not to say you shouldn't install postgres as an unprivileged user, you should - simply that it shouldn't be necessary to accomplish what you listed.
Ok, you edited your post to include issuing crazy queries. I don't believe postgres currently has a way to limit query resources per user.
您可以以非特权用户身份安装 postgres，并以这种方式运行它。通过这种方式，您可以利用现有的系统权限来限制人们可以对数据库执行的操作，包括隔离他们对自己数据库的访问。请参阅本页底部的说明：
http://www.postgresql .org/docs/current/interactive/tutorial-start.html
如果您将其中的某些部分自动化，比如说给人们一个“setup postgres”命令来运行，瞧。
You can install postgres as an unprivileged user, and run it that way. This way you take advantage of the existing system permissions to restrict what people can do with the database, including isolating their access to just their own database. See instructions at the bottom of this page:
http://www.postgresql.org/docs/current/interactive/tutorial-start.html
If you automate some part of this, say give people a 'setup postgres' command to run, voila.
对于＃3，您可以编写一些代码来监视查询活动并使用 postgre 中的系统视图根据某些条件终止您认为疯狂的查询吗？
For #3 could you write some code that monitors query activity and kill those queries you deem CRAZY based on some criteria using the system views in postgre?
当用户可以执行自己的 SQL 时，#3 是无法阻止的。您需要一个执行预定义 SQL 的（小型）应用程序。即使是 VIEW 也无法帮助您，每个人都可以加入几个视图来破坏您的系统。
#3 can't be prevented when the user can execute his own piece of SQL. You need a (small) application that executes predefined SQL. Not even VIEW's can help you here, everybody can join a couple of views to cripple your system.