pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feednon-superuser login phpPgAdmin PostgreSQL 9.1
14+ messages / 4 participants
[nested] [flat]
* non-superuser login phpPgAdmin PostgreSQL 9.1
@ 2012-09-13 08:04 johnkn63 <john.knightley@gmail.com>
2012-09-13 08:15 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 Albe Laurenz <laurenz.albe@wien.gv.at>
0 siblings, 1 reply; 14+ messages in thread
From: johnkn63 @ 2012-09-13 08:04 UTC (permalink / raw)
To: pgsql-admin
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.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: non-superuser login phpPgAdmin PostgreSQL 9.1
2012-09-13 08:04 non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
@ 2012-09-13 08:15 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2012-09-13 08:30 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Albe Laurenz @ 2012-09-13 08:15 UTC (permalink / raw)
To: johnkn63 *EXTERN* <john.knightley@gmail.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: non-superuser login phpPgAdmin PostgreSQL 9.1
2012-09-13 08:04 non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
2012-09-13 08:15 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 Albe Laurenz <laurenz.albe@wien.gv.at>
@ 2012-09-13 08:30 ` johnkn63 <john.knightley@gmail.com>
2012-09-13 08:35 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 Devrim GÜNDÜZ <devrim@gunduz.org>
0 siblings, 1 reply; 14+ messages in thread
From: johnkn63 @ 2012-09-13 08:30 UTC (permalink / raw)
To: pgsql-admin
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-tp5723838p5723...
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: non-superuser login phpPgAdmin PostgreSQL 9.1
2012-09-13 08:04 non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
2012-09-13 08:15 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 Albe Laurenz <laurenz.albe@wien.gv.at>
2012-09-13 08:30 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
@ 2012-09-13 08:35 ` Devrim GÜNDÜZ <devrim@gunduz.org>
2012-09-13 08:45 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Devrim GÜNDÜZ @ 2012-09-13 08:35 UTC (permalink / raw)
To: johnkn63 <john.knightley@gmail.com>; +Cc: pgsql-admin
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
Attachments:
signature.asc (application/pgp-signature, 197B) download
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: non-superuser login phpPgAdmin PostgreSQL 9.1
2012-09-13 08:04 non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
2012-09-13 08:15 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 Albe Laurenz <laurenz.albe@wien.gv.at>
2012-09-13 08:30 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
2012-09-13 08:35 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 Devrim GÜNDÜZ <devrim@gunduz.org>
@ 2012-09-13 08:45 ` johnkn63 <john.knightley@gmail.com>
2012-09-13 09:29 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 Albe Laurenz <laurenz.albe@wien.gv.at>
0 siblings, 1 reply; 14+ messages in thread
From: johnkn63 @ 2012-09-13 08:45 UTC (permalink / raw)
To: pgsql-admin
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-tp5723838p5723...
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: non-superuser login phpPgAdmin PostgreSQL 9.1
2012-09-13 08:04 non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
2012-09-13 08:15 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 Albe Laurenz <laurenz.albe@wien.gv.at>
2012-09-13 08:30 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
2012-09-13 08:35 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 Devrim GÜNDÜZ <devrim@gunduz.org>
2012-09-13 08:45 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
@ 2012-09-13 09:29 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2012-09-13 10:05 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Albe Laurenz @ 2012-09-13 09:29 UTC (permalink / raw)
To: johnkn63 *EXTERN* <john.knightley@gmail.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: non-superuser login phpPgAdmin PostgreSQL 9.1
2012-09-13 08:04 non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
2012-09-13 08:15 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 Albe Laurenz <laurenz.albe@wien.gv.at>
2012-09-13 08:30 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
2012-09-13 08:35 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 Devrim GÜNDÜZ <devrim@gunduz.org>
2012-09-13 08:45 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
2012-09-13 09:29 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 Albe Laurenz <laurenz.albe@wien.gv.at>
@ 2012-09-13 10:05 ` johnkn63 <john.knightley@gmail.com>
2012-09-13 10:27 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 Albe Laurenz <laurenz.albe@wien.gv.at>
0 siblings, 1 reply; 14+ messages in thread
From: johnkn63 @ 2012-09-13 10:05 UTC (permalink / raw)
To: pgsql-admin
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-tp5723838p5723...
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: non-superuser login phpPgAdmin PostgreSQL 9.1
2012-09-13 08:04 non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
2012-09-13 08:15 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 Albe Laurenz <laurenz.albe@wien.gv.at>
2012-09-13 08:30 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
2012-09-13 08:35 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 Devrim GÜNDÜZ <devrim@gunduz.org>
2012-09-13 08:45 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
2012-09-13 09:29 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 Albe Laurenz <laurenz.albe@wien.gv.at>
2012-09-13 10:05 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
@ 2012-09-13 10:27 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2012-09-13 13:31 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Albe Laurenz @ 2012-09-13 10:27 UTC (permalink / raw)
To: johnkn63 *EXTERN* <john.knightley@gmail.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: non-superuser login phpPgAdmin PostgreSQL 9.1
2012-09-13 08:04 non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
2012-09-13 08:15 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 Albe Laurenz <laurenz.albe@wien.gv.at>
2012-09-13 08:30 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
2012-09-13 08:35 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 Devrim GÜNDÜZ <devrim@gunduz.org>
2012-09-13 08:45 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
2012-09-13 09:29 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 Albe Laurenz <laurenz.albe@wien.gv.at>
2012-09-13 10:05 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
2012-09-13 10:27 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 Albe Laurenz <laurenz.albe@wien.gv.at>
@ 2012-09-13 13:31 ` johnkn63 <john.knightley@gmail.com>
2012-09-13 14:11 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 Albe Laurenz <laurenz.albe@wien.gv.at>
0 siblings, 1 reply; 14+ messages in thread
From: johnkn63 @ 2012-09-13 13:31 UTC (permalink / raw)
To: pgsql-admin
> 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-tp5723838p5723...
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: non-superuser login phpPgAdmin PostgreSQL 9.1
2012-09-13 08:04 non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
2012-09-13 08:15 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 Albe Laurenz <laurenz.albe@wien.gv.at>
2012-09-13 08:30 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
2012-09-13 08:35 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 Devrim GÜNDÜZ <devrim@gunduz.org>
2012-09-13 08:45 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
2012-09-13 09:29 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 Albe Laurenz <laurenz.albe@wien.gv.at>
2012-09-13 10:05 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
2012-09-13 10:27 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 Albe Laurenz <laurenz.albe@wien.gv.at>
2012-09-13 13:31 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
@ 2012-09-13 14:11 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2012-09-13 14:46 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Albe Laurenz @ 2012-09-13 14:11 UTC (permalink / raw)
To: johnkn63 *EXTERN* <john.knightley@gmail.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: non-superuser login phpPgAdmin PostgreSQL 9.1
2012-09-13 08:04 non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
2012-09-13 08:15 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 Albe Laurenz <laurenz.albe@wien.gv.at>
2012-09-13 08:30 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
2012-09-13 08:35 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 Devrim GÜNDÜZ <devrim@gunduz.org>
2012-09-13 08:45 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
2012-09-13 09:29 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 Albe Laurenz <laurenz.albe@wien.gv.at>
2012-09-13 10:05 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
2012-09-13 10:27 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 Albe Laurenz <laurenz.albe@wien.gv.at>
2012-09-13 13:31 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
2012-09-13 14:11 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 Albe Laurenz <laurenz.albe@wien.gv.at>
@ 2012-09-13 14:46 ` johnkn63 <john.knightley@gmail.com>
2012-09-13 15:08 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
2012-09-14 07:59 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 Albe Laurenz <laurenz.albe@wien.gv.at>
0 siblings, 2 replies; 14+ messages in thread
From: johnkn63 @ 2012-09-13 14:46 UTC (permalink / raw)
To: pgsql-admin
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-tp5723838p5723...
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: non-superuser login phpPgAdmin PostgreSQL 9.1
2012-09-13 08:04 non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
2012-09-13 08:15 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 Albe Laurenz <laurenz.albe@wien.gv.at>
2012-09-13 08:30 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
2012-09-13 08:35 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 Devrim GÜNDÜZ <devrim@gunduz.org>
2012-09-13 08:45 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
2012-09-13 09:29 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 Albe Laurenz <laurenz.albe@wien.gv.at>
2012-09-13 10:05 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
2012-09-13 10:27 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 Albe Laurenz <laurenz.albe@wien.gv.at>
2012-09-13 13:31 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
2012-09-13 14:11 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 Albe Laurenz <laurenz.albe@wien.gv.at>
2012-09-13 14:46 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
@ 2012-09-13 15:08 ` johnkn63 <john.knightley@gmail.com>
1 sibling, 0 replies; 14+ messages in thread
From: johnkn63 @ 2012-09-13 15:08 UTC (permalink / raw)
To: pgsql-admin
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-tp5723838p5723...
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: non-superuser login phpPgAdmin PostgreSQL 9.1
2012-09-13 08:04 non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
2012-09-13 08:15 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 Albe Laurenz <laurenz.albe@wien.gv.at>
2012-09-13 08:30 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
2012-09-13 08:35 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 Devrim GÜNDÜZ <devrim@gunduz.org>
2012-09-13 08:45 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
2012-09-13 09:29 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 Albe Laurenz <laurenz.albe@wien.gv.at>
2012-09-13 10:05 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
2012-09-13 10:27 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 Albe Laurenz <laurenz.albe@wien.gv.at>
2012-09-13 13:31 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
2012-09-13 14:11 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 Albe Laurenz <laurenz.albe@wien.gv.at>
2012-09-13 14:46 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
@ 2012-09-14 07:59 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2012-09-14 13:02 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 john knightley <john.knightley@gmail.com>
1 sibling, 1 reply; 14+ messages in thread
From: Albe Laurenz @ 2012-09-14 07:59 UTC (permalink / raw)
To: johnkn63 *EXTERN* <john.knightley@gmail.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: non-superuser login phpPgAdmin PostgreSQL 9.1
2012-09-13 08:04 non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
2012-09-13 08:15 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 Albe Laurenz <laurenz.albe@wien.gv.at>
2012-09-13 08:30 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
2012-09-13 08:35 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 Devrim GÜNDÜZ <devrim@gunduz.org>
2012-09-13 08:45 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
2012-09-13 09:29 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 Albe Laurenz <laurenz.albe@wien.gv.at>
2012-09-13 10:05 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
2012-09-13 10:27 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 Albe Laurenz <laurenz.albe@wien.gv.at>
2012-09-13 13:31 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
2012-09-13 14:11 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 Albe Laurenz <laurenz.albe@wien.gv.at>
2012-09-13 14:46 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
2012-09-14 07:59 ` Re: non-superuser login phpPgAdmin PostgreSQL 9.1 Albe Laurenz <laurenz.albe@wien.gv.at>
@ 2012-09-14 13:02 ` john knightley <john.knightley@gmail.com>
0 siblings, 0 replies; 14+ messages in thread
From: john knightley @ 2012-09-14 13:02 UTC (permalink / raw)
To: Albe Laurenz <laurenz.albe@wien.gv.at>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2012-09-14 13:02 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-09-13 08:04 non-superuser login phpPgAdmin PostgreSQL 9.1 johnkn63 <john.knightley@gmail.com>
2012-09-13 08:15 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2012-09-13 08:30 ` johnkn63 <john.knightley@gmail.com>
2012-09-13 08:35 ` Devrim GÜNDÜZ <devrim@gunduz.org>
2012-09-13 08:45 ` johnkn63 <john.knightley@gmail.com>
2012-09-13 09:29 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2012-09-13 10:05 ` johnkn63 <john.knightley@gmail.com>
2012-09-13 10:27 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2012-09-13 13:31 ` johnkn63 <john.knightley@gmail.com>
2012-09-13 14:11 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2012-09-13 14:46 ` johnkn63 <john.knightley@gmail.com>
2012-09-13 15:08 ` johnkn63 <john.knightley@gmail.com>
2012-09-14 07:59 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2012-09-14 13:02 ` john knightley <john.knightley@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox