Create a test user in PostgreSQL Updated 2025-07-16
View more
In order to create a test user with password instead of peer authentication, let's create test user:
createuser -P user0
createdb user0
-P makes it prompt for the users password.
Alternatively, to create the password non-interactively stackoverflow.com/questions/42419559/postgres-createuser-with-password-from-terminal:
psql -c "create role NewRole with login password 'secret'"
Can't find a way using the createuser helper.
We can then login with that password with:
psql -U user0 -h localhost
which asks for the password we've just set, because the -h option turns off peer authentication, and turns off password authentication.
The password can be given non-interactively as shown at stackoverflow.com/questions/6405127/how-do-i-specify-a-password-to-psql-non-interactively with the PGPASSWORD environment variable:
PGPASSWORD=a psql -U user0 -h localhost
Now let's create a test database which user0 can access with an existing superuser account:
createdb user0db0
psql -c 'GRANT ALL PRIVILEGES ON DATABASE user0db0 TO user0'
We can check this permission with:
psql -c '\l'
which now contains:
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 user0db0  | ciro     | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =Tc/ciro             +
           |          |          |             |             | ciro=CTc/ciro        +
           |          |          |             |             | user0=CTc/ciro
The permission letters are explained at:
user0 can now do the usual table operations on that table:
PGPASSWORD=a psql -U user0 -h localhost user0db0 -c 'CREATE TABLE table0 (int0 INT, char0 CHAR(16));'
PGPASSWORD=a psql -U user0 -h localhost user0db0 -c "INSERT INTO table0 (int0, char0) VALUES (2, 'two'), (3, 'three'), (5, 'five'), (7, 'seven');"
PGPASSWORD=a psql -U user0 -h localhost user0db0 -c 'SELECT * FROM table0;'
Read the full article
PostgreSQL getting started Updated 2025-07-16
View more
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