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

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

Altering Users

Existing users may only be modified by PostgreSQL superusers. Possible modifications include each of the options available at the creation of the user (e.g., password, password expiration date, global rights), except for the system ID of an existing user, which may not be modified. Modification of existing users is achieved through the use of the ALTER USER SQL statement.

Here is the syntax for ALTER USER:

  ALTER USER 
username

             [ WITH PASSWORD '
password
' ]
             [ CREATEDB | NOCREATEDB ]
             [ CREATEUSER | NOCREATEUSER ]
             [ VALID UNTIL '
abstime
' ]

The required username argument specifies which user is to be modified. Any of the following parameters may additionally be specified:

WITH PASSWORD ' password '

Sets username 's password to password .

CREATEDB | NOCREATEDB

Grants or revokes from username the right to create databases.

CREATEUSER | NOCREATEUSER

Grants or revokes from username the status of superuser, which enables all possible right within PostgreSQL (most notably the ability to create and destroy users and superusers).

VALID UNTIL ' abstime '

Sets username 's password to expire at abstime , which must be of some valid timestamp format. This value is only relevant for systems requiring password authentication, and is otherwise ignored (e.g., for trusted sites).

A common function of ALTER USER is to reset the password (and potentially the expiration date) of a user. If a PostgreSQL user had an expiration date set when their user was originally added, and that date has passed, and the user requires password-based authentication, a superuser will have to reset both the password and the expiration date to re-activate a user's ability to connect. If you want to cause a user's password to never expire, set it to the special timestamp infinity .

Example 10-6 modifies a user named salesuser. The user's password is set to n3Wp4s4 by the WITH PASSWORD clause, and set to expire on January 1st, 2003 by the VALID UNTIL clause.

Example 10-6. Resetting a password

template1=# 
ALTER USER salesuser

template1-# 
           WITH PASSWORD 'n3WP4s4'

template1-# 
           VALID UNTIL '2003-01-01';

ALTER USER

At times you may wish to grant a user additional rights beyond those originally granted to them. The use of the CREATEUSER keyword in Example 10-7 modifies the user salesuser to have all rights in PostgreSQL, making the user into a superuser. Note that this makes the CREATEDB right moot, as superusers can create databases implicitly.

Example 10-7. Adding superuser rights

template1=# 
ALTER USER salesuser

template1-# 
           CREATEUSER;

ALTER USER

Conversely, there may be times when a user no longer deserves rights that have been granted in the past. These rights may be just as easily removed by a superuser with the NOCREATEDB and NOCREATEUSER keywords.

Example 10-8. Removing superuser rights

template1=# 
ALTER USER salesuser

template1-# 
           NOCREATEDB NOCREATEUSER;

ALTER USER

Warning

As any superuser may revoke rights from another superuser, or even remove another superuser, it is wise to be extremely careful when granting the CREATEUSER right.

Databases - Practical PostgreSQL
Previous Page Home Next Page

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