Re: Putting the O/S user for "local" "peer" authentication in the "postgres" group vs chmod'ing the "pg*.conf" files to be readable by "all"

> david(dot)g(dot)johnston(at)gmail(dot)com wrote:
>
>> bryn(at)yugabyte(dot)com wrote:
>>
>> It would seem proper to put any user who you want to set up for "local", "peer" authentication into the "postgres" group
>
> Did you really mean to write that?
>
> The postgres o/s user should be able to login using peer. It is a one-way idea though. Wanting to login using peer says nothing about whether the user getting that capability should be allowed to mess with the running server in the operating system.
>
> As for the rest, all I see is that you are using an opinionated package manager to install software whose opinions you don't agree with. Maybe there is some buggy behavior with respect to shared o/s db administration among users in a common group...you haven't demonstrated that one way or the other here. I think it is pointless to have the o/s admin and postgres bootstrap user be anything but postgres and this whole thing is counter-productive. But if you are going down to first principles maybe you should install from source and build your own "package" from that.

I meant only to ask a question—and not to pre-judge anything. I should have thought more carefully about its wording. (I'm thinking specifically of a PG installation on Linux—and only that.)

About "opinionated package manager", I created my installation by following the steps described here:

https://wiki.postgresql.org/wiki/Apt

My aim is simply to conform to recommended practice. When I've said (like I believe that I have) that I didn't get a chance, during the flow, to specify <this or that>, my intention was only to note this fact and to imply that what I got without any intervention coincided with the recommended practice. This suits me.

I'm going to need to use a term to denote the O/S user that very often ends up with the name "postgres". I don't know what the official term is. I'll simply say "postgres" here. I haven't yet found an overview of the set of files that jointly implement a freshly installed PG system. But, of course, I've noticed where critical directories are. For example:

/usr/lib/postgresql/11/bin

Lots of familiar names, like "initdb" are found there. They all have owner/group "root". And they're all executable by "all". The point has been made that its not who owns them that matters but, rather, what the effective user ID is at execution time. I can therefore ignore these for the purpose of my present question.

In contrast, on (my) "data_directory" (I have just one) here:

/var/lib/postgresql/11/main

I see that every file has owner/group "postgres/postgres". The "owner" has all privileges. And each of "group" and "all" have no privileges. This doesn't seem at all surprising. But it does indicate that whatever privileges some critical file has (anywhere), the set has been determined by design.

There are several "*.conf" files on the same directory as the "config_file ". For me, it's this:

/etc/postgresql/11/main

These, too, all have owner/group "postgres/postgres" — and "postgres" has all meaningful privileges (presumably "execute" means nothing for a text config file). But "group” has only "read" on them all and "all" has no privileges except (I believe) for “postgresql.conf"—which is readable by all.

I mentioned that I'll presently re-do my PG installation from scratch. Then I'll record the permissions that all the config files are set up with and know for sure. I looked in this section:

20.2. File Locations
https://www.postgresql.org/docs/current/runtime-config-file-locations.html <https://www.postgresql.org/docs/current/runtime-config-file-locations.html>

But "permission" isn't found on the page.

Anyway, it's only natural to assume that privileges on all of the "postgres/postgres" files have been determined in accordance with a deliberate design. The docs tell me to edit certain of these files to achieve certain intended effects. I've done this—by authorizing as the "postgres" O/S user. So there's an inevitable risk (at least for me) that I might have changed some permissions accidentally.

I've observed that, for my present use case (enable "local", "peer" authentication for an O/S user other than "postgres), the authorization attempt fails unless that user can read one critical config file. I just did a careful test with the brand-new O/S user "bob". Here's what "id" shows:

id=1003(bob) gid=1003(bob) groups=1003(bob)
uid=1001(postgres) gid=1001(postgres) groups=1001(postgres),27(sudo),114(ssl-cert)

<aside>
I know that I've been told off for allowing "sudo" for "postgres". I'm only experimenting on my laptop. But I want to be able to stop the server, delete the datafiles, create a new cluster, and then start that using a single script. I can't use "pg_ctl stop/start" because it expects to find its config files on the data directory. (That's a different story. And I'm not ready to ask about that yet.) So I use "sudo systemctl stop/start postgresql" because this method looks in the right place for the config files.
</aside>

Here's what my "pg_hba.conf" has:

local all bob peer

My experiment shows that my attempt to authorize as "bob" using "local", "peer" authorization fails when every config file is readable only by "owner" and "group". And it shows that a sufficient fix is to make just "postgresql.conf" readable by "all" (as I believe is the intention). Of course, an alternative fix (in the sesne that it would work) would be to have "postgresql.conf" not readable by all but to put "bob" in the "postgres" group.

All this leads to an obvious question:

«
Given that all of the config files have been made readable by "group" (in contrast to the regime for the data files), what is the intention of this design? In other words, when is it proper to put an O/S user in the "postgres" group? After all, if the answer is "never" than no privileges on "postgres/postgres" files would ever have been granted to "group".
»