How to setup postgresql to have user root as postgres

Guides
1

Hi,

I’ve been trying this for a long time but couldn’t make it work.
I want to be able to run sudo psql as if psql -U postgres.
I’ve tried to use

# TYPE    DATABASE        USER            ADDRESS                 METHOD
local         all                       postgres                                          peer map=root

where

# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
root                      root                                       postgres

but it doesn’t work…

the error is:

● postgresql.service - PostgreSQL Server
   Loaded: loaded (/nix/store/x7ffh3hgggla7g7s08dg1r7rr5ym6pzf-unit-postgresql.service/postgresql.service; enabled; vendor preset: enabled)
   Active: failed (Result: timeout) since Sat 2020-02-29 21:46:12 CET; 56ms ago
  Process: 764 ExecStartPre=/nix/store/78q3l3majyc42d0cn7ffmlajys9g25nq-unit-script-postgresql-pre-start (code=exited, status=0/SUCCESS)
  Process: 765 ExecStart=/nix/store/fbhq6m6h1278z5w9vycpjb13rkgsf062-unit-script-postgresql-start (code=exited, status=0/SUCCESS)
  Process: 778 ExecStartPost=/nix/store/nikim8yvhhmbwl7hkm7d5s4rphc8m0m5-unit-script-postgresql-post-start (code=exited, status=1/FAILURE)
 Main PID: 765 (code=exited, status=0/SUCCESS)
       IP: 18.9K in, 18.9K out
      CPU: 19.062s

Feb 29 21:46:12 nt fbhq6m6h1278z5w9vycpjb13rkgsf062-unit-script-postgresql-start[765]: 2020-02-29 20:46:12.524 GMT [774] WARNING:  archiving write-ahead log file "00000001000000000000000C" failed too many times, will try again later
Feb 29 21:46:12 nt fbhq6m6h1278z5w9vycpjb13rkgsf062-unit-script-postgresql-start[765]: 2020-02-29 20:46:12.526 GMT [765] LOG:  database system is shut down
Feb 29 21:46:12 nt sudo[5953]:     root : TTY=unknown ; PWD=/ ; USER=postgres ; COMMAND=/nix/store/6s14rkwklrmsnp2xjwpi0w66rs4zgglb-postgresql-11.7/bin/psql --port=5432 -d postgres -c
Feb 29 21:46:12 nt sudo[5953]: pam_unix(sudo:session): session opened for user postgres by (uid=0)
Feb 29 21:46:12 nt sudo[5953]: pam_unix(sudo:session): session closed for user postgres
Feb 29 21:46:12 nt nikim8yvhhmbwl7hkm7d5s4rphc8m0m5-unit-script-postgresql-post-start[778]: /nix/store/nikim8yvhhmbwl7hkm7d5s4rphc8m0m5-unit-script-postgresql-post-start: line 5: kill: (765) - No such process
Feb 29 21:46:12 nt systemd[1]: postgresql.service: Control process exited, code=exited, status=1/FAILURE
Feb 29 21:46:12 nt systemd[1]: postgresql.service: Failed with result 'timeout'.
Feb 29 21:46:12 nt systemd[1]: Failed to start PostgreSQL Server.
Feb 29 21:46:12 nt systemd[1]: postgresql.service: Consumed 19.062s CPU time, received 18.9K IP traffic, sent 18.9K IP traffic.
warning: error(s) occurred while switching to the new configuration
1 Like
2

OK, since you aren’t seeing any responses I will give it a shot.

The syntax for this is:

sudo -u postgres psql

That being said, don’t put that in a service file. If you want the service to run as a different user. You can specify that in the service with the User and Group settings. If you built the service declaratively in nixos, you can set that in systemd.services.<servicename>.serviceConfig.

Better yet, if you give us more information or a copy of the config you are using, we could probably provide better help.

1 Like
3

There is little misunderstanding here. Ident map (where you specify that root may login as postgres) doesn’t mean sudo psql will make root login as postgres. It only “allows” to do that. You can’t tweak postgresql config to login root as postgres by default.

What your config does, is allows sudo psql -U postgres (instead of sudo -u postgres psql).

PS. Your postgresql doesn’t starts probably because you forgot to add ident mapping for postgres user (for sudo -u postgres psql case). This is used in postStart script:

    services.postgresql = {
      authentication = ''
        local all   postgres       peer map=eroot
      '';
      identMap = ''
        eroot     root      postgres
        eroot     postgres  postgres
      '';
    };
2 Likes
4

I see. thank you very much for your detailed explanation!
Now I have much better understanding about Postgres on NixOS.

1 Like