postgres connect as different user

Question

New to postgres. Till now I used mysql and whenever I needed to login I did this:

mysql -u root -p

which then prompted me to provide password for user root

But postgres feels a lot different than that. After installation I created a new user like this:

create user root with password 'something';
create database test;
grant all privileges on database test to root;

Now when I use pgadmin4 I get a prompt to enter my sudo password. Even when I try to connect to database via terminal using:

sudo -i -u postgres

It asks me for my sudo password.

How can I login via different user?

lsb_release -a gives:

No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 21.10
Release:    21.10
Codename:   impish

Answer 1

Generally one would use the psql command when connecting to a PostgreSQL database, and the -U option will allow you to specify which username you would like to connect with:

psql -U {username}

As an aside ...

The use of root as a username is generally "bad form" and strongly discouraged in most database and development circles, as it's an obvious attack target – even for locally-installed instances that are not exposed to the Internet. Instead people generally recommend creating accounts for applications that are limited to single databases or accounts for people that are limited to a few. Administrators, of course, would have access to all, but with a unique username.

If you need to connect to PostgreSQL with the default postgres account, you can do so like this:

sudo –i –u postgres psql