Обсуждение: Can we have a new SQL callable function to get Postmaster PID?

Hi,

Can we have a new function, say pg_postgres_pid(), to return
postmaster PID similar to pg_backend_pid()? At times, it will be
difficult to use OS level commands to get the postmaster pid of a
backend to which it is connected. It's even worse if we have multiple
postgres server instances running on the same system. I'm not sure
whether it's safe to expose postmaster pid this way, but it will be
useful at least for debugging purposes on say Windows or other
non-Linux platforms where it's a bit difficult to get process id.
Users can also look at the postmaster.pid file to figure out what's
the current postmaster pid, if not using OS level commands, but having
a SQL callable function makes life easier.

The function can look like this:
Datum
pg_postgres_pid(PG_FUNCTION_ARGS)
{
    PG_RETURN_INT32(PostmasterPid);
}

Thoughts?

With Regards,
Bharath Rupireddy.
EnterpriseDB: http://www.enterprisedb.com

Can we have a new function, say pg_postgres_pid(), to return

postmaster PID similar to pg_backend_pid()?

Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com> writes:
> Can we have a new function, say pg_postgres_pid(), to return
> postmaster PID similar to pg_backend_pid()?

I'm disinclined to think that this is a good idea from a security
perspective.  Maybe if it's superuser-only it'd be ok (since a
superuser would have other routes to discovering the value anyway).

            regards, tom lane

On 2/3/21 7:12 AM, Bharath Rupireddy wrote:
> Hi,
> 
> Can we have a new function, say pg_postgres_pid(), to return
> postmaster PID similar to pg_backend_pid()? At times, it will be
> difficult to use OS level commands to get the postmaster pid of a
> backend to which it is connected. It's even worse if we have multiple
> postgres server instances running on the same system. I'm not sure
> whether it's safe to expose postmaster pid this way, but it will be
> useful at least for debugging purposes on say Windows or other
> non-Linux platforms where it's a bit difficult to get process id.
> Users can also look at the postmaster.pid file to figure out what's
> the current postmaster pid, if not using OS level commands, but having
> a SQL callable function makes life easier.
> 
> The function can look like this:
> Datum
> pg_postgres_pid(PG_FUNCTION_ARGS)
> {
>     PG_RETURN_INT32(PostmasterPid);
> }
> 
> Thoughts?
> 

Curious question - why do you actually need PID of the postmaster? For
debugging, I'd say it's not quite necessary - you can just attach a
debugger to the backend and print the PostmasterPid directly. Or am I
missing something?


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 2/3/21 4:08 PM, Tom Lane wrote:
> Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com> writes:
>> Can we have a new function, say pg_postgres_pid(), to return
>> postmaster PID similar to pg_backend_pid()?
> 
> I'm disinclined to think that this is a good idea from a security
> perspective.  Maybe if it's superuser-only it'd be ok (since a
> superuser would have other routes to discovering the value anyway).
> 

Is the postmaster PID really sensitive? Users with OS access can just
list the processes, and for users without OS access / privileges it's
mostly useless, no?


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Tomas Vondra <tomas.vondra@enterprisedb.com> writes:
> On 2/3/21 4:08 PM, Tom Lane wrote:
>> I'm disinclined to think that this is a good idea from a security
>> perspective.  Maybe if it's superuser-only it'd be ok (since a
>> superuser would have other routes to discovering the value anyway).

> Is the postmaster PID really sensitive? Users with OS access can just
> list the processes, and for users without OS access / privileges it's
> mostly useless, no?

We disallow ordinary users from finding out the data directory location,
even though that should be equally useless to unprivileged users.  The
postmaster PID seems like the same sort of information.  It does not
seem like a non-administrator could have any but nefarious use for that
value.  (Admittedly, this argument is somewhat weakened by exposing
child processes' PIDs ... but you can't take down the whole installation
by zapping a child process.)

I'm basically in the same place you are in your other response: the
question to ask is not "why not allow this?", but "why SHOULD we allow
this?"

            regards, tom lane

On Thu, Feb 4, 2021 at 2:39 AM Tomas Vondra
<tomas.vondra@enterprisedb.com> wrote:
>
> On 2/3/21 7:12 AM, Bharath Rupireddy wrote:
> > Hi,
> >
> > Can we have a new function, say pg_postgres_pid(), to return
> > postmaster PID similar to pg_backend_pid()? At times, it will be
>
> Curious question - why do you actually need PID of the postmaster? For
> debugging, I'd say it's not quite necessary - you can just attach a
> debugger to the backend and print the PostmasterPid directly. Or am I
> missing something?

But sometimes we may also have to debug postmaster code, on different
platforms maybe. I don't know how the postmaster pid from the user
perspective will be useful in customer environments and I can't think
of other usages of the pg_postgres_pid().

With Regards,
Bharath Rupireddy.
EnterpriseDB: http://www.enterprisedb.com

On Thu, Feb 4, 2021 at 3:27 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Tomas Vondra <tomas.vondra@enterprisedb.com> writes:
> > On 2/3/21 4:08 PM, Tom Lane wrote:
> >> I'm disinclined to think that this is a good idea from a security
> >> perspective.  Maybe if it's superuser-only it'd be ok (since a
> >> superuser would have other routes to discovering the value anyway).
>
> > Is the postmaster PID really sensitive? Users with OS access can just
> > list the processes, and for users without OS access / privileges it's
> > mostly useless, no?
>
> We disallow ordinary users from finding out the data directory location,
> even though that should be equally useless to unprivileged users.  The
> postmaster PID seems like the same sort of information.  It does not
> seem like a non-administrator could have any but nefarious use for that
> value.  (Admittedly, this argument is somewhat weakened by exposing
> child processes' PIDs ... but you can't take down the whole installation
> by zapping a child process.)
>
> I'm basically in the same place you are in your other response: the
> question to ask is not "why not allow this?", but "why SHOULD we allow
> this?"

If we still think that the new function pg_postgres_pid() is useful in
some ways to the users or developers, then we can have it as a
superuser only function and error out for non-super users.

Thoughts?

With Regards,
Bharath Rupireddy.
EnterpriseDB: http://www.enterprisedb.com

On Thu, Feb 04, 2021 at 11:30:09AM +0530, Bharath Rupireddy wrote:
> But sometimes we may also have to debug postmaster code, on different
> platforms maybe. I don't know how the postmaster pid from the user
> perspective will be useful in customer environments and I can't think
> of other usages of the pg_postgres_pid().

I had the same question as Tomas in mind when reading this thread, and
the use case you are mentioning sounds limited to me.  Please note
that you can already get this information by using pg_read_file() on
postmaster.pid so I see no need for an extra function.
--
Michael