 Create a test user in PostgreSQL

 New to topics? Read the documentation here!

 Top articles  Latest articles + New article in topic

Ciro Santilli 34  Updated 2024-12-15  +Created 1970-01-01

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