Databases - Practical PostgreSQL

Databases - Practical PostgreSQL
Previous Page	Home	Next Page

Removing Users

PostgreSQL users may at any time be removed from the system by authenticated superusers. The only restriction is that a user may not be removed if any databases exist which are owned by that user. If a user owns a database, that database must be dropped before the user can be removed from the system.

As with the creation of PostgreSQL users, there are two methods by which users may be removed. These are the DROP USER SQL command, and the dropuser command-line executable.

Removing users with the DROP USER SQL command

A superuser may remove a user by issuing the DROP USER command from a valid PostgreSQL client. The psql program is most commonly used to achieve this task.

Here is the syntax for DROP USER:

  DROP USER 
username

In this syntax, username is the name of the user that you intend to permanently remove from the system. Example 10-9 shows the use of the psql client to connect to PostgreSQL as the manager user in order to remove the salesuser database user.

Example 10-9. Removing a user with DROP USER

[jworsley@booktown ~]$ 
psql -U manager template1

Welcome to psql, the PostgreSQL interactive terminal.

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

template1=# 
DROP USER salesuser;

DROP USER

The DROP USER server message indicates that the user was successfully removed from the system. Other messages that you might receive from this command include:

ERROR: DROP USER: permission denied: Indicates that the user initiating the command does not have the right to drop a user. Only superusers may drop existing database users.
ERROR: DROP USER: user "salesuser" does not exist: Indicates that there is no such user with the name salesuser.

Removing users with the dropuser operating system command

The dropuser command operates much like the createuser script. It offers the same connection options, ensuring that it can be used remotely as well as locally, and requires only the username of the user to be removed from the system.

Here is the syntax for dropuser :

  dropuser [ 
options
 ] [ 
username
 ]

Each of the connectivity options is identical to those for createuser , described in the Section called Creating a user with the createuser script ," earlier in this chapter. Example 10-10 demonstrates the same net effect as the SQL statement in Example 10-9 by connecting to the PostgreSQL backend as the manager user, and removing the user named salesuser.

Example 10-10. Removing a user with dropuser

[jworsley@booktown ~]$ 
dropuser -U manager salesuser

DROP USER

The output from dropuser is the same as the output for the SQL DROP USER command. If you omit the username that you wish to remove when you execute the script dropuser , you will be prompted interactively for the name of that user to be removed from the system.