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