Can anyone please throw some light on the behavior of postgres when it comes to permission issues... I'm using enterprisedb 8.3 on opensuse linux 10.3 and 11 (2 machines). This is what i have understood so far:
Lets say user user1 is a non-root user. I login to the machine as root and install enterprisedb. During installation i'm asked for an existing non-root user to act as the 'owner' of the installation. I provide 'user1'. So user1 becomes the 'owner' of the installation and has the privilege of starting and stopping the service. Also the file .pgpass (postgres password file) gets automatically created in user1's home folder and it contains a line for localhost, specifying the password of user postgres. If this file exists in any user's home, then that user will be allowed to connect to postgres server without a password.
Now during installation, the user 'postgres' will be created if it doesnt already exist. And these 2 users (user1, postgres) will have write permissions to each other's home directories.
Please tell me if my understanding so far is correct and if you can enlighten me further.
My requirement is to let postgres have write access to another user's home directory (say 'tomcat' which could be a system user) and vice versa ie tomcat should have write access to postgres' home and also password-less access to psql. And similarly other OS users might need the same permissions.
Another doubt: How and when does the .pgpass file gets created and its contents get added? I saw that explicitly creating a file for an OS user and adding an entry for localhost enables the user to connect to psql w/o a password. And similarly if remote connection is enabled in a remote server, an entry should exist for the remote server in the local .pgpass file. I want to know how much of this happens automatically and when.