From: | john knightley <john(dot)knightley(at)gmail(dot)com> |
---|---|
To: | Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> |
Cc: | pgsql-admin(at)postgresql(dot)org |
Subject: | Re: non-superuser login phpPgAdmin PostgreSQL 9.1 |
Date: | 2012-09-14 13:02:20 |
Message-ID: | CA+nPCM9peAQgrmNM=a_vRu=HVVXoT9gaxViudJnoyr1tJESqyg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Laurenz,
here are the results
On Fri, Sep 14, 2012 at 3:59 PM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2012-09-14 14:26:16 | Re: pg_upgrade from 9.1.3 to 9.2 failed |
Previous Message | Albe Laurenz | 2012-09-14 07:59:18 | Re: non-superuser login phpPgAdmin PostgreSQL 9.1 |