Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1

    Question Unanswered: Role Authentication Failure

    At some point I broke authentication for my role 'carlos' to be able to login. I just stood up a PostgreSQL 9.1.9 server. I know I am inserting the correct password for my 'carlos' role. I've also changed the password several times to be sure.

    When I view the logs, it's says:

    Code:
    2013-04-14 00:41:52 EDT FATAL:  password authentication failed for user "carlos"
    In psql, my role permissions look as follows:

    Code:
    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 | {}
    My pg_hba.conf is also fairly simple:

    Code:
    # Database administrative login by Unix domain socket
    local   all             postgres                                peer
    
    # TYPE  DATABASE        USER            ADDRESS                 METHOD
    
    # "local" is for Unix domain socket connections only
    local   all             all                                     md5
    # IPv4 local connections:
    host    all             all             127.0.0.1/32            md5
    host    all             all             192.168.1.0/24          md5
    Can anyone help me figure out why 'carlos' can't login into my database? I thought about dropping the role and recreating but I want to understand how to troubleshoot this and see what is blocking him.

    Thanks for any info / help!

  2. #2
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    If it helps...
    Attached Thumbnails Attached Thumbnails Screen Shot 2013-04-14 at 1.17.37 AM.png  

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Based on the first message that you posted, I'd guess that the password was incorrect. Have you intentionally changed it recently? Have you retried it to see if the problem was a typo?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    The password has been changed to 'password' so I know I'm entering the correct password. It's an authentication issue...

  5. #5
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    I dropped the user and recreated him. Once I re-created the role 'carlos', he was able to login fine. It's when I:

    Code:
    GRANT dba TO carlos;
    All of a sudden, 'carlos' is no longer able to login. I don't know why. The 'dba' role is just a group role with NOLOGIN and INHERIT so as long as 'carlos' can LOGIN, I don't see why this is happening. Is it a bug or am I doing something wrong?

    Dropping the 'dba' role from the user 'carlos' doesn't remedy the problem either. I don't understand why the GRANT breaks authentication but dropping the GRANT still doesn't fix it either. Only thing that works is dropping the role and creating him again.
    Last edited by CacheDrive; 04-15-13 at 05:30.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •