Using Postgres
The installation automatically creates a user named postgres.
Login as default user postgres
sudo -i -u postgres
On command line, to change the password of the default user:
psql -c "alter user postgres with password 'StrongAdminP@ssw0rd'"
On command line, access the Postgres prompt:
psql
The terminal will now show the name of the user who entered the Postgre prompt:
postgres=#
Exit the Postgre prompt:
\q
Create a new database:
On the postgres user's command prompt:
createdb sammy
Show databases:
\l
To list the tables in a database, first connect to the database:
\c sammy
Then
\dt
get specific information about a table named todos:
\d+ todos
to show the contents of a table named todos:
SELECT * FROM todos;
Note: The query has to be upper-case and have the semi-colon at the end.
Create a new user:
sudo -u postgres createuser --interactive
For Example:
Enter name of role to add: sammy
Shall the new role be a superuser? (y/n) y
To be able to use Postgre with this new user, need to make admin
sudo adduser sammy
Set Authentication Method
Modify the configuration file
sudo nano /etc/postgresql/14/main/pg_hba.conf
and change authentication method, for example to password:
# IPv4 local connections:
host all all 127.0.0.1/32 password
and restart Postgres
sudo systemctl restart postgresql
