View source for Postgres

=Introduction=
This is a page for keeping track of using postgres
=Install=
Nice and easy lemon squeezy
<syntaxhighlight "bash">
sudo apt install postgresql postgresql-contrib
</syntaxhighlight>
=Things to change=
Listen on the network by changing postgresql
<syntaxhighlight "bash">
listen_addresses = '0.0.0.0'
</syntaxhighlight>
And add pg_hba.conf
<syntaxhighlight "bash">
host    all             all            xxx.xxx.xxx.xxx/32            scram-sha-256
</syntaxhighlight>
Don't forget to restart

=Getting to CLI=
Login and envoke the CLI
<syntaxhighlight "bash">
sudo -i -u postgres
psql
</syntaxhighlight>
Quiting..
<syntaxhighlight "psql">
\q
</syntaxhighlight>
=Forgot Password=
To reset password
<syntaxhighlight "bash">
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
</syntaxhighlight>
At the psql prompt
<syntaxhighlight "psql">
ALTER USER postgres PASSWORD 'newpassword';
\q
</syntaxhighlight>
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
<syntaxhighlight "bash">
psql --host=localhost --dbname=test_db --username=test_user
</syntaxhighlight>
To fix this 
<syntaxhighlight "bash">
sudo apt install locate
locate pg_hba.conf
</syntaxhighlight>
When you find this change
<syntaxhighlight "bash">
 local   all             all                                peer
</syntaxhighlight>
to
<syntaxhighlight "bash">
 local   all             all                                md5
</syntaxhighlight>
Restart the service
<syntaxhighlight "bash">
sudo service postgresql restart
</syntaxhighlight>
Make sure the public schema has permissions. Connect to datasase as postgres
<syntaxhighlight "psql">
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public to test_user;
</syntaxhighlight>

=Useful commands=
Connect as a user
<syntaxhighlight lang="bash">
  psql -d mydb -U myuser
</syntaxhighlight>

<syntaxhighlight lang="psql">
/* 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

</syntaxhighlight>

<syntaxhighlight lang="psql">
  create user user_name with encrypted password 'mypassword'
</syntaxhighlight>

=Set up a User and Database=

Start postgres
<syntaxhighlight lang="bash">
sudo systemctl restart postgresql
</syntaxhighlight>

Get to psql root user
<syntaxhighlight lang="bash">
sudo -i -u postgres
psql 
</syntaxhighlight>

<syntaxhighlight lang="sql">
/* 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;

</syntaxhighlight>

=Restore Database from CLI=
Just in case I forget. I used the database on [https://neon.tech/postgresql/postgresql-getting-started/postgresql-sample-database here]
<syntaxhighlight lang="bash">
/usr/bin/pg_restore --host "localhost" --port "5432" --username "rest_admin"  --dbname "rest_db" --verbose "/tmp/dvdrental.tar"
</syntaxhighlight>