PostgreSQL #5 - Roles & Schemas

PostgreSQL #5 - Roles&Schemas

In the last post I described the physical layer(files on disk) of PostgreSQL db. From now on everything that we will talk about can be explained because you already now the physical and logical structure of PostgreSQL.

In this post I'm gonna focus on roles and schemas. The topics that I am going to focus on this post are: 

-What are roles and schemas?

-How can I create each one of them ?

-How can I manage them ?

-What is search path and how we configure it ?

-Priviledges

-Important schemas

Roles

The official and most clear defenition of a role is that a role is an entity that can own database objects and has database priviledges (- PostgreSQL 9.6 documentation). A role can be a specific user or a group or even sometimes it can both, depends on how you use it. Roles are defined on the cluster level and therefore they are available in all the databases in our cluster.

 You can get a list of all the roles in your cluster with the next query : 

or you can use the \du command to get a nice summary of your roles : 

The different between  a role and  a user is that a user is a role that has the LOGIN priviledge. Lets check the next example to understand what I just said : 

Now lets try to login with both of the roles : 

Like the login priviledge a role can has many other priviledges on the cluster level like SUPERUSER,password,valid until and more.. Now, using the documentation lets create a user according to the next requirements : 

-The user name is mc_haflap

-The password is 123456 and is saved in the catalog as encrypted password.

-The password validation is until next year

-The user can be connected simulaniously 5 times.

Try to do it by yourself with the help of the documentation. You can check my answer afterwards :

(You could do it in different ways..)

Another usefull way to use roles is as groups. Using groups to manage your users priviledges will make your life easier. Instead of granting the appropriate priviledges to every user we can just grant the priviledges to a group and then grant the group role to the user. In this way we know that every user got the right priviledges and we dont need to manage the objects priviledges in a user level but rather in a groups level. In the next example I create a new group for my new development team in the company and I grant the group the appropriate priviledges. Instead of granting the priviledges to the development team members directly(to each one of them) I grant them the team`s group role.

Schemas

When multiple users working on the same database it can create a mess. What happens when two developers from different teams create a table with the same name but a different need?  What about a third side app that tries to create a table that already exists and used by someone else ? How can we seperate our database to multiple environments so that many users can work on together? Schemas are the solution.

 Schema is a logical group that can contain database objects. A database can contain multiple schemas. Think of schemas as boxes that are part of the same locker. If we didnt had schemas and I put my pencil in the locker and my friend put the same pencil in the locker how can we identify to who belong each pencil ? When we use schemas, each on of us will put his pencil in his schema - in his box and it will make is easy to identify to whom belong each of the pencils. Check out the next example where I create 2 new schemas and in every schema I create a table name mcblog with one int column : 

By default when you create a new database a default schema that is called "public" is created. When you create objects in your new database, all those objects are stored in the public schema by default(to be honest its not exactly true and you will understand why later).  The next command shows the relation in the current database and to what schema each relation is related:

By default all users have the CREATE and USAGE priviledges in the public schema. It means that once a user is connected to a specific database it can create objects freely and use (select,insert,update,delete,drop) different objects if he has the appropriate priviledge on that object. To avoid such a mess in the public schema, it is recommened to revoke all those priviledges from the schema and to use schemas in your database. To revoke all priviledges from all users in the public schema in the current database : 

-If you want to avoid from doing it for every new database, connect to the database  template1 and do it there one time. From that moment this change will be saved in every new database that will be created.

-Dont forget to grant the USAGE priviledge to a user on a new schema to allow the user to work on that schema.

-If you want to create a schema and to make someone else the owner, use the authorization option : 

search_path

In what schema an object is created when you dont specify the schema name ? If you try to select form a table that is existed in two schemas, what table the query work on?  Well to answer those questions the parameter search_path was created.

When you want to run a query (create,select,drop...) on a specific object and you dont specify a schema name postgres uses this parameter. As you can see the search_path consist from two values in this case. The meaning of those values is that if you dont specify a schema_name and you trying to access or create an object the first place postgres will search  for that object will be in the first schema that is specified. If it doesnt find the object there postgres will continue the search to the second schema which in this case is public. The "$user" string means the schema that is identical to the user`s name. For example, in the screenshot I'm logged as the user postgres and that is my search path. At first postgres will search the schema postgres and if it will not find the object there it will search in the schema public. If after searching in all the schemas it wont find the object it will raise the next error : 

In a case of object creation, the object will be created in the first schema that the current user has CREATE priviledge. 

Obviously we can change the search_path and set it to what we want. You can do it for the current session:

Or you can make this change permanent  : 

In both cases you dont need special priviledges for it. However, you cant do it for other users unless you are a superuser. 

Information_schema & pg_catalog

Like the public schema there are 2 more schemas that are created by default : information_schema and pg_catalog. Those schemas are administrative schemas that include metadata on all the objects in the database. When you search for pg_ objects that are located in the pg_catalog schema postgres searches first in the pg_catalog schema even if it isnt mentioned in the search_path and afterwards it will search in the search_path if it wont find anything. If you specify the pg_catalog schema in the search_path postgres will use the order that is mentioned in the search_path. 

To be honest there are many important objects in those schemas and If I will start to talk about them in this post it will take me forever : 

But!! to make your life easier try reading and accessing the next tables first :

pg_database ->  Database about all the databases in the cluster

pg_proc -> info about procedures and functions

pg_class -> info about all the relations

pg_authid -> info about roles

pg_namespace -> info about schemas

pg_stat_activity -> info about the connected sessions

and so on..

Regarding the information_schema -> In contrary to the pg_catalog in order to access its objects you must specify the information_schema name before accessing its objects! This schema is also very helpfull and has simillar data but the tables name might seem more suitable for the data that they contain in contrary to the pg_catalog schema. Some usefull tables that you should check : 

information_schema.tables

information_schema.columns

and so on ....

In conclusion, I hope that now you understand better what are roles and schemas. It is important to manage your database with multiple schemas and not use the default public schema. The information_schema and pg_catalog schemas are also very important and very usefull so check them out. If you have any questions,comments,answers or anything feel free to leave a comment in the post and I will get back to you.