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

Password Authentication

Passwords allow PostgreSQL users a way to identify themselves and prevent unauthorized individuals from connecting with a user that is not theirs. As of PostgreSQL 7.1.x, user passwords are stored in plain text in the pg_shadow system table. The structure of this table is illustrated in Table 8-1. Note that while the passwords are stored as plain text, only PostgreSQL superusers are allowed to view the pg_shadow table.

Table 8-1. The pg_shadow table

Column

Type

usename

name

usesysid

integer

usecreatedb

boolean

usetrace

boolean

usesuper

boolean

usecatupd

boolean

passwd

text

valuntil

abstime

The pg_shadow table is a system table, and thus is accessible from any database. It follows, therefore, that users are not assigned to a specific database. If a user exists in the pg_shadow table, that user will be able to connect to any database on the server machine, though not necessarily from any remote machine (depending on your configuration).

Users typically set passwords in PostgreSQL when the user is created (with the CREATE USER command) or after the user has been created (using the ALTER USER command). Alternatively, you may manually modify a user's password by using an UPDATE statement. (For a more detailed explanation about defining passwords for users, see Chapter 10.)

If a password is not set, a user's password defaults to NULL. If password-based authentication is enabled in the pg_hba.conf file, connection attempts will always fail for such a user. Conversely, if the host that establishes the connection is a trusted host (such as localhost , by default), anyone from the trusted host may connect as a user with a NULL password. In fact, passwords are ignored entirely for trusted hosts.

Note: The GRANT command allows you to restrict or allow a variety of access types to tables within a database. See Chapter 10 for more on this topic.

Unless your needs for security are very minimal, you will not want to rely on password-only authentication with your PostgreSQL server. Using a password-only method to authenticate users will allow any verified user access to any database on the system, and authenticating with a password over clear text can result in unauthorized individuals acquiring user passwords. If you are likely to have your database connected to the Internet in some fashion, we strongly suggest that you read the following sections. These cover the use of the pg_hba.conf file and session encryption.

Databases - Practical PostgreSQL
Previous Page Home Next Page

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