Not an Ideal Configuration
PostgreSQL was created such that the permissions internal to the database are intended to mirror the system directly. You can get around that and it won't be too painful, but you're supposed to run
user2 per your example.
Now what you're asking to do is an especially bad idea.
postgres isn't a regular user, it's a Super User. These users can
DROP DB and
TRUNCATE at will. You probably don't want that account to be shared.
But let's say that you do NOT want to grant each user the ability to connect to the database, or to create new databases. This isn't ideal but if you want to do that there are some mechanisms that may assists you, though subject to the caveat above, I would not share access to the
postgres user ever.
PSQL_HISTORY or the environmental variable,
The file name that will be used to store the history list. If unset, the file name is taken from the
PSQL_HISTORY environment variable. If that is not set either, the default is
%APPDATA%\postgresql\psql_history on Windows. For example, putting:
\set HISTFILE ~/.psql_history- :DBNAME
~/.psqlrc will cause psql to maintain a separate history for each database.
Note: This feature was shamelessly plagiarized from Bash.
You can do this like
postgres$ SQL_HISTORY="/home/otheruser/.psql" psql
You can also do
sudo -E (
❯ sudo -u postgres -- /bin/sh -c "echo ~/.psql_history"
❯ sudo -E -u postgres -- /bin/sh -c "echo ~/.psql_history"
You can see above with the
-E flag we get the directory of the user that invokes
sudo not the user that you're changing to. So you could log in as
postgres and log history to your home directory by doing this,
❯ sudo -E -u postgres -- psql
Now you just have to decide who you want to give this very dangerous access to!