Only owners can ANALYZE tables...seems overly restrictive
Given the amount of damage a person with write access to a table can get
into it seems pointless to not allow them to analyze the table after their
updates - since best practices would say that normal work with a table
should not be performed by an owner.
I should the check for whether a given user can or cannot analyze a table
should be whether the user has INSERT, UPDATE, or DELETE privileges.
I suppose row-level-security might come into play here...
David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
Given the amount of damage a person with write access to a table can get
into it seems pointless to not allow them to analyze the table after their
updates - since best practices would say that normal work with a table
should not be performed by an owner.
I should the check for whether a given user can or cannot analyze a table
should be whether the user has INSERT, UPDATE, or DELETE privileges.
By that argument, we should allow anyone with any write access to do
TRUNCATE. Or perhaps even DROP TABLE. I'm not impressed.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Sun, Feb 28, 2016 at 9:58 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
Given the amount of damage a person with write access to a table can get
into it seems pointless to not allow them to analyze the table aftertheir
updates - since best practices would say that normal work with a table
should not be performed by an owner.I should the check for whether a given user can or cannot analyze a table
should be whether the user has INSERT, UPDATE, or DELETE privileges.By that argument, we should allow anyone with any write access to do
TRUNCATE. Or perhaps even DROP TABLE. I'm not impressed.
TRUNCATE indeed also seems overly restrictive. But in any case we have a
GRANT for TRUNCATE. If you are saying that you'd be OK with adding a GRANT
for ANALYZE that would probably suffice.
I'd place DROP TABLE into a different category simply because it alters the
design of the database - some only owners in the database should be allowed
to do. We also don't tell people to "run DROP TABLE" so that the planner
can choose better query plans. We do tell people that after inserting or
deleting lots of data they should run ANALYZE to ensure subsequent queries
have good info to work with. Telling them to pray that (and/or wait an
indefinite period of time for) the auto-vacuum process ran - or to call
their DBA - it not a good solution to that problem.
As much as I respect your opinion I was hoping for something less trite.
ANALYZE, even on a large table (though statistics target would influence
this), is seemingly fast due to the random sampling. It also operates
fully concurrently with other activity.
David J.
On 2/28/2016 8:58 PM, Tom Lane wrote:
I should the check for whether a given user can or cannot analyze a table
should be whether the user has INSERT, UPDATE, or DELETE privileges.By that argument, we should allow anyone with any write access to do
TRUNCATE. Or perhaps even DROP TABLE. I'm not impressed.
I don't see why anyone with delete privileges shouldn't be able to
truncate (after all, thats the same as deleting all records).
analyze has arguably fewer side effects, its a performance enhancement,
its neither altering the schema or changing the data.
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 2/28/16, John R Pierce <pierce@hogranch.com> wrote:
I don't see why anyone with delete privileges shouldn't be able to
truncate (after all, thats the same as deleting all records).
Firstly, because you can prevent deleting some rows by a trigger;
TRUNCATE doesn't deal with rows.
Secondary, TRUNCATE is _NOT_ MVCC. Even in a not yet finished
transaction other connections can see empty table.
Thirdly, TRUNCATE is often used for clearing most of (or even all) DB
tables for tests. Splitting privileges is one of possible protection
for running tests on a prod server (if config files are copied
wrongly).
--
john r pierce, recycling bits in santa cruz
--
Best regards,
Vitaly Burovoy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
John R Pierce wrote:
On 2/28/2016 8:58 PM, Tom Lane wrote:
I should the check for whether a given user can or cannot analyze a table
should be whether the user has INSERT, UPDATE, or DELETE privileges.
By that argument, we should allow anyone with any write access to do
TRUNCATE. Or perhaps even DROP TABLE. I'm not impressed.
I don't see why anyone with delete privileges shouldn't be able to
truncate (after all, thats the same as deleting all records).analyze has arguably fewer side effects, its a performance enhancement,
its neither altering the schema or changing the data.
In a production environment you don't want a user to change your table
statistics.
They could just set default_statistics_target to something stupid,
run ANALYZE and wreck the statistics for everyone.
And then come back to the DBA and complain that things don't work.
We have a policy that users are not table owners, and with the
current behaviour we can be certain that any bad table statistics
are the fault of the DBA or wrong configuration.
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
* David G. Johnston (david.g.johnston@gmail.com) wrote:
Given the amount of damage a person with write access to a table can get
into it seems pointless to not allow them to analyze the table after their
updates - since best practices would say that normal work with a table
should not be performed by an owner.I should the check for whether a given user can or cannot analyze a table
should be whether the user has INSERT, UPDATE, or DELETE privileges.
Realistically, ANALYZE is a background/maintenance task that autovacuum
should be handling for you.
I suppose row-level-security might come into play here...
Yes, you may only have access to a subset of the table.
If we had plenty more bits to allow ANALYZE to be independently
GRANT'able, then maybe, but those are a limited resource.
Thanks!
Stephen
2016-02-29 14:31 GMT+01:00 Stephen Frost <sfrost@snowman.net>:
* David G. Johnston (david.g.johnston@gmail.com) wrote:
Given the amount of damage a person with write access to a table can get
into it seems pointless to not allow them to analyze the table aftertheir
updates - since best practices would say that normal work with a table
should not be performed by an owner.I should the check for whether a given user can or cannot analyze a table
should be whether the user has INSERT, UPDATE, or DELETE privileges.Realistically, ANALYZE is a background/maintenance task that autovacuum
should be handling for you.
Realistically, that can't happen every time. Think of temporary tables for
example...
I suppose row-level-security might come into play here...
Yes, you may only have access to a subset of the table.
If we had plenty more bits to allow ANALYZE to be independently
GRANT'able, then maybe, but those are a limited resource.
Agreed.
--
Guillaume.
http://blog.guillaume.lelarge.info
http://www.dalibo.com
On 02/29/2016 03:15 PM, Guillaume Lelarge wrote:
2016-02-29 14:31 GMT+01:00 Stephen Frost <sfrost@snowman.net
<mailto:sfrost@snowman.net>>:Realistically, ANALYZE is a background/maintenance task that autovacuum
should be handling for you.Realistically, that can't happen every time. Think of temporary tables
for example...
Hmm. How are you not the owner of a temporary table?
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
2016-02-29 15:20 GMT+01:00 Vik Fearing <vik@2ndquadrant.fr>:
On 02/29/2016 03:15 PM, Guillaume Lelarge wrote:
2016-02-29 14:31 GMT+01:00 Stephen Frost <sfrost@snowman.net
<mailto:sfrost@snowman.net>>:Realistically, ANALYZE is a background/maintenance task that
autovacuum
should be handling for you.
Realistically, that can't happen every time. Think of temporary tables
for example...Hmm. How are you not the owner of a temporary table?
Oh, you obviously are :)
--
Guillaume.
http://blog.guillaume.lelarge.info
http://www.dalibo.com
On Mon, Feb 29, 2016 at 6:31 AM, Stephen Frost <sfrost@snowman.net> wrote:
* David G. Johnston (david.g.johnston@gmail.com) wrote:
Given the amount of damage a person with write access to a table can get
into it seems pointless to not allow them to analyze the table aftertheir
updates - since best practices would say that normal work with a table
should not be performed by an owner.I should the check for whether a given user can or cannot analyze a table
should be whether the user has INSERT, UPDATE, or DELETE privileges.Realistically, ANALYZE is a background/maintenance task that autovacuum
should be handling for you.
Then my recent experience of adding a bunch of records and having the
subsequent select query take forever because the table wasn't analyzed is
not supposed to happen? What am I doing wrong then that autovacuum didn't
run for me?
I suppose row-level-security might come into play here...
Yes, you may only have access to a subset of the table.
TBH, since you cannot see the data being analyzed I don't see a security
implication here if you allow someone to ANALYZE the whole table even when
RLS is in place.
If we had plenty more bits to allow ANALYZE to be independently
GRANT'able, then maybe, but those are a limited resource.
The planner and system performance seems important enough to give it such
a resource. But as I stated initially I personally believe that a user
with INSERT/DELETE/UPDATE permissions on a table (maybe require all three)
should also be allowed to ANALYZE said table.
David J.
On Mon, Feb 29, 2016 at 2:52 AM, Albe Laurenz <laurenz.albe@wien.gv.at>
wrote:
John R Pierce wrote:
On 2/28/2016 8:58 PM, Tom Lane wrote:
I should the check for whether a given user can or cannot analyze a
table
should be whether the user has INSERT, UPDATE, or DELETE privileges.
By that argument, we should allow anyone with any write access to do
TRUNCATE. Or perhaps even DROP TABLE. I'm not impressed.I don't see why anyone with delete privileges shouldn't be able to
truncate (after all, thats the same as deleting all records).analyze has arguably fewer side effects, its a performance enhancement,
its neither altering the schema or changing the data.In a production environment you don't want a user to change your table
statistics.They could just set default_statistics_target to something stupid,
run ANALYZE and wreck the statistics for everyone.
And then come back to the DBA and complain that things don't work.We have a policy that users are not table owners, and with the
current behaviour we can be certain that any bad table statistics
are the fault of the DBA or wrong configuration.
Setting default_statistics_target and running ANALYZE are two entirely
different things.
As it stands, from the standpoint of a DBA, it isn't much different for
autovacuum to run ANALYZE compared to having a user run ANALYZE. In both
cases the DBA's job is to ensure that the act of running ANALYZE is
properly configured. All this does is allows an informed user to run
ANALYZE when they suspect they performed sufficient changes to the table -
just in case the thresholds for autovacuum are not met.
I really don't care to prevent legitimate uses of ANALYZE just because
someone might do something stupid like.
INSERT INTO table VALUES (1);
ANALYZE;
INSERT INTO table VALUES(2);
ANALYZE;
etc...
There is enough gap between the volume of changes needed for auto-ANALYZE
to kick in and a sufficiently large insertion of unique data to cause the
planner to fail that allowing a user to act has merit. I'm looking for
downsides and still haven't seen any that are serious enough that a
well-meaning user can accidentally perform to hurt the system.
The only obvious thing they can do is run:
ANALYZE;
ANALYZE;
ANALYZE;
over and over again - but I'd call that malicious and they can already do
worse.
For me it boils down to that we already have an auto-vacuum daemon so we've
given up strict control of when the statistics are refreshed. We should
acknowledge that such a mechanism is not perfect - which we do by having
ANALYZE - but then also recognize that the user causing the statistics to
become stale, if permissions are securely provisioned, has no way to
correct the deficiency themselves but must rely upon a heuristic or a DBA.
To me that is overly restrictive, discourages good security practices, and
gains little to nothing in way of protecting the system.
David J.
* David G. Johnston (david.g.johnston@gmail.com) wrote:
On Mon, Feb 29, 2016 at 6:31 AM, Stephen Frost <sfrost@snowman.net> wrote:
* David G. Johnston (david.g.johnston@gmail.com) wrote:
Given the amount of damage a person with write access to a table can get
into it seems pointless to not allow them to analyze the table aftertheir
updates - since best practices would say that normal work with a table
should not be performed by an owner.I should the check for whether a given user can or cannot analyze a table
should be whether the user has INSERT, UPDATE, or DELETE privileges.Realistically, ANALYZE is a background/maintenance task that autovacuum
should be handling for you.Then my recent experience of adding a bunch of records and having the
subsequent select query take forever because the table wasn't analyzed is
not supposed to happen? What am I doing wrong then that autovacuum didn't
run for me?
Perhaps nothing. Making autovacuum more aggressive is a trade-off and
evidently there weren't enough changes or perhaps not enough time for
autovacuum to realize it needed to kick in and re-analyze the table.
One thought about how to address that might be to have a given backend,
which is already sending stats info to the statistic collector, somehow
also bump autovacuum to wake it up from its sleep to go analyze the
tables just modified. This is all very hand-wavy as I don't have time
at the moment to run it down, but I do think it'd be good to reduce the
need to run ANALYZE by hand after every data load.
I suppose row-level-security might come into play here...
Yes, you may only have access to a subset of the table.
TBH, since you cannot see the data being analyzed I don't see a security
implication here if you allow someone to ANALYZE the whole table even when
RLS is in place.
I wasn't looking at it from a security implication standpoint as I
suspect that any issue there could actually be addressed, if any exist.
What I was getting at is that you're making an assumption that any user
with DML rights on the table also has enough information about the table
overall to know when it makes sense to ANALYZE the table or not. That's
a bit of a stretch to begin with, but when you consider that RLS may be
involved and the user may only have access to 1% (or less) of the
overall table, it's that much more of a reach.
If we had plenty more bits to allow ANALYZE to be independently
GRANT'able, then maybe, but those are a limited resource.
The planner and system performance seems important enough to give it such
a resource. But as I stated initially I personally believe that a user
with INSERT/DELETE/UPDATE permissions on a table (maybe require all three)
should also be allowed to ANALYZE said table.
I don't think requiring all three would make any sense and would,
instead, simply be confusing. I'm not completely against your general
idea, but let's keep it simple.
Thanks!
Stephen
David G. Johnston wrote:
On Mon, Feb 29, 2016 at 2:52 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
John R Pierce wrote:
analyze has arguably fewer side effects, its a performance enhancement,
its neither altering the schema or changing the data.
In a production environment you don't want a user to change your table
statistics.They could just set default_statistics_target to something stupid,
run ANALYZE and wreck the statistics for everyone.
And then come back to the DBA and complain that things don't work.We have a policy that users are not table owners, and with the
current behaviour we can be certain that any bad table statistics
are the fault of the DBA or wrong configuration.
Setting default_statistics_target and running ANALYZE are two entirely different things.
Setting default_statistics_target affects the statistics computed by ANALYZE,
so I cannot follow you here.
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Mon, Feb 29, 2016 at 8:28 AM, Stephen Frost <sfrost@snowman.net> wrote:
* David G. Johnston (david.g.johnston@gmail.com) wrote:
On Mon, Feb 29, 2016 at 6:31 AM, Stephen Frost <sfrost@snowman.net>
wrote:
* David G. Johnston (david.g.johnston@gmail.com) wrote:
Given the amount of damage a person with write access to a table can
get
into it seems pointless to not allow them to analyze the table after
their
updates - since best practices would say that normal work with a
table
should not be performed by an owner.
I should the check for whether a given user can or cannot analyze a
table
should be whether the user has INSERT, UPDATE, or DELETE privileges.
Realistically, ANALYZE is a background/maintenance task that autovacuum
should be handling for you.Then my recent experience of adding a bunch of records and having the
subsequent select query take forever because the table wasn't analyzed is
not supposed to happen? What am I doing wrong then that autovacuumdidn't
run for me?
Perhaps nothing. Making autovacuum more aggressive is a trade-off and
evidently there weren't enough changes or perhaps not enough time for
autovacuum to realize it needed to kick in and re-analyze the table.
One thought about how to address that might be to have a given backend,
which is already sending stats info to the statistic collector, somehow
also bump autovacuum to wake it up from its sleep to go analyze the
tables just modified. This is all very hand-wavy as I don't have time
at the moment to run it down, but I do think it'd be good to reduce the
need to run ANALYZE by hand after every data load.
Improving it is desirable but it wouldn't preclude this desire.
I suppose row-level-security might come into play here...
Yes, you may only have access to a subset of the table.
TBH, since you cannot see the data being analyzed I don't see a security
implication here if you allow someone to ANALYZE the whole table evenwhen
RLS is in place.
I wasn't looking at it from a security implication standpoint as I
suspect that any issue there could actually be addressed, if any exist.What I was getting at is that you're making an assumption that any user
with DML rights on the table also has enough information about the table
overall to know when it makes sense to ANALYZE the table or not. That's
a bit of a stretch to begin with, but when you consider that RLS may be
involved and the user may only have access to 1% (or less) of the
overall table, it's that much more of a reach.
So the typical user doesn't know or even care that what they just did
needs to be analyzed. The situation is no worse than it is today. But as
someone who writes many scripts and applications to perform bulk writing
and data analysis I'd like those scripts to use restricted authorization
credentials while still being able to run ANALYZE between performing the
bulk DML and the running the SELECT statements needed to get the newly
generated data out of the database.
If we had plenty more bits to allow ANALYZE to be independently
GRANT'able, then maybe, but those are a limited resource.
The planner and system performance seems important enough to give it
such
a resource. But as I stated initially I personally believe that a user
with INSERT/DELETE/UPDATE permissions on a table (maybe require allthree)
should also be allowed to ANALYZE said table.
I don't think requiring all three would make any sense and would,
instead, simply be confusing. I'm not completely against your general
idea, but let's keep it simple.
Agreed.
David J.
On Mon, Feb 29, 2016 at 8:46 AM, Albe Laurenz <laurenz.albe@wien.gv.at>
wrote:
David G. Johnston wrote:
On Mon, Feb 29, 2016 at 2:52 AM, Albe Laurenz <laurenz.albe@wien.gv.at>
wrote:
John R Pierce wrote:
analyze has arguably fewer side effects, its a performance enhancement,
its neither altering the schema or changing the data.In a production environment you don't want a user to change your table
statistics.They could just set default_statistics_target to something stupid,
run ANALYZE and wreck the statistics for everyone.
And then come back to the DBA and complain that things don't work.We have a policy that users are not table owners, and with the
current behaviour we can be certain that any bad table statistics
are the fault of the DBA or wrong configuration.Setting default_statistics_target and running ANALYZE are two entirely
different things.
Setting default_statistics_target affects the statistics computed by
ANALYZE,
so I cannot follow you here.
Just because I can run ANALYZE doesn't mean I should be able to update the
statistic targets. While the features are related the permissions are not.
David J.
David G. Johnston wrote:
In a production environment you don't want a user to change your table
statistics.They could just set default_statistics_target to something stupid,
run ANALYZE and wreck the statistics for everyone.
And then come back to the DBA and complain that things don't work.
Setting default_statistics_target and running ANALYZE are two entirely different things.
Setting default_statistics_target affects the statistics computed by ANALYZE,
so I cannot follow you here.
Just because I can run ANALYZE doesn't mean I should be able to update the statistic targets. While
the features are related the permissions are not.
See http://www.postgresql.org/docs/current/static/planner-stats.html
"The amount of information stored in pg_statistic by ANALYZE, in particular the
maximum number of entries in the most_common_vals and histogram_bounds arrays
for each column, can be set on a column-by-column basis using the
ALTER TABLE SET STATISTICS command, or globally by setting the
default_statistics_target configuration variable."
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 02/29/2016 05:31 AM, Stephen Frost wrote:
* David G. Johnston (david.g.johnston@gmail.com) wrote:
Given the amount of damage a person with write access to a table can get
into it seems pointless to not allow them to analyze the table after their
updates - since best practices would say that normal work with a table
should not be performed by an owner.I should the check for whether a given user can or cannot analyze a table
should be whether the user has INSERT, UPDATE, or DELETE privileges.Realistically, ANALYZE is a background/maintenance task that autovacuum
should be handling for you.
Incorrect. That would be autoanalyze and although they are similar they
are not the same. ANALYZE is used for a number of things, not the least
is query profiling.
Sincerely,
JD
--
Command Prompt, Inc. http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Mon, Feb 29, 2016 at 9:27 AM, Albe Laurenz <laurenz.albe@wien.gv.at>
wrote:
David G. Johnston wrote:
In a production environment you don't want a user to change your table
statistics.They could just set default_statistics_target to something stupid,
run ANALYZE and wreck the statistics for everyone.
And then come back to the DBA and complain that things don't work.Setting default_statistics_target and running ANALYZE are two
entirely different things.
Setting default_statistics_target affects the statistics computed by
ANALYZE,
so I cannot follow you here.
Just because I can run ANALYZE doesn't mean I should be able to update
the statistic targets. While
the features are related the permissions are not.
See http://www.postgresql.org/docs/current/static/planner-stats.html
"The amount of information stored in pg_statistic by ANALYZE, in
particular the
maximum number of entries in the most_common_vals and histogram_bounds
arrays
for each column, can be set on a column-by-column basis using the
ALTER TABLE SET STATISTICS command, or globally by setting the
default_statistics_target configuration variable."
Being able to run ANALYZE on a table in no way implies that I should be
allowed to run ALTER TABLE SET STATISTICS on the same.
Only table owners should be allowed to execute ALTER TABLE while, in my
opinion, anyone with write capabilities on a table should be allowed to
execute ANALYZE. I would accept a GRANT permission if that could get
committed but I find the status-quo mildly annoying.
David J.
On 02/29/16 06:20, Vik Fearing wrote:
Hmm. How are you not the owner of a temporary table?
After 'set session authorization ...'
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
