1. Hierarchical access control
Now that we have covered the basics of privileges and roles, we will take this opportunity to build on our knowledge of this topic. Let's shift our attention to hierarchical privileges using groups and schemas.
2. Access control with schemas
Previously, we covered schemas as a database object that can be used as a named container for database objects such as tables.
Granting privileges specific to different schemas in a database can be used to easily control access to multiple objects simultaneously.
3. Example: schema use in finances database
Imagine this scenario. Your spouse is quite impressed with how well you have been managing your finances recently and would like to be able to do the same with her specific finances.
All tables in the finances database have been created in the public schema to date. But, while certain finances are shared between you and your spouse, you have individual accounts as well such that it makes sense to maintain them separately in the database.
You decide to manage the individual accounts in two separate schemas. One will be named the "me" schema and the other will be named the "spouse" schema.
After creating these schemas using the familiar commands seen here as the database superuser, you add the account table and all other tables that existed under the public schema to these new schemas.
4. Granting schema privileges
Next, a new user account is created for your spouse named "better underscore half".
The new user account must be granted USAGE privileges on the "spouse" schema in order to access it.
You can then give the familiar privileges to the "better underscore half" account for these schemas. You also give privileges to this account for the public schema but give no access privileges for the "better underscore half" account to the me schema. You can then execute similar commands for the fin user account giving access to the public and "me" schemas.
With this arrangement, both you and your spouse can maintain your finances using PostgreSQL with access only being shared where necessary.
5. Using groups
It might become tedious to continuously grant the same set of privileges to a group of similar database users.
While a user is a type of role in PostrgeSQL that is typically associated to an individual, the group role provides a way for managing privileges for groups of individual users.
This might prove useful when multiple users should be given the same privileges for access to database objects. Rather than just creating an account for your spouse, other members of your family might want to access the database as well.
In this case, a "family" group can be created.
A set of privileges can then be granted to the family group such as INSERT, UPDATE, DELETE, and SELECT on a database table or schema. Here the "family" group is given SELECT, INSERT, UPDATE, and DELETE privileges on all tables in the public schema.
Then individual user accounts can be added to the newly created group. Here both fin and "better underscore half" are added to the "family" group.
Alternatively, we can add both users to the "family" group using a single command and a comma-separated list of users. Using either approach, these users would then inherit the privileges from the group without the need to individually grant these privileges.
6. Shared and individual data access
With such a setup, the family group can be given access to all tables in the public schema.
Each family member could then have a schema of their own for maintaining their individual financial data.
7. Let's practice!
It should be clear by now that hierarchical privileges can enable some rather sophisticated database access configurations. Let's take this opportunity to practice what we just learned.