Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2011
    Posts
    9

    Unanswered: How do I login as Superuser in Postgresql 9.1

    Hi,

    I have created a new superuser, say Super, and now I want to use Super to create a new database?

    I have tried to login as Super with:
    su Super
    but this does not work, then I thought I would login as postgres and change over to Super as follows:
    su postgres
    psql -U Super ...but again this fails

    Anyone have some insight that they would like to share on this issue?

    Thanks for any pointers.

    Tom.

  2. #2
    Join Date
    Oct 2011
    Posts
    9
    Hi again,

    I think I have it,

    I logged in as postgres and then from that prompt CREATEDB alloc -O Super and then I went into psql and used \l to list the databases and there was alloc and Super.

    The only problem is that I cannot find the switch to define the access privileges and description of the database?

    Any help would be appreciated.

    Thanks,

    Tom.

  3. #3
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    Create your user which it appears you already have but confirm using the \du or \dg commands:

    Code:
    Role name |                   Attributes                   | Member of 
    -----------+------------------------------------------------+-----------
     postgres  | Superuser, Create role, Create DB, Replication | {}
     test      | Superuser, Create role, Create DB, Replication | {}
    Make sure you set a secure password for your new user:

    Code:
    postgres=# ALTER ROLE test ENCRYPTED PASSWORD 'test123';
    ALTER ROLE
    **WARNING**
    Entering a password in plain text as shown above is not recommended because it's stored in .psql_history file. There's a secure psql command that allows you to do this but I don't remember it.

    Now just logout with \q from pgsql client and attempt to login as your new user...

    Code:
    cmennens@tuna:~$ psql -d postgres -U test -W
    Password for user test: 
    psql (9.1.1)
    SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
    Type "help" for help.
    
    postgres=#
    If this doesn't work for you. The issue lies within your hba_conf file and make sure local connections are not set to 'peer' but rather 'md5'.

    **EDIT**

    To securely change a users password, I found this in the 'psql' man page:

    \password [USERNAME] securely change the password for a user
    It's up to you how you prefer to alter a users password on your server...
    Last edited by CacheDrive; 11-09-11 at 12:54.

  4. #4
    Join Date
    Oct 2011
    Posts
    9
    Hi CarlosinFL,

    Thanks for your reply to my question and your detailed response which I find informative and helpful.

    Thanks again,

    Tom.

  5. #5
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    No problem at all. It's extremely rare where I can answer questions here since I'm still fairly new to PostgreSQL or RDBMS so I'm more than happy to help out.

    -Carlos

Tags for this Thread

Posting Permissions

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