I have followed these instructions from the Postgres documentation:
# Create the file repository configuration: sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' # Import the repository signing key: wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - # Update the package lists: sudo apt-get update # Install the latest version of PostgreSQL. # If you want a specific version, use 'postgresql-12' or similar instead of 'postgresql': sudo apt-get -y install postgresql
After that, I was having trouble authenticating to Postgresql after installing the db server on Linux Lite. This stackoverflow answer was very helpful.
Open the file
pg_hba.conf
. For Ubuntu, use for example/etc/postgresql/13/main$ sudo nano pg_hba.conf
and change this line at the bottom of the file, it should be the first line of the settings:local all postgres peer
to
local all postgres trust
Side note: If you want to be able to connect with other users as well, you also need to change:
local all all peer
to
local all all md5
If you used nano editor, exit with double
Escape
,x
,y
,Enter
to save the config file.Restart the server
$ sudo service postgresql restart
Output:
* Restarting PostgreSQL 13 database server
Login into psql and set your password
$ psql -U postgres db> ALTER USER postgres with password 'your-pass';
Output:
ALTER ROLE
Side note: If you have other users, they will need a password as well:
db> ALTER USER my_user with password 'your-pass';
Then enter:
exit
Finally change the
pg_hba.conf
fromlocal all postgres trust
to
local all postgres md5
Restart the server again
$ sudo service postgresql restart
Output:
* Restarting PostgreSQL 13 database server
Login at psql with postgres user
After restarting the postgresql server, the postgres user accepts the password that you chose:
psql -U postgres
Output:
Password for user postgres:
psql (13.4 (Ubuntu 13.4-1.pgdg20.04+1))
Type "help" for help.
And you are in psql:
postgres=#
Side note: Same now works for
my_user
if you added the user and password:psql -d YOUR_DB_NAME -U my_user
Which will ask you for the new password of
my_user
.
Authentication methods details:
trust - anyone who can connect to the server is authorized to access the database
peer - use client's operating system user name as database user name to access it.
md5 - password-base authentication
Comments
Post a Comment