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.
TLDR for my original post:
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';
Find your pg_hba.conf
:
sudo updatedb
sudo locate pg_hba.conf
Replace "trust" with "md5" in your pg_hba.conf
:
Restart PostgreSQL:
sudo systemctl restart postgresql
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With