With a freshly installed version of Postgres 9.2 via yum repository on Centos 6, how do you run postgres as a different user when it is configured to run as 'postgres:postgres' (u:g) out of the box?
4
-
I'm curious: Why? What's the underlying problem you're trying to solve by running Pg as a different user? Also, if you're running with SELinux enabled I strongly recommend sticking to the default user/group.Craig Ringer– Craig Ringer2012-10-17 22:19:34 +00:00Commented Oct 17, 2012 at 22:19
-
1If you're constrained to the users/groups you can create on a box - as I was. Enterprise "security" is fun!AndrewPK– AndrewPK2012-10-18 21:06:37 +00:00Commented Oct 18, 2012 at 21:06
-
Wow, that's a super-clever policy! Do they require you to get signed authorization in triplicate for every minor patch release so you'll be on 9.2.1 until the end of time, too? I see that a lot...Craig Ringer– Craig Ringer2012-10-18 22:09:07 +00:00Commented Oct 18, 2012 at 22:09
-
@CraigRinger, a practical example: postgres database is located on a mounted NTFS volume and you are on Linux. (Mounted NTFS files show up as owned by a given userid and cannot be chowned easily). Postgres would then complain that ownership of config files and data files is mismatched. The easiest solution: run postgres under different user.akhmed– akhmed2015-05-27 21:59:44 +00:00Commented May 27, 2015 at 21:59
Add a comment
|
2 Answers
In addition to AndrewPK's explanation, I'd like to note that you can also start new PostgreSQL instances as any user by stopping and disabling the system Pg service, then using:
initdb -D /path/to/data/directory
pg_ctl start -D /path/to/data/directory
This won't auto-start the server on boot, though. For that you must integrate into your init system. On CentOS 6 a simple System V-style init script in /etc/init.d/ and a suitable symlink into /etc/rc3.d/ or /etc/rc3.d/ (depending on default runlevel) is sufficient.
If running more than one instance at a time they must be on different ports. Change the port directive in postgresql.conf in the datadir or set it on startup with pg_ctl -o "-p 5433" .... You may also need to override the unix_socket_directories if your user doesn't have write permission to the default socket directory.
2 Comments
AndrewPK
Before running
initdb you would need to su/login as the user as well. Great tip!
akhmed
Note that for psql 9.4 you will get
pg_ctl: command not found since pg_ctl is no longer located in the PATH. You have to manually navigate to /usr/lib/postgresql/9.4/bin/ (on Ubuntu). Both initdb and pg_ctl should be there. And then this solution works perfectly for 9.4 as well.This is only for a fresh installation (as it pertained to my situation) as it involves blowing away the data dir.
The steps I took to resolve this issue while utilizing the packaged startup scripts for a fresh installation:
- Remove the postgres data dir
/var/lib/pgsql/9.2/dataif you've already gone through the initdb process with the postgres user:group configured as default. - Modify the startup script (
/etc/init.d/postgresql-9.2) to replace all instances ofpostgres:postgreswithNEWUSER:NEWGROUP. - Modify the startup script to replace all instances of
postgresin any$SU -l postgreslines with theNEWUSER. - run
/etc/init.d/postgres initdbto regenerate the cluster using the new username - Make sure any logs created are owned by the new user or remove old logs if error on initdb (the configuration file in my case was found in
/var/lib/pgsql/9.2/data/postgresql.conf). - Startup postgres and it should now be running under the new user/group.
I understand this might not be what other people are looking for if they have existing postgres db's and want to restart the server to run as a different user/group combo - this was not my case, and I didn't see an answer posted anywhere for a 'fresh' install utilizing the pre-packaged startup scripts.