1 подписчик
The 'create user' SQL command creates a user in postgres, but it doesn't create an operating system user. In order to use sudo or su commands to act as another user that user must be defined at the operating system level. You can do that with e.g. the adduser command.
By default postgres is configured to use peer authentication for connections over unix sockets. Peer authentication means the database knows which operating system user you are and selects the database level user with the same name. For that to work you have to have defined both a database level user and an operating system level user.
Conversely, if you don't want to create an operating system level user and just want to connect to the database directly, you will need to bypass peer authentication and use password authentication. The simplest way to do that is to connect using a network socket instead of a unix socket - i.e. to pass the hostname that you want to connect to. Peer authentication isn't possible when connecting over a network socket, so you will be asked for a password to authenticate. e.g.
$ psql -h localhost blog blog
I cannot access with:
~$ psql blog blog
psql: error: FATAL: Peer authentication failed for user "blog"
1 минута
23 марта 2023