This section is from the "Practical PostgreSQL" book, by John Worsley and Joshua Drake. Also available from Amazon: Practical PostgreSQL.
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:
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;
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.
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;
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)
 
Continue to: