"root" execution of the PostgreSQL server is not permitted

Question

When I try to start postgresql I get an error:

postgres

postgres does not know where to find the server configuration file.
You must specify the --config-file or -D invocation option or set the PGDATA environment variable.

So then I try to set my config file:

postgres -D /usr/local/var/postgres

And I get the following error:

postgres cannot access the server configuration file "/usr/local/var/postgres/postgresql.conf": Permission denied

Hmm okay. Next, I try to perform that same action as an admin:

 sudo postgres -D /usr/local/var/postgres

And I receive the following error:

"root" execution of the PostgreSQL server is not permitted.
The server must be started under an unprivileged user ID to prevent possible system security compromise. See the documentation for more information on how to properly start the server.

I googled around for that error message but cannot find a solution.
Can anyone provide some insight into this?

Answer 1

For those trying to run custom command using the official docker image, use the following command. docker-entrypoint.sh handles switching the user and handling other permissions.

docker-entrypoint.sh -c 'shared_buffers=256MB' -c 'max_connections=200'

Answer 2

Your command does not do what you think it does. To run something as system user postgres:

 sudo -u postgres command

To run the command (also named postgres!):

sudo -u postgres postgres -D /usr/local/var/postgres

Your command does the opposite:

sudo postgres -D /usr/local/var/postgres

It runs the program postgres as the superuser root (sudo without -u switch), and Postgres does not allow to be run with superuser privileges for security reasons. Hence the error message.

If you are going to run a couple of commands as system user postgres, change the user with:

sudo -u postgres -i

... and exit when you are done.

• PostgreSQL error: Fatal: role "username" does not exist

If you see this error message while operating as system user postgres, then something is wrong with permissions on the file or one of the containing directories.

postgres cannot access the server configuration file "/usr/local/var/postgres/postgresql.conf": Permission denied /usr/local/var/postgres/postgresql.conf

Consider instruction in the Postgres manual.
Also consider the wrapper pg_ctl - or pg_ctlcluster in Debian-based distributions.
And know the difference between su and sudo. Related:

• PostgreSQL error: Fatal: role "username" does not exist

Answer 3

The answer of Muthukumar is the best !! After all day searching by the more simple way of change my Alpine Postgres deployment in Kubernetes, I found this simple answer.

There is my complete description. Enjoy it !!

First I need to create/define a ConfigMap with correct values. Save in the file "custom-postgresql.conf":

# DB Version: 12
# OS Type: linux
# DB Type: oltp
# Total Memory (RAM): 16 GB
# CPUs num: 4
# Connections num: 9999
# Data Storage: ssd
# https://pgtune.leopard.in.ua/#/
# 2020-10-29
listen_addresses = '*'
max_connections = 9999
shared_buffers = 4GB
effective_cache_size = 12GB
maintenance_work_mem = 1GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 209kB
min_wal_size = 2GB
max_wal_size = 8GB
max_worker_processes = 4
max_parallel_workers_per_gather = 2
max_parallel_workers = 4
max_parallel_maintenance_workers = 2

Create the Config/Map:

kubectl create configmap custom-postgresql-conf --from-file=custom-postgresql.conf

Please, take care that the values in custom settings are defined according to the Pod resources, mainly by memory and CPU assignments.

There is the manifest (postgres.yml):

---
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: postgres-pvc
  namespace: default
spec:
  accessModes: [ "ReadWriteOnce" ]
  resources:
    requests:
      storage: 128Gi
---
apiVersion: v1
kind: Service
metadata:
  name: postgres
  namespace: default
spec:
  type: ClusterIP
  selector:
    app: postgres
    tier: core
  ports:
    - name: port-5432-tcp
      port: 5432

---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: postgres
  namespace: default
spec:
  replicas: 1
  selector:
    matchLabels:
      app: postgres
      tier: core
  template:
    metadata:
      labels:
        app: postgres
        tier: core
    spec:
      restartPolicy: Always
      terminationGracePeriodSeconds: 30
      volumes:
        - name: postgres-storage
          persistentVolumeClaim:
            claimName: postgres-pvc
        - name: postgresql-conf
          configMap:
            name: postgresql-conf
            items:
              - key: custom-postgresql.conf
                path: postgresql.conf
      containers:
        - name: postgres
          image: postgres:12-alpine
          resources:
            requests:
              memory: 128Mi
              cpu: 600m
            limits:
              memory: 16Gi
              cpu: 1500m
          readinessProbe:
            exec:
              command:
                - "psql"
                - "-w"
                - "-U"
                - "postgres"
                - "-d"
                - "postgres"
                - "-c"
                - "SELECT 1"
            initialDelaySeconds: 15
            timeoutSeconds: 2
          livenessProbe:
            exec:
              command:
                - "psql"
                - "-w"
                - "postgres"
                - "-U"
                - "postgres"
                - "-d"
                - "postgres"
                - "-c"
                - "SELECT 1"
            initialDelaySeconds: 45
            timeoutSeconds: 2
          imagePullPolicy: IfNotPresent
          # this was the problem !!!
          # I found the solution here: https://stackoverflow.com/questions/28311825/root-execution-of-the-postgresql-server-is-not-permitted
          command: [ "docker-entrypoint.sh", "-c", "config_file=/etc/postgresql/postgresql.conf" ]
          ports:
            - containerPort: 5432
          volumeMounts:
            - name: postgres-storage
              mountPath: /var/lib/postgresql/data
              subPath: postgresql
            - name: postgresql-conf
              mountPath: /etc/postgresql/postgresql.conf
              subPath: postgresql.conf
          env:
            - name: POSTGRES_USER
              valueFrom:
                secretKeyRef:
                  name: etldatasore-username
                  key: ETLDATASTORE__USERNAME
            - name: POSTGRES_DB
              valueFrom:
                secretKeyRef:
                  name: etldatasore-database
                  key: ETLDATASTORE__DATABASE
            - name: POSTGRES_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: etldatasore-password
                  key: ETLDATASTORE__PASSWORD

You can apply with

kubectl apply -f postgres.yml

Go to your pod and check for applied settings:

kubectl get pods
kubectl exec -it postgres-548f997646-6vzv2 bash

bash-5.0# su - postgres
postgres-548f997646-6vzv2:~$ psql

postgres=# show config_file;
           config_file
---------------------------------
 /etc/postgresql/postgresql.conf
(1 row)

postgres=#

# if you want to check all custom settings, do
postgres=# SHOW ALL;

Thank you Muthukumar !!

Please, try yourself, validate, and share !!!

Answer 4

I faced this issue in 2 areas : docker-compose & kubernetes deployment.

To solve it on docker-compose it was enough to edit the command arg :

services:
  db:
    image: postgres
    ports: 
      - 5432:5432
    command: postgres -c max_connections=200

For the kubernetes deployment, I had to pass the configuration that we want to set (max_connection) as the args in the deployment, e.g :

args: ["-c", "max_connections=200"]

Answer 5

Terminal returns postgres command not found.

Unable to update postgresql