postgres users can login with any or no password

Question

So I set up a user called 'paperwork' with a database of the same name

postgres=# create role paperwork;
postgres=# create database paperwork;
postgres=# grant all privileges on database paperwork to paperwork;
postgres=# ALTER ROLE paperwork WITH LOGIN;
postgres=# ALTER ROLE paperwork WITH PASSWORD 'paperwork';

But it still lets me log in as paperwork without a password

[###@EMOO modules]$ psql --username=paperwork --host=localhost
psql (9.6.3)
Type "help" for help.

paperwork=> \q

and when I force it to use a password, it accepts any password including blank password:

[###@EMOO modules]$ psql --username=paperwork --host=localhost --password
Password for user paperwork: 
psql (9.6.3)
Type "help" for help.

When I open up pgadmin3 and click on the "paperwork" user it seems to have an encrypted password.

-- Role: paperwork

-- DROP ROLE paperwork;

CREATE ROLE paperwork LOGIN
  ENCRYPTED PASSWORD 'md585ff97314dbeb9953b989fd363a8e96f'
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

Also, when I open up pgadmin3 it asks me for the postgres password, but again will accept anything for the postgres password. (and I remember setting the postgres password when I installed postgres) How do I make it so you need the right password to login? Or is there some context here that I am missing entirely? . . . like passwords are only needed for remote logins or some weirdness. Thanks.

EDIT: I didn't have a /usr/share/postgresql/pg_hba.conf (EDIT: actually I did I just couldn't find it because I wasn't using sudo on the "locate" command) I created one from the sample file: /usr/share/postgresql/pg_hba.conf.sample

Got this idea from here: http://blog.mattsch.com/2012/05/19/postgresql-accepts-any-or-no-password-when-connecting/ I tried making it have md5 authentication but I still have the same problem. What I tried is below from the file /usr/share/postgresql/pg_hba.conf

@authcomment@

# TYPE  DATABASE        USER            ADDRESS                 METHOD

@remove-line-for-nolocal@# "local" is for Unix domain socket connections only
@remove-line-for-nolocal@local   all             all                                     @authmethodlocal@
# 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.

@remove-line-for-nolocal@#local   replication     @default_username@ @authmethodlocal@
#host    replication     @default_username@        127.0.0.1/32 @authmethodhost@
#host    replication     @default_username@        ::1/128 @authmethodhost@

I then restarted postgresql but still have the same problem.

EDIT: Thanks Abelisto. that "show config_file" command (after logging in with pgsql) put me on the right track. It didn't occure to me that "locate pg_hba.conf" run from my linux user's command line didn't have permission to find the actual config file in the postgres directory: /var/lib/postgres/data/ The user "paperwork" now gets rejected with the wrong password after I changed "trust" to "md5" in /var/lib/postgres/data/pg_hba.conf on these lines to make it:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

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

Will probably mark solved in a bit just want to test a couple things.

Solution

TLDR for my original post:

1. Make sure you have set the postgres password to something you know:

   [###@EMOO ~]$ psql -U postgres 
   psql (9.6.3)
   Type "help" for help.
   
   postgres=# ALTER ROLE postgres WITH PASSWORD 'postgres password';
   

2. Find your pg_hba.conf:

   sudo updatedb
   sudo locate pg_hba.conf
   

3. Replace "trust" with "md5" in your pg_hba.conf:

4. Restart PostgreSQL:

   sudo systemctl restart postgresql
   

5. Log in as postgres and change whatever user passwords you need. Users will now be rejected if they don't provide the right password.

   psql -U postgres
   

Comments

[Abelisto]

In the psql execute show config_file; to find where your configs is.