PostgreSQL

psql: FATAL: Ident authentication failed for user "username" Error and Solution

last updated in Categories , , , , , ,

Q. I’ve installed Postgresql under Red Hat Enterprise Linux 5.x server. I’ve created username / password and database. But when I try to connect it via PHP or psql using following syntax:

psql -d myDb -U username -W

It gives me an error that read as follows:

psql: FATAL: Ident authentication failed for user “username”

How do I fix this error?

CentOS / RHEL Linux Install Postgresql Database Server

last updated in Categories , , ,

Q. How do I install and configure Postgresql database server under Red hat Enterprise Linux 5 / CentOS 5 / Fedora Linux?

A. The postgresql-server package includes the programs needed to create and run a PostgreSQL server, which will in turn allow you to create
and maintain PostgreSQL databases. PostgreSQL is an advanced Object-Relational database management system (DBMS) that supports almost all SQL constructs (including transactions, subselects and user-defined types and functions). You should install postgresql-server if you want to create and maintain your own PostgreSQL databases and/or your own PostgreSQL server. You also need to install the postgresql package.

You also need to install client package called postgrsql. This package contains the docs in HTML for the whole package, as well as command-line utilities for managing PostgreSQL databases on a PostgreSQL server.

Install Postgrsql Server

Login as the root user and enter the command:
# yum install postgresql postgresql-server
Output:

Loading "installonlyn" plugin
Setting up Install Process
Setting up repositories
Reading repository metadata in from local files
Parsing package install arguments
Resolving Dependencies
--> Populating transaction set with selected packages. Please wait.
---> Downloading header for postgresql-server to pack into transaction set.
postgresql-server-8.1.9-1 100% |=========================|  87 kB    00:00     
---> Package postgresql-server.i386 0:8.1.9-1.el5 set to be updated
--> Running transaction check
--> Processing Dependency: postgresql = 8.1.9-1.el5 for package: postgresql-server
--> Restarting Dependency Resolution with new changes.
--> Populating transaction set with selected packages. Please wait.
---> Downloading header for postgresql to pack into transaction set.
postgresql-8.1.9-1.el5.i3 100% |=========================| 119 kB    00:00     
---> Package postgresql.i386 0:8.1.9-1.el5 set to be updated
--> Running transaction check

Dependencies Resolved

=============================================================================
 Package                 Arch       Version          Repository        Size 
=============================================================================
Installing:
 postgresql-server       i386       8.1.9-1.el5      updates           4.0 M
Installing for dependencies:
 postgresql              i386       8.1.9-1.el5      updates           2.8 M

Transaction Summary
=============================================================================
Install      2 Package(s)         
Update       0 Package(s)         
Remove       0 Package(s)         

Total download size: 6.8 M
Is this ok [y/N]: y
Downloading Packages:
(1/2): postgresql-server- 100% |=========================| 4.0 MB    00:05     
(2/2): postgresql-8.1.9-1 100% |=========================| 2.8 MB    00:04     
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing: postgresql                   ######################### [1/2] 
  Installing: postgresql-server            ######################### [2/2] 

Installed: postgresql-server.i386 0:8.1.9-1.el5
Dependency Installed: postgresql.i386 0:8.1.9-1.el5
Complete!

Start Postgresql

Type the following two command:
# chkconfig postgresql on
# service postgresql start
Output:

Initializing database:                                     [  OK  ]
Starting postgresql service:                               [  OK  ]

Connect to Postgresql Server

Connect to server using
# su - postgres
Connect using psql command line tool:
$ psql -d template1 -U postgres
Output:

Welcome to psql 8.1.9, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

Open TCP port 5432

Finally make sure iptables allows remote access to Postgresql database server:
Open /etc/sysconfig/iptables file:
# vi /etc/sysconfig/iptables
Append following line before COMMIT line to open port 5432:

-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT

Save and close the file. Restart the firewall:
# service iptables restart

See also: PostgreSQL add or create a user account and grant permission for database

Howto: Debian / Ubuntu Linux Install PostgreSQL Database Server

last updated in Categories , , , ,

Q. How do I install PostgreSQL server under Ubuntu Linux?

A. PostgreSQL is an Open Source Software object relational database management system. POSTGRES pioneered many of the object-relational concepts now becoming available in some commercial databases. Traditional relational database management systems (RDBMS) support a data model consisting of a collection of named relations, containing attributes of a specific type. In current commercial systems, possible types include floating point numbers, integers, character strings, money, and dates.

Task: Install PostgreSQL

The best way to install PostgreSQL under Debian or Ubuntu Linux is to use apt-get command
# apt-get install postgresql
OR
$ sudo apt-get install postgresql
Output:

Reading package lists... Done
Building dependency tree... Done
The following extra packages will be installed:
  openssl postgresql-7.4 postgresql-client postgresql-client-7.4 postgresql-client-common postgresql-common ssl-cert
Suggested packages:
  ca-certificates postgresql-doc-7.4
Recommended packages:
  postgresql-plpython-7.4 postgresql-plperl-7.4 postgresql-pltcl-7.4
The following NEW packages will be installed
  openssl postgresql postgresql-7.4 postgresql-client postgresql-client-7.4 postgresql-client-common postgresql-common
  ssl-cert
0 upgraded, 8 newly installed, 0 to remove and 0 not upgraded.
Need to get 5620kB of archives.
After unpacking 14.3MB of additional disk space will be used.
Do you want to continue [Y/n]? y
Get: 1 http://cudlug.cudenver.edu stable/main openssl 0.9.8c-4 [1004kB]
......
........
..
...
Setting up postgresql-7.4 (7.4.16-1) ...
Creating new cluster (configuration: /etc/postgresql/7.4/main, data: /var/lib/postgresql/7.4/main)...
Moving configuration file /var/lib/postgresql/7.4/main/postgresql.conf to /etc/postgresql/7.4/main...
Moving configuration file /var/lib/postgresql/7.4/main/pg_hba.conf to /etc/postgresql/7.4/main...
Moving configuration file /var/lib/postgresql/7.4/main/pg_ident.conf to /etc/postgresql/7.4/main...
Configuring postgresql.conf to use port 5432...
Starting PostgreSQL 7.4 database server: main.

Setting up postgresql-client (7.5.22) ...
Setting up postgresql (7.5.22) ...

Task: Connect to PostgreSQL server

You can login as PostgreSQL superuser, enter command:
$ su - postgres
Now connect to default database called template1:
$ psql template1
Output:

postgres@debian:~$ psql template1
Welcome to psql 7.4.16, 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=#

Type \l to list database
Type \q to quit

See also: