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/' exitsudo systemctl start postgresql sudo systemctl enable postgresql sudo systemctl status postgresqlsource : https://wiki.archlinux.org/index.php/PostgreSQLCreate Database
Create your first database/user
Become the postgres user. Add a new database user using the createuser command:CREATE USER
[postgres]$ createuser --interactiveCreate 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-usernameto the following command):$ createdb myDatabaseNameRemote 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 ispostgresql.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 thepg_hba.confwhich defines authentication settings, for both local users and other hosts ones.Restricts access rights to the database superuser by default
The defaultspg_hba.confallow 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 trustTo:/var/lib/postgres/data/pg_hba.conf# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all postgres peerYou 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.conflisten_addresses = ''This will disable network listening completely. After this you should restartpostgresql.servicefor the changes to take effect.Configure PostgreSQL to be accessible from remote hosts
In the connections and authentications section, set thelisten_addressesline to your needs:/var/lib/postgres/data/postgresql.conflisten_addresses = 'localhost,my_local_ip_address'You can use'*'to listen on all available 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 md5whereip_addressis the IP address of the remote client.See the documentation for pg_hba.conf.After this you should restartpostgresql.servicefor the changes to take effect.For troubleshooting take a look in the server log file:$ journalctl -u postgresql.serviceAnother reference: https://superuser.com/questions/800201/postgresql-connection-refusedNow install DBeaver or pgadmin4 (a GUI to postgresql)set a password for user using commandalter role rprajapa with password 'xxxxzzzzz';To test connection from a remote machine using clipsql -h 10.2.2.11 -U rprajapa -d rprajapaDBEAVER 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 keyssh-keygen -p -m PEM -f /path/to/openssh/keywill re-write the private key in the correct format. (source : https://github.com/rundeck/rundeck/issues/4813#issuecomment-492358649)
