Ciro Santilli 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-existThis is the one that worked on Ubuntu 21.04: stackoverflow.com/questions/11919391/postgresql-error-fatal-role-username-does-not-exist/38444152#38444152
Explanation:
sudo -u postgres createuser -s $(whoami)
createdb $(whoami)sudo -u postgresuses thepostgresuser via peer authentication-sincreateuser -s: make it a superusercreatedb: TODO why do we have to create a table with the same name as the user? Otherwise login fails.
You can now run
shows that peer authentication is available to all users apparently:
psql without any password. This works without password due to peer authentication,
sudo cat /etc/postgresql/12/main/pg_hba.conflocal all postgres peer
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all peerList users:
output:
psql -c '\du' 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 testdb0Help toplevel:
helpGet help for Postgres commands such as
\h and so on:
\?List supported SQL commands:
\hShow syntax for one type of command:
\h SELECTList all databases:
which shows:
psql -c '\l' 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.:
you can do that afterwards with:
psql\c testdb0Let'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:
gives:
psql testdb0 -c '\dt' List of relations
Schema | Name | Type | Owner
--------+--------+-------+-------
public | table0 | table | ciro
(1 row)View table schema: stackoverflow.com/questions/109325/postgresql-describe-table
output:
psql testdb0 -c '\d+ table0' 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:
output:
psql testdb0 -c "INSERT INTO table0 (int0, char0) VALUES (2, 'two'), (3, 'three'), (5, 'five'), (7, 'seven');"
psql testdb0 -c 'SELECT * FROM table0;' int0 | char0
------+------------------
2 | two
3 | three
5 | five
7 | seven
(4 rows)Delete the table:
psql testdb0 -c 'DROP TABLE table0;'- output one column per line: stackoverflow.com/questions/9604723/alternate-output-format-for-psql-showing-one-column-per-line-with-column-name
- PostgreSQL does not automatically index foreign keys! stackoverflow.com/questions/970562/postgres-and-indexes-on-foreign-keys-and-primary-keys