Can't access a newly installed PostgreSQL from a php script

Question

First of all I am new to PostgreSQL coming from MySQL. I have just installed PostgreSQL from the Fedora repositories and created the postgres user as per the fedora wiki pages, by su-ing into the postgres user and creating its role using the psql shell.

# su - postgres
$ psql
> \password

Then I created a test script that is served through nginx that will create a test DB and populate it. I'm trying to log in from the script by using the postgres user and its password via peer authentication, with the following pdo dsn:

new PDO('pgsql:user=postgres password=**********');

However, after struggling with SELinux, the connection fails saying that the peer authentication failed, so I looked into the postgresql logs and found that:

The delivered username (postgres) and the athenticated username (apache) do not match.

So I can deduce that the user that owns the process that's trying to access the db is the user that's being authenticated, so I assume I have to create the corresponding role for the apache user in the db. But why does it do this? Is it some sort of security measure? Is there a way to use the postgres role or am I bound to the apache role?

Craig Ringer Craig Ringer · Accepted Answer · 2015-10-07T00:35:26

Do not use the postgres user for applications. It's an administrative role. It's like running a web server as root. Create less-privileged users for your applications. You may also want to CREATE DATABASE ... WITH OWNER myappuser; to give the app ownership of a database, unless you plan to use finer-grained security.

Create a user for your application to use. Set a randomly generated password, and record that password so your app can use it.

Create a database for the app to use, with the app user as the owner of the database. (This isn't ideal, but it's the simple way. To learn more, read the PostgreSQL manual's chapters on security and privileges.)

Modify pg_hba.conf to add an entry for the user on that database that uses md5 authentication, at least for the database(s) of interest. Make sure the entry is above the default peer entries.

Then reload PostgreSQL's configuration - SELECT pg_reload_conf().

Your app can now connect to PostgreSQL using a password, but you can still get simple peer authentication with psql on the command line.

Can't access a newly installed PostgreSQL from a php script

1 Answers