setting up postgresql in manjaro linux

This article covers
1. installing postgresql
2. Creating database
3. Create user
4. Configure for remote connections
5. Dbeaver private key issue.

Install Postgresql

source : https://manjaro.site/how-to-install-postgresql-server-on-manjaro-18-0/

sudo pacman -Sy postgresql

sudo su postgres -l
initdb --locale $LANG -E UTF8 -D '/var/lib/postgres/data/'
exit

sudo systemctl start postgresql
sudo systemctl enable postgresql
sudo systemctl status postgresql
source : https://wiki.archlinux.org/index.php/PostgreSQL

Create Database

Create your first database/user

Tip: If you create a PostgreSQL user with the same name as your Linux username, it allows you to access the PostgreSQL database shell without having to specify a user to login (which makes it quite convenient).

Become the postgres user. Add a new database user using the createuser command:



CREATE USER




[postgres]$ createuser --interactive


Create a new database over which the above user has read/write privileges using the createdb command (execute this command from your login shell if the database user has the same name as your Linux user, otherwise add -O database-username to the following command):
$ createdb myDatabaseName


Remote Connectivity
Also make sure we can connect from remote hosts
[rprajapa@hpmanjaro ~]$ psql
psql (11.5)
Type "help" for help.

rprajapa=# show config_file;
              config_file               
----------------------------------------
 /var/lib/postgres/data/postgresql.conf
(1 row)

rprajapa=# show hba_file;
              hba_file              
------------------------------------
 /var/lib/postgres/data/pg_hba.conf
(1 row)





Optional configuration

The PostgreSQL database server configuration file is postgresql.conf. This file is located in the data directory of the server, typically /var/lib/postgres/data. This folder also houses the other main configuration files, including the pg_hba.conf which defines authentication settings, for both local users and other hosts ones.

Note: By default, this folder will not be browsable or searchable by a regular user. This is why find and locate are not finding the configuration files.

Restricts access rights to the database superuser by default

The defaults pg_hba.conf allow any local user to connect as any database user, including the database superuser. This is likely not what you want, so in order to restrict global access to the postgres user, change the following line:
/var/lib/postgres/data/pg_hba.conf
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust

To:
/var/lib/postgres/data/pg_hba.conf
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             postgres                                peer

You might later add additional lines depending on your needs or software ones.

Configure PostgreSQL to be accessible exclusively through UNIX Sockets

In the connections and authentications section of your configuration, set:
/var/lib/postgres/data/postgresql.conf
listen_addresses = ''

This will disable network listening completely. After this you should restart postgresql.service for the changes to take effect.

Configure PostgreSQL to be accessible from remote hosts

In the connections and authentications section, set the listen_addresses line to your needs:
/var/lib/postgres/data/postgresql.conf
listen_addresses = 'localhost,my_local_ip_address'

You can use '*' to listen on all available addresses.

Note: PostgreSQL uses TCP port 5432 by default for remote connections. Make sure this port is open in your firewall and able to receive incoming connections. You can also change it in the configuration file, right below listen_addresses

Then add a line like the following to the authentication config:
/var/lib/postgres/data/pg_hba.conf
# TYPE  DATABASE        USER            ADDRESS                 METHOD
# IPv4 local connections:
host    all             all             ip_address/32   md5

where ip_address is the IP address of the remote client.

See the documentation for pg_hba.conf.

Note: Neither sending your plain password nor the md5 hash (used in the example above) over the Internet is secure if it is not done over an SSL-secured connection. See Secure TCP/IP Connections with SSL for how to configure PostgreSQL with SSL.

After this you should restart postgresql.service for the changes to take effect.

For troubleshooting take a look in the server log file:
$ journalctl -u postgresql.service

Another reference: https://superuser.com/questions/800201/postgresql-connection-refused


Now install DBeaver or pgadmin4 (a GUI to postgresql)

set a password for user using command 
alter role rprajapa with password 'xxxxzzzzz';

To test connection from a remote machine using cli
psql -h 10.2.2.11 -U rprajapa -d rprajapa



DBEAVER Private key issue
IF DBeaver cannot connect to DB with public key and when you add the private key it gives error INVALID PRIVATE KEY :[
run the command to change the format of the key
ssh-keygen -p -m PEM -f /path/to/openssh/key will re-write the private key in the correct format.  (source : https://github.com/rundeck/rundeck/issues/4813#issuecomment-492358649)

copy ssh public key to remote server with one command

cat ~/.ssh/id_rsa.pub | ssh $server "mkdir -p ~/.ssh && cat >> ~/.ssh/authorized_keys && chmod 600 ~/.ssh/authorized_keys"

just substitute the $server above with user@remote-server

or I have created a bash script here

!/usr/bin/env bash

usage(){
  echo usage:: $0 -s [user@]\
}

if [ "$#" -ne 1 ]; then
    usage
    exit 1
fi

server=$1

if [ -z $server ]; then
  echo variable \$server is not set
  usage
  exit 1
fi

cat ~/.ssh/id_rsa.pub | ssh $server "mkdir -p ~/.ssh && cat >> ~/.ssh/authorized_keys && chmod 600 ~/.ssh/authorized_keys"

if [ $? -ne 0 ]; then
  echo "Command failed"
  exit 1
else
  echo "Command successful"
fi