Ciro Santilli @cirosantilli 34

 Incoming links: sudo

PostgreSQL getting started  Updated 2024-12-15  +Created 1970-01-01

On Ubuntu 20.10 PostgreSQL 12.6, login with psql on my default username without sudo fails with: stackoverflow.com/questions/11919391/postgresql-error-fatal-role-username-does-not-exist

This is the one that worked on Ubuntu 21.04: stackoverflow.com/questions/11919391/postgresql-error-fatal-role-username-does-not-exist/38444152#38444152

sudo -u postgres createuser -s $(whoami)
createdb $(whoami)

Explanation:

sudo -u postgres uses the postgres user via peer authentication
-s in createuser -s: make it a superuser
createdb: TODO why do we have to create a table with the same name as the user? Otherwise login fails.

You can now run psql without any password. This works without password due to peer authentication,

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

shows that peer authentication is available to all users apparently:

local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer

List users:

psql -c '\du'

output:

                                    List of roles
  Role name  |                         Attributes                         | Member of 
-------------+------------------------------------------------------------+-----------
 ciro        | Superuser, Create role, Create DB                          | {}
 owning_user |                                                            | {}
 postgres    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Delete user later on:

psql -c 'DROP USER username;'

Create a database:

createdb testdb0

Help toplevel:

help

Get help for Postgres commands such as \h and so on:

\?

List supported SQL commands:

\h

Show syntax for one type of command:

\h SELECT

List all databases:

psql -c '\l'

which shows:

    Name     |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-------------+----------+----------+-------------+-------------+-----------------------
 ciro        | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
 postgres    | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
 template0   | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres          +
             |          |          |             |             | postgres=CTc/postgres
 template1   | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres          +
             |          |          |             |             | postgres=CTc/postgres
 testdb0     | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
(6 rows)

Delete a database:

psql -c 'DROP DATABASE "testdb0";'

If you didn't give a database from the command line e.g.:

psql

you can do that afterwards with:

\c testdb0

Let's create a table and test that it is working:

psql testdb0 -c 'CREATE TABLE table0 (int0 INT, char0 CHAR(16));'

List tables, no special tables:

psql testdb0 -c '\dt'

gives:

        List of relations
 Schema |  Name  | Type  | Owner
--------+--------+-------+-------
 public | table0 | table | ciro
(1 row)

View table schema: stackoverflow.com/questions/109325/postgresql-describe-table

psql testdb0 -c '\d+ table0'

output:

                                      Table "public.table0"
 Column |     Type      | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+---------------+-----------+----------+---------+----------+--------------+-------------
 int0   | integer       |           |          |         | plain    |              | 
 char0  | character(16) |           |          |         | extended |              |

Insert some data into it and get the data out:

psql testdb0 -c "INSERT INTO table0 (int0, char0) VALUES (2, 'two'), (3, 'three'), (5, 'five'), (7, 'seven');"
psql testdb0 -c 'SELECT * FROM table0;'

output:

 int0 |      char0
------+------------------
    2 | two
    3 | three
    5 | five
    7 | seven
(4 rows)

Delete the table:

psql testdb0 -c 'DROP TABLE table0;'

 Read the full article

Universal asynchronous receiver-transmitter  Updated 2024-12-15  +Created 1970-01-01

 View more

A good project to see UARTs at work in all their beauty is to connect two Raspberry Pis via UART, and then:

type in one and see characters appear in the other: scribles.net/setting-up-uart-serial-communication-between-raspberry-pis/
send data via a script: raspberrypi.stackexchange.com/questions/29027/how-should-i-properly-communicate-2-raspberry-pi-via-uart

Part of the beauty of this is that you can just connect both boards directly manually with a few wire-to-wire connections with simple jump wire. Its simplicity is just quite refreshing. Sure, you could do something like that for any physical layer link presumably...

Remember that you can only have one GNU screen connected at a time or else they will mess each other up: unix.stackexchange.com/questions/93892/why-is-screen-is-terminating-without-root/367549#367549

On Ubuntu 22.04 you can screen without sudo by adding yourself to the dialout group with:

sudo usermod -a -G dialout $USER

 Read the full article