Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Fix continuous aggregate privileges during upgrade #2845

Merged
merged 1 commit into from Jan 27, 2021

Conversation

erimatnor
Copy link
Contributor

@erimatnor erimatnor commented Jan 20, 2021

Copy ACL privileges (grants) from the query view (user-facing object)
to the internal objects (e.g., materialized hypertable, direct view,
and partial view) when updating the extension to the new version. A
previous change added such propagation of privileges when executing
GRANT statements, but didn't apply it retrospectively to the
internal objects of existing continuous aggregates.

Having the right permissions on internal objects is also necessary for
the watermark function used by real-time aggregation since it queries
the materialized hypertable directly.

The update script copies the ACL information from the user-facing view
of every continuous aggregate to its internal objects (including the
materialized hypertable and its chunks). This is done by direct insert
into pg_class instead of executing a GRANT statement in the update
script, since the latter will record the grant/ACL as an init
privilege (i.e., the system believes the GRANT is for an extension
object). The init privilege will prevent this ACL from being included
in future dump files, since pg_dump only includes non-init
privileges as it believes such privileges will be recreated with the
extension.

Fixes #2825

@erimatnor erimatnor added bug update-script upgrade Issue is related to upgrading the extension or the PostgreSQL version. permissions labels Jan 20, 2021
@erimatnor erimatnor force-pushed the fix-cagg-permissions branch 2 times, most recently from 4a14fd3 to cac17ff Compare January 20, 2021 10:47
@codecov
Copy link

codecov bot commented Jan 20, 2021

Codecov Report

Merging #2845 (eaed20c) into master (126f1c8) will increase coverage by 0.12%.
The diff coverage is n/a.

Impacted file tree graph

@@            Coverage Diff             @@
##           master    #2845      +/-   ##
==========================================
+ Coverage   90.07%   90.20%   +0.12%     
==========================================
  Files         212      212              
  Lines       34772    34722      -50     
==========================================
  Hits        31322    31322              
+ Misses       3450     3400      -50     
Impacted Files Coverage Δ
src/loader/bgw_message_queue.c 87.09% <0.00%> (ø)
src/import/planner.c 70.30% <0.00%> (+11.12%) ⬆️

Continue to review full report at Codecov.

Legend - Click here to learn more
Δ = absolute <relative> (impact), ø = not affected, ? = missing data
Powered by Codecov. Last update 126f1c8...eaed20c. Read the comment docs.

@erimatnor erimatnor removed the upgrade Issue is related to upgrading the extension or the PostgreSQL version. label Jan 21, 2021
@erimatnor erimatnor force-pushed the fix-cagg-permissions branch 5 times, most recently from f21aad0 to 834f1c8 Compare January 22, 2021 08:51
@erimatnor erimatnor marked this pull request as ready for review January 25, 2021 10:22
@erimatnor erimatnor requested a review from a team as a code owner January 25, 2021 10:22
@erimatnor erimatnor requested review from pmwkaa, k-rus, svenklemm and mkindahl and removed request for a team January 25, 2021 10:22
sql/updates/latest-dev.sql Outdated Show resolved Hide resolved
test/sql/updates/post.continuous_aggs.sql Outdated Show resolved Hide resolved
@erimatnor erimatnor force-pushed the fix-cagg-permissions branch 2 times, most recently from 19e1fee to 7d29949 Compare January 27, 2021 10:27
Copy ACL privileges (grants) from the query view (user-facing object)
to the internal objects (e.g., materialized hypertable, direct view,
and partial view) when updating the extension to the new version. A
previous change added such propagation of privileges when executing
`GRANT` statements, but didn't apply it retrospectively to the
internal objects of existing continuous aggregates.

Having the right permissions on internal objects is also necessary for
the watermark function used by real-time aggregation since it queries
the materialized hypertable directly.

The update script copies the ACL information from the user-facing view
of every continuous aggregate to its internal objects (including the
materialized hypertable and its chunks). This is done by direct insert
into `pg_class` instead of executing a `GRANT` statement in the update
script, since the latter will record the grant/ACL as an init
privilege (i.e., the system believes the GRANT is for an extension
object). The init privilege will prevent this ACL from being included
in future dump files, since `pg_dump` only includes non-init
privileges as it believes such privileges will be recreated with the
extension.

Fixes timescale#2825
@erimatnor erimatnor merged commit 86f8e30 into timescale:master Jan 27, 2021
@erimatnor erimatnor deleted the fix-cagg-permissions branch January 27, 2021 13:01
svenklemm added a commit to svenklemm/timescaledb that referenced this pull request Jan 27, 2021
This maintenance release contains bugfixes since the 2.0.0 release. We deem it
high priority for upgrading.

In particular the fixes contained in this maintenance release address issues
in continuous aggregates, compression, JOINs with hypertables and when
upgrading from previous versions.

**Bugfixes**
* timescale#2772 Always validate existing database and extension
* timescale#2780 Fix config enum entries for remote data fetcher
* timescale#2806 Add check for dropped chunk on update
* timescale#2828 Improve cagg watermark caching
* timescale#2842 Do not mark job as started when seting next_start field
* timescale#2845 Fix continuous aggregate privileges during upgrade
* timescale#2860 Fix projection in ChunkAppend nodes
* timescale#2861 Remove compression stat update from update script

**Thanks**
* @sgorsh for reporting an issue when using pgAdmin on windows
* @brian-from-quantrocket for reporting an issue with extension update and dropped chunks
* @PhilippJust for reporting an issue with add_job and initial_start
* @alex88 for reporting an issue with joined hypertables
@svenklemm svenklemm mentioned this pull request Jan 27, 2021
svenklemm added a commit to svenklemm/timescaledb that referenced this pull request Jan 27, 2021
This maintenance release contains bugfixes since the 2.0.0 release. We deem it
high priority for upgrading.

In particular the fixes contained in this maintenance release address issues
in continuous aggregates, compression, JOINs with hypertables and when
upgrading from previous versions.

**Bugfixes**
* timescale#2772 Always validate existing database and extension
* timescale#2780 Fix config enum entries for remote data fetcher
* timescale#2806 Add check for dropped chunk on update
* timescale#2828 Improve cagg watermark caching
* timescale#2842 Do not mark job as started when seting next_start field
* timescale#2845 Fix continuous aggregate privileges during upgrade
* timescale#2851 Fix nested loop joins that involve compressed chunks
* timescale#2860 Fix projection in ChunkAppend nodes
* timescale#2861 Remove compression stat update from update script
* timescale#2865 Apply volatile function quals at decompresschunk node

**Thanks**
* @alex88 for reporting an issue with joined hypertables
* @brian-from-quantrocket for reporting an issue with extension update and dropped chunks
* @dhodyn for reporting an issue when joining compressed chunks
* @PhilippJust for reporting an issue with add_job and initial_start
* @sgorsh for reporting an issue when using pgAdmin on windows
svenklemm added a commit to svenklemm/timescaledb that referenced this pull request Jan 27, 2021
This maintenance release contains bugfixes since the 2.0.0 release. We deem it
high priority for upgrading.

In particular the fixes contained in this maintenance release address issues
in continuous aggregates, compression, JOINs with hypertables and when
upgrading from previous versions.

**Bugfixes**
* timescale#2772 Always validate existing database and extension
* timescale#2780 Fix config enum entries for remote data fetcher
* timescale#2806 Add check for dropped chunk on update
* timescale#2828 Improve cagg watermark caching
* timescale#2842 Do not mark job as started when seting next_start field
* timescale#2845 Fix continuous aggregate privileges during upgrade
* timescale#2851 Fix nested loop joins that involve compressed chunks
* timescale#2860 Fix projection in ChunkAppend nodes
* timescale#2861 Remove compression stat update from update script
* timescale#2865 Apply volatile function quals at decompresschunk node

**Thanks**
* @alex88 for reporting an issue with joined hypertables
* @brian-from-quantrocket for reporting an issue with extension update and dropped chunks
* @dhodyn for reporting an issue when joining compressed chunks
* @PhilippJust for reporting an issue with add_job and initial_start
* @sgorsh for reporting an issue when using pgAdmin on windows
svenklemm added a commit to svenklemm/timescaledb that referenced this pull request Jan 27, 2021
This maintenance release contains bugfixes since the 2.0.0 release. We deem it
high priority for upgrading.

In particular the fixes contained in this maintenance release address issues
in continuous aggregates, compression, JOINs with hypertables and when
upgrading from previous versions.

**Bugfixes**
* timescale#2772 Always validate existing database and extension
* timescale#2780 Fix config enum entries for remote data fetcher
* timescale#2806 Add check for dropped chunk on update
* timescale#2828 Improve cagg watermark caching
* timescale#2842 Do not mark job as started when seting next_start field
* timescale#2845 Fix continuous aggregate privileges during upgrade
* timescale#2851 Fix nested loop joins that involve compressed chunks
* timescale#2860 Fix projection in ChunkAppend nodes
* timescale#2861 Remove compression stat update from update script
* timescale#2865 Apply volatile function quals at decompresschunk node

**Thanks**
* @alex88 for reporting an issue with joined hypertables
* @brian-from-quantrocket for reporting an issue with extension update and dropped chunks
* @dhodyn for reporting an issue when joining compressed chunks
* @PhilippJust for reporting an issue with add_job and initial_start
* @sgorsh for reporting an issue when using pgAdmin on windows
svenklemm added a commit to svenklemm/timescaledb that referenced this pull request Jan 27, 2021
This maintenance release contains bugfixes since the 2.0.0 release. We deem it
high priority for upgrading.

In particular the fixes contained in this maintenance release address issues
in continuous aggregates, compression, JOINs with hypertables and when
upgrading from previous versions.

**Bugfixes**
* timescale#2772 Always validate existing database and extension
* timescale#2780 Fix config enum entries for remote data fetcher
* timescale#2806 Add check for dropped chunk on update
* timescale#2828 Improve cagg watermark caching
* timescale#2842 Do not mark job as started when seting next_start field
* timescale#2845 Fix continuous aggregate privileges during upgrade
* timescale#2851 Fix nested loop joins that involve compressed chunks
* timescale#2860 Fix projection in ChunkAppend nodes
* timescale#2861 Remove compression stat update from update script
* timescale#2865 Apply volatile function quals at decompresschunk node

**Thanks**
* @alex88 for reporting an issue with joined hypertables
* @brian-from-quantrocket for reporting an issue with extension update and dropped chunks
* @dhodyn for reporting an issue when joining compressed chunks
* @PhilippJust for reporting an issue with add_job and initial_start
* @sgorsh for reporting an issue when using pgAdmin on windows
svenklemm added a commit to svenklemm/timescaledb that referenced this pull request Jan 28, 2021
This maintenance release contains bugfixes since the 2.0.0 release. We deem it
high priority for upgrading.

In particular the fixes contained in this maintenance release address issues
in continuous aggregates, compression, JOINs with hypertables and when
upgrading from previous versions.

**Bugfixes**
* timescale#2772 Always validate existing database and extension
* timescale#2780 Fix config enum entries for remote data fetcher
* timescale#2806 Add check for dropped chunk on update
* timescale#2828 Improve cagg watermark caching
* timescale#2842 Do not mark job as started when setting next_start field
* timescale#2845 Fix continuous aggregate privileges during upgrade
* timescale#2851 Fix nested loop joins that involve compressed chunks
* timescale#2860 Fix projection in ChunkAppend nodes
* timescale#2861 Remove compression stat update from update script
* timescale#2865 Apply volatile function quals at decompresschunk node
* timescale#2866 Avoid partitionwise planning of partialize_agg
* timescale#2868 Fix corruption in gapfill plan
* timescale#2874 Fix partitionwise agg crash due to uninitialized memory

**Thanks**
* @alex88 for reporting an issue with joined hypertables
* @brian-from-quantrocket for reporting an issue with extension update and dropped chunks
* @dhodyn for reporting an issue when joining compressed chunks
* @markatosi for reporting a segfault with partitionwise aggregates enabled
* @PhilippJust for reporting an issue with add_job and initial_start
* @sgorsh for reporting an issue when using pgAdmin on windows
* @WarriorOfWire for reporting the bug with gapfill queries not being
  able to find pathkey item to sort
svenklemm added a commit to svenklemm/timescaledb that referenced this pull request Jan 28, 2021
This maintenance release contains bugfixes since the 2.0.0 release. We deem it
high priority for upgrading.

In particular the fixes contained in this maintenance release address issues
in continuous aggregates, compression, JOINs with hypertables and when
upgrading from previous versions.

**Bugfixes**
* timescale#2772 Always validate existing database and extension
* timescale#2780 Fix config enum entries for remote data fetcher
* timescale#2806 Add check for dropped chunk on update
* timescale#2828 Improve cagg watermark caching
* timescale#2842 Do not mark job as started when setting next_start field
* timescale#2845 Fix continuous aggregate privileges during upgrade
* timescale#2851 Fix nested loop joins that involve compressed chunks
* timescale#2860 Fix projection in ChunkAppend nodes
* timescale#2861 Remove compression stat update from update script
* timescale#2865 Apply volatile function quals at decompresschunk node
* timescale#2866 Avoid partitionwise planning of partialize_agg
* timescale#2868 Fix corruption in gapfill plan
* timescale#2874 Fix partitionwise agg crash due to uninitialized memory

**Thanks**
* @alex88 for reporting an issue with joined hypertables
* @brian-from-quantrocket for reporting an issue with extension update and dropped chunks
* @dhodyn for reporting an issue when joining compressed chunks
* @markatosi for reporting a segfault with partitionwise aggregates enabled
* @PhilippJust for reporting an issue with add_job and initial_start
* @sgorsh for reporting an issue when using pgAdmin on windows
* @WarriorOfWire for reporting the bug with gapfill queries not being
  able to find pathkey item to sort
svenklemm added a commit to svenklemm/timescaledb that referenced this pull request Jan 28, 2021
This maintenance release contains bugfixes since the 2.0.0 release. We deem it
high priority for upgrading.

In particular the fixes contained in this maintenance release address issues
in continuous aggregates, compression, JOINs with hypertables and when
upgrading from previous versions.

**Bugfixes**
* timescale#2772 Always validate existing database and extension
* timescale#2780 Fix config enum entries for remote data fetcher
* timescale#2806 Add check for dropped chunk on update
* timescale#2828 Improve cagg watermark caching
* timescale#2842 Do not mark job as started when setting next_start field
* timescale#2845 Fix continuous aggregate privileges during upgrade
* timescale#2851 Fix nested loop joins that involve compressed chunks
* timescale#2860 Fix projection in ChunkAppend nodes
* timescale#2861 Remove compression stat update from update script
* timescale#2865 Apply volatile function quals at decompresschunk node
* timescale#2866 Avoid partitionwise planning of partialize_agg
* timescale#2868 Fix corruption in gapfill plan
* timescale#2874 Fix partitionwise agg crash due to uninitialized memory

**Thanks**
* @alex88 for reporting an issue with joined hypertables
* @brian-from-quantrocket for reporting an issue with extension update and dropped chunks
* @dhodyn for reporting an issue when joining compressed chunks
* @markatosi for reporting a segfault with partitionwise aggregates enabled
* @PhilippJust for reporting an issue with add_job and initial_start
* @sgorsh for reporting an issue when using pgAdmin on windows
* @WarriorOfWire for reporting the bug with gapfill queries not being
  able to find pathkey item to sort
svenklemm added a commit that referenced this pull request Jan 28, 2021
This maintenance release contains bugfixes since the 2.0.0 release. We deem it
high priority for upgrading.

In particular the fixes contained in this maintenance release address issues
in continuous aggregates, compression, JOINs with hypertables and when
upgrading from previous versions.

**Bugfixes**
* #2772 Always validate existing database and extension
* #2780 Fix config enum entries for remote data fetcher
* #2806 Add check for dropped chunk on update
* #2828 Improve cagg watermark caching
* #2842 Do not mark job as started when setting next_start field
* #2845 Fix continuous aggregate privileges during upgrade
* #2851 Fix nested loop joins that involve compressed chunks
* #2860 Fix projection in ChunkAppend nodes
* #2861 Remove compression stat update from update script
* #2865 Apply volatile function quals at decompresschunk node
* #2866 Avoid partitionwise planning of partialize_agg
* #2868 Fix corruption in gapfill plan
* #2874 Fix partitionwise agg crash due to uninitialized memory

**Thanks**
* @alex88 for reporting an issue with joined hypertables
* @brian-from-quantrocket for reporting an issue with extension update and dropped chunks
* @dhodyn for reporting an issue when joining compressed chunks
* @markatosi for reporting a segfault with partitionwise aggregates enabled
* @PhilippJust for reporting an issue with add_job and initial_start
* @sgorsh for reporting an issue when using pgAdmin on windows
* @WarriorOfWire for reporting the bug with gapfill queries not being
  able to find pathkey item to sort
svenklemm added a commit that referenced this pull request Jan 28, 2021
This maintenance release contains bugfixes since the 2.0.0 release. We deem it
high priority for upgrading.

In particular the fixes contained in this maintenance release address issues
in continuous aggregates, compression, JOINs with hypertables and when
upgrading from previous versions.

**Bugfixes**
* #2772 Always validate existing database and extension
* #2780 Fix config enum entries for remote data fetcher
* #2806 Add check for dropped chunk on update
* #2828 Improve cagg watermark caching
* #2838 Fix catalog repair in update script
* #2842 Do not mark job as started when setting next_start field
* #2845 Fix continuous aggregate privileges during upgrade
* #2851 Fix nested loop joins that involve compressed chunks
* #2860 Fix projection in ChunkAppend nodes
* #2861 Remove compression stat update from update script
* #2865 Apply volatile function quals at decompresschunk node
* #2866 Avoid partitionwise planning of partialize_agg
* #2868 Fix corruption in gapfill plan
* #2874 Fix partitionwise agg crash due to uninitialized memory

**Thanks**
* @alex88 for reporting an issue with joined hypertables
* @brian-from-quantrocket for reporting an issue with extension update and dropped chunks
* @dhodyn for reporting an issue when joining compressed chunks
* @markatosi for reporting a segfault with partitionwise aggregates enabled
* @PhilippJust for reporting an issue with add_job and initial_start
* @sgorsh for reporting an issue when using pgAdmin on windows
* @WarriorOfWire for reporting the bug with gapfill queries not being
  able to find pathkey item to sort
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

Successfully merging this pull request may close these issues.

Permission failure on cagg after upgrade
3 participants