postgresql

•  ubuntu 24.04 install postgresql

• sudo apt install postgresql postgresql-contrib > Y
• sudo systemctl enable postgresql.service
• sudo systemctl start postgresql.service

• test
• sudo -i -u postgres
• psql
• \q
• exit

• create user
• sudo -i -u postgres
• createuser --interactive
• >myuser
• >n,n,n
• createdb mydb
• psql
• sudo -u postgres psql
• ALTER USER myuser WITH PASSWORD 'pass';
• GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;
• \q

• connect config
• sudo nano /etc/postgresql/XX/main/postgresql.conf
• listen_addresses = '*'
• sudo nano /etc/postgresql/16/main/pg_hba.conf
• IPV4
• host    db       user       ip           mode:md5(trust)

• firewall config
• sudo ufw enable
• sudo ufw start
• sudo ufw allow ssh
• sudo ufw allow 5432/tcp
• sudo ufw status numbered
• sudo ufw logging on

• debug can not connect
• sudo lsof -i :5432
• test connect
• psql -h out_host -U user -d database  

• Note
• $ sudo -u postgres psql
• postgres=# CREATE DATABASE yourdbname;
• postgres=# CREATE USER youruser WITH ENCRYPTED PASSWORD 'yourpass';
• postgres=# GRANT ALL PRIVILEGES ON DATABASE yourdbname TO youruser;