How do PostgreSQL users with same name as operating system users work?-CodePudding

I stumbled upon PostgreSQL installations, where there is a PostgreSQL user with the same name as an operating system (Linux) user. If you are logged into the operating system as such a user, you can usually login to psql without specifying a username and password as this user.

How does this work? Is this a pure convention, or is there a special mechanism in PostgreSQL for this kind of behavior?

There are cases, where no corresponding PostgreSQL user is present for an operating system user. In this case, how do I create one, that can be logged into without having to specify username and password once I am logged into the operating system as this user?

CodePudding user response：

https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNECT-PASSFILE

create a pgpass file: vim ~/.pgpass

fill your credentials: hostname:port:database:username:password

exit file. and change ~/.pgpassfile access permission:
https://www.postgresql.org/docs/current/libpq-pgpass.html
chmod 0600 ~/.pgpass

If your linux user is the same as one of the pgpassfile user, then just type psql to connect to database.

CodePudding user response：

There are several mechanisms. I don't know what makes a mechanism a special one, though.

The client always sends a username to the server. If you don't specify one to use then libpq-based clients look up the operating system name and use that. Some non-libpq-based clients might adopt the same convention.

For not requiring a password, maybe local connections are set to 'trust' in pg_hba.conf, in which case anyone else on the same machine can also log in to PostgreSQL as you, just by specifying your username to use, such as with -U martinw. Or maybe a password is required, but it is provided automatically via .pgpass file or via PGPASSWORD env variable, but if you set that up presumably you would remember doing so. Or maybe you have local connections set to peer in pg_hba, in which case the db server asks the OS "who is on the other end of this socket?" and verifies the answer matches the requested username (this is a common default setup, for example on Ubuntu Linux) or if a pg_ident map is in use, then it verifies the response system username is allowed to log in as the specified db username.