Re: type "xxxxxxx" does not exist

Wow, so many helpful replies already! Thanks everyone! I'm going to do
my best at answering questions … Starting from the first email reply.
:)

On Fri, May 19, 2017 at 1:20 PM, Adrian Klaver
<adrian(dot)klaver(at)aklaver(dot)com> wrote:
>> sudo -i -u username
> You should not need to do above.
>> psql -U otherusername -d database
> Just do the above.

Perfect, thanks for clarification.

> Are either username or otherusername a superuser?
> In psql \du will show you.

Great question.

username is a superuser and otherusername is not.

In fact, otherusername has no "local attributes" listed.

>> … I get:
>> psql: FATAL: Peer authentication failed for user "otherusername"
> This is coming from:
> https://www.postgresql.org/docs/9.6/static/auth-methods.html#AUTH-PEER
> which is set in pg_hba.conf.

Ahh, thank you for tip!

> 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

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

>> Do I need to create a Linux user to login as "otherusername" so I can
>> test calling the functionName() with xxx_xxx_xxxxx type?
> No that is not necessary. Postgres usernames do not have to be the same as
> the OS usernames. Peer authentication is just a method to map OS usernames
> to Postgres usernames if you want to.

Great, thank you for the clarification!

Thank you for the help Adrian, I really appreciate it!