Postgresql : Just added a new user, but now says "unknown user"?

Question

Still havent understood the basics !!! The username "postgres" created initially, immediately after installation, allows me to log in. But later I added a new user "p2" with the password "123". When I try to login using the p2 username it doesnt work. I dont know if I am following the right steps, please guide.

revoltman@G41:~$ service postgresql start
revoltman@G41:~$ sudo -u postgres -i
postgres@G41:~$ exit
logout
revoltman@G41:~$ sudo -u p2 -i
sudo: unknown user: p2
sudo: unable to initialize policy plugin

Earlier I had created the new user :

postgres@G41:~$ createuser -P -s -e p2
Enter password for new role: 
Enter it again: 
CREATE ROLE p2 PASSWORD 'md576be8f31b92b38740013f11b102caa1c' 
    SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;
postgres@G41:~$ sudo -u p2 -i
sudo: unknown user: p2
sudo: unable to initialize policy plugin

If I type \du in psql to find list of all users :

                                 List of roles
 Role name |                      Attributes                         | Member of 

-----------+------------------------------------------------------------+-
p1         |                                                            | {}
p2         | Superuser, Create role, Create DB                          | {}
postgres   | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Am I creating new user in a wrong way or my login method is wrong ? If you want to know any other details please ask.

Please suggest a good tutorial/guide on the internet. I am totally in the dark about basics. — revoltman, Jan 29 '18 at 14:09

score 3 · Answer 1 · answered Jan 29 '18 at 13:33

3

createuser p2 is creating a Postgres user named p2.

sudo is attempting to run a command with the Unix user p2.

These are two completely different things.

answered Jan 29 '18 at 13:33

Jonathon Reinhart

132,704
33
254
328

1

Then how do I login into the role "p2" ? ...and then create new databases. – revoltman Jan 29 '18 at 13:37

score 2 · Accepted Answer · answered Jan 29 '18 at 13:42

2

to connect to posgresql in command line you should use psql psql -U p2 DATABASE_NAME

answered Jan 29 '18 at 13:42

blackxel

196
1
8

I did...postgres=# psql -U p2 hr2; And I get.... ERROR: syntax error at or near "psql" LINE 1: psql -U p2 hr2; ^ What mistake am I doing ? – revoltman Jan 29 '18 at 13:56
you need to executed psql inside your terminal, log out from postgresql with ctrl + D. you need to see that revoltman@G41:~$ – blackxel Jan 29 '18 at 14:34
revoltman@G41:~$ psql -U p2 hr2 I am getting...... psql: FATAL: Peer authentication failed for user "p2" It didnt even ask for the password. – revoltman Jan 29 '18 at 14:40
you need to configure pg_hba.conf file see this question https://stackoverflow.com/questions/18664074/getting-error-peer-authentication-failed-for-user-postgres-when-trying-to-ge – blackxel Jan 29 '18 at 14:46
# "local" is for Unix domain socket connections only local all all >>> trust<<<< Changed from peer to trust. And it worked thanks for your time. – revoltman Jan 29 '18 at 15:05

Postgresql : Just added a new user, but now says "unknown user"?

2 Answers2