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

    Question Unanswered: Created a Superuser Who Can't Login

    Today on my new server I created a SUPERUSER as explained here:

    Code:
    postgres@db1:~$ psql -d postgres
    psql (8.4.5)
    Type "help" for help.
    
    postgres=# ALTER ROLE carlos WITH SUPERUSER;
    ALTER ROLE
    postgres=# \q
    So I didn't get any errors & assume that I created this super user account correctly. Now I logout and log back in as 'carlos' and try to access the database and get the following error...

    Code:
    postgres@db1:~$ logout
    root@db1:/home# su - carlos 
    carlos@db1:~$ psql -l
    psql: FATAL:  role "carlos" is not permitted to log in
    Anyone know what I'm doing wrong?

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    How did you create that role?
    What was the exact CREATE ROLE command you used?

    My guess is, that you simply forgot to grant the login privilege to the role
    Code:
    ALTER ROLE carlos WITH login
    should fix this

  3. #3
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    Quote Originally Posted by shammat View Post
    How did you create that role?
    What was the exact CREATE ROLE command you used?

    My guess is, that you simply forgot to grant the login privilege to the role
    Code:
    ALTER ROLE carlos WITH login
    should fix this
    I just pulled this out of the history file:

    Code:
    CREATE ROLE carlos WITH superuser;
    So you mean to tell me that if I configure a new user / role with the 'SUPERUSER' option, I still have to also tell him I need 'login' privileges as well. Seems a little redundant, no? Am I missing something here because it's not making sense to me...

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    From PostgreSQL: Documentation: Manuals: PostgreSQL 9: CREATE ROLE

    Quote Originally Posted by Postgres Manual
    A role having the LOGIN attribute can be thought of as a user. Roles without this attribute are useful for managing database privileges, but are not users in the usual sense of the word.
    If not specified, NOLOGIN is the default, except when CREATE ROLE is invoked through its alternative spelling CREATE USER]

  5. #5
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    So unless I use 'create user', I have to specify 'LOGIN' even if I'm also specifying 'SUPERUSER'? That sounds very odd but that's what I gathered from your snip.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Well, a role is nothing that can login, a user is someone who can.
    I don't see anything "odd" with that.

    The term "superuser" simply grants all privileges it does not actually define a "user"

  7. #7
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    OK - for some reason I thought 'CREATE ROLE' was just the way PostgreSQL created a user account. Obviously today I learned they're two different commands.

    Thanks!

Posting Permissions

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