Обсуждение: convert system from not using a password to using passwords
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
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?
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
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.
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
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.
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
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
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