Postgres

Alex included in category DevOps

2023-08-07 2023-08-07 188 words One minute

Contents

Install PostgreSql

sudo apt install postgresql

Create new Role

create role user;

Show all roles

\du

Change attributes on existing roles

ALETR ROLE "user" WITH LOGIN CREATEDB;

Change role password

Show all database

ALTER ROLE "user" WITH PASSWORD 'password';

Set password for postgres user

sudo -u postgres psql

and

\password

Enable remote access to Postgres

Modify the PostgreSQL configuration file

sudo nano /etc/postgresql/12/main/postgresql.conf

Then, find the line #listen_addresses = 'localhost' and uncomment it next, change the value of listen_addresses to *.

Modify the pg_hba.conf file

sudo nano /etc/postgresql/12/main/pg_hba.conf

append next line

host    all             all             0.0.0.0/0            md5

Restart PostgreSQL

sudo service postgresql restart

Connect to remove postgres server

psql postgres://user:password@ip_add_or_domain:port/db_name

psql -h <REMOTE HOST> -p <REMOTE PORT> -U <DB_USER> <DB_NAME> -W

-W option will prompt for password

check port

# sudo netstat -peanut | grep "postgres"
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      123        614086     92337/postgres      
tcp6       0      0 :::5432                 :::*                    LISTEN      123        614087     92337/postgres      
udp        0      0 127.0.0.1:41721         127.0.0.1:41721         ESTABLISHED 123        614090     92337/postgres

Change PostgreSQL user password

ALTER USER user_name WITH PASSWORD 'new_password';