Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2008
    Posts
    22

    Unanswered: user login: problems in linux

    Dear members

    i work in linux fedora core 5
    and i am rookie with postgresql 8.2.4

    i cant understand this behaviour

    i only can loggin in postgres with superuser and not like a simple user

    see that "root" is a user for postgresql
    Code:
    usename  | usesysid | usecreatedb | usesuper | usecatupd |  passwd  | valuntil | useconfig
    ----------+----------+-------------+----------+-----------+----------+----------+-----------
     postgres |       10 | t           | t        | t         | ******** |          |
     root     |    16395 | t           | t        | t         | ******** |          |
    (2 filas)
    Code:
    [SomeUser@localhost ~]$ su
    Contraseña:
    [root@localhost SomeUser]# psql -d somedb -U root
    Bienvenido a psql 8.2.4, la terminal interactiva de PostgreSQL.
    
    Digite:  \copyright para ver los términos de distribución
           \h para ayuda de comandos SQL
           \? para ayuda de comandos psql
           \g o or termine con punto y coma para ejecutar una consulta
           \q para salir
    
    somedb=#
    see that i need logged like root in linux (#)
    to enter

    but
    it fall if i am not root in linux, thats mean $

    Code:
    [SomeUser@localhost ~]$ psql -d somedb -U root
    Contraseña para usuario root:
    psql: FATAL:  la autentificación Ident falló para el usuario «root»
    [SomeUser@localhost ~]$ psql -d somedb -U root
    psql: FATAL:  la autentificación Ident falló para el usuario «root»
    [SomeUser@localhost ~]$
    how i can resolver this??
    i cant get a successful connection with my java application and i cant
    use my aqua data studio to make the connection and test some queries

    and worst

    according with the manual
    Code:
      -W               preguntar contraseña (debería suceder automáticamente)
    ask the password automatically

    but this never happens, what is wrong?

    here my pg_hba.conf
    located in
    Code:
    [root@localhost pgsql]# pwd
    /usr/share/pgsql
    
    
    host 	somedb   root	127.0.0.1/32 	      255.255.255.255 trust
    thanks in advanced

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    You will need to add a new user (role with login privilege) to your pg_hba.conf file, per pg documentation. For instance, supposing that you only wish to allow the user postgres to log in from IP address 192.168.1.1, and able to access all the databases on the server, using md5 hashing...
    Code:
    # TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
    host    postgres    all         192.168.12.10/32      md5
    ref http://www.postgresql.org/docs/8.2/s...ntication.html
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Jan 2008
    Posts
    22
    Hi loquin
    thanks for the reply

    i did, but it still doesnt work, even with this

    Code:
        # TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
        local    all          all                        trust
        host    postgres    all         127.0.0.1/32      trust
    and then

    Code:
        [root@localhost ~]# /sbin/service postgresql restart
        Parando el servicio  postgresql:                           [  OK  ]
        Iniciando servicios postgresql:                            [  OK  ]

    Code:
        [SomeUser@localhost pgsql]$ psql -d somedb -U root
        psql: FATAL:  la autentificación Ident falló para el usuario «root»
        [SomeUser@localhost pgsql]$

    i cant understand what is wrong

    i can logged if in my terminal i am superuser, so if i am a simple user like SomeUser
    i cant get a successful connection

    coz, is very different the users of linux that postgres
    i am confuse


    thanks in advanced

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    is very different the users of linux that postgres
    True. Postgres maintains its own list of users and roles.

    It appears that you are trying to log in to Postgres from the server itself?

    If so, try:

    Code:
        # TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
          local   all       postgres    127.0.0.1/32          trust
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  5. #5
    Join Date
    May 2005
    Location
    San Antonio, Texas
    Posts
    134
    it seems you are using IDENT.

    if you are going to use the network address for the same machine you should use -h localhost.

    so.. psql -d somedb -h localhost -U root

    I am not sure if there are any issues with using root as the db user. I wouldn't imagine any. You could also create a dbuser account that you can use with IDENT.
    Vi veri veniversum vivus vici
    By the power of truth, I, a living man, have conquered the universe

  6. #6
    Join Date
    Jan 2008
    Posts
    22
    True. Postgres maintains its own list of users and roles.
    ok, we are right

    It appears that you are trying to log in to Postgres from the server itself?
    if this means in the same computer, thats right

    like the classic localhost or 127.0.0.1

    so
    Code:
     # TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
          local   all       postgres    127.0.0.1/32          trust
    Code:
    [SomeUser@localhost ~]$ psql -d somedb -U postgres
    psql: FATAL:  la autentificación Ident falló para el usuario «postgres»
    [SomeUser@localhost ~]$


    and more funny
    see this
    Code:
    [SomeUser@localhost ~]$ psql -d somedb -U postgres
    psql: FATAL:  la autentificación Ident falló para el usuario «postgres»
    [SomeUser@localhost ~]$
    coz, i am like simple user $, of course this works if i am like root in linux #

    now check this

    Code:
    [root@localhost SomeUser]# su
    [root@localhost ~]#  psql -d somedb -U postgres
    psql: FATAL:  la autentificación Ident falló para el usuario «postgres»
    [root@localhost ~]#  psql -d somedb -U root
    Bienvenido a psql 8.2.4, la terminal interactiva de PostgreSQL.
    
    Digite:  \copyright para ver los términos de distribución
           \h para ayuda de comandos SQL
           \? para ayuda de comandos psql
           \g o or termine con punto y coma para ejecutar una consulta
           \q para salir
    
    somedb=#
    i must enter like su

    i am really confuse

    pls see this photo in the link
    http://www.aquafold.com/screenshots/...esql-large.jpg

    apart of fail via terminal, i cant use my db tools

    thanks in advanced
    Last edited by dr_pompeii; 01-08-08 at 13:41.

  7. #7
    Join Date
    Jan 2008
    Posts
    22
    Hi amthomas

    thanks for the reply

    Code:
    so.. psql -d somedb -h localhost -U root
    Code:
    [SomeUser@localhost ~]$ psql -d somedb -h localhost -U root
    psql: FATAL:  la autentificación Ident falló para el usuario «root»
    [omeUser@localhost ~]$ psql -d somedb -h 127.0.0.1 -U root
    psql: FATAL:  la autentificación Ident falló para el usuario «root»
    [SomeUser@localhost ~]$
    it seems you are using IDENT.
    why you say that?

    I am not sure if there are any issues with using root as the db user.
    work like root in linux is a suicide, is no t a good practice, instead i use my user for exmaple
    Someuser

    now the user root for user list for postgresql is a other superuser like
    postgres, i read in the documentation that is not a good practice work like
    postgres

    I wouldn't imagine any. You could also create a dbuser account that you can use with IDENT.
    i am confuse about ident, i will the read doc documentation

    thanks so much for your time

  8. #8
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Root user issues are security issues - If a hacker manages to hack through pg into the system, they would have root access... ... not a good thing at all...

    This is why you don't want db users to be given any admin privileges on the server.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  9. #9
    Join Date
    Jan 2008
    Posts
    22
    Hi loquin

    Root user issues are security issues - If a hacker manages to hack through pg into the system, they would have root access... ... not a good thing at all...

    This is why you don't want db users to be given any admin privileges on the server.
    my english is not perfect,
    but if you suggestion try to say
    avoid to create "db users" in postgresql with superuser attribute, thanks for the tip

    Code:
    Bienvenido a psql 8.2.4, la terminal interactiva de PostgreSQL.
    
    Digite:  \copyright para ver los términos de distribución
           \h para ayuda de comandos SQL
           \? para ayuda de comandos psql
           \g o or termine con punto y coma para ejecutar una consulta
           \q para salir
    
    template1=# drop user root;
    DROP ROLE
    template1=# \du
                                    Lista de roles
     Nombre de rol | Superusuario | Crear rol | Crear BD | Conexiones | Miembro de
    ---------------+--------------+-----------+----------+------------+------------
     postgres      | sí           | sí        | sí       | sin límite |
    (1 fila)
    
    template1=# CREATE USER root;
    CREATE ROLE
    template1=# \du
                                    Lista de roles
     Nombre de rol | Superusuario | Crear rol | Crear BD | Conexiones | Miembro de
    ---------------+--------------+-----------+----------+------------+------------
     postgres      | sí           | sí        | sí       | sin límite |
     root          | no           | no        | no       | sin límite |
    (2 filas)
    
    template1=# ALTER USER root CREATEDB;
    ALTER ROLE
    template1=# \du
                                    Lista de roles
     Nombre de rol | Superusuario | Crear rol | Crear BD | Conexiones | Miembro de
    ---------------+--------------+-----------+----------+------------+------------
     postgres      | sí           | sí        | sí       | sin límite |
     root          | no           | no        |        | sin límite |
    (2 filas)
    
    template1=#
    
    template1=# ALTER USER root PASSWORD 'doom64';
    ALTER ROLE
    template1=#
    the issue problems still happen

    now
    after to read the documentation about IDENT i see this



    Code:
    [root@localhost pgsql]# pwd
    /usr/share/pgsql
    [root@localhost pgsql]#  gedit pg_ident.conf
    and i wrote this line


    Code:
    # Put your actual configuration here
    # ----------------------------------
    #
    # No map names are defined in the default configuration.  If all ident
    # user names and PostgreSQL user names are the same, you don't need
    # this file.  Instead, use the special map name "sameuser" in
    # pg_hba.conf.
    
    # MAPNAME     IDENT-USERNAME    PG-USERNAME
    root	               SomeUser		root
    Code:
    [root@localhost ~]# pg_ctl reload
    pg_ctl: no puede ser ejecutado como root
    Por favor conéctese (por ej. usando «su») con un usuario no privilegiado,
    quien ejecutará el proceso servidor.
    
    [SomeUser@localhost pgsql]$ pg_ctl reload
    pg_ctl: no se especificó directorio de datos y la variable PGDATA no está definida
    Use «pg_ctl --help» para obtener más información.



    regards
    Last edited by dr_pompeii; 01-08-08 at 16:04.

  10. #10
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    The operating system user name is entirely separate from the postgresql user logon role. There is NO correlation between them at all.

    Create a new user, named "test" (do NOT make a new linux user name)

    Add a line to your pg_hba.conf file
    Code:
        # TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
          local   all       test        127.0.0.1/32          trust
    stop and restart the server

    Then, connect, using logon name "test" (no password required, as you specify "trust."

    If successful, modify pg_hba.conf, replacing "trust" with "md5"
    Now, you need to supply a password to connect...
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

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