Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2013
    Posts
    23

    Unanswered: Not able to login as / or drop user and related dbs

    Hello -

    Am new to PostgreSQL, from Oracle background.
    ...
    Running / Installed PostgreSQL 9.1 under Ubuntu 13.04.
    DBMS starts / stops ok.

    I had created a user like so:
    sudo -u postgres createuser testuser
    Shall the new role be a superuser? (y/n) n
    Shall the new role be allowed to create databases? (y/n) y
    Shall the new role be allowed to create more new roles? (y/n) n

    Worked fine.
    ...
    Then I created 2 databases under testuser and db created as well.
    ...
    Then I log in as superuser postgres:
    sudo -u postgres psql
    ...
    Logs in fine and at the psql prompt I try to update the password of testuser:
    alter user testuser with password 'postgresql';

    I get: role "testuser" does not exist
    ...
    I queried with a: select * from pg_roles; as well from pg_user;
    I see testuser in both queries.
    ...
    I also tried from cmd line:
    sudo -u postgres psql -c "ALTER USER testuser PASSWORD 'postgres';"
    same message: testuser does not exist.
    ...
    Essentially I am trying to drop the 2 databases I created.
    My command: psql -U testuser -W - does not work as it fails authentication.
    Even when I try: psql -U testuser
    I think because I did not set a password initially.
    But then I cannot set a password with 'alter user'.
    ...
    Like I said I can see the user when I query pg_user and pg_role.
    But do not understand why I cannot alter the user.

    And when I log in from cmd as: sudo -u postgres psql
    The how do I change to user testuser so I can at least drop my databases with the dropdb command - as that command does not run from postgres superuser.
    ...
    Also coming from an Oracle background, user and role are different concepts.
    Here both seem to be the same.

    Is there a better way to create user than the one I did (from an example).

    If someone could pls help with my questions and help with my confusions will be much appreciated.

    Thank you.

  2. #2
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    First of all, get into the 'psql' client as the 'postgres' user as so:

    Code:
    [carlos@db1 ~]$ psql -d postgres -U postgres
    Password for user postgres:
    psql (9.2.4)
    Type "help" for help.
    
    postgres=#
    postgres=# SELECT current_user;
     current_user
    --------------
     postgres
    (1 row)
    Now look at all your user / role accounts on the database server:

    Code:
    postgres=# \du
                                 List of roles
     Role name |                   Attributes                   | Member of
    -----------+------------------------------------------------+-----------
     ben       |                                                | {}
     carl      |                                                | {}
     carlos    | Superuser, Create role, Create DB, Replication | {}
     chris     |                                                | {}
     dave      |                                                | {}
     nick      |                                                | {}
     postgres  | Superuser, Create role, Create DB, Replication | {}
    I am creating the 'testaccount' in psql client as I suggest you try as well and post back your results:

    Code:
    postgres=# CREATE ROLE testuser LOGIN;
    CREATE ROLE
    postgres=# \password testuser
    Enter new password:
    Enter it again:
    postgres=# COMMENT ON ROLE testuser IS 'Test Account';
    COMMENT
    postgres=# \du
                                 List of roles
     Role name |                   Attributes                   | Member of
    -----------+------------------------------------------------+-----------
     ben       |                                                | {}
     carl      |                                                | {}
     carlos    | Superuser, Create role, Create DB, Replication | {}
     chris     |                                                | {}
     dave      |                                                | {}
     nick      |                                                | {}
     postgres  | Superuser, Create role, Create DB, Replication | {}
     testuser  |                                                | {}
    Quote Originally Posted by mdr View Post
    Hello -

    My command: psql -U testuser -W - does not work as it fails authentication.
    Even when I try: psql -U testuser
    I think because I did not set a password initially.

    Thank you.

    Avoid setting role / user passwords in plain text as you did. Use the \password psql command as shown above. It keeps anyone from seeing it and doesn't dump it in .psql_history via plain text for the world to steal. Try what I recommend / show you above and post back here with your \l and \du outputs.

    Last edited by CacheDrive; 06-17-13 at 15:42.

  3. #3
    Join Date
    Jun 2013
    Posts
    23
    Hi CarlosInFL -

    Thank you for your help.

    Listed below are the commands / output:
    ...
    postgres=# select current_user;
    current_user
    --------------
    postgres
    (1 row)

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

    postgres=# create ROLE testuser LOGIN;
    CREATE ROLE
    postgres=# \password testuser
    Enter new password:
    Enter it again:
    postgres=# COMMENT ON ROLE testuser IS 'Test Account';
    COMMENT
    postgres=# \du
    List of roles
    Role name | Attributes | Member of
    ------------+------------------------------------------------+-----------
    masterUser | Create DB | {}
    postgres | Superuser, Create role, Create DB, Replication | {}
    testuser | | {}
    ...

    Now Carlos after your test - I try to update the user I had previously created: masterUser and I get as follows:

    postgres=# \password masterUser
    Enter new password:
    Enter it again:
    ERROR: role "masteruser" does not exist
    postgres=# \password masteruser
    Enter new password:
    Enter it again:
    ERROR: role "masteruser" does not exist
    ...
    As you can see masterUser exists as per output you requested, but cannot update password or log in as masterUser to delete its databases, although I had created the databases under it.

    Hope that helps.

    Thank you for your help.

    Mono

  4. #4
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    I'm confused about your actual problem. In your original post you had issues with logging / setting credentials for 'testuser'. Before you jump around to 'masterUser', can you confirm that you're now able to login with 'testuser'?

    Code:
    psql -d <somedb> -U testuser -W
    Can you login as him? Does he have GRANTS or own any objects you can verify with?

    As for 'masterUser', I created an identical account with cAmeLcAsE spelling as you did and...

    Code:
    postgres=# CREATE ROLE masterUser
    postgres-# LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE;
    CREATE ROLE
    postgres=# \du
                                  List of roles
     Role name  |                   Attributes                   | Member of
    ------------+------------------------------------------------+-----------
     ben        |                                                | {}
     blake      |                                                | {}
     carl       |                                                | {}
     carlos     | Superuser, Create role, Create DB, Replication | {}
     chris      |                                                | {}
     john       | Superuser, Create role, Create DB, Replication | {}
     masteruser |                                                | {}
     nick       |                                                | {}
     postgres   | Superuser, Create role, Create DB, Replication | {}
    
    postgres=# \password masteruser
    Enter new password:
    Enter it again:
    Worked fine for me and I could login as this user 'masteruser' as well. Notice how I created the role as 'masterUser' but it displays as 'masteruser' when I perform a /du.
    Last edited by CacheDrive; 06-18-13 at 12:59.

  5. #5
    Join Date
    Jun 2013
    Posts
    23
    Hi Carlos -

    Sorry for the confusion. I had created a virtual machine and created testuser first and then deleted it and proceeded with masterUser.

    So I had created masterUser as follows from cmd line:

    sudo -u postgres createuser masterUser
    Shall the new role be a superuser? (y/n) n
    Shall the new role be allowed to create databases? (y/n) y
    Shall the new role be allowed to create more new roles? (y/n) n
    ...
    Then from the command line I created 2 databases as:
    sudo -u masterUser createdb masterCore
    sudo -u masterUser createdb masterHealth

    And they created fine.
    I got the commands from here:
    https://help.ubuntu.com/community/PostgreSQL
    ...
    Now I have the databases owned by masterUser. When I select from pg_database I get:
    datname | datdba | encoding | datcollate | datctype | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | dattablespace | datacl
    --------------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+--------------+---------------+-------------------------------------
    template1 | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | t | t | -1 | 11943 | 706 | 1663 | {=c/postgres,postgres=CTc/postgres}
    template0 | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | t | f | -1 | 11943 | 706 | 1663 | {=c/postgres,postgres=CTc/postgres}
    postgres | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 11943 | 706 | 1663 |
    masterCore | 16395 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 11943 | 706 | 1663 |
    masterHealth | 16395 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 11943 | 706 | 1663 |
    ...
    I am trying to drop the databases and masterUser.

    However I cannot drop the dbs from root user postgres - as dbs are owned by masterUser.
    And I can't log in as masterUser as it can't be found!
    ...
    The command you requested:

    monosij@blue-vm:~$ psql -d masterCore -U masterUser -W
    Password for user masterUser:
    psql: FATAL: Peer authentication failed for user "masterUser"
    ...
    when id \password as below:
    postgres=# \password masterUser
    Enter new password:
    Enter it again:
    ERROR: role "masteruser" does not exist
    postgres=#
    ...
    But on select from pg_roles:
    select * from pg_roles - gives me this:
    ...
    rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolconfig | oid
    ------------+----------+------------+---------------+-------------+--------------+-------------+----------------+--------------+-------------+---------------+-----------+-------
    postgres | t | t | t | t | t | t | t | -1 | ******** | | | 10
    masterUser | f | t | f | t | f | t | f | -1 | ******** | | | 16395
    testuser | f | t | f | f | f | t | f | -1 | ******** | | | 16398
    ...

    I am now trying to get rid of masterUser and the 2 dbs under masterUser: masterCore and masterHealth - w/o having to start from scratch on a new vm.

    And also so I understand what is going on.

    ...
    Thanks a lot for your help Carlos.

    Also, I have a good Oracle background. So I understand what I need to do. Just that the commands are not complying.

    Monosij

  6. #6
    Join Date
    Jun 2013
    Posts
    23
    Carlos - To update you, I had created the two databases with this command under masterUser:
    sudo -u postgres createdb masterCore -O masterUser
    sudo -u postgres createdb masterHealth -O masterUser
    ...
    And I had created masterUser as:
    sudo -u postgres createuser masterUser
    Shall the new role be a superuser? (y/n) n
    Shall the new role be allowed to create databases? (y/n) y
    Shall the new role be allowed to create more new roles? (y/n) n
    ...
    Now why does Postgres switch between USER and ROLE?
    What is the difference? Are they the same?
    In most databases USER and ROLE are different concepts.

    Thanks.

    Monosij

  7. #7
    Join Date
    Dec 2012
    Posts
    43
    Quote Originally Posted by mdr View Post
    Carlos - To update you, I had created the two databases with this command under masterUser:
    sudo -u postgres createdb masterCore -O masterUser
    sudo -u postgres createdb masterHealth -O masterUser
    ...
    And I had created masterUser as:
    sudo -u postgres createuser masterUser
    Shall the new role be a superuser? (y/n) n
    Shall the new role be allowed to create databases? (y/n) y
    Shall the new role be allowed to create more new roles? (y/n) n
    ...
    Now why does Postgres switch between USER and ROLE?
    What is the difference? Are they the same?
    In most databases USER and ROLE are different concepts.

    Thanks.

    Monosij
    Hello friends,

    the difference between CREATE ROLE and CREATE USER is that CREATE USER implies the token LOGIN so the new role can login without explicit LOGIN token in CREATE USER.
    But this wasn't your problem.

    In PostgreSQL all identifiers will be converted into lowercase unless you quote an identifier ( ROLES, TABLES, COLUMNS etc.).
    Some examples:
    Code:
    CRETAE USER masterUser
    Creates a ROLE named "masteruser".


    Code:
    CREATE USER "masterUser"
    Creates a ROLE named "masterUser".


    Code:
    CREATE USER "MASTERUSER"
    Creates a ROLE named "MASTERUSER".

    The external program "createuser" quotes the name of the user you typed in.
    This was the reason why the name of the new user in your PostgreSQL cluster was "masterUser".
    After creation with "createuser" you tried to set the password of user "masteruser" although you typed masterUser - but without quotes.
    "masteruser" is not identical to "masterUser".

    To avoid such mistakes it's a good idea to avoid capitalized letters in any identifier.

    Btw: In ORACLE the behavior is exactly inverted.

  8. #8
    Join Date
    Jun 2013
    Posts
    23
    Hi bibjet -

    Thanks for your suggestions. I understand what you mean and accordingly I tried the following. Please note, I created a new role called 'pimuser' from within postgres (as user postgres) and it works fine.

    As you can see masterUser shows up with pg_roles
    ...
    postgres=> \du
    List of roles
    Role name | Attributes | Member of
    ------------+------------------------------------------------+-----------
    masterUser | Create DB | {}
    pimuser | Create DB | {}
    postgres | Superuser, Create role, Create DB, Replication | {}

    postgres=> set session authorization postgres;
    SET
    postgres=# set session authorization pimuser;
    SET
    postgres=> set session authorization masteruser;
    ERROR: role "masteruser" does not exist
    postgres=> set session authorization 'masteruser';
    ERROR: role "masteruser" does not exist
    postgres=> set session authorization "masteruser";
    ERROR: role "masteruser" does not exist
    postgres=> set session authorization '"masteruser"';
    ERROR: role ""masteruser"" does not exist
    postgres=> set session authorization ''masteruser'';
    ERROR: syntax error at or near "masteruser"
    LINE 1: set session authorization ''masteruser'';
    ...
    Also, what I am really trying to do is drop 2 databases I created from command line under user masteruser - masterCore and masterHealth.

    I can connect to them as user postgres but cannot drop them as they are owned by masterUser:
    ...
    masterCore=# drop database masterHealth;
    ERROR: database "masterhealth" does not exist
    masterCore=# \c masterHealth
    You are now connected to database "masterHealth" as user "postgres".
    masterHealth=# \c masterCore
    You are now connected to database "masterCore" as user "postgres".
    masterCore=# \c masterHealth
    You are now connected to database "masterHealth" as user "postgres".
    masterHealth=# drop database masterCore;
    ERROR: database "mastercore" does not exist
    ...
    I will not create roles or databases from command line again.

    If you think my creating roles / databases from command line has corrupted the usage I can remove PG and re-install in the vm.

    However I would like to see if I can resolve this problem the 'dba' way if posssible.

    I had initially created testuser from command line and had run into similar problem. I guess there may be some incompatibility issues between the command line and internals of PG - maybe on Linux?
    ...
    Thanks for your, and earlier Carlos' help if possible.

  9. #9
    Join Date
    Dec 2012
    Posts
    43
    Hello mdr,

    I'm sure you didn't understand my reply. So let's try it again:

    To drop the database "masterHealth" (note the capitalized "H") you have to type as an SQL statement

    drop database "masterHealth";

    The command drop database masterHealth; will fail because identifiers without quotations will automatically converted into lower case (in this example to masterhealth)

    Please note that external commands like createdb or createuser are not SQL statements.
    Example:
    createdb myTestDatabase
    will send the SQL statement CREATE DATABASE "myTestDatabase" .... to PostgreSQL.

    The internal command \c in psql client program isn't a SQL statement too.
    So the command \c myTestDatabase connects to the database "myTestDatabase".

    Resumee:
    Please quote the identifier in SQL if there is one ore more capitalized letter.

    Please note that it is required to quote an identifier in some cases also when there are no capitalized letters: To create a user called John-Doe you have to type it as SQL statement CREATE USER "John-Doe" because the statemant CREATE USER John-Doe will fail ('-' will identified as a minus sign and not as a part of the identifier.

  10. #10
    Join Date
    Jun 2013
    Posts
    23
    Thank Bibjet. Your instructions were great and finally got through to me.

    Worked out fine. Dropped masterHealth, masterCore and masterUser.

    However, if you have time - one last question.

    If you see my notes above - why does the - set session authorization - not work even I user "masterUser";

    such as the 2nd one here:
    postgres=> set session authorization 'masteruser';
    ERROR: role "masteruser" does not exist
    postgres=> set session authorization "masteruser";
    ERROR: role "masteruser" does not exist
    postgres=> set session authorization '"masteruser"';
    ERROR: role ""masteruser"" does not exist
    postgres=> set session authorization ''masteruser'';
    ERROR: syntax error at or near "masteruser"
    ...
    Even when \du returns:
    postgres=> \du
    List of roles
    Role name | Attributes | Member of
    ------------+------------------------------------------------+-----------
    masterUser | Create DB | {}
    pimuser | Create DB | {}
    postgres | Superuser, Create role, Create DB, Replication | {}
    ...
    Btw it seems that PG has users and roles confused and uses them interchangable.

    I assume \du stands for 'display users'.
    However, it shows roles. \dr does not error but does not return anything.
    ...
    Am I right in making this assertion about 'confusion' in PG.
    I am not saying this to criticize PG - just asking so I know I am thinking about it correctly.
    PG is a great DBMS but maybe some older functionality was left over?

    Thank you again.

  11. #11
    Join Date
    Dec 2012
    Posts
    43
    Ok. Let's talk something about quotes, ROLE and USER:

    String literals must be quoted with inverted comma: '
    Example: INSERT INTO foo_table (dummy) values ('this is a literal');
    Example: INSERT INTO foo_table (dummy) values ('this is a literal with an inverted comma in it: '' (type inverted comma twice)');

    Identifiers (databases, users, tables, views, columns etc.) must be quoted by " if there is at least one capitalized letter or a special character (like "-") or it's a reserved word.
    Example: SET SESSION AUTHORZATION "masterUser";

    Your commands failed because:

    postgres=> set session authorization 'masteruser';
    ERROR: role "masteruser" does not exist
    REASON: The existing rolename is "masterUser" -> read the error line

    postgres=> set session authorization "masteruser";
    ERROR: role "masteruser" does not exist
    REASON: Same as above: type "U" instead of "u". --> read the error line.

    postgres=> set session authorization '"masteruser"';
    ERROR: role ""masteruser"" does not exist
    REASON: The rolename you typed begins and ends with quotes?

    postgres=> set session authorization ''masteruser'';
    ERROR: syntax error at or near "masteruser"
    REASON: You typed >'< twice. In this case PostgreSQL assumes that you mean >'< inside a literal string. PostgreSQL didn't understand that. And this is not what you want.

    The working command is:
    set session authorization "masterUser";
    Note the "U" instead of "u".

    Hint: To avoid confusion always use completely lowercase identifiers.

    About ROLE and USER:
    In PostgreSQL a ROLE can be granted to other roles and to objects.
    For historical reason you can say "CREATE USER ..." instead of "CREATE ROLE ... LOGIN".
    A ROLE with the "LOGIN" attribute can connect to the PostgreSQL Server. this is normally a user.
    ROLE and USER are not exactly identical. You can say that "CREATE USER ..." is a synonym for "CREATE ROLE ... LOGIN ..."

    Some examples:
    Create a user named john:
    CREATE ROLE john LOGIN PASSWORD 'mySecretPassword';
    Note: This is the same as CREATE USER john PASSWORD 'mySecretPassword';

    Lets create an other user: jane
    CREATE ROLE jane LOGIN PASSWORD 'anotherPassword';

    Now we create the role "sales". This is not a user. Only a role.
    CREATE ROLE sales;
    Note: No "LOGIN" here, because this is not a user.

    At least we grant the role "sales" to your user "john":
    GRANT sales TO john;
    Note: john is now member of the role (aka GROUP) "sales".

    The psql command "\du" shows all roles. Not only users. I think it's a very very old psql command.

    On my PostgreSQL server the command \du
    shows:
    Code:
    postgres=# \du
                                    Liste der Rollen
     Rollenname |                      Attribute                      | Mitglied von 
    ------------+-----------------------------------------------------+--------------
     jane       |                                                     | {}
     john       |                                                     | {sales}
     postgres   | Superuser, Rolle erzeugen, DB erzeugen, Replikation | {}
     sales      | kann nicht einloggen                                | {}
     useradmin  | Superuser, Replikation                              | {}
    Sorry, my PostgreSQL server answers in german.
    "kann nicht einloggen" means "unable to login" because it's NOT a user only a role.
    You can see that john is member of ("Mitglied von") the role "sales".

    If you use psql you can use the psql command \h to show the syntax of all or one command.
    Example: \h create role

    It's a good idea to read the PostgreSQL documentation:
    PostgreSQL: Documentation: 9.2: PostgreSQL 9.2.4 Documentation

  12. #12
    Join Date
    Jun 2013
    Posts
    23
    Thanks a lot for your clarifications Bibjet. Much appreciate the details.

    I agree I should use lowercase. I was testing to see what happens and I was of the understanding it all reverted to lowercase internally. So I thought it did not matter if U was uppercase.

    And appreciate the note of the ROLE vs USER.

    I will look through the docs more carefully.

    Mono

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
  •