sql - Postgres : Is there a way to tie a User to a Schema?

Question

在我们的数据库中，我们有用户:A、B、C。

每个用户都有自己对应的架构:A、B、C。

通常，如果我想从其中一个模式中的表中进行选择，我必须这样做:

select * from A.table;

我的问题是:

有没有办法让:

从表中选择 *

根据登录的用户转到正确的架构？

Answer 1

这是 PostgreSQL 的默认行为。确保您的 search_path 设置正确。

SHOW search_path;

默认应该是:

search_path
--------------
"$user",public

参见 PostgreSQL 的 documentation有关更多信息的模式。具体这部分:

You can create a schema for each user with the same name as that user. Recall that the default search path starts with $user, which resolves to the user name. Therefore, if each user has a separate schema, they access their own schemas by default.

If you use this setup then you might also want to revoke access to the public schema (or drop it altogether), so users are truly constrained to their own schemas.

更新您的评论:这是我的机器上发生的事情。这就是我相信您想要的。

skrall=# \d
No relations found.
skrall=# show search_path;
  search_path   
----------------
 "$user",public
(1 row)

skrall=# create schema skrall;
CREATE SCHEMA
skrall=# create table test(id serial);
NOTICE:  CREATE TABLE will create implicit sequence "test_id_seq" for serial column "test.id"
CREATE TABLE
skrall=# \d
            List of relations
 Schema |    Name     |   Type   | Owner  
--------+-------------+----------+--------
 skrall | test        | table    | skrall
 skrall | test_id_seq | sequence | skrall
(2 rows)

skrall=# select * from test;
 id 
----
(0 rows)

skrall=#