Hello everybody.

This may sound the dumbest confusion but yes, I am very confused with the concept of Users, Databases and Schemas in Postgresql. Can somebody help me to explain this with reference to a Cluster. Any explanation appreciated......

Thanks

The CLUSTER contains all DATABASES and ROLES of the PostgreSQL server.

Predefined are the databases "postgres", "template0" and "template1".
The databases "template0" and "template1" are templates for new databases you create.

ROLES (USERS are also ROLES who can login on the database server) are stored also global on the CLUSTER. There are no ROLES in a single database.

A database contains all objects (TABLE, INDEX, FUNCTION, etc.).
To avoid namespace pollution between more than one database application you can create and use an own schema for the objects.
If no SCHEMA is given the SCHEMA named like the username or the default SCHEMA "public" is used. The SCHEMA "public" is predifined in all new databases. You can change this by setting the search_pat.

For example: