1. Introduction to access control
We have explored some fundamental aspects of creating PostgreSQL databases. This includes creating the database components, understanding the available data types, and normalizing databases. After such an effort, it is important to protect the database from unauthorized access.
2. The default superuser
In a default installation of PostgreSQL, a database user named "postgres" is created. This user has what is known as a superuser role.
This user is necessary because database administration tasks can be accomplished through this user account.
Some of the capabilities of this user include:
creating databases,
dropping databases,
inserting records,
deleting records,
and dropping tables. Needless to say, this user is very powerful and should be used with extreme care.
3. Example: a personal finance database
Imagine you are not enthusiastic about any of the personal finance tools that are readily available online.
Therefore, you decide to create a PostgeSQL database of your own to manage your finances.
4. Example: a personal finance database
This database will be used to track your bank accounts, purchases, debts, and investments.
5. Example: a personal finance database
This database is going to live on your personal computer which rarely leaves your home and is not publicly accessible.
While this database is likely safe, after the initial database creation, you decide to create a user specifically for working with this database.
The user will perform operations specific to this database such as
adding records to tables
querying records in the database
and editing records. You might have the need to change the structure of the database by adding a new column or changing the data type of a column. But when such a need arises, the "postgres" user is still available for such tasks.
6. Creating new users
Therefore, one of the first things that you will want to do when creating a new database is to create a user or users with database access restrictions.
PostgreSQL provides a simple command for creating new users using the words CREATE USER.
This command initializes a new account for the PostgreSQL database management system.
Typing the command CREATE USER newuser (followed by a semicolon) would result in the creation of a new account with the name newuser.
This new user can create tables in the database that is currently in use when the user is created.
However, the user has no access to tables created by other users. We will take advantage of this fact to finely control database access in later parts of this chapter.
7. Setting user password
Let's return to the personal finance database example discussed previously. It is possible that your computer and, therefore, the database could fall into the wrong hands.
User accounts can be secured using passwords.
New user accounts do not have passwords added to the account without this action being taken explicitly.
The CREATE USER command can be modified to also set a password for the newly created user. The format of this version of the command adds the phrase "WITH PASSWORD" followed by the password's characters enclosed in single quotation marks. In this example, newuser would be assigned the password "secret".
The user can change the password later using the ALTER USER command followed by the same WITH PASSWORD syntax. After executing this ALTER USER command, newuser's password would become new underscore password.
8. Let's practice!
Now that you have learned how to add new users to a database, let's put this knowledge to use.