Part 13- PostgreSQL user and role Management

hello and welcome to email to skills video tutorial in this video tutorial we will learn what is a user rule and a group in poor screen sequel so the agenda of this this tutorial is what is the user or a rule in poor screen what is public schema what is a search path and how do we revoke public from a rule how do we create a read-only rule or readwrite role and how do we create a role and then we how do we grant privileges to the user which is created the first one is the users and roles so what is a user and role in post crave and when when we say create user in a post great that means it is equivalent to create rule plus log in Perham permission that means the only difference between user and the role is user is able to log in but the role will not be able to log in and role has its own advantage so if I if I say a sequel statement to create the poster a sequel user so I'll just say create user username then with password and I'll give the password if I have to create the same user with the create role SQL statement I'll say create role role name and the only additional thing which I have to add is with login and then we need to give the password and that's it so in post krei create user is equal to create role plus login permissions so by default all the new users and roles they inherit the permission from the public role and we'll see how it plays an important role so what is a public schema and public role so whenever you create a new database let's say you created a new database DB one then by default when you when you check the schemas which are available in that particular database so you will see a schema with the name like that means a user who has access to this particular database will be able to select or create the objects in that particular schema so it is by default the design is like that so all the new users and rules they have the default access to the public role and they can create the objects in the public schema which is not good then what is the search path if I'm connected to a database let's say which is called as DVD rental and if I say select star from actor actor is one of the table and if I don't give the fully qualified name then how po scree will come to know like from which schema it has to pick the information so that information is mentioned in something which is called as a parameter search part if you log in to PostScript prompt and say search path show search path it will show you two values one is dollar user another is public let's say if I'm connected to post GRA user on on post GRA server then by default when you fire any query it will search for a schema which is equivalent to the name of the user which is logged and let's say then it will look for that particular user so by default schema with the same name will not be present what is present is a public schema so any user who is connected to that particular database will be able to see the objects or create the objects in the public schema so as I mentioned our DVD rental database if I am just saying select star from actor then it will search and am look logged in through the post screen so it will use it will try to look for the schema which is equivalent to the database user name which is not the case so it will look the information in public schema so instead of saying select star from public dot dot actor I'll simply say select star from actor so it is going to search from the public if you have a schema with the name let's say a post gray and your object is lying over there then it will look into that and it will display the result this value can be changed you can change the schema name so that by default it will look into this so that's the importance of the search path and it plays a very important law what you should do about the public schema you should revoke any creation of object on to the public schema because if n number of users are using that particular database and then it may be a messy task so you should remove the create on schema public from the public and you should also revoke all own database from public so that anybody cannot use that particular one how do we create a role create role is it's very simple let's you say create role a role name and to that particular role what you can do is let's say I have an application I wanted to create or read only a rule for some of the table so I can create the rule then I can say grant connect on that particular database so my database in this case is DVD rental then I can give access to this particular rule and that particular rule can use the app schema if I have created a schema with the name so you can do lot of things with the rule and finally this rule can be granted to the application user or the user which you have created also what we can do is we can grant any kind of let's say select insert update delete whatever you wanted to do but but as the name suggests it has a read-only schema so I'm just going to give select on all the tables or a table like actor or to do that particular role or what also you can do is you can give all table access on that particular schema to that particular rules who so you created a blank role you give it connect access to the particular database then you you said like you use particularly schema then you give select access on n number of tables and this way it is very very useful you so you create a rule and then how do we create a database user so you simply say create user username then with password and password name and finally you can grant the role to the user so any user who is joining that application so any user in that application group you can grant the role so it is very convenient so any changes which is happening to the app underscore read-only will be applicable to all the users they will be impacted any table which is added or removed the impact will be on the rule and the rule is finally granted to the user and now there is a demo which is following up so this is the demo so so let me close this presentation and this is the tempo I have listed all the steps in the notepad and we'll perform this either on the so I'm connected through moba acts from on my server which is on a Linux server 192 168 1.61 and connected through the post gray user which is having the sudo access on this so what I am going to do is so what we saw earlier is we are going to create a user and we'll see how it is different from the rule so we say create user is equal to create role plus the log in permission so I'm just going to create a user let's name it as create user app user one with password which is equal to the password not a good practice but this is a demo so we are using this this is the wrong password the your role is created you can see the user has created app user so where is the app user one so this is the app user one and there is no attributes cannot login that means it is able to login a that is the only difference between user and the rule roles cannot connect to the it's not able to login but user is able to do that if I create a rule let's say app user role one and if I say with login then it is equivalent to the create user statement so that is what we discussed in the first slide create user is it's already existing so let us create another one app role to so if I say slash do you you will be able to see a pro true and it is able to login next one is what is the public schema and public rule they just to showcase this log into the PG admin GUI tool and I'll connect to my database which is on Linux environment and I'll connect to the database which is DVD rental so here we go with the DVD renter and what happens let me delete the role which I have created okay so I have a database with that I'm delete rental and when we I created it was applying database so whenever you create a database you get a schema which has were just called as the name public that means any user who is having the access on this particular database will be able to use public schema that means any any object which is created will go into this let's say if I say I'm connected through the poster a user in in DVD rental database and if I say create table E so that table will be created in public schema so that's that's the way if you wanted to use a specific schema for the particular user you need to create using the create a schema command so we will see right away how to use that search path so I am connected as postre user and let's see how many databases we have let's go to DVD rental and switched on to the DVD rental database if I say let me see the data tables we have we have actor table and it is there in the public schema if I say select star from actor or select count star from actor I don't want to display the entire trailer onto the screen I'll be able to get the details but how post gray is knowing how the server is knowing from which ASCII might has to pick it up so I'm connected to the DVD rental database and there is only one schema which is public schema and there is something called a search path if I say show search path I'll be able to see two things one is taller user and another is public so by default if a user is connected let's say my user name is app user then it will look for the schema whose name is is equivalent to the user which is connected if that is not present it will look for the object in the public schema so either I say select count star from actor or I if I give fully qualified name which is a schema name dot dot the table name it will display the same item so if I just wanted to omit using the the schema name I need to mention the the value in search path and that that can be set all right what we will do is we will do a small demo as a best practice we will revoke privileges from the public rules so let's do that so we will see revoke create on a schema public from public and revoke all on database or DVD rental from just in this demo what we are doing is we are using DVD rental as the database I'll go to the PG admin and and we will do it over there so let me connect to DVD rental then I say query tool so it will open a query editor and we will execute all the statements from there all right so what I'm saying is Gris we will revoke creator so by default nobody will be able to use that a schema and on all the database DVD rentals so that will also be revoked so just execute this either f5 or execute both will work ok this is done now what we are going to do is we are going to create a rule which is app read-only then create role rule name then we are going to say it is going to connect to the database DVD rental then we are going to create a schema app schema then we are saying just use that particular schema and then we are going to select all tables in that particular schema and that will be granted to that particular rule let us create this particular role we use the command prompt or the GUI tool but it is good to watch it over here so create role and then connect privilege then create a schema then grant usage on the schema and then all the tables in that particular schema will be given the selected access to that particular rule and that's it rule already exists so let me drop the rule so let's drop it and then we'll execute it okay so the inner cell and let me change it instead of so we will say it will create a new role which is app read and only in a school one I will say replace all and we are going to use this and we are using PG admin for I am just clearing the screen and we are creating a new role it's very very easy there is nothing tricky in this so this it is displaying the last message so this is completed now we are going to create a read write role here what we are saying is in read-only role we we gave only the Select access here what we are going to say we are going to give insert update read on all the all the tables in that particular schema so let's create role readwrite let me see if that role is available or it is not okay so we tried ok just not so let's execute this so we are going to create a role read right then this is giving the Kinect access on DVD rental then we are going to use the we are going to create a schema app schema which is already created and you can use the name according to your requirement and will execute all this a statement Ori right already exists so let me create instead of readwrite we'll see app underscore readwrite 1 let's create the role that's it's very very easy it has it has done the next one is will create a user which is called as a proportion will give some password then we will create another user app reporting to then then what we are going to do is we are going to grant the privileges so this this we have seen earlier also there is nothing special it is just the user and the role the only differences they have login access there's done and finally the roles which we have created can be assigned to the user so so we had a pre-dawn Lee and we have a pre write one so let's grant all this so this object level things are very useful when you when you work for the the application team and you have to grant some kind of access on the tables or a schema to do the rules and that rule has to be assigned to the to the user so just to recap what we have done we have seen what is a user user is nothing just a create rule plus login permission then we saw what is public schema public schema is something which is available by default in our database which is newly created and the user has the the access on that which is connected to that particular database and then they can create the objects into that that is not a good practice to have that in place but it is by design so you should remove the excess over there then we saw something as search path if you say show search path it will show you two value dollar user comma public that means a user which is connected to a particular database by default it will look into the schema with the same name as the user connected if that is not available it will search for the objects or it will create the objects which is there in the public schema so instead of saying select star from table or instead of giving the full fully qualified name select star from schema door table you simply say select star from table then it will check this this criteria and if it is validated it will display the results then we saw how to create the user then we we created role read-only role then we created a rewrite role and then we created two users and then we assigned the the roles to the user I hope this is going to work and thanks for watching

Channel: E-MultiSkills Database Tutorials

Views: 18,285

Keywords: users and roles in potsgresql, what is a user in postgresql, how to create a user in postgresql, how to create a role in postgresql, how to manage user and role in postgresql

Id: -2kYJ0gZmCo

Length: 22min 52sec (1372 seconds)

Published: Sat Jul 25 2020