Re: Directory/File Access Permissions for COPY and Generic File Access Functions

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> If you are letting untrustworthy people read the postmaster log
> you've already got security problems, as per other recent
> threads.

That seems like a rather naive perspective. In a large operation
there are different roles. To draw some analogies, you might trust
your attorney with sensitive information, yet not trust her to
perform open heart surgery. You might trust your accountant with
sensitive financial data, yet not want to get into an airplane
piloted by him. If your electrician tried to fix your brakes you
might wind up careening down a hill out of control, while an
attempt by your mechanic to install a new circuit might see your
house in flames. A one-dimensional concept of "trustworthy" is
worse than useless; it's downright dangerous. Any security system
based on that is going to be weak.

>>> This feature is valuable because it is an alternative to allowing a
>>> user you don't trust *either* an OS login to the database server
>>> *or* a superuser database login. Can anyone suggest an exploit
>>> which would be available if we allowed someone who has permission
>>> to view all data in the database read permission to the pg_log
>>> directory and the files contained therein, assuming they do *not*
>>> have an OS login to the database server?
>
> Capture the postmaster log. Keep on capturing it till somebody
> fat-fingers their login to the extent of swapping the username and
> password (yeah, I've done that, haven't you?). Scrape password from
> the connection-failure log entry, figure out who it belongs to from
> the next successful login, away you go. Mean time to break-in might
> or might not be less than time to brute-force the MD5 you could've
> read from pg_authid.

At Wisconsin Courts we had people authorized to see all data in the
database and who had to support applications using the database.
They very frequently needed to look at the logs to diagnose
problems, and time was often of the essence. We wound up creating
crontab jobs to copy the log files off the database servers to
directories on the file servers where they could be examined, so
that operations could be kept running properly. The expose to the
problem you describe was, I would argue, *greater* with this
approach than if they could access the logs at need through a
database connection.

The fact that we write such things to the log is a serious problem
that we should fix, rather than pretending that anyone who has
access to the logs should be trustworthy enough not to impersonate
another user. That position is sure to leave us vulnerable to
security breaches, and keep PostgreSQL out of many high-security
environments.

> But in any case, I don't find the assumption that the user can
> already read everything in the database to correspond to an
> untrusted user, so I'm not sure what this exercise proves.

A one-dimensional trust model is not in any way appropriate for a
large-scale environment which cares about security. Heck, even a
"Principles of Accounting 101" class is sure involve a significant
discussion of the importance of the separation of duties. IMV it
becomes *more* significant in a computerized system, not less.

> Or in short: you really shouldn't give server-filesystem access to
> a user you have no trust in,

Again, a one-dimensional measure of "trustworthy" is naive and
without merit.

> and I'm unclear on what the use case would be for that even if we
> could restrict it reliably.

Auditors or application support staff are a couple.

> The use cases I can see for this are for DBAs to be able to do
> maintenance things remotely without using a full no-training-wheels
> superuser account.

That is not the sort of use case that I feel is the primary target
of this.

> ISTM that that type of use-case would be satisfied well enough
> --- not ideally, perhaps, but well enough --- by being able to
> grant full filesystem read and/or write to non-superuser accounts.

IMV, if we can't have a read-only version there's no real point.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company