Thread: Role Authentication Failure

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"

My roles look as follows:

postgres=# \dg
                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 carlos    | Superuser, Create role, Create DB, Replication | {}
 dba       | No inheritance, Cannot login                   | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}

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

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

On 04/15/2013 09:10 AM, Carlos Mennens wrote:
> I'm doing this all in psql.
>
> Example:
>
> CREATE ROLE carlos LOGIN CREATEDB CREATE ROLE REPLICATION;

I going to assume you actually did:

CREATE ROLE carlos LOGIN CREATEDB CREATEROLE REPLICATION;

otherwise it would fail on the second CREATE ROLE.

>
> 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?

Unfortunately I do not have a 9.1.x instance handy. I tried the above on
9.0.x and everything worked.

Could you run the above sequence and show the log information from the run?

>
> 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'.
>


--
Adrian Klaver
adrian.klaver@gmail.com

Adrian Klaver <adrian.klaver@gmail.com> writes:
> On 04/15/2013 09:10 AM, Carlos Mennens wrote:
>> That's the end of 'carlos' being able to login. What am I doing wrong?

> Unfortunately I do not have a 9.1.x instance handy. I tried the above on
> 9.0.x and everything worked.

Works for me on 9.1.9, too.  Perhaps there is a special case for "dba"
members in Carlos' pg_hba.conf file?

            regards, tom lane

I dropped both roles (Carlos & DBA) from the database and I will show
you exactly what I'm doing:

postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 chris     | Create DB, Replication                         | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}

I am creating the new roles as the 'postgres' database user:

postgres=# CREATE ROLE carlos LOGIN CREATEDB CREATEROLE REPLICATION;
CREATE ROLE

postgres=# CREATE ROLE dba NOLOGIN;
CREATE ROLE

postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 carlos    | Create role, Create DB, Replication            | {}
 chris     | Create DB, Replication                         | {}
 dba       | Cannot login                                   | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}

I was curious what happens when I used 'CREATE GROUP' versus 'CREATE
ROLE' so I gave it a shot but looks like it is the same:

postgres=# CREATE GROUP web;
CREATE ROLE

postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 carlos    | Create role, Create DB, Replication            | {}
 chris     | Create DB, Replication                         | {}
 dba       | Cannot login                                   | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 web       | Cannot login                                   | {}

postgres=# \password carlos
Enter new password:
Enter it again:

Now I login as 'carlos':

carlos@debian:~$ psql -d postgres
Password:
psql (9.1.9)
Type "help" for help.

postgres=>

but...

--
Carlos Mennens

On 04/15/2013 07:29 PM, Carlos Mennens wrote:
> I dropped both roles (Carlos & DBA) from the database and I will show
> you exactly what I'm doing:
>
> postgres=# \du
>                               List of roles
>   Role name |                   Attributes                   | Member of
> -----------+------------------------------------------------+-----------
>   chris     | Create DB, Replication                         | {}
>   postgres  | Superuser, Create role, Create DB, Replication | {}
>
> I am creating the new roles as the 'postgres' database user:
>
> postgres=# CREATE ROLE carlos LOGIN CREATEDB CREATEROLE REPLICATION;
> CREATE ROLE
>
> postgres=# CREATE ROLE dba NOLOGIN;
> CREATE ROLE
>
> postgres=# \du
>                               List of roles
>   Role name |                   Attributes                   | Member of
> -----------+------------------------------------------------+-----------
>   carlos    | Create role, Create DB, Replication            | {}
>   chris     | Create DB, Replication                         | {}
>   dba       | Cannot login                                   | {}
>   postgres  | Superuser, Create role, Create DB, Replication | {}
>
> I was curious what happens when I used 'CREATE GROUP' versus 'CREATE
> ROLE' so I gave it a shot but looks like it is the same:
>
> postgres=# CREATE GROUP web;
> CREATE ROLE
>
> postgres=# \du
>                               List of roles
>   Role name |                   Attributes                   | Member of
> -----------+------------------------------------------------+-----------
>   carlos    | Create role, Create DB, Replication            | {}
>   chris     | Create DB, Replication                         | {}
>   dba       | Cannot login                                   | {}
>   postgres  | Superuser, Create role, Create DB, Replication | {}
>   web       | Cannot login                                   | {}
>
> postgres=# \password carlos
> Enter new password:
> Enter it again:
>
> Now I login as 'carlos':
>
> carlos@debian:~$ psql -d postgres
> Password:
> psql (9.1.9)
> Type "help" for help.
>
> postgres=>
>
> but...

?
Hit enter to soon?

>
> --
> Carlos Mennens
>
>


--
Adrian Klaver
adrian.klaver@gmail.com

Hi,

I have two IBM Power System servers, the architecture is ppc64,  the PostgreSQL on RHEL6 for IBM Power is version 8.4
butI need to install 9.2.4 as I need to use the PostgreSQL replication features. 

I am new to PostgreSQL on ppc64, can any one advise me where to get the proper 9.2.4 installation package for ppc64?
orplease advise me the steps to compile from the 9.2.4 source. 

Regards

> I have two IBM Power System servers, the architecture is ppc64,  the PostgreSQL on RHEL6 for IBM Power is version 8.4
butI need to install 9.2.4 as I need to use the PostgreSQL replication features. 
>
> I am new to PostgreSQL on ppc64, can any one advise me where to get the proper 9.2.4 installation package for ppc64?
orplease advise me the steps to compile from the 9.2.4 source. 

I think there's nothing special with installing PostgreSQL from
9.2.4. source code on Power. Please let me know if you have any problem.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

On 04/15/2013 07:29 PM, Carlos Mennens wrote:
> I dropped both roles (Carlos & DBA) from the database and I will show
> you exactly what I'm doing:
>
>
> Now I login as 'carlos':
>
> carlos@debian:~$ psql -d postgres
> Password:
> psql (9.1.9)
> Type "help" for help.
>
> postgres=>
>
> but...

To add to my post from last night:

1) What is in your pg_hba.conf file?

2) When you do  psql -d postgres are you sure you are logging in as
carlos? In other words no PGUSER set. While debugging this I would try
to be as explicit as possible in your command line

3) Do you have a .pgpass file and does its contents match the
users/passwords you are using?

>
> --
> Carlos Mennens
>
>


--
Adrian Klaver
adrian.klaver@gmail.com

Hi,

I have managed to install 9.2.4 to an IBM Power System server ppc64 by compiling pg 9.2.4 from source as I cannot find
the9.2.4 installer package for ppc64.  Can anyone advise me how to build my own installer package for ppc64 (e.g.
tools,steps)?  since I have more than one ppc64 server and I want to avoid the compile-from-source approach. 

regards

On 04/16/2013 07:04 PM, ascot.moss@gmail.com wrote:
>
> Hi,
>
> I have managed to install 9.2.4 to an IBM Power System server ppc64 by compiling pg 9.2.4 from source as I cannot
findthe 9.2.4 installer package for ppc64.  Can anyone advise me how to build my own installer package for ppc64 (e.g.
tools,steps)?  since I have more than one ppc64 server and I want to avoid the compile-from-source approach. 
>
> regards

Are you using AIX or Linux?

Either way... you can just use the same binaries/libs on each system
(assuming they are all the same OS). Just tarball them up.

JD

On 4/16/2013 7:04 PM, ascot.moss@gmail.com wrote:
> I have managed to install 9.2.4 to an IBM Power System server ppc64 by compiling pg 9.2.4 from source as I cannot
findthe 9.2.4 installer package for ppc64.  Can anyone advise me how to build my own installer package for ppc64 (e.g.
tools,steps)?  since I have more than one ppc64 server and I want to avoid the compile-from-source approach. 

on AIX, I build mine to run in /opt/$MYGROUP/pgsql/9.2 and I just tar it
up and distribute it as a tarball to my operations people. I'd
previously asked them if they wanted it as a system package, and their
response was 'why bother?'.      I throw in a script that adds it as a
service (via /etc/init.d & rc3.d, I'm too lazy to figure out the new AIX
6.1 service manager).


--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

Hi John,

On 17 Apr 2013, at 11:30 AM, John R Pierce wrote:

> on AIX, I build mine to run in /opt/$MYGROUP/pgsql/9.2 and I just tar it up and distribute it as a tarball to my
operationspeople. I'd previously asked them if they wanted it as a system package, and their response was 'why
bother?'.     I throw in a script that adds it as a service (via /etc/init.d & rc3.d, I'm too lazy to figure out the
newAIX 6.1 service manager). 

Thanks, would you mind to share your script and steps that add pg as a service (I am using IBM ppc64 Linux)?


regards