11

I'm trying to set up phpPgAdmin on my test machine so that I can interface with PostgreSQL without always having to use the psql CLI. I have PostgreSQL 9.1 installed via the RPM repository, while I installed phpPgAdmin 5.0.4 "manually" (by extracting the archive from the phpPgAdmin website). For the record, my host OS is CentOS 6.2.

I made the following configuration changes already:

PostgreSQL

  • Inside pg_hba.conf, I changed all METHODs to md5.
  • I gave the postgres account a password
  • I added a new account named webuser with a password (note that I did not do anything else to the account, so I can't exactly say that I know what permissions it has and all)

phpPgAdmin config.inc.php

  • Changed the line $conf['servers'][0]['host'] = ''; to $conf['servers'][0]['host'] = '127.0.0.1'; (I've also tried using localhost as the value there).
  • Set $conf['extra_login_security'] to false.

Whenever I try to log in to phpPgAdmin, I get "Login failed", even if I use successful credentials (ones that work in psql). I've tried to go through some of the steps noted in Question 3 in the FAQ, but it hasn't worked out well so far there. It likely does not help that this is my first day working with PostgreSQL. I'm farily familiar with MySQL, but I have to use PostgreSQL for the project I'm working on.

Could anyone offer some help for how to set up phpPgAdmin on CentOS 6.2? If I've done something terribly wrong in my configuration so far, it's no big deal to blow something/everything away, as it's not like I've stored any data there yet!

I appreciate any insight you may have!

Devin
  • 355
  • 1
  • 3
  • 10

5 Answers5

7

Well, I figured it out - it was a combination of inexperience with PostgreSQL and very non-descriptive errors from phpPgAdmin.

Turns out I did not have TCP/IP access enabled, which meant that no PHP applications could access the database(s). To fix this, I had to make two changes:

  • It seemed to work better if I set listen_addresses to '*' in postgresql.conf. I'm not sure if that's entirely necessary or not, but on a well-firewalled machine like this one is, it shouldn't be a problem.
  • I had to run the command setsebool -P httpd_can_network_connect_db 1 from a terminal window to get Apache to actually get with the program and connect. I don't remember if I had to restart PostgreSQL after this step, but it probably wouldn't hurt.

One final note: I saw a few things suggesting changing a line tcpip_socket=true in postgresql.conf. This apparently does not work in PostgreSQL 9.1. In fact, it refused to start after I added that. So don't do it.

Anyways, hopefully this can help someone who runs into the same issues I did!

Devin
  • 355
  • 1
  • 3
  • 10
  • On systems with Security Enhanced Linux (SELinux), the setting the `httpd_can_network_connect_db` flag with `setsebool` is necessary. – postrational Dec 29 '13 at 23:35
  • Just to clarify, you don't necessarily need TCP/IP access enabled to get phpPgAdmin (or other PHP scripts) to connect to Postgres if you are on the same server, you can as an alternative connect via a local unix socket. You'd still probably run into SELinux related issues though. – xzilla Oct 21 '16 at 22:31
1

Running on a server with GitLab-CE, the message I receive on the log file was: 

tail -f -n 8 /var/log/postgresql/postgresql-*-main.log
......
2019-07-24 16:33:15.073 EDT [9640] postgres@template1 LOG:  provided user name (postgres) and authenticated user name (gitlab-www) do not match
2019-07-24 16:33:15.073 EDT [9640] postgres@template1 FATAL:  Peer authentication failed for user "postgres"
2019-07-24 16:33:15.073 EDT [9640] postgres@template1 DETAIL:  Connection matched pg_hba.conf line 85: "local   all             postgres                                peer"

so, I connect to psql via cli and create the missing role/user (in my case gitlab-www):

# sudo -u postgres psql
CREATE USER "gitlab-www" WITH PASSWORD 'YouKnow..' CREATEDB CREATEROLE SUPERUSER LOGIN;

And then, the login process open it's doors.

fcm
  • 398
  • 1
  • 2
  • 12
  • In general, peer authentication will limit the given user to only being able to access Postgres via the command line (like with psql), and will usually not work with phpPgAdmin. Creating a new user is a fine solution, but you could also just change the authentication method in the pg_hba.conf. – xzilla Aug 06 '19 at 01:09
0

I was unable to set up phpPgAdmin until I see your solution. To complete your solution :

  • it is not usefull to set listen_address to '*' if your phpPgAdmin server is on the same computer than your postgresql server (9.2 for me), you can set it to localhost.
  • the only thing that is usefull is to set up the SELinux boolean.
j0k
  • 401
  • 9
  • 16
user176931
  • 1
0

I tried the above solution but still got the 'Login Failed' message. To resolve, I had to ensure IPv6 is also set to password in the pg_hba.conf file. So do: vi /var/lib/pgsql/9.4/data/pg_hba.conf and edit accordingly. Then restart both httpd & postgresql (whatever the version you are using)

Olu
  • 1
0

Well, the default user is postgres and no password of a new installation. So in the user box put this and clear the password box. Click. done.

If you used pgAdmin4 and entered a password on first login that is the password you using for the user postgres. Now in phppgadmin type postgres as user name and that password in the password box. Click. Done. Thank you

NetBug
  • 9
  • 1