Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com
Answertopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

Creating Users

PostgreSQL provides two methods by which database users may be created. Each requires authentication as a superuser, for only superusers can create new users.

The first method is through the use of the SQL command CREATE USER, which may be executed by any properly authenticated PostgreSQL client (e.g., psql  ). The second is a command-line wrapper called createuser , which may be more convenient for a system administrator, as it can be executed in a single command without the need to interact with a PostgreSQL client.

The following sections document each of these methods.

Creating a user with the CREATE USER SQL command

The CREATE USER command requires only one parameter: the name of the new user. There are also a variety of options that may be set, including a password, explicit system ID, group, and a set of rights that may be explicitly allocated. Here is the complete syntax for CREATE USER:

  CREATE USER 
username

    [ WITH [ SYSID 
uid
 ]
           [ PASSWORD '
password
' ] ]
    [ CREATEDB   | NOCREATEDB ]
    [ CREATEUSER | NOCREATEUSER ]
    [ IN GROUP 
groupname
 [, ...] ]
    [ VALID UNTIL '
abstime
' ]

In this syntax, username is the name of the new user to be created. You cannot have two users with the same name. By specifying the WITH keyword, either or both of the SYSID and PASSWORD keywords may be applied.

Every other optional keyword may follow in the order displayed (not requiring the use of the WITH keyword). The following is a detailed explanation of each optional keyword and its meaning:

SYSID uid

Specifies that the system ID is to be set to the value of uid . If omitted, a reasonable, unique numeric default is chosen.

PASSWORD ' password '

Sets the new user's password to password . If unspecified, the password defaults to NULL.

CREATEDB | NOCREATEDB

Specifying the CREATEDB keyword grants the new user the right to create new databases, as well as the right to destroy databases which they own. Specifying NOCREATEDB explicitly enforces the default, which is the lack of this right.

CREATEUSER | NOCREATEUSER

Grants the right to create new users, which implicitly creates a superuser. Notice that a user with the rights to create other users will therefore have all rights , in all databases (including the rights to create a database, even if NOCREATEDB was specified). NOCREATEUSER explicitly enforces the default, which is the lack of this right.

IN GROUP groupname [, ...]

Adds the new user to the group named groupname . Multiple group names may be specified by separating them with commas. The group(s) must exist in order for the statement to succeed.

VALID UNTIL ' abstime '

Sets the user's password to expire at abstime , which must be of a recognizable timestamp format. After that date, the password must be reset, and the expiration moved forward.

VALID UNTIL ' infinity '

Sets the user's password to be valid indefinitely.

By not specifying either CREATEDB or CREATEUSER, users are implicitly "normal" with no special rights. They may not create databases or other users, nor may they destroy databases or users. Such users may connect to databases in PostgreSQL, but they can only perform the statements which they have been granted access to (see the Section called Granting Privileges " for more on granting rights).

Example 10-1 creates a normal user named salesuser. It also sets a password of N0rm4! by the use of the WITH PASSWORD clause. By omitting the VALID UNTIL clause, this password will never expires.

Example 10-1. Creating a normal user

template1=# 
CREATE USER salesuser

template1-# 
            WITH PASSWORD 'N0rm4!';

CREATE USER

The CREATE USER server message returned in Example 10-1 indicates that the user was added successfully. Other messages you may receive from this command are as follows:

ERROR: CREATE USER: permission denied

This message is returned if the user issuing the CREATE USER command is not a superuser. Only superusers may create new users.

ERROR: CREATE USER: user name "salesuser" already exists

This message indicates that a user with the name salesuser already exists.

If you wish to create a user who has the ability to create databases within PostgreSQL but not create or destroy PostgreSQL users, you may specify the CREATEDB keyword rather than CREATEUSER. This allows the named user to arbitrarily create databases, as well as drop any databases which they own. See Chapter 9, for more on this the topic of creating and destroying databases.

Example 10-2 illustrates the creation of a user named dbuser who has the right to create new databases. This is achieved by specifying the CREATEDB keyword after the username. Notice also the use of the WITH PASSWORD and VALID UNTIL keywords. These set the password for dbuser to DbuS3r , which will be valid until November 11th, 2002.

Example 10-2. Creating a user with CREATEDB rights

template1=# 
CREATE USER dbuser CREATEDB

template1-# 
            WITH PASSWORD 'DbuS3r'

template1-# 
            VALID UNTIL '2002-11-11';

CREATE USER

Resetting an expired user's password does not modify the VALID UNTIL value. In order to re-active a user's access whose password has expired, both the WITH PASSWORD and VALID UNTIL keywords must be provided to the ALTER USER command. See the Section called Altering Users " for more on this command.

Warning

VALID UNTIL settings are only relevant to systems which are not trusted; sites which are trusted do not require passwords. See Chapter 8 for more on host-based authentication.

You may wish to create an alternate superuser from the postgres user, though caution should be exercised in creating superusers. These users are granted every right within PostgreSQL, including creating users, removing users, and destroying databases. Example 10-3 demonstrates the creation of a PostgreSQL superuser named manager from the psql prompt.

Example 10-3. Creating a superuser

template1=# 
CREATE USER manager CREATEUSER;

CREATE USER

Creating a user with the createuser script

The createuser script is executed directly from the command line, and can operate in one of two ways. If issued without any arguments, it will interactively prompt you for the username and each of the rights, and attempt to make a local connection to PostgreSQL. Alternatively, you may choose to specify the options and the username to be created on the command line.

As with other command-line applications for PostgreSQL, arguments may be supplied either in their short form (with a single dash, and character), or in their long form (with two dashes, and the full name of the argument).

Here is the syntax for createuser  :

  createuser [ 
options
 ] [ 
username
 ]

The username in the syntax represents the name of the user you wish to create. Replace options with one or more of the following flags:

-d, - -createdb

Equivalent to the CREATEDB keyword of the CREATE USER SQL command. Allows the new user to create databases.

-D, - -no-createdb

Equivalent to the NOCREATEDB keyword of the CREATE USER SQL command. Explicitly indicates that the new user may not create databases. This is the default.

-a, - -adduser

Equivalent to the CREATEUSER keyword of the CREATE USER SQL command. Allows the new user to create users, and raises the status of the user to a superuser (enabling all rights within PostgreSQL).

-A, - -no-adduser

Equivalent to the NOCREATEUSER keyword of the CREATE USER SQL command. Explicitly indicates that the new user is not a superuser. This is the default.

-i SYSID , - -sysid= SYSID

Sets the new users system ID to SYSID .

-P, - -pwprompt

Results in a password prompt allowing you to set the password of the new user username .

-h HOSTNAME , - -host= HOSTNAME

Specifies that HOSTNAME will be connected to, rather than the localhost, or the host defined by the PGHOST environment variable.

-p PORT , - -port= PORT

Specifies that the database connection will be made on port PORT , rather than the default port (usually 5432).

-U USERNAME , - -username= USERNAME

Specifies that USERNAME will be the user who connects to PostgreSQL (The default is to connect using the name of the system user executing the createuser script).

-W, - -password

Results in a password prompt for the connecting user, which happens automatically if the pg_hba.conf file is configured not to trust the requesting host.

-e, - -echo

Causes the CREATE USER command sent to PostgreSQL to be displayed to the screen as it is executed by createuser .

-q, - -quiet

Prevents output from being sent to stdout (though errors will still be sent to stderr ).

If any of the -d , -D , -a , -A , or username arguments are omitted, createuser will prompt you for each missing argument. This is because PostgreSQL will not make any assumptions about the rights intended for the new user, nor about the new user's name. Example 10-4 creates a user named newuser, who has neither the right to create a database, nor create users.

Example 10-4. Creating a user with createuser

[jworsley@booktown ~]$ 
createuser -U manager -D -A newuser

CREATE USER

Notice also the -U manager flag passed to the createuser script. This indicates that the user with which to connect to PostgreSQL is manager, not jworsley as the script would otherwise assume, based on the name of the system account invoking the script.

If you prefer to be interactively prompted for each setting, (instead of having to remember the meaning of each flag or check the reference each time) you may simply omit the flags you are uncertain of. The createuser script will then prompt you for the basic createuser options. These options include the PostgreSQL username, whether the user may create databases, and whether or not the user may add new users to PostgreSQL.

Example 10-5 demonstrates using the createuser script in interactive mode. The net effect of this example is the same as the single line executed in Example 10-4.

Example 10-5. Interactively creating a user with createuser

[jworsley@booktown ~]$ 
createuser

Enter name of user to add: newuser
Shall the new user be allowed to create databases? (y/n) n
Shall the new user be allowed to create more new users? (y/n) n
CREATE USER
Databases - Practical PostgreSQL
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire