[dr_pompeii]

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

usename  | usesysid | usecreatedb | usesuper | usecatupd |  passwd  | valuntil | useconfig
----------+----------+-------------+----------+-----------+----------+----------+-----------
 postgres |       10 | t           | t        | t         | ******** |          |
 root     |    16395 | t           | t        | t         | ******** |          |
(2 filas)

[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 $

[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

  -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

[root@localhost pgsql]# pwd
/usr/share/pgsql


host 	somedb   root	127.0.0.1/32 	      255.255.255.255 trust

thanks in advanced

[loquin]

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...

# 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

[dr_pompeii]

Hi loquin
thanks for the reply

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

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

and then

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

    [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

[loquin]

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:

    # TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
      local   all       postgres    127.0.0.1/32          trust

[amthomas]

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.

[dr_pompeii]

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

 # TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
      local   all       postgres    127.0.0.1/32          trust

[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

[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

[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

[dr_pompeii]

Hi amthomas

thanks for the reply

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

[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

[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.

[dr_pompeii]

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

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        | sí       | 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

[root@localhost pgsql]# pwd
/usr/share/pgsql
[root@localhost pgsql]#  gedit pg_ident.conf

and i wrote this line

# 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

[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

[loquin]

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

    # 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...