Postgres连接池-多个用户 [英] Postgres connection pooling - multiple users

问题描述

为了保护我们的数据库，我们为每个新客户创建一个架构.然后，我们为此模式创建一个用户，当客户通过Web登录时，我们将使用他们的用户，从而阻止他们访问数据库的其他区域.

In order to secure our database we create a schema for each new customer. We then create a user for this schema and when a customer logs in via the web we use their user and hence prevent them gaining access to other areas of the database.

我们的问题是连接池，因为为这些用户继续创建/删除新连接效率不高.我们希望有一个可以在数百个不同的数据库用户中使用的解决方案.

Our issue is with connection pooling as it is a bit inefficient to keep creating/dropping new connections for these users. We would like to have a solution that can work across many hundreds of different database users.

我们已经看过pg_bouncer，但是这里的问题是我们必须在ini文件中为每个用户创建一个文本记录，并在每次建立客户时重新启动pg_bouncer.这不是一个很好的解决方案.

We've looked at pg_bouncer, but the issue here is that we have to create a text record in an ini file for each user and restart pg_bouncer every time we set up a customer. This is not a great solution.

是否存在可实时工作的替代解决方案，这意味着客户连接在活动期间仍会留在池中?

Is there an alternative solution that works in real time and would mean a customers connection/connection(s) would stay in the pool whilst they were active?

推荐答案

根据最新发行说明 pgbouncer实际上可以执行此操作.但是我没有尝试过.

According to the latest release notes pgbouncer might actually do this. But I haven't tried.

可以根据数据库和用户配置池模式.

Pooling mode can be configured both per-database and per-user.

关于一般用例.不久前我们也遇到过此类问题.我们只使用了一个用户/数据库和多个模式的连接池.在运行psql查询之前，我们只使用了 SET search_path TO schemaName .至于日志记录，我们拥有合规模式，可以记录每个客户的活动并将其保存在适当的模式中.

As for use case in general. We also had this kind of issue a while ago. We just went with connection pooling with one user/database and multiple schemas. Before running psql query we just used SET search_path TO schemaName. As for logging, we had compliance mode, when we could log activity per customer and save it in appropriate schema.

这篇关于Postgres连接池-多个用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！