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

Question

In our database we have users: A, B, C.

Each user has its own corresponding schema: A, B, C.

Normally if I wanted to select from a table in one of the schemas I would have to do:

select * from A.table;

My question is:

Is there a way to make:

select * from table

go to the correct schema based on the user that is logged in?

Answer 1

This is the default behavior for PostgreSQL. Make sure your search_path is set correctly.

SHOW search_path;

By default it should be:

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

See PostgreSQL's documentation on schemas for more information. Specifically this part:

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.

Update RE you comment: Here is what happens on my machine. Which is what I believe you are wanting.

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=#