1. PostgreSQL access privileges
In the previous video, we learned how to create new database users, set a password for a user, and how to update the user's password. We will now learn how users can be given access to database objects such as tables and schemas.
2. PostgreSQL roles and privileges
The concept of a user account introduced previously is one type of role in PostgreSQL.
Another type of role is a group which can consist of one or more users. We will discuss groups later in this chapter.
Access to different database objects is given to roles within the PostgreSQL database management system. These database objects include the database itself, tables, as well as schemas.
3. The GRANT command
When creating a database object, the user that created the object owns it. Other roles can access the object if granted privileges to access the object by its owner.
This is done using the GRANT command.
Some privileges that can be granted include:
SELECT for reading from a table
DELETE for removing a record from a table
and UPDATE for changing a record in a table.
The basic format of the GRANT command is GRANT p ON o-b-j TO grantee where privilege "p" is given to the role "grantee" for database object "o-b-j".
4. Example: personal finance database
The GRANT command is best demonstrated with an example.
The first table added to the personal finance database might be an account table to list different accounts that you have such as bank accounts, credit cards, retirement investments, etc.
This table was created by a superuser account, therefore, when a new user account fin is created the fin user account does not have the ability to access or modify the account table.
5. Example: personal finance database
Access privileges can be granted for fin based on how this user should be using the table.
You want to give the fin user the following access to the account table:
the ability to add new accounts,
the ability to update accounts,
and the ability to query account data.
The following commands, run as the superuser, would grant these privileges:
the ability for the fin user to insert records into the account table,
the ability for the fin user to update records in the account table,
and, finally, the ability for the fin user to select records from the account table. After executing these 3 commands fin would have the limited set of privileges that we want to enable for this table.
6. Table modification privileges
Before we end, I would like to stress one limitation of the GRANT command.
While many of the privileges on a table can be granted directly to a role by the owner, certain commands can only be executed by the table's owner.
For example, modifying the structure of a table requires ownership of the table.
The fin user would not be able to issue this command to add a column to the table.
And the fin account could not rename the "short underscore name" column to become "nickname".
However, if such table modifications were required, the superuser could transfer table ownership to fin using an ALTER TABLE command. After this command is executed, the fin user would have permission to issue the previously restricted ALTER TABLE commands.
7. Let's practice!
After introducing you to the GRANT command, you can now control user access to database objects. Before moving forward, let's make sure you are comfortable using the GRANT command.