Re: 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
>
>
>
>
>