{"id":13105,"date":"2019-11-27T17:56:28","date_gmt":"2019-11-27T16:56:28","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/a-schema-and-a-user-are-not-the-same-in-postgresql\/"},"modified":"2019-11-27T17:56:28","modified_gmt":"2019-11-27T16:56:28","slug":"a-schema-and-a-user-are-not-the-same-in-postgresql","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/a-schema-and-a-user-are-not-the-same-in-postgresql\/","title":{"rendered":"A schema and a user are not the same in PostgreSQL"},"content":{"rendered":"

When people with an Oracle background attend our PostgreSQL DBA Essentials<\/a> training there is always a bit of confusion about schemas<\/a> and users<\/a>. 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.<\/p>\n

<\/p>\n

The hierarchy in PostgreSQL is this: <\/p>\n

\n|-------------------------------------------|---|\n| PostgreSQL instance                       |   |\n|-------------------------------------------| U |\n|     Database 1      |     Database 2      | S |\n|---------------------|---------------------| E |\n| Schema 1 | Schema 2 | Schema 1 | Schema 2 | R |\n|----------|----------|----------|----------| S |\n| t1,t2,t3 | t1,t2,t3 | t1,t2,t3 | t1,t2,t3 |   |\n-------------------------------------------------\n<\/pre>\n
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:<\/p>\n
\npostgres@centos8pg:\/home\/postgres\/ [pgdev] psql -X postgres\npsql (13devel)\nType \"help\" for help.\n\npostgres=# create table t1 ( a int );\nCREATE TABLE\npostgres=# \n<\/pre>\n
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<\/a> and the second one is using a psql<\/a> shortcut)<\/p>\n
\npostgres=# select schemaname from pg_tables where tablename = 't1';\n schemaname \n------------\n public\n(1 row)\n\npostgres=# d t1\n                 Table \"public.t1\"\n Column |  Type   | Collation | Nullable | Default \n--------+---------+-----------+----------+---------\n a      | integer |           |          | \n<\/pre>\n
Btw: The public schema<\/a> 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<\/a> for more information on that.<\/p>\n
So what happens when you drop the public schema and try to create a table afterwards?<\/p>\n
\npostgres=# drop schema public cascade;\nNOTICE:  drop cascades to table t1\nDROP SCHEMA\npostgres=# create table t1 ( a int );\nERROR:  no schema has been selected to create in\nLINE 1: create table t1 ( a int );\n                     ^\npostgres=# \n<\/pre>\n
As we do not have a single schema anymore:<\/p>\n
\npostgres=# dn\nList of schemas\n Name | Owner \n------+-------\n(0 rows)\n<\/pre>\n
… 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:<\/p>\n
\npostgres=# create schema my_schema;\nCREATE SCHEMA\npostgres=# create table t1 ( a int );\nERROR:  no schema has been selected to create in\nLINE 1: create table t1 ( a int );\n                     ^\npostgres=# \n<\/pre>\n
… 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<\/a> comes into the game:<\/p>\n
\npostgres=# show search_path;\n   search_path   \n-----------------\n \"$user\", public\n(1 row)\n\npostgres=# \n<\/pre>\n
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:<\/p>\n
\npostgres=# create table my_schema.t1 ( a int );\nCREATE TABLE\npostgres=# d my_schema.t1\n               Table \"my_schema.t1\"\n Column |  Type   | Collation | Nullable | Default \n--------+---------+-----------+----------+---------\n a      | integer |           |          | \n<\/pre>\n
… or adjust the search_path so that your preferred schema comes first:<\/p>\n
\npostgres=# set search_path = 'my_schema',\"$user\",public;\nSET\npostgres=# show search_path ;\n        search_path         \n----------------------------\n my_schema, \"$user\", public\n(1 row)\n\npostgres=# create table t2 ( a int );\nCREATE TABLE\npostgres=# d t2\n               Table \"my_schema.t2\"\n Column |  Type   | Collation | Nullable | Default \n--------+---------+-----------+----------+---------\n a      | integer |           |          | \n\npostgres=# \n<\/pre>\n
That all might look a bit strange at the beginning, especially when you are used to Oracle, but it also provides great flexibility:<\/p>\n