Обсуждение: pgsql: Adjust user-facing documentation to explain why we don't check

Log Message:
-----------
Adjust user-facing documentation to explain why we don't check pgpass
file permissions on Windows.

Modified Files:
--------------
    pgsql/doc/src/sgml:
        libpq.sgml (r1.232 -> r1.233)
        (http://developer.postgresql.org/cvsweb.cgi/pgsql/doc/src/sgml/libpq.sgml.diff?r1=1.232&r2=1.233)

Tom Lane wrote:
> Log Message:
> -----------
> Adjust user-facing documentation to explain why we don't check pgpass
> file permissions on Windows.
>

+On Microsoft Windows, the file is stored in a directory that is secure by
+default, so no special permissions check is made.



I doubt this is true if PGPASSFILE is set.


cheers

andrew

Andrew Dunstan wrote:
> Tom Lane wrote:
> > Log Message:
> > -----------
> > Adjust user-facing documentation to explain why we don't check pgpass
> > file permissions on Windows.
> >
>
> +On Microsoft Windows, the file is stored in a directory that is secure by
> +default, so no special permissions check is made.
>
>
>
> I doubt this is true if PGPASSFILE is set.

I thought it would be in the user's Application Data directory, or are
you saying they might put it somewhere else?

--
  Bruce Momjian  <bruce@momjian.us>          http://momjian.us
  EnterpriseDB                               http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Bruce Momjian wrote:
> Andrew Dunstan wrote:
>> Tom Lane wrote:
>>> Log Message:
>>> -----------
>>> Adjust user-facing documentation to explain why we don't check pgpass
>>> file permissions on Windows.
>>>
>> +On Microsoft Windows, the file is stored in a directory that is secure by
>> +default, so no special permissions check is made.
>>
>>
>>
>> I doubt this is true if PGPASSFILE is set.
>
> I thought it would be in the user's Application Data directory, or are
> you saying they might put it somewhere else?
>

PGPASSFILE takes a full path name, so you can put the file anywhere you
want. Just like on Unix.

//Magnus

Magnus Hagander wrote:
> Bruce Momjian wrote:
> > Andrew Dunstan wrote:
> >> Tom Lane wrote:
> >>> Log Message:
> >>> -----------
> >>> Adjust user-facing documentation to explain why we don't check pgpass
> >>> file permissions on Windows.
> >>>
> >> +On Microsoft Windows, the file is stored in a directory that is secure by
> >> +default, so no special permissions check is made.
> >>
> >>
> >>
> >> I doubt this is true if PGPASSFILE is set.
> >
> > I thought it would be in the user's Application Data directory, or are
> > you saying they might put it somewhere else?
> >
>
> PGPASSFILE takes a full path name, so you can put the file anywhere you
> want. Just like on Unix.

OK, so we _do_ need to check the permissions on pgpass on Win32, but we
just don't know how to do that?

--
  Bruce Momjian  <bruce@momjian.us>          http://momjian.us
  EnterpriseDB                               http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

>> PGPASSFILE takes a full path name, so you can put the file anywhere you
>> want. Just like on Unix.
>
> OK, so we _do_ need to check the permissions on pgpass on Win32, but we
> just don't know how to do that?
>

If we _need_ to check, I don't know. If you've set PGPASSFILE to
something, then you've made a decision to change from the default, and
it could be argued that we don't have to check for that. It can of
course equally well be argued that we should, yes.

Which would bring is to the "how". If there was an easy way to do the
how, we should probably do it. However, I'm very concerned that we will
break a whole lot more than we fix because the permissions system is
much more complex.

//Magnus

Bruce Momjian wrote:
> Andrew Dunstan wrote:
>
>> Tom Lane wrote:
>>
>>> Log Message:
>>> -----------
>>> Adjust user-facing documentation to explain why we don't check pgpass
>>> file permissions on Windows.
>>>
>>>
>> +On Microsoft Windows, the file is stored in a directory that is secure by
>> +default, so no special permissions check is made.
>>
>>
>>
>> I doubt this is true if PGPASSFILE is set.
>>
>
> I thought it would be in the user's Application Data directory, or are
> you saying they might put it somewhere else?
>
>

PGPASSFILE lets you put the file in any arbitrary location. If it's not
set then libpq looks in the default location. See
http://www.postgresql.org/docs/current/static/libpq-pgpass.html and
src/interfaces/libpq/fe-connect.c

We've had this since 8.1 IIRC - I've actually found it quite useful.

cheers

andrew

Bruce Momjian <bruce@momjian.us> writes:
> Magnus Hagander wrote:
>> PGPASSFILE takes a full path name, so you can put the file anywhere you
>> want. Just like on Unix.

> OK, so we _do_ need to check the permissions on pgpass on Win32, but we
> just don't know how to do that?

We could ignore PGPASSFILE on Windows, or restrict it to be in the
secure directory ...

            regards, tom lane

Magnus Hagander wrote:
>>> PGPASSFILE takes a full path name, so you can put the file anywhere you
>>> want. Just like on Unix.
>> OK, so we _do_ need to check the permissions on pgpass on Win32, but we
>> just don't know how to do that?
>>
>
> If we _need_ to check, I don't know. If you've set PGPASSFILE to
> something, then you've made a decision to change from the default, and
> it could be argued that we don't have to check for that. It can of
> course equally well be argued that we should, yes.

Not necessarily - wasn't that one of the suggestions given to Tony
during our recent disagreement on pgpass files? Users may not realise
their app is setting PGPASSFILE.

> Which would bring is to the "how". If there was an easy way to do the
> how, we should probably do it. However, I'm very concerned that we will
> break a whole lot more than we fix because the permissions system is
> much more complex.

I think the only thing you could do would be to specify that the user
and only the user have full control over the file. *Any* other ACL
entries, deny or allow, are not allowed. Access via a group is not allowed.

Now the next problem is how this should be set on Home Editions which do
their best to hide ACLs from the user. I suppose we could just document
the correct cacls command line to get exactly the acl we want.

Regards, Dave.

Dave Page wrote:
> Magnus Hagander wrote:
>>>> PGPASSFILE takes a full path name, so you can put the file anywhere you
>>>> want. Just like on Unix.
>>> OK, so we _do_ need to check the permissions on pgpass on Win32, but we
>>> just don't know how to do that?
>>>
>> If we _need_ to check, I don't know. If you've set PGPASSFILE to
>> something, then you've made a decision to change from the default, and
>> it could be argued that we don't have to check for that. It can of
>> course equally well be argued that we should, yes.
>
> Not necessarily - wasn't that one of the suggestions given to Tony
> during our recent disagreement on pgpass files? Users may not realise
> their app is setting PGPASSFILE.

Well, if you don't trust your app, why are you running it ;-)


>> Which would bring is to the "how". If there was an easy way to do the
>> how, we should probably do it. However, I'm very concerned that we will
>> break a whole lot more than we fix because the permissions system is
>> much more complex.
>
> I think the only thing you could do would be to specify that the user
> and only the user have full control over the file. *Any* other ACL
> entries, deny or allow, are not allowed. Access via a group is not allowed.

That will break every default install in the world. They will all
contain at least ACLs for Administrators and SYSTEM. If they're in a
domain, also the admins from the domain. Not sure about power users. And
in a domain, it's not uncommon at all to push down a group of people in
IT who have access to users profiles to fix things. Etc.


> Now the next problem is how this should be set on Home Editions which do
> their best to hide ACLs from the user. I suppose we could just document
> the correct cacls command line to get exactly the acl we want.

I seriously don't think that will ever work, if we're broken on the
*default install*. If we're fine on default, and someone has changed it,
then  they can likely fix it if they have the instructions. But if we
break the default install, we're out.

//Magnus

I updated the documentation to say:

    On Microsoft Windows, it is assumed that the file is stored in a
    directory that is secure, so no special permissions check is made.

This might be as good as we can get.

---------------------------------------------------------------------------

Magnus Hagander wrote:
> Dave Page wrote:
> > Magnus Hagander wrote:
> >>>> PGPASSFILE takes a full path name, so you can put the file anywhere you
> >>>> want. Just like on Unix.
> >>> OK, so we _do_ need to check the permissions on pgpass on Win32, but we
> >>> just don't know how to do that?
> >>>
> >> If we _need_ to check, I don't know. If you've set PGPASSFILE to
> >> something, then you've made a decision to change from the default, and
> >> it could be argued that we don't have to check for that. It can of
> >> course equally well be argued that we should, yes.
> >
> > Not necessarily - wasn't that one of the suggestions given to Tony
> > during our recent disagreement on pgpass files? Users may not realise
> > their app is setting PGPASSFILE.
>
> Well, if you don't trust your app, why are you running it ;-)
>
>
> >> Which would bring is to the "how". If there was an easy way to do the
> >> how, we should probably do it. However, I'm very concerned that we will
> >> break a whole lot more than we fix because the permissions system is
> >> much more complex.
> >
> > I think the only thing you could do would be to specify that the user
> > and only the user have full control over the file. *Any* other ACL
> > entries, deny or allow, are not allowed. Access via a group is not allowed.
>
> That will break every default install in the world. They will all
> contain at least ACLs for Administrators and SYSTEM. If they're in a
> domain, also the admins from the domain. Not sure about power users. And
> in a domain, it's not uncommon at all to push down a group of people in
> IT who have access to users profiles to fix things. Etc.
>
>
> > Now the next problem is how this should be set on Home Editions which do
> > their best to hide ACLs from the user. I suppose we could just document
> > the correct cacls command line to get exactly the acl we want.
>
> I seriously don't think that will ever work, if we're broken on the
> *default install*. If we're fine on default, and someone has changed it,
> then  they can likely fix it if they have the instructions. But if we
> break the default install, we're out.
>
> //Magnus
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
>                 http://www.postgresql.org/about/donate

--
  Bruce Momjian  <bruce@momjian.us>          http://momjian.us
  EnterpriseDB                               http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Magnus Hagander wrote:

> Well, if you don't trust your app, why are you running it ;-)

Theres a world of difference between trusting your app and knowing what
it's doing.

>>> Which would bring is to the "how". If there was an easy way to do the
>>> how, we should probably do it. However, I'm very concerned that we will
>>> break a whole lot more than we fix because the permissions system is
>>> much more complex.
>> I think the only thing you could do would be to specify that the user
>> and only the user have full control over the file. *Any* other ACL
>> entries, deny or allow, are not allowed. Access via a group is not allowed.
>
> That will break every default install in the world. They will all
> contain at least ACLs for Administrators and SYSTEM. If they're in a
> domain, also the admins from the domain. Not sure about power users. And
> in a domain, it's not uncommon at all to push down a group of people in
> IT who have access to users profiles to fix things. Etc.

Yes - and not knowing who is/should be in the default ACL is exactly the
problem.

The only thing that will *break* though is libpq which would do the same
thing as it would on *nix if the mode was 0622 or whatever. It's not
going to break Windows or the users profile if the ACL on their pgpass
file is tightened up.

>> Now the next problem is how this should be set on Home Editions which do
>> their best to hide ACLs from the user. I suppose we could just document
>> the correct cacls command line to get exactly the acl we want.
>
> I seriously don't think that will ever work, if we're broken on the
> *default install*. If we're fine on default, and someone has changed it,
> then  they can likely fix it if they have the instructions. But if we
> break the default install, we're out.

huh?

/D

Dave Page wrote:
> I think the only thing you could do would be to specify that the user
> and only the user have full control over the file. *Any* other ACL
> entries, deny or allow, are not allowed. Access via a group is not allowed.
>


Are these conditions true of the default location? If not, then either
this list is too restrictive or the comment we just committed is wrong
even for that location.

I think the permissions on the default are probably good enough, so we
should look for a similar ACL.

cheers

andrew

>>> I think the only thing you could do would be to specify that the user
>>> and only the user have full control over the file. *Any* other ACL
>>> entries, deny or allow, are not allowed. Access via a group is not allowed.
>> That will break every default install in the world. They will all
>> contain at least ACLs for Administrators and SYSTEM. If they're in a
>> domain, also the admins from the domain. Not sure about power users. And
>> in a domain, it's not uncommon at all to push down a group of people in
>> IT who have access to users profiles to fix things. Etc.
>
> Yes - and not knowing who is/should be in the default ACL is exactly the
> problem.
>
> The only thing that will *break* though is libpq which would do the same
> thing as it would on *nix if the mode was 0622 or whatever. It's not
> going to break Windows or the users profile if the ACL on their pgpass
> file is tightened up.

It would break insofar that it wouldn't work. the pgpass file. It will
of course not break *windows*, but people will consider PostgreSQL broken.


>>> Now the next problem is how this should be set on Home Editions which do
>>> their best to hide ACLs from the user. I suppose we could just document
>>> the correct cacls command line to get exactly the acl we want.
>> I seriously don't think that will ever work, if we're broken on the
>> *default install*. If we're fine on default, and someone has changed it,
>> then  they can likely fix it if they have the instructions. But if we
>> break the default install, we're out.
>
> huh?

If we don't work with a default install, people will not use it. We get
enough complaints of the run-as-admin stuff, and that only breaks if the
user has changed things away from the default.

//Magnus

Andrew Dunstan wrote:
> Dave Page wrote:
>> I think the only thing you could do would be to specify that the user
>> and only the user have full control over the file. *Any* other ACL
>> entries, deny or allow, are not allowed. Access via a group is not
>> allowed.
>>
>
>
> Are these conditions true of the default location? If not, then either
> this list is too restrictive or the comment we just committed is wrong
> even for that location.

No, as Magnus has pointed out there are almost certainly adminstrator
and system entries in the default ACL - and we don't necessarily know
the local installation well enough to be able to tell if an insecure
entry has been added.

> I think the permissions on the default are probably good enough, so we
> should look for a similar ACL.

We could make that assumption in the the default location, and require a
strict ACL elsewhere.

Regards, Dave.

Magnus Hagander wrote:
>
> It would break insofar that it wouldn't work. the pgpass file. It will
> of course not break *windows*, but people will consider PostgreSQL broken.
>

Please explain why it won't work. *Existing* pgpass files would need to
be secured, but that could be handled with documentation and suitable
checks and hints in pgAdmin/libpq. New pgpass files created by pgAdmin
(or other apps) would simply need to be secured by those apps at
creation time.

/D

Dave Page wrote:
> Magnus Hagander wrote:
>> It would break insofar that it wouldn't work. the pgpass file. It will
>> of course not break *windows*, but people will consider PostgreSQL broken.
>>
>
> Please explain why it won't work. *Existing* pgpass files would need to
> be secured, but that could be handled with documentation and suitable
> checks and hints in pgAdmin/libpq. New pgpass files created by pgAdmin
> (or other apps) would simply need to be secured by those apps at
> creation time.

Files created using notepad (or whatever) will not work. Unless you
manually fiddle around with the permissions.

//Magnus

Magnus Hagander wrote:
> Dave Page wrote:
>> Magnus Hagander wrote:
>>> It would break insofar that it wouldn't work. the pgpass file. It will
>>> of course not break *windows*, but people will consider PostgreSQL broken.
>>>
>> Please explain why it won't work. *Existing* pgpass files would need to
>> be secured, but that could be handled with documentation and suitable
>> checks and hints in pgAdmin/libpq. New pgpass files created by pgAdmin
>> (or other apps) would simply need to be secured by those apps at
>> creation time.
>
> Files created using notepad (or whatever) will not work. Unless you
> manually fiddle around with the permissions.

If you are savvy enough to read the docs, un-hide the directory, and
manually create the file in the correct format, surely you can also run
cacls on it? We expect *nix users to run chmod on theirs after all.

I don't see this as such a big issue.

Regards, Dave.

Dave Page wrote:
> If you are savvy enough to read the docs, un-hide the directory, and
> manually create the file in the correct format, surely you can also run
> cacls on it? We expect *nix users to run chmod on theirs after all.
>
> I don't see this as such a big issue.
>
>


I think this is OK if we also go with your other suggestion of trusting
the default location, which will cover 95% of uses anyway.

cheers

andrew