How to set user-based .psql_history in linux?

All we need is an easy explanation of the problem, so here it is.

If use postgres in linux, it has a postgres user. So when use psql from this user, it will generate a .psql_history file under this user – /home/postgres/.psql_history.

When set many users on the linux server, all of them want to use the psql under their session like /home/user1. How to make them use psql and generate the psql history to each of them?

Like these

  • /home/user1/.psql_history
  • /home/user2/.psql_history
  • /home/…/.psql_history

Is it necessary to install postgres client for each user?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Create unix user accounts for each person, and set user-name maps in pg_ident so that everyone connects as postgres.

https://www.postgresql.org/docs/10/auth-username-maps.html

or alternatively create individual database users for each account and give them superuser privs. Optionally "set session authenticaton postgres" can be set as a startup action in ~/.psqlrc if you want new relations to be owned by postgres – not that it makes much difference if everyone is already superuser.

Method 2

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 psql as user1 and 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.

Environmental Variables

Use HISTFILE or PSQL_HISTORY or the environmental variable,

HISTFILE

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 ~/.psql_history, or %APPDATA%\postgresql\psql_history on Windows. For example, putting:

\set HISTFILE ~/.psql_history- :DBNAME

in ~/.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

Using sudo

You can also do sudo -E (-E is --preserve-env) observe,

❯ sudo -u postgres -- /bin/sh -c "echo ~/.psql_history"
/var/lib/postgresql/.psql_history
❯ sudo -E -u postgres -- /bin/sh -c "echo ~/.psql_history"
/home/ecarroll/.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!

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply