Postgres

Introduction

This is a page for keeping track of using postgres

Install

Nice and easy lemon squeezy

sudo apt install postgresql postgresql-contrib

Things to change

Listen on the network by changing postgresql

listen_addresses = '0.0.0.0'

And add pg_hba.conf

host    all             all            xxx.xxx.xxx.xxx/32            scram-sha-256

Don't forget to restart

Getting to CLI

Login and envoke the CLI

sudo -i -u postgres
psql

Quiting..

\q

Forgot Password

To reset password

sudo bash
vi /etc/postgresql/17/main/xxx/pg_hba.conf
# Change
# local   all             postgres                                scram-sha-256
# To
# local   all             postgres                                 peer
sudo systemctl restart postgresql
sudo -u postgres psql

At the psql prompt

ALTER USER postgres PASSWORD 'newpassword';
\q

Then reverse the pg_hba.conf change and restart postgres

Getting it to Work from Install

Found that by default it does not work. Sigh! Tested with

psql --host=localhost --dbname=test_db --username=test_user

To fix this

sudo apt install locate
locate pg_hba.conf

When you find this change

 local   all             all                                peer

to

 local   all             all                                md5

Restart the service

sudo service postgresql restart

Make sure the public schema has permissions. Connect to datasase as postgres

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public to test_user;

Useful commands

Connect as a user

  psql -d mydb -U myuser

/* Show users */
\du

/* Show databases */
\l

/* Show tables */
\dt

/* Set password */
alter user test with encrypted password 'testpass'

/* Create database */
CREATE DATABASE testdb

/* Grant Privileges */
grant all privileges on database testdb to test


/* Use database */
\c testdb

/* Create Table */
CREATE TABLE employees ( 
    id SERIAL PRIMARY KEY, 
    "Name" VARCHAR (50), 
    birthdate DATE CHECK (birthdate > '1900-01-01'), 
    salary numeric CHECK(salary > 0) 
); 

/* Describe the Table */
\d test_table

/* Create Index */
CREATE UNIQUE INDEX name_idx ON employees (name)

/* Command History */
\s

  create user user_name with encrypted password 'mypassword'

Set up a User and Database

Start postgres

sudo systemctl restart postgresql

Get to psql root user

sudo -i -u postgres
psql

/* Create user */
create user rust_admin; 

/* Set password */
alter user rust_admin with encrypted password 'blahhh';

/* Create database */
CREATE DATABASE rust_db

/* Make them the owner */
GRANT ALL PRIVILEGES ON DATABASE rust_db TO rust_admin;

/* Change to Database */
\c rust_db
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO rust_admin;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO rust_admin;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO rust_admin;

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO rust_admin;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON SEQUENCES TO rust_admin;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON FUNCTIONS TO rust_admin;

Restore Database from CLI

Just in case I forget. I used the database on here

/usr/bin/pg_restore --host "localhost" --port "5432" --username "rest_admin"  --dbname "rest_db" --verbose "/tmp/dvdrental.tar"