0

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?

1
  • If user1 is a Unix user, why are not running psql and connecting to the database as that user? When user1 logs, what does he type to connect to the database, something isn't making sense here. – Evan Carroll Jun 17 at 0:20
2

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.

2
  • He already has unix user accounts. He has /home/user2/ I think we need to know how that user is connecting to the database to make sense of this. – Evan Carroll Jun 17 at 0:21
  • he says "su postgres" (or similar) then "psql" – Jasen Jun 17 at 0:44
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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.