Chapter 8. User Management

Database users are separate from the operating system users. The database users are able to call the database management system to perform a retrieval, insertion, deletion, or update of the data. Users are defined within a database for security reasons.

There are database objects in which the users have privileges to. These objects can be any object within a database. They can can be tables, forms, views... etc. The types of privileges a user is given define which commands they are and are not permitted to perform. A superuser is defined to have all privileges.

For instance, the small company named Book Town has a manager named Mark. They also have a sales department with two sales persons, Jessica and William. The accounting department has an accountant, Jennifer, and a book keeper, Jonathan. The accounting department should only have access to the invoice, shipped orders, and customer table. The sales department should also have access to the customer table, but they should only be able to view, but not modify it because this table is owned by the accountant. Sales need access to the inventory table of books. The manager should have access to all tables and an additional employee table. The next table summarizes the relation between employees and tables.

This table shows the employee names and the tables which these employees need access to:

The previous table is used extensively to show how to define users and their properties. You should keep this relation in mind when reading through the examples in this chapter.

Adding users

From the command line

There are two methods to create this user. With either method, access to postgres is required (we're assuming that no other users with user creation permissions have been created). First of all, you can switch to the postgres user by performing the following command:

 su - postgres
   

Then create the user by typing this command at the terminal:

 createuser superuser
Shall the new user be allowed to create databases? (y/n) y
Shall the new user be allowed to create more new users? (y/n) y   
   

	Note
	If you specify no for both of these options, then this user is a normal user, not the database superuser. For example, if a user is able to create databases but not create users, it does not make that user a super user. The same concept applies when the user can create users but not create databases.

From the psql Application

The second method is to start a psql session and go into the template1 database. This involves the command:

   psql template1
   

This message should appear:

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit
   

You can now use the PostgreSQL create user command to create a superuser. The advantage of creating a user here is that you can specify additional attributes to tag onto this user. For instance, you can give this user a password, or limit this user from creating databases. The options available for the CREATE USER command is discussed in detail at the User Management chapter.

This is an example of how to create as super user when logged in a database:

Example 8-1. Creating a Postgres Superuser

    CREATE USER superuser CREATEUSER CREATEDB;
  

	superuser
	The properties that make this user a super user are create user and create database. If only one of these properties are specified, then this user is a normal PostgreSQL database user.

Help us make a better book, leave feedback.