5

PostgreSQL 10.6 and CentOS 7

pg_ctl status
pg_ctl: could not access directory "/var/lib/pgsql/data": Permission denied`

Wouldn't pg_ctl have access to this, given /var/lib/pgsql/data has ownership postgres:postgres? 

drwx------   3 postgres postgres   94 Nov 14 06:43 pgsql

How can I fix this without creating a vulnerability? Why is this throwing an error?

Additional info (edit):

su - postgres
cd /var/lib
/var/lib/pgsql: drwx------  3 postgres postgres   94 Nov 14 06:43 pgsql
/var/lib/pgsql/10: drwx------ 4 postgres postgres  33 Nov 14 06:38 10
/var/lib/pgsql/10/data:  drwx------ 20 postgres postgres 4096 Nov 15 03:47 data
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
Rich_F
  • 1,830
  • 3
  • 24
  • 45
  • I’m completely confused by this, but I managed to start Postgres with: `sudo -u postgres /usr/lib/postgresql/13/bin/pg_ctl -D /etc/postgresql/13/main start`. The help says, `-D, --pgdata=DATADIR location of the database storage area`, but when I actually specified the data directory there, it tried to find postgresql.conf in there. So I instead put the config directory there, and it magically worked. But two problems remain: (1) `sudo service postgresql start` still doesn’t work and (2) this is probably not how it’s supposed to work. – Dawn Drescher Sep 05 '21 at 13:02

2 Answers2

3

In UNIX, each process runs with the permissions of the user that starts the executable, not the owner of the executable (unless the SETUID flag is set).

So it doesn't matter who owns pg_ctl, but you have to be user postgres when you run it.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • My point was that I would think `pg_ctl` would be the way of getting this to other users, instead of having to log in as the `postgres` user to see what the status is of the db server. It's a bit odd to jump through those hoops. Ah but then again, in a db-only deployment, that's probably the case I'd be `postgres`. I just thought they would allow tools available to normal users, or at least `su`. – Rich_F Nov 15 '18 at 16:51
  • Nothing wrong with it. First time I've seen it, actually. Different result as well. – Rich_F Nov 15 '18 at 16:55
  • What exactly is your need? – Laurenz Albe Nov 15 '18 at 17:12
  • Just tripped over the process thinking it would give me lots of feedback including variables displayed inside the db using `SHOW all;`. – Rich_F Nov 15 '18 at 17:13
2

This needs few troubleshooting steps to pinpoint the real issue.

  1. Find out the user/owner and files permissions for that location in Linux:
Ls - al /var/lib/pgsql/data/

ls - al /var/lib/pgsql/

  1. Try to change to the postgres user and access the directory in 1

    # su - postgres

Following links should fill in blanks for few steps to check things out. On #2 link, you aren’t moving the dir, but you see steps to ensure dir is ready/accessible

https://wiki.postgresql.org/wiki/First_steps

https://www.digitalocean.com/community/tutorials/how-to-move-a-postgresql-data-directory-to-a-new-location-on-ubuntu-16-04

Update

From comments, it looks like pg ctl is run as user x... and lacks sufficient permissions

Without knowing much about your environment, it may be better to let postgres be that user who runs pg ctl since it’s already doing stuff related..

salah-1
  • 1,299
  • 11
  • 15
  • Sorry, I think you've missed what I put. It is owned __postgres:postgres__. I stated that in the original post. pg_ctl is run as __rich__ and it can't see inside `/var/lib`. The database works. I just can't use `pg_ctl status` due to the permissions that the install set itself. It's odd. – Rich_F Nov 15 '18 at 13:54
  • In original post. – Rich_F Nov 15 '18 at 14:06