Postgres user UID and GID

Postgres user UID and GID

[Martin Baulig]

[-- Attachment #1: Type: text/plain, Size: 2342 bytes --]

Hello,

I have a bit of an unusual setup, as I am running GNU Guix in a VM on a Synology NAS. Unfortunately, their DSM software sucks quite badly, but I am currently stuck with this hardware as I don't have the budget to replace it. But I don't want to make any long-term commitment to their software either.
One of the awesome features of GNU Guix is that it can nicely and easily be deployed elsewhere. However, for this to work, I need to be able to extract my data easily, so storing anything inside that VM that's not on GitLab isn't an option. I have decided to NFS-mount an encrypted shared folder from the NAS and store all data there.

This is working perfectly fine so far, but there is a tiny little problem with PostgreSQL:

The 'postgresql-service-type' contains an 'account-service-type' extension to create the 'postgres' database user - which is what you'd normally want to do.
But since I'd like to store all the actual data on that NFS share, the UID and GID of the 'postgres' user needs to match that of the server.
The UID and GID values on the server aren't going to change for as long as I'm using that hardware, but I might want to deploy GNU Guix to a second machine to have a test server and have it reliably boot and setup.

I'm currently using a temporary work-around by commenting out the 'postgresql-activation' in gnu/services/database.scm and creating the account via (operating-system (users ...)). This works, but is ugly, and I'd rather upstream this than depending on a local fork.

This morning, I decided to submit a patch, but then realized that there are two ways of doing this, and I'd like to get your feedback about which one you'd prefer. Or maybe there's a better solution for this?

- We could add (create-account? #f) to <postgresql-configuration>.
- We could add (uid #f) and (gid #f) - possibly in addition to (create-account? #f) there.

There is already 'data-directory' and 'log-directory', so I think it would make sense to add an option to specify a custom UID / GID for the service account.
I've looked at other services and there doesn't seem to be a consistent way of doing such thing; most services also won't need such option.

If you like either of these two approaches - or have a better idea - I will gladly submit a patch for it.

Looking forward to hearing back from you,

Martin

[-- Attachment #2: Type: text/html, Size: 3986 bytes --]

Re: Postgres user UID and GID

[Denis 'GNUtoo' Carikli]

[-- Attachment #1: Type: text/plain, Size: 2754 bytes --]

On Mon, 17 Jul 2023 18:06:04 +0000
Martin Baulig <martin@baulig.is> wrote:

> Hello,
Hi,

> I have a bit of an unusual setup, as I am running GNU Guix in a VM on
> a Synology NAS. Unfortunately, their DSM software sucks quite badly,
> but I am currently stuck with this hardware as I don't have the
> budget to replace it. But I don't want to make any long-term
> commitment to their software either. One of the awesome features of
> GNU Guix is that it can nicely and easily be deployed elsewhere.
> However, for this to work, I need to be able to extract my data
> easily, so storing anything inside that VM that's not on GitLab isn't
> an option. I have decided to NFS-mount an encrypted shared folder
> from the NAS and store all data there.
I've also a setup where I share a postgresql data folder between
various distributions.

In my case it's shared between Parabola i686, Parabola x86_64, Guix
i686 and Guix x86_64. And I often need to chroot inside Parabola so
this is why I need to use the same IDs (than Parabola).

So for keeping the same id across different distributions, I hardcoded
it in the users and groups like that in my system.scm:
>   (users (cons* [...]
>                 (user-account
>                   (name "postgres")
>                   (uid 88)
>                   (group "postgres")
>                   (comment "PostgreSQL user")
>                   (home-directory
> "/path/to/shared-dir/var/lib/postgres") (shell (file-append bash
> "/bin/bash")) (system? #t)) %base-user-accounts))

And in the groups too:
>   (groups (cons* [...]
>                  (user-group
>                    (name "postgres")
>                    (id 88)
>                    (system? #t))
>                  [...]
>                  %base-groups))

And for the record here's how I use a different architecture: I define
a package:
> (define postgresql-14-i686-linux
>   (package                    
>    (inherit postgresql-14)  
>    (name "postgresql-14-i686-linux")
>    (arguments
>     (ensure-keyword-arguments
>      (package-arguments postgresql-14)
>      '(#:system "i686-linux")))))

And then use it in the PostgreSQL service:
>                    (service
>                      postgresql-service-type
>                      (postgresql-configuration
>                       (postgresql
>                        (if (target-x86-64?)
>                            postgresql-14-i686-linux
>                            postgresql-14))
>                       [...]))

The downside is that it prints a warning during boot if I recall
well but it works fine.

I'm unsure if that helps the conversation or not though as you might
want something cleaner.

Denis.

[-- Attachment #2: OpenPGP digital signature --]
[-- Type: application/pgp-signature, Size: 833 bytes --]

Re: Postgres user UID and GID

[Martin Baulig]

Hello,

------- Original Message -------
On Monday, July 17th, 2023 at 7:49 PM, Denis 'GNUtoo' Carikli <GNUtoo@cyberdimension.org> wrote:


> And for the record here's how I use a different architecture: I define
> a package:
> 
> > (define postgresql-14-i686-linux
> > (package
> > (inherit postgresql-14)
> > (name "postgresql-14-i686-linux")
> > (arguments
> > (ensure-keyword-arguments
> > (package-arguments postgresql-14)
> > '(#:system "i686-linux")))))
> 
> 
> And then use it in the PostgreSQL service:
> 
> > (service
> > postgresql-service-type
> > (postgresql-configuration
> > (postgresql
> > (if (target-x86-64?)
> > postgresql-14-i686-linux
> > postgresql-14))
> > [...]))
> 
> 
> The downside is that it prints a warning during boot if I recall
> well but it works fine.
> 
> I'm unsure if that helps the conversation or not though as you might
> want something cleaner.

This is quite similar to what I have at the moment; I copied gnu/services/databases.scm locally and removed the account creation from it.  That works fine, but instead of maintaining my own copy of that file, I'd rather submit a patch to have this done nicely in the upstream version.

If I use the unmodified service and with the (operating-system (user ...)) entry, it works sometimes, but not reliably due to having two conflicting entries for the 'postgres' user.

Best regards,

Martin

Re: Postgres user UID and GID

[Denis 'GNUtoo' Carikli]

[-- Attachment #1: Type: text/plain, Size: 352 bytes --]

On Mon, 17 Jul 2023 21:35:00 +0000
Martin Baulig <martin@baulig.is> wrote:
> If I use the unmodified service and with the (operating-system (user
> ...)) entry, it works sometimes, but not reliably due to having two
> conflicting entries for the 'postgres' user.
I see. So if I touch too much my system it could break at any time then.

Denis.

[-- Attachment #2: OpenPGP digital signature --]
[-- Type: application/pgp-signature, Size: 833 bytes --]

Re: Postgres user UID and GID

[Martin Baulig]

Hello,

I played around a little bit with that, and it seems like you'll end up getting two entries for the
'postgres' user in your /etc/passwd if you just ignore the warning.  And the order doesn't seem to be
consistent.

Here in my current patch:
https://gitlab.com/martin-baulig/forks/guix/-/commit/5710b3bff17f4edaa7af397108e08f4dc842adef

And my current config is here:
https://gitlab.com/martin-baulig/config-and-setup/guix-packages/-/blob/work-postgres/packages/baulig/config/lothlorien/postgresql.scm#L25

I wasn't sure whether the (create-accout?) is really needed, probably not because you really need
that postgres service account.

Already wanted to remove that and submit a patch, but then got distracted by my new "secrets service".
I'm running some complex packages on GNU Guix - Bacula, Loki / Promtail and the GitLab Runner - and am
currently cleaning up things nicely, to possibly upstream some of that.

Best regards,

Martin

------- Original Message -------
On Tuesday, July 18th, 2023 at 10:10 PM, Denis 'GNUtoo' Carikli <GNUtoo@cyberdimension.org> wrote:


> 
> 
> On Mon, 17 Jul 2023 21:35:00 +0000
> Martin Baulig martin@baulig.is wrote:
> 
> > If I use the unmodified service and with the (operating-system (user
> > ...)) entry, it works sometimes, but not reliably due to having two
> > conflicting entries for the 'postgres' user.
> 
> I see. So if I touch too much my system it could break at any time then.
> 
> Denis.

Re: Postgres user UID and GID

[Felix Lechner via Development of GNU Guix and the GNU System distribution.]

Hi Martin,

On Mon, Jul 17, 2023 at 11:44 AM Martin Baulig <martin@baulig.is> wrote:
>
> I have decided to NFS-mount an encrypted shared folder

I use a similar setup and use Gocryptfs for encryption. How do you
encrypt, please?

> there is a tiny little problem with PostgreSQL:
> the UID and GID of the 'postgres' user needs to match that of the server.

Perhaps I do not understand the needs of your setup completely.
Doesn't 'idmapd' in NFSv4 address your conundrum effectively?

For performance reasons, I would discourage such a setup, though. It
would be better to run Postgres on the NAS, if it is an option, or to
replicate the database for backup purposes.

Kind regards
Felix

Re: Postgres user UID and GID

[Martin Baulig]

Hello,

I had considered idmap before, but realized there might be a bit of a chicken-egg problem with it.  Even though that likely doesn't actually exist because GNU Guix is smart enough about it, the circular dependency still feels weird:

What I mean is that the NFS client would depend on the existence of the 'postgres' user, to put it into the idmap file, and to resolve its UID / GID on service startup.

But the PostgreSQL service also depends on the NFS share already being mounted, so the postgres process can access its data directory.

The only clean solution I could think about is to create the account during system initialization via an explicit entry in (operating-system (users ...)).  But then I won't need idmap because I can just hard-code the UID and GID there.

About running the database on the server - unfortunately, there is no official package for Synology's DSM and I don't feel good about some third-party sites that only provide binaries.  They also made some custom changes to the Linux kernel and use some kind of custom libc - it's a nightmare to install anything on that thing!

And the "official" recommendation that you get on Reddit, Stack Overflow, etc. about running PostgreSQL on Synology DSM is to install a Docker image.

I figured running GNU Guix in a VM to be a much better choice than messing with a bunch of Docker images.

Best regards,

Martin

------- Original Message -------
On Monday, July 17th, 2023 at 8:23 PM, Felix Lechner <felix.lechner@lease-up.com> wrote:


> 
> 
> Hi Martin,
> 
> On Mon, Jul 17, 2023 at 11:44 AM Martin Baulig martin@baulig.is wrote:
> 
> > I have decided to NFS-mount an encrypted shared folder
> 
> 
> I use a similar setup and use Gocryptfs for encryption. How do you
> encrypt, please?
> 
> > there is a tiny little problem with PostgreSQL:
> > the UID and GID of the 'postgres' user needs to match that of the server.
> 
> 
> Perhaps I do not understand the needs of your setup completely.
> Doesn't 'idmapd' in NFSv4 address your conundrum effectively?
> 
> For performance reasons, I would discourage such a setup, though. It
> would be better to run Postgres on the NAS, if it is an option, or to
> replicate the database for backup purposes.
> 
> Kind regards
> Felix