I have done some testing with the IP4 and IP6 host settings. Even though my desktop and the server use IP4, and if I only change IP4 entry for postgres to md5, leaving IP6 host setting as trust, the server allows postgres to work without a password. After I set IP6 (::1/128) to md5 the server then rejects access from web pages using the postgres account, since it has no password set yet.
Even with local postgres set to md5, I was able to run a script on the server that uses the account postgres to perform a backup. I thought it would fail since postgres as no password set yet. However, after setting host postgres IP6 (::1/128) to md5, then after executing the script I was prompted for a password.
It seems the local and IP4 are being ignored and only IP6 is controlling the trust/md5 behavior.
local all postgres md5
local all web_u1 md5
host all postgres 127.0.0.1/32 md5
host all web_u1 127.0.0.1/32 md5
host all postgres ::1/128 trust
host all web_u1 ::1/128 md5
From: Keith [mailto:keith@xxxxxxxxxxx]
Sent: Monday, November 09, 2015 4:35 PM
To: Marc Fromm <Marc.Fromm@xxxxxxx>
Cc: pgsql-admin@xxxxxxxxxxxxxx
Subject: Re: convert system from not using a password to using passwords
On Mon, Nov 9, 2015 at 6:38 PM, Marc Fromm <Marc.Fromm@xxxxxxx> wrote:
Thanks Keith,
Since in the pg_hba.conf file the all databases column is set to “all” can “web_u1” user not be a superuser, as I created with the CREATEUSER flag, and still work with all the databases? The GRANT option seems to be tale or database specifc.
The pg_hba.conf is all about authentication and completely independent of the GRANT system in the database.
From: Keith [mailto:keith@xxxxxxxxxxx]
Sent: Monday, November 09, 2015 2:36 PM
To: Marc Fromm <Marc.Fromm@xxxxxxx>
Cc: pgsql-admin@xxxxxxxxxxxxxx
Subject: Re: convert system from not using a password to using passwords
On Mon, Nov 9, 2015 at 5:16 PM, Marc Fromm <Marc.Fromm@xxxxxxx> wrote:
I inherited a setup where php pages use postgresql databases. Currently the php pages use pg_connect with user=postgres and password=’’. I want to change this to using a different user that has a password.
1. First created a user that can access all the databases:
postgres=# CREATE USER web_u1 with PASSWORD '********' CREATEUSER;
2. Next I changed pg_hba.conf with the entries
# "local" is for Unix domain socket connections only
#local all all trust
local all all md5
# IPv4 local connections:
#host all all 127.0.0.1/32 trust
host all all 127.0.0.1/32 md5
# IPv6 local connections:
#host all all ::1/128 trust
host all all ::1/128 md5
3. I changed the php code as follows
$conn = pg_connect("host=localhost port=5432 user=web_u1 dbname=db_name password='********'");
This all worked. My problem is the obvious, all pages are broken until I update each page that has a pg_connect statement. Is there a way to configure the pg_hba.conf file to accept the “user=postgres with no password,” if “user=web_u1” with a password is not provided?
Also is there anything I missed in my steps with creating the user with a password and updating the pg_hba.conf file?
Thanks
Marc
The third column in those config lines is for the roles (users). You can define the authentication method per role. "all" is just a keyword for any role.
So if you want to allow the "postgres" role to connect with no password, but restrict the new user to requiring a password you could do.
local all postgres trust
local all web_u1 md5
host all postgres 127.0.0.1/32 trust
host all web_u1 127.0.0.1/32 md5
host all postgres ::1/128 trust
host all web_u1 ::1/128 md5
Then once you've got all your config files fixed, you can remove those trust lines
Keith
"local" only controls unix socket connections (see the documentation linked).
I'm really not sure what controls whether it uses IPv4 or 6. Maybe someone else can provide insight.
On Tue, Nov 10, 2015 at 12:39 PM, Marc Fromm <Marc.Fromm@xxxxxxx> wrote:
