Users are not schemas in PostgreSQL. Schemas in PostgreSQL are different entities, a schema in PostgreSQL sits under a database and a schema is always assigned to a user.We can assign more than one schema to a user.In PostgreSQL, it is a good practice to create a schema for each user with the same name as that user, so that each user will access their own schema.
Schemas in Oracle are similar to a user or we can say they are synonymous, depending upon the purpose the words are interchanged between schema and user. One advantage in PostgreSQL compared to Oracle is one can assign grant access at the schema level to the user, which is not possible in Oracle. For example, in Oracle, if user A wants to access complete tables in schema or user B, one should have to write a code to give access at the individual table level. If there are 80 objects under that schema, then the grant loop will repeat 80 times.
However, in both Oracle and PostgreSQL schemas are used to avoid conflict of the table names when different applications are using the same database.
Would like to show simple practical on users, schemas, search path and schema level grant access in PostgreSQL for better understanding
postgres=# \c DEVL
You are now connected to database “DEVL” as user “postgres”. -postgres is super user at cluster level
\dn Helps to show you the relationship between schema and user
DEVL=# \dn
List of schemas
Name | Owner
——–+———-
public | postgres
(1 row)
In DEVL database, creating user a1, and schemas with name a1 and a2, and also assigning both schemas to user a1
DEVL=# create user a1 password ‘**’;
CREATE ROLE
DEVL=#
DEVL=# create schema a1 authorization a1;
CREATE SCHEMA
DEVL=# create schema a2 authorization a1;
CREATE SCHEMA
Now user a1 is owning two schemas in DEVL database.
DEVL=# \dn
List of schemas
Name | Owner
——–+———-
a1 | a1
a2 | a1
public | postgres
(3 rows)
Now creating table t1 in 3 schemas (public, a1 and a2)
create table t1(name char(30));
CREATE TABLE
insert into t1(name) values (‘table is in public schema’);
postgres=# \c DEVL a1
Password for user a1:
You are now connected to database “DEVL” as user “a1”.
DEVL=> create table t1(name char(30));
CREATE TABLE
insert into a1.t1(name) values (‘table is in a1 schema’);
As the user name and schema name are not same we have to put prefix a2.t1
DEVL=> create table a2.t1(name char(30));
CREATE TABLE
insert into a2.t1(name) values (‘table is in a2 schema’);
Now check the output of the dictionary views
postgres=# \c DEVL
You are now connected to database “DEVL” as user “postgres”.
DEVL=# select table_catalog,table_schema,table_name from information_schema.tables where table_name=’t1′;
table_catalog | table_schema | table_name
—————+————–+————
DEVL | public | t1
DEVL | a1 | t1
DEVL | a2 | t1
(3 rows)
DEVL=# select catalog_name,schema_name,schema_owner from information_schema.schemata where schema_owner=’a1′;
catalog_name | schema_name | schema_owner
————–+————-+————–
DEVL | a1 | a1
DEVL | a2 | a1
(2 rows)
DEVL=> \dn
List of schemas
Name | Owner
——–+———-
a1 | a1
a2 | a1
public | postgres
The PostgreSQL determines which table it is meant, by following the search_path
DEVL=> show search_path;
search_path
—————–
“$user”, public
As default search_path is “$user, public”, if application logins as user a1 when it tries to select t1 table without suffix then it will select table from a1 schema.
DEVL=# \c DEVL a1
Password for user a1:
You are now connected to database “DEVL” as user “a1”.
DEVL=> select * from t1;
name
——————————–
table is in a1 schema
(1 row)
DEVL=> select * from a2.t1;\
name
——————————–
table is in a2 schema
(1 row)
invalid command \
Try \? for help.
DEVL=>
DEVL=> select * from a2.t1;
name
——————————–
table is in a2 schema
(1 row)
Once you exit from a1, here is the output of the table from public schema
DEVL=#
DEVL=# select * from t1;
name
——————————–
table is in public schema
(1 row)
One more example to show how the grants are assigned at schema level
DEVL=# create user u1 password ‘**’;
CREATE ROLE
DEVL=# create schema u1 authorization u1;
CREATE SCHEMA
postgres=# \c DEVL u1
Password for user u1:
You are now connected to database “DEVL” as user “u1”.
DEVL=# \dn
List of schemas
Name | Owner
——–+———-
a1 | a1
a2 | a1
public | postgres
u1 | u1
(4 rows)
DEVL=> create table t1(name char(30));
CREATE TABLE
DEVL=>
DEVL=> insert into t1(name) values (‘table is in u1 schema’);
INSERT 0 1
Now logging as user a1
DEVL=> \c DEVL a1
Password for user a1:
You are now connected to database “DEVL” as user “a1”.
DEVL=>
User a1 can’t access table in schema u1
DEVL=> select * from u1.t1;
ERROR: permission denied for schema u1
LINE 1: select * from u1.t1;
postgres=# \c DEVL
You are now connected to database “DEVL” as user “postgres”.
Granting access on schema u1 to user a1
DEVL=# grant usage on schema u1 to a1;
GRANT
DEVL=# grant select on all tables in schema u1 to a1;
GRANT
DEVL=# \c DEVL a1
Password for user a1:
You are now connected to database “DEVL” as user “a1”.
DEVL=> select * from u1.t1;
name
——————————–
table is in u1 schema
(1 row)