Privileges are the actions a user is limited to perform on a database object. These privileges are defined by attributes and interactions with the client authentication system.

This is a list of the most commonly used attributes for the CREATE USER command:

create superuser

The superuser can bypass all privilege checks. Only a superuser can create new users. If you need to create a user, you must have superuser privileges. To create a database superuser, go into psql and use the command: 

        CREATE USER username CREATEUSER;

create database

All users besides the superuser need to be given explicit permissions to create databases. This can be done with the command: 

        CREATE USER username CREATEDB;

When you perform this command, besides creating a database, it also creates a user.

specify password

A password only applies to a user when password authentication is used. Note that database passwords are separate from operating system passwords. Use the following to specify a password upon creation of a user: 

        CREATE USER username WITH PASSWORD 'string';

Using the structure of the Book Town company, create users to represent each employee. This creates user jessica with a password and ability to create tables:

Example 8-2. Creating a User with a Password 

   CREATE USER jessica WITH PASSWORD 'jess5429';

User Mark needs all privileges, because he is the manager. He should be able to perform anything on the database. A password should also be given because he has extended privileges. To create him, use: 

   CREATE USER mark WITH PASSWORD 'mk6798' CREATEUSER;

Tip Listing User Attributes
 

If you don't specify any privileges, then those privileges will default to false.

To list user attributes and their privileges, you can use:

Example 8-3. Viewing User Attributes 

    
    SELECT * FROM pg_user;

If you want to change a user attribute or privilege, then you can use the ALTER USER command. This command can only be used on existing database users. For instance, you have a user named Jonathan in your database. You want to give him a password or change his current password. Use the following command to assign Jonathan the new password jt1591:

Example 8-4. Modifying a User Attribute 

   ALTER USER jonathan WITH PASSWORD 'jt1591';

Help us make a better book, leave feedback. (http://www.opendocspublishing.com/entry.lxp?lxpe=92)