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.klaver@aklaver.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!