PostgreSQL user permission setup for database tables access

Help
1

I’m trying to setup permissions for my user account using ensurePermissions. I want to grant permissions (ALL) to all tables from a database mydb to the user paluh (so I can perform some data manipulation operations from this account). I’ve tried this:

services.postgresql = {
  ensureUsers = [
    { name = "paluh";
      ensurePermissions = {
        "DATABASE mydb" = "ALL PRIVILEGES";
        "ALL SEQUENCES IN SCHEMA public" = "ALL";
        "ALL TABLES IN SCHEMA public" = "ALL";
      };
    }
  ];
};

I’ve found that this was wrong attempt (I’m getting ... permission denied for relation ...) from my side because the last two GRANT statements should be executed in the given database context. What I mean is that this imperative flow works fine and gives my user appropriate permissions:

$ sudo su - postgres
$ psql -d mydb

mydb =# GRANT ALL ON DATABASE mydb TO paluh;
mydb =# GRANT ALL ON ALL TABLES IN SCHEMA public TO paluh;
mydb =# GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO paluh;

because the last two statements are executed in the mydb context (without -d mydb this is not working).

I’m testing this against 19.09 (but it seems that on the master branch relevant pieces of the module are the same) and it seems that it is impossible to achive what I want using postgresql.nix module where we can find this code:

${concatStringsSep "\n" (mapAttrsToList (database: permission: ''
   $PSQL -tAc 'GRANT ${permission} ON ${database} TO "${user.name}"'
'') user.ensurePermissions)}

where

PSQL="${pkgs.sudo}/bin/sudo -u ${cfg.superUser} psql --port=${toString cfg.port}"

Am I’m missing something obvious? Could you please suggest me how to correctly setup these permissions?

2

the ensure* stuff for PG is not mature enough. There is an attempt to bring database specific options in [WIP]: PostgreSQL declarative databases by talyz · Pull Request #72365 · NixOS/nixpkgs · GitHub. I think, it can also host per-db permission configs.

If you want to include your permission settings to declarative config, you can hack on postStart script.

systemd.services.postgresql.postStart = lib.mkAfter ''
  $PSQL mydb -tAc 'GRANT ALL ON ALL TABLES IN SCHEMA public TO paluh' || true
  $PSQL mydb -tAc 'GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO paluh' || true
'';

(of top of my head, didn’t test, but I did use this)

The || true is optional

3

Thanks a lot for postStart suggestion @danbst - it works like a charm :wink:

4

P.S.
Really interesting discussion in this related thread. Thanks! I’m not sure if I should additionally spam there about my minor problem or if this autogenerated backlinking here comment is just enough…

5

I also just ran into this. The PR has been closed.

Would it make sense to go the simple route and just extend the ensure stuff with a database to connect to for putting the grant?