Re: convert system from not using a password to using passwords

"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(dot)Fromm(at)wwu(dot)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(at)keithf4(dot)com]
> *Sent:* Monday, November 09, 2015 4:35 PM
>
> *To:* Marc Fromm <Marc(dot)Fromm(at)wwu(dot)edu>
> *Cc:* pgsql-admin(at)postgresql(dot)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(dot)Fromm(at)wwu(dot)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(at)keithf4(dot)com]
> *Sent:* Monday, November 09, 2015 2:36 PM
> *To:* Marc Fromm <Marc(dot)Fromm(at)wwu(dot)edu>
> *Cc:* pgsql-admin(at)postgresql(dot)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(dot)Fromm(at)wwu(dot)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.
>
>
>
> http://www.postgresql.org/docs/9.4/static/auth-pg-hba-conf.html
>
>
>
> 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
>
>
>
>
>
>
>