I'm doing this all in psql.
Example:
CREATE ROLE carlos LOGIN CREATEDB CREATE ROLE REPLICATION;
Then set password \password carlos
Now I create the ROLE:
CREATE ROLE dba NOLOGIN;
So now I have two roles:
-carlos = user role
-dba = group role
I can login just fine as 'carlos' now with no authentication failure.
But when I do:
GRANT dba TO carlos;
That's the end of 'carlos' being able to login. What am I doing wrong?
The only files I have edited in PostgreSQL post installation is
pg_hba.conf & postgres.conf. It's a new 9.1.9 installation with no
data yet. I just don't understand why granting a role to a user
destroys his authentication and even REVOKE his dba group role doesn't
fix 'carlos'.
On Mon, Apr 15, 2013 at 11:27 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
>
>
> On Mon, Apr 15, 2013 at 7:30 AM, Carlos Mennens <carlos.mennens@gmail.com>
> wrote:
>>
>> I can't understand what is going on with my PostgreSQL server. For
>> some reason after I GRANT my role 'carlos' to the 'dba' group role, I
>> lose the ability to login. I've reset the password over and over for
>> 'carlos' and even reversing the grant doesn't remedy the
>> authentication failure. The logs are extremely vague:
>>
>> 2013-04-14 00:41:52 EDT FATAL: password authentication failed for user
>> "carlos"
>>
>>
>
> How are you doing the above, via psql. pgAdmin, other or some combination?
> Do you have a .pgpass file set up?
>
>>
>> As you can see I've removed 'dba' role from 'carlos' but he still
>> can't login. My only option is to DROP the 'carlos' role and re-create
>> him.
>>
>> The problem occurs when I:
>>
>> GRANT dba TO carlos;
>>
>> I don't understand which role (carlos or dba) needs INHERIT or
>> NOINHERIT if that's causing this...
>>
>>
>> --
>> Carlos Mennens
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>
>
>
>
> --
> Adrian Klaver
> adrian.klaver@gmail.com
--
Carlos Mennens