1. Database roles and access control
Welcome back! In this chapter, we're going to cover different topics within database management, beginning with database roles and access control.
2. Granting and revoking access to a view
In the last chapter, we learned how views are helpful for access control.
We granted and revoked a user's access to a view (or any object!). We also briefly discussed roles and privileges in the context of access control.
3. Database roles
Now we'll dive deeper. First, database roles.
Roles are used to manage database access permissions.
A database role is an entity that contains information that
Firstly, define its privileges, like whether that role can login, create databases, and many more, and
Secondly, interact with the client authentication system, like what the role's password is.
Roles can be assigned to one or more users.
Since roles are global, you can reference roles across all individual databases in your cluster.
4. Create a role
Say you are about to hire a bunch of data analysts. You can create the data_analyst role with the CREATE ROLE SQL command. The information that defines what the data_analyst role can do is currently empty.
We can also set some, but not all, of this information when creating a role, too. Say you're hiring an intern whose internship ends at the end of the year. You create the role intern, specifying the password attribute and valid until date attribute. One second into 2020, the password is no longer valid.
Say you want to create an admin role with the ability to create databases: create role admin with the attribute CREATEDB. For now, just know that attributes define some (but, again, not all) of what your roles can do.
To change an attribute for an already created role, you use the ALTER keyword, here allowing the admin role to create roles too.
5. GRANT and REVOKE privileges from roles
To grant specific access control privileges on objects, like tables, views, and schemas, you use GRANT and REVOKE. Say you want all of your data analysts to be able to update the ratings table. Grant that role update access. Don't need it anymore? Revoke it.
Here is list of privileges for roles in PostgreSQL. You'll only use the first few in this course.
6. Users and groups (are both roles)
Now, a common misunderstanding: a role can be a user role or a group role. A role may be a member of other roles, and we call the larger role a group. As this graphic shows, the concept of roles encompasses the concepts of “users” and “groups”.
Database roles - that is, user roles AND group roles - are conceptually completely separate from operating system users. Sometimes you will create a user role that belongs to one specific user, but that's not required.
7. Users and groups (are both roles)
Think of the data_analyst role as a group role: you want all of your data analysts to have the same level of access. Think of the intern role as a user role. Sometimes you'll use the actual user's name.
8. Users and groups (are both roles)
Say Alex is hired as an intern to support the data analysts, so you want them to have the same level of access. In PostgreSQL, to add the user role alex to the group role data_analyst, you write GRANT data_analyst TO alex. Alex can do data analyst work now! If Alex no longer needs to do that type of work, use REVOKE to remove them from the group.
9. Common PostgreSQL roles
PostgreSQL has a set of default roles which provide access to commonly needed privileged capabilities and information. These are beyond the scope of this course.
10. Benefits and pitfalls of roles
Roles are great because while employees come and go; roles live on.
They can even be created before those employees get accounts.
By grouping together common access levels, database administrators save time.
One common pitfall: sometimes a role gives an individual too much access. You need to be careful and mindful of your roles and their access levels.
11. Let's practice!
Let's keep the ball role-ing with some exercises.