search

neondatabase / neon

Neon: Serverless Postgres. We separated storage and compute to offer autoscaling, code-like database branching, and scale to zero.
https://neon.tech
Apache License 2.0
13.7k stars 385 forks source link

Temp schemas not being deleted #4140

Open Rooba opened 1 year ago

Rooba commented 1 year ago

When creating a temp view, upon deletion of view, or session reset the schema containing the temporary view persists sessions and cannot be deleted due to schema owner being cloud_admin.

neondb=> CREATE TEMP VIEW test123 AS SELECT 1 a, 2 b, 3 c;
CREATE VIEW
neondb=> \dvS
                         List of relations
   Schema   |              Name               | Type |    Owner    
------------+---------------------------------+------+-------------
 ...
 pg_temp_12 | test123                         | view | User
 ...
(81 rows)

neondb=> \dnS
            List of schemas
        Name        |       Owner       
--------------------+-------------------
 ...
 pg_toast_temp_25   | cloud_admin
 ...
(13 rows)

neondb=> DROP VIEW test123;
DROP VIEW
neondb=> \dvS
                         List of relations
   Schema   |              Name               | Type |    Owner    
------------+---------------------------------+------+-------------
 ...
(80 rows)

--- No view as expected

neondb=> \dnS
            List of schemas
        Name        |       Owner       
--------------------+-------------------
 ...
 pg_toast_temp_25   | cloud_admin
 ...
(13 rows)
kelvich commented 1 year ago

Thank you for the report! Hm, that need some investigation.

knizhnik commented 1 year ago

Sorry, I do not completely understand what it the problem. Postgres implicitly creates temp schema for session temporary objects. The same schema is used for all temp objects created by one backend.'So it should not be removed if temp object (view in your case) is dropped.

There is the same behavior with Vanilla Postgres:

postgres=# CREATE TEMP VIEW test123 AS SELECT 1 a, 2 b, 3 c;
CREATE VIEW
postgres=# \dnS
            List of schemas
        Name        |       Owner       
--------------------+-------------------
 information_schema | knizhnik
 pg_catalog         | knizhnik
 pg_temp_3          | knizhnik
 pg_toast           | knizhnik
 pg_toast_temp_3    | knizhnik
 public             | pg_database_owner
(6 rows)

postgres=# DROP VIEW test123;
DROP VIEW
postgres=# \dvS
                       List of relations
   Schema   |              Name               | Type |  Owner   
------------+---------------------------------+------+----------
 pg_catalog | pg_available_extension_versions | view | knizhnik
 pg_catalog | pg_available_extensions         | view | knizhnik
 pg_catalog | pg_backend_memory_contexts      | view | knizhnik
 pg_catalog | pg_config                       | view | knizhnik
 pg_catalog | pg_cursors                      | view | knizhnik
 pg_catalog | pg_file_settings                | view | knizhnik
 pg_catalog | pg_group                        | view | knizhnik
 pg_catalog | pg_hba_file_rules               | view | knizhnik
 pg_catalog | pg_ident_file_mappings          | view | knizhnik
 pg_catalog | pg_indexes                      | view | knizhnik
 pg_catalog | pg_locks                        | view | knizhnik
 pg_catalog | pg_matviews                     | view | knizhnik
 pg_catalog | pg_policies                     | view | knizhnik
 pg_catalog | pg_prepared_statements          | view | knizhnik
 pg_catalog | pg_prepared_xacts               | view | knizhnik
 pg_catalog | pg_publication_tables           | view | knizhnik
 pg_catalog | pg_replication_origin_status    | view | knizhnik
 pg_catalog | pg_replication_slots            | view | knizhnik
 pg_catalog | pg_roles                        | view | knizhnik
 pg_catalog | pg_rules                        | view | knizhnik
 pg_catalog | pg_seclabels                    | view | knizhnik
 pg_catalog | pg_sequences                    | view | knizhnik
 pg_catalog | pg_settings                     | view | knizhnik
 pg_catalog | pg_shadow                       | view | knizhnik
 pg_catalog | pg_shmem_allocations            | view | knizhnik
 pg_catalog | pg_stat_activity                | view | knizhnik
 pg_catalog | pg_stat_all_indexes             | view | knizhnik
 pg_catalog | pg_stat_all_tables              | view | knizhnik
 pg_catalog | pg_stat_archiver                | view | knizhnik
 pg_catalog | pg_stat_bgwriter                | view | knizhnik
 pg_catalog | pg_stat_database                | view | knizhnik
 pg_catalog | pg_stat_database_conflicts      | view | knizhnik
 pg_catalog | pg_stat_gssapi                  | view | knizhnik
 pg_catalog | pg_stat_io                      | view | knizhnik
 pg_catalog | pg_stat_progress_analyze        | view | knizhnik
 pg_catalog | pg_stat_progress_basebackup     | view | knizhnik
 pg_catalog | pg_stat_progress_cluster        | view | knizhnik
 pg_catalog | pg_stat_progress_copy           | view | knizhnik
 pg_catalog | pg_stat_progress_create_index   | view | knizhnik
 pg_catalog | pg_stat_progress_vacuum         | view | knizhnik
 pg_catalog | pg_stat_recovery_prefetch       | view | knizhnik
 pg_catalog | pg_stat_replication             | view | knizhnik
 pg_catalog | pg_stat_replication_slots       | view | knizhnik
 pg_catalog | pg_stat_slru                    | view | knizhnik
 pg_catalog | pg_stat_ssl                     | view | knizhnik
 pg_catalog | pg_stat_subscription            | view | knizhnik
 pg_catalog | pg_stat_subscription_stats      | view | knizhnik
 pg_catalog | pg_stat_sys_indexes             | view | knizhnik
 pg_catalog | pg_stat_sys_tables              | view | knizhnik
 pg_catalog | pg_stat_user_functions          | view | knizhnik
 pg_catalog | pg_stat_user_indexes            | view | knizhnik
 pg_catalog | pg_stat_user_tables             | view | knizhnik
 pg_catalog | pg_stat_wal                     | view | knizhnik
 pg_catalog | pg_stat_wal_receiver            | view | knizhnik
 pg_catalog | pg_stat_xact_all_tables         | view | knizhnik
 pg_catalog | pg_stat_xact_sys_tables         | view | knizhnik
 pg_catalog | pg_stat_xact_user_functions     | view | knizhnik
 pg_catalog | pg_stat_xact_user_tables        | view | knizhnik
 pg_catalog | pg_statio_all_indexes           | view | knizhnik
 pg_catalog | pg_statio_all_sequences         | view | knizhnik
 pg_catalog | pg_statio_all_tables            | view | knizhnik
 pg_catalog | pg_statio_sys_indexes           | view | knizhnik
 pg_catalog | pg_statio_sys_sequences         | view | knizhnik
 pg_catalog | pg_statio_sys_tables            | view | knizhnik
 pg_catalog | pg_statio_user_indexes          | view | knizhnik
 pg_catalog | pg_statio_user_sequences        | view | knizhnik
 pg_catalog | pg_statio_user_tables           | view | knizhnik
 pg_catalog | pg_stats                        | view | knizhnik
 pg_catalog | pg_stats_ext                    | view | knizhnik
 pg_catalog | pg_stats_ext_exprs              | view | knizhnik
 pg_catalog | pg_tables                       | view | knizhnik
 pg_catalog | pg_timezone_abbrevs             | view | knizhnik
 pg_catalog | pg_timezone_names               | view | knizhnik
 pg_catalog | pg_user                         | view | knizhnik
 pg_catalog | pg_user_mappings                | view | knizhnik
 pg_catalog | pg_views                        | view | knizhnik
(76 rows)

postgres=#          
postgres=# \dnS
            List of schemas
        Name        |       Owner       
--------------------+-------------------
 information_schema | knizhnik
 pg_catalog         | knizhnik
 pg_temp_3          | knizhnik
 pg_toast           | knizhnik
 pg_toast_temp_3    | knizhnik
 public             | pg_database_owner
(6 rows)

postgres=# create temp table t(x integer);
CREATE TABLE
postgres=# \dnS
            List of schemas
        Name        |       Owner       
--------------------+-------------------
 information_schema | knizhnik
 pg_catalog         | knizhnik
 pg_temp_3          | knizhnik
 pg_toast           | knizhnik
 pg_toast_temp_3    | knizhnik
 public             | pg_database_owner
(6 rows)
Rooba commented 1 year ago

The issue is that when creating a temp view, it sets cloud_admin as the schema owner for the pg_toasttemp as well as the pgtemp schemas. Is it a possibility to grant owner on the schemas created to the user who creates the temp table?