Re: [pgsql-novice] Daily digest v1.3706 (6 messages)

2015-11-17 13:49 GMT-02:00 Michael Convey <smconvey(at)gmail(dot)com>:

> On Mon, Nov 16, 2015 at 6:26 PM, Alan Hodgson <ahodgson(at)simkin(dot)ca> wrote:
>
>> On Monday, November 16, 2015 05:36:08 PM Michael Convey wrote:
>> > ​Thank you. Assuming a default Fedora installation, if I am logged in as
>> > Linux user lab and I don't know the Linux "postgres"​ password, but I do
>> > know the PostgreSQL "postgres" role password; what is the syntax (from
>> the
>> > Linux command line) to login as the "postgres" role and connect to the
>> > "postgres" database?
>>
>> psql -U postgres postgres
>>
>> It'll prompt for the password.
>>
>> Make sure you've enabled md5 or password as an authentication method
>> allowed
>> for local in pg_hba.conf.
>
>
> ​Thank you. I received the following error:
>
> bash-4.2 ~ $ psql -U postgres postgres
> psql: FATAL: Peer authentication failed for user "postgres"
>
> Here's the relevant portion of my ​pg_hba.conf file:
>
>
> -----------------------------------------------------------------------------------------------------------
> # 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 ident
> # IPv6 local connections:
> host all all ::1/128 ident
> # 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 modify the first section, as follows?
>
> # TYPE DATABASE USER ADDRESS METHOD
>
> # "local" is for Unix domain socket connections only
> local all all peer
> local all all samehost md5 #added
>
> Or, do I replace the first line with #added?
>
>
>

Hi Michael,

1) Modify your PG_HBA file with this config:

# "local" is for Unix domain socket connections only
local all all password
# IPv4 local connections:
host all all 127.0.0.1/32 password
# IPv6 local connections:
host all all ::1/128 password

2) Reload your postgres service from the Linux bash forcing it to read the
new config:

service postgres reload

or

/etc/init.d/postgresql reload

3) Try connecting to the database:

psql -U postgres -W postgres

Cheers,