Обсуждение: non-superuser login phpPgAdmin PostgreSQL 9.1
Dear All, I am in the process of upgrading an existing database from PostgreSQL 8.1 to 9.1 . In trial upgrades I have discovered that in 9.1 only superusers can login to phpPgAdmin. The database has a number of users who are already accustomed to using phpPgAdmin but for whom it would not be appropriate to be superusers. How is it possible to permit non-superusers to login? -- View this message in context: http://postgresql.1045698.n5.nabble.com/non-superuser-login-phpPgAdmin-PostgreSQL-9-1-tp5723838.html Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
johnkn63 wrote: > I am in the process of upgrading an existing database from PostgreSQL 8.1 to > 9.1 . In trial upgrades I have discovered that in 9.1 only superusers can > login to phpPgAdmin. The database has a number of users who are already > accustomed to using phpPgAdmin but for whom it would not be appropriate to > be superusers. How is it possible to permit non-superusers to login? What is the error message you get for non-superusers? You could also ask here: http://phppgadmin.sourceforge.net/doku.php?id=getting_help Yours, Laurenz Albe
The phpPgAdmin message is "login failed" if I change the same user to a superuser they can login. -- View this message in context: http://postgresql.1045698.n5.nabble.com/non-superuser-login-phpPgAdmin-PostgreSQL-9-1-tp5723838p5723841.html Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
Hi, On Thu, 2012-09-13 at 01:30 -0700, johnkn63 wrote: > The phpPgAdmin message is "login failed" if I change the same user to > a superuser they can login. Did you check pg_hba.conf? What's in the PostgreSQL logs? Regards, -- Devrim GÜNDÜZ Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz
Вложения
I have checked the pg_hba.conf the log says 'CST FATAL: password authentication failed for user "test8"'. -- View this message in context: http://postgresql.1045698.n5.nabble.com/non-superuser-login-phpPgAdmin-PostgreSQL-9-1-tp5723838p5723843.html Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
johnkn63 wrote: > I have checked the pg_hba.conf the log says 'CST FATAL: password > authentication failed for user "test8"'. That indicates a wrong password. How does pg_hba.conf look? Yours, Laurenz Albe
Thank you for your patience. Whilst this is the error one gets for a wrong
password, this is not the case here simply changing the status of the user
to superuser without touching the password allows the user to login,
removing superuser status the reverse.
This login error might be caused by the following, or something similar:-
"pg_upgrade already starts the postmaster with a -b option that disables
non-super-user logins:
/*
* Binary upgrades only allowed super-user connections
*/
if (IsBinaryUpgrade && !am_superuser)
{
ereport(FATAL,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("must be superuser to connect in binary upgrade
mode")));
} "
taken from
http://postgresql.1045698.n5.nabble.com/new-maintenance-db-options-td5714000.html
http://postgresql.1045698.n5.nabble.com/new-maintenance-db-options-td5714000.html
. This seems to be new in 9.1 .
pg_hba.conf is the standard
local all postgres peer
local all all peer
host all all 127.0.0.1/32 md5
host all all ::1/128 md5
Regards
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/non-superuser-login-phpPgAdmin-PostgreSQL-9-1-tp5723838p5723854.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
johnkn63 wrote:
> Thank you for your patience. Whilst this is the error one gets for a
wrong
> password, this is not the case here simply changing the status of the
user
> to superuser without touching the password allows the user to login,
> removing superuser status the reverse.
>
> This login error might be caused by the following, or something
similar:-
>
> "pg_upgrade already starts the postmaster with a -b option that
disables
> non-super-user logins:
>
> /*
> * Binary upgrades only allowed super-user connections
> */
> if (IsBinaryUpgrade && !am_superuser)
> {
> ereport(FATAL,
> (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
> errmsg("must be superuser to connect in binary upgrade
> mode")));
> } "
>
> taken from
>
http://postgresql.1045698.n5.nabble.com/new-maintenance-db-options-td571
4000.html
>
http://postgresql.1045698.n5.nabble.com/new-maintenance-db-options-td571
4000.html
> . This seems to be new in 9.1 .
That's if you started the server with -b.
Don't do that.
I guess I misunderstood the original problem.
I thought that you only have a problem with phpPgAdmin.
Can you connect with
"psql -h 127.0.0.1 -U nonsuperuser -d database" ?
> pg_hba.conf is the standard
>
> local all postgres peer
> local all all peer
> host all all 127.0.0.1/32 md5
> host all all ::1/128 md5
That should be OK as long as you only connect from localhost.
Yours,
Laurenz Albe
> That's if you started the server with -b. now how do I find out how server is starting and how do I change it? I am running 9.1 on debian >Can you connect with >"psql -h 127.0.0.1 -U nonsuperuser -d database" ? no, psql -h 127.0.0.1 -U nonsuperuser -d database does not work though of course, psql -h 127.0.0.1 -U superuser -d database works -- View this message in context: http://postgresql.1045698.n5.nabble.com/non-superuser-login-phpPgAdmin-PostgreSQL-9-1-tp5723838p5723872.html Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
johnkn63 wrote: >> That's if you started the server with -b. > now how do I find out how server is starting and how do I change it? I am > running 9.1 on debian Look at the output of "pg_ctl status". What do you see there? Actually, the error message would be different in this case, so I don't think that that's your problem. >> Can you connect with >> "psql -h 127.0.0.1 -U nonsuperuser -d database" ? > > no, > > psql -h 127.0.0.1 -U nonsuperuser -d database > > does not work > > though of course, > psql -h 127.0.0.1 -U superuser -d database > works This sounds mysterious. Could you try the following with psql (as superuser): psql -U postgres -d postgres postgres=# CREATE ROLE testuser LOGIN PASSWORD 'testpwd'; postgres=# GRANT CONNECT ON DATABASE postgres TO testuser; postgres=# \q Then try to connect with psql -h 127.0.0.1 -U testuser -d postgres -W and enter the password. That should not work according to your report. Now login again as superuser and try: psql -U postgres -d postgres postgres=# ALTER USER testuser SUPERUSER; postgres=# \q Then see if you can connect as "testuser" as above. Yours, Laurenz Albe
I tried - the results where as predicted whilst a non-superuser does not work but once altered to superuser does work. So what next? Regards -- View this message in context: http://postgresql.1045698.n5.nabble.com/non-superuser-login-phpPgAdmin-PostgreSQL-9-1-tp5723838p5723886.html Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
And pg_ctl status seems normal :-
/usr/lib/postgresql/9.1/bin/pg_ctl -D /var/lib/postgresql/9.1/main/
status
returns:-
pg_ctl: server is running (PID: 1040)
/usr/lib/postgresql/9.1/bin/postgres "-D" "/var/lib/postgresql/9.1/main"
"-c" "config_file=/etc/postgresql/9.1/main/postgresql.conf
So where else to look?
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/non-superuser-login-phpPgAdmin-PostgreSQL-9-1-tp5723838p5723890.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
johnkn63 wrote: [can connect as superuser but not as regular user] > I tried - the results where as predicted whilst a non-superuser does not > work but once altered to superuser does work. > > So what next? Hmm, I'm running out of ideas here. You still get "password authentication failed" in the log? Can you run these three queries and post the result: SELECT usesuper, count(*) FROM pg_stat_activity JOIN pg_user USING (usesysid) GROUP BY usesuper; SHOW superuser_reserved_connections; SHOW max_connections; Yours, Laurenz Albe
Laurenz, here are the results On Fri, Sep 14, 2012 at 3:59 PM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote: > johnkn63 wrote: > [can connect as superuser but not as regular user] > >> I tried - the results where as predicted whilst a non-superuser does > not >> work but once altered to superuser does work. >> >> So what next? > > Hmm, I'm running out of ideas here. > You still get "password authentication failed" in the log? > > > Can you run these three queries and post the result: > > SELECT usesuper, count(*) FROM pg_stat_activity JOIN pg_user USING > (usesysid) GROUP BY usesuper; > usesuper | count ----------+------- t | 1 (1 row) > SHOW superuser_reserved_connections; > superuser_reserved_connections -------------------------------- 3 (1 row) > SHOW max_connections; > max_connections ----------------- 100 (1 row) > Yours, > Laurenz Albe experimenting I think I have found the solution:- For each non-superuser do:- GRANT CONNECT ON DATABASE postgres TO "nonsuperuser"; in the 8.1 database have:- CREATE ROLE "name"; ALTER ROLE "name" WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN PASSWORD 'md5....'; Looking at the manual pages it would appear CONNECT was added in postgesql 8.2 cf http://www.postgresql.org/docs/8.1/static/sql-grant.html and http://www.postgresql.org/docs/8.1/static/sql-grant.html Thank you for your help in getting to the bottom of this. John