Re: convert system from not using a password to using passwords - Mailing list pgsql-admin

From Keith
Subject Re: convert system from not using a password to using passwords
Date
Msg-id CAHw75vseZxcyfYS1U-GN2g6TU55XzR9riFoZFmxC5xC35cbnVw@mail.gmail.com
Whole thread Raw
In response to Re: convert system from not using a password to using passwords  (Marc Fromm <Marc.Fromm@wwu.edu>)
List pgsql-admin
"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@wwu.edu> wrote:

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@keithf4.com]
Sent: Monday, November 09, 2015 4:35 PM


To: Marc Fromm <Marc.Fromm@wwu.edu>
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] convert system from not using a password to using passwords

 

 

 

On Mon, Nov 9, 2015 at 6:38 PM, Marc Fromm <Marc.Fromm@wwu.edu> 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@keithf4.com]
Sent: Monday, November 09, 2015 2:36 PM
To: Marc Fromm <Marc.Fromm@wwu.edu>
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] convert system from not using a password to using passwords

 

 

 

On Mon, Nov 9, 2015 at 5:16 PM, Marc Fromm <Marc.Fromm@wwu.edu> 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

 

 

 


pgsql-admin by date:

Previous
From: Marc Fromm
Date:
Subject: Re: convert system from not using a password to using passwords
Next
From: koff10
Date:
Subject: Re: interactif pg environnement script