Re: [GENERAL] type "xxxxxxx" does not exist

On 05/19/2017 01:57 PM, Micky Hulse wrote:
> Wow, so many helpful replies already! Thanks everyone! I'm going to do
> my best at answering questions … Starting from the first email reply.
> :)
>

>> What version of Postgres, OS and how was it installed?
>
> PostgreSQL 9.3.9 on i686-redhat-linux-gnu, compiled by gcc (GCC) 4.9.2
> 20150212 (Red Hat 4.9.2-6), 32-bit

FYI, 9.3 is now up to 9.3.17:

https://www.postgresql.org/docs/9.3/static/release.html

At some point, once you have gotten a handle on using Postgres, you
should probably update. Read the Release Notes for each of the minor
releases to see what has been fixed.

>
> Installed via yum:
>
> $ sudo yum install postgresql-server postgresql-contrib
>
>> I am asking because that will help find where pg_hba.conf is. If you have
>> found it, can you share it here?
>
> Totally! pg_hba.conf lives here:
>
> /var/lib/pgsql/data/pg_hba.conf
>
> The only modifications I made was to change ident to md5 for IPv4 and
> IPv6 local connections:
>
> # TYPE  DATABASE        USER            ADDRESS                 METHOD
> # "local" is for Unix domain socket connections only
> local   all             all                                     peer
> # IPv4 local connections:
> host    all             all             127.0.0.1/32            md5
> # IPv6 local connections:
> host    all             all             ::1/128                 md5
> # Allow replication connections from localhost, by a user with the
> # replication privilege.
> #local   replication     postgres                                peer
> #host    replication     postgres        127.0.0.1/32            ident
> #host    replication     postgres        ::1/128                 ident

So when you did this:

"
psql -U otherusername -d database

… I get:

psql: FATAL:  Peer authentication failed for user "otherusername"

"

you where connecting using local, which is the socket connection.

If you had done:

psql -U otherusername -d database -h localhost

it would have asked for a password(md5 auth method). If otherusername
does not have the LOGIN attribute you would not been able to log in
anyway. For more detailed information see:

https://www.postgresql.org/docs/9.3/static/sql-createrole.html

>

>
> Great, thank you for the clarification!
>
> Thank you for the help Adrian, I really appreciate it!
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com