Re: Role Authentication Failure

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(dot)klaver(at)gmail(dot)com> wrote:
>
>
> On Mon, Apr 15, 2013 at 7:30 AM, Carlos Mennens <carlos(dot)mennens(at)gmail(dot)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(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>
>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)gmail(dot)com

--
Carlos Mennens