Skip to main content

Installing Postgres on Linux Lite (Ubuntu)

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.

  1. 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.

  2. Restart the server

     $ sudo service postgresql restart
    

    Output: * Restarting PostgreSQL 13 database server

  3. 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
    
  4. Finally change the pg_hba.conf from

    local   all             postgres                                trust

    to

    local   all             postgres                                md5
  5. Restart the server again

     $ sudo service postgresql restart
    

    Output: * Restarting PostgreSQL 13 database server

  6. 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

Popular posts from this blog

How to generate a self-signed certificate with Openssl (using git windows bash)

I recently needed to generate a self-signed certificate to test a website locally. I wasn't familiar with the process, and I wanted to share here what I learned. First, we need a private key. A private key is a long series of characters that must be kept secret. In my context, it will be used to encrypt messages before the client and the server, in a way secure enough to prevent anybody to spy on them. Once the private key is created, we need to generate another file that will be the "signature" of our certificate. Among other data, this file will contains some information specific to the server's context: country, organization's name, email address of the organization's technical contact, etc. Once this signature is established, there are two paths: - Path A: If we want our server to be publicly accessible, every browser in the world must able to trust the certificate. In order for that to happen, we need to send our signature file to one of the official SSL ...

How to Compile a GTK4 program on Windows (using MSYS)

  I wanted to create a GUI program using C and GTK. I am using a windows operating system, so I had followed the instructions from the GTK Windows installation page . However, when trying to compile my program using: gcc main.c -o main.exe `pkg-config --cflags --libs gtk4` I had the following errors: gcc.exe: error: pkg-config: No such file or directory gcc.exe: error: gtk4`: No such file or directory gcc.exe: error: unrecognized command-line option '--cflags' gcc.exe: error: unrecognized command-line option '--libs' I noticed that the command  pkg-config --cflags --libs gtk4 was actually returning a list of required includes. It seemed that the gcc command simply was not able to recognize them. To solve the problem, I then created a Makefile, in which a variable called GTK_4_INCLUDES contains the list of includes.  The compilation command becomes:   gcc main.c -o main.exe $(GTK_4_INCLUDES) To compile the program in the console, I just had to type mingw32-make , a...

Connecting to MongoDB from WSL2

My application runs a web server connected to a MongoDB database. For all my development needs, I am using WSL on windows 10. Unfortunately, MongoDB is not available on WSL, so I have installed the Windows version and pointed my server to the localhost:27017 address, no issue. It worked well until the day I switched to WSL2. Then I would get some ECONNREFUSED errors. To solve this problem, I have found a simple three steps solution.  The whole idea is that WSL2 now uses a true VM and got its own network interface (IP, MAC address, etc.). I think it's a bit more complicated than that, but that's how I understand it. Long story short, you now have a windows IP, and a WSL IP. 1. Accessing  the windows host from WSL The first step is to make sure that you can ping the windows IP from WSL. It was not working for me right away. It seems like there are still inconsistencies in the WSL implementation, but this github issue thread gave a good solution. It's a script yo have to run...