Infrastructure at your Service

Daniel Westermann

A schema and a user are not the same in PostgreSQL

When people with an Oracle background attend our PostgreSQL DBA Essentials training there is always a bit of confusion about schemas and users. In Oracle a schema and a user is a one to one relationship and there is no real distinction between a user and a schema. In PostgreSQL the situation is different: All the objects a user is creating are created in a specific schema (or namespace). Other users may or may not have permissions to work with this objects or even to create new objects in a specific schema. Compared to Oracle there is one layer more.

The hierarchy in PostgreSQL is this:

|-------------------------------------------|---|
| PostgreSQL instance                       |   |
|-------------------------------------------| U |
|     Database 1      |     Database 2      | S |
|---------------------|---------------------| E |
| Schema 1 | Schema 2 | Schema 1 | Schema 2 | R |
|----------|----------|----------|----------| S |
| t1,t2,t3 | t1,t2,t3 | t1,t2,t3 | t1,t2,t3 |   |
-------------------------------------------------

What this little ASCII image shall tell you: Users (and roles) in PostgreSQL are global objects and are not defined in a database but on the instance level. Schemas are created by users in a specific database and contain database objects. Where a lot of people get confused with is this:

[email protected]:/home/postgres/ [pgdev] psql -X postgres
psql (13devel)
Type "help" for help.

postgres=# create table t1 ( a int );
CREATE TABLE
postgres=# 

Nothing in this create table statement references a schema but according to what I just said above all objects must go to a schema. Where did this table go then? Each PostgreSQL database comes with a public schema by default and if you do not explicitly specify a schema the new object will go there. There are several ways of asking PostgreSQL for the schema of a given table but probably the two most used ones are these (the first one is asking a catalog view and the second one is using a psql shortcut)

postgres=# select schemaname from pg_tables where tablename = 't1';
 schemaname 
------------
 public
(1 row)

postgres=# \d t1
                 Table "public.t1"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 

Btw: The public schema is a special schema in PostgreSQL and you should either remove it or at least revoke permission from public on the public schema. Check here for more information on that.

So what happens when you drop the public schema and try to create a table afterwards?

postgres=# drop schema public cascade;
NOTICE:  drop cascades to table t1
DROP SCHEMA
postgres=# create table t1 ( a int );
ERROR:  no schema has been selected to create in
LINE 1: create table t1 ( a int );
                     ^
postgres=# 

As we do not have a single schema anymore:

postgres=# \dn
List of schemas
 Name | Owner 
------+-------
(0 rows)

… PostgreSQL has no idea where to put the table. At this point it should already be clear that a schema in PostgreSQL is not the same as a user. We are connected as the “postgres” user, but we do not have a schema to create our objects in. Lets create the first schema and right afterwards the same table as above:

postgres=# create schema my_schema;
CREATE SCHEMA
postgres=# create table t1 ( a int );
ERROR:  no schema has been selected to create in
LINE 1: create table t1 ( a int );
                     ^
postgres=# 

… again PostgreSQL is not able to create the table. The question is: Why did it work when then public schema was there? We did not specify the public schema above but it worked. This is where the search_path comes into the game:

postgres=# show search_path;
   search_path   
-----------------
 "$user", public
(1 row)

postgres=# 

By default the search_path contains you current username and public. As none of these schemas exist right now the create table statement will fail. There are two options to fix that. Either use the fully qualified name:

postgres=# create table my_schema.t1 ( a int );
CREATE TABLE
postgres=# \d my_schema.t1
               Table "my_schema.t1"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 

… or adjust the search_path so that your preferred schema comes first:

postgres=# set search_path = 'my_schema',"$user",public;
SET
postgres=# show search_path ;
        search_path         
----------------------------
 my_schema, "$user", public
(1 row)

postgres=# create table t2 ( a int );
CREATE TABLE
postgres=# \d t2
               Table "my_schema.t2"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 

postgres=# 

That all might look a bit strange at the beginning, especially when you are used to Oracle, but it also provides great flexibility:

  • A user can create many different schemas, no need to create separate users
  • A user can grant permission to create objects in one of his schemas to someone else
  • You can logically divide your application
  • (no, there are no synonyms in PostgreSQL)
  • The are default privileges you can use

One Comment

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Daniel Westermann
Daniel Westermann

Principal Consultant & Technology Leader Open Infrastructure