postgres tables created by alembic update have no permissions set

[corviday]

Not sure if this is an issue we can actually solve with code in this repository. I did a practice run of the alembic 614911daf883 -> 12f290b63791 update today, and the newly created tables have no permissions at all.

Default database permissions, which should be propagated to new tables:

ce_test=> \ddp
                Default access privileges
  Owner   | Schema  |   Type   |    Access privileges     
----------+---------+----------+--------------------------
 postgres | ce_test | sequence | ce_test=rwU/postgres    +
          |         |          | ce_test_ro=rU/postgres  +
          |         |          | ce_test_rw=rwU/postgres
 postgres | ce_test | table    | ce_test=arwdDxt/postgres+
          |         |          | ce_test_ro=r/postgres   +
          |         |          | ce_test_rw=arwd/postgres
 postgres | public  | sequence | ce_test=rwU/postgres    +
          |         |          | ce_test_ro=rU/postgres  +
          |         |          | ce_test_rw=rwU/postgres
 postgres | public  | table    | ce_test=arwdDxt/postgres+
          |         |          | ce_test_ro=r/postgres   +
          |         |          | ce_test_rw=arwd/postgres
(4 rows)

New tables in ce_test should start with these permissions (full access for the database owner ce_test, read and write for ce_test_rw, and read only for ce_test_ro). Instead, the new tables created by alembic have no permissions at all:

ce_test=> \dp data_file_variables*
                                                      Access privileges
 Schema  |                      Name                      |   Type   |    Access privileges    | Column privileges | Policies 
---------+------------------------------------------------+----------+-------------------------+-------------------+----------
 ce_test | data_file_variables                            | table    | ce_test=arwdDxt/ce_test+|                   | 
         |                                                |          | ce_test_ro=r/ce_test   +|                   | 
         |                                                |          | ce_test_rw=arwd/ce_test |                   | 
 ce_test | data_file_variables_data_file_variable_id_seq  | sequence | ce_test=rwU/ce_test    +|                   | 
         |                                                |          | ce_test_rw=rwU/ce_test +|                   | 
         |                                                |          | ce_test_ro=rU/ce_test   |                   | 
 ce_test | data_file_variables_dsg_time_series            | table    |                         |                   | 
 ce_test | data_file_variables_dsg_time_series_x_stations | table    |                         |                   | 
 ce_test | data_file_variables_gridded                    | table    |                         |                   | 
 ce_test | data_file_variables_qc_flags                   | table    | ce_test=arwdDxt/ce_test+|                   | 
         |                                                |          | ce_test_ro=r/ce_test   +|                   | 
         |                                                |          | ce_test_rw=arwd/ce_test |                   | 
(6 rows)

ce_test=> \dp stations*
                                        Access privileges
 Schema  |          Name           |   Type   | Access privileges | Column privileges | Policies 
---------+-------------------------+----------+-------------------+-------------------+----------
 ce_test | stations                | table    |                   |                   | 
 ce_test | stations_station_id_seq | sequence |                   |                   | 
(2 rows)

[corviday]

Further testing has shown this problem is not exclusive to tables created with alembic and applies also to tables created by other means. I think there's just something wrong with the default permissions setup in my test database. Closing this error.

[corviday]

It turns out that default table permissions are set separately based on which user creates the table. If you look at the \dpp output above, the column that says "owner" means that these defaults only apply when that specific user creates a table or sequence - in this case, the user postgres. No defaults apply to any other user.

@matthewbenstead fixed the default permissions as follows:

ce_test=> \ddp
                Default access privileges
  Owner   | Schema  |   Type   |    Access privileges     
----------+---------+----------+--------------------------
 ce_test  | ce_test | sequence | ce_test=rwU/ce_test     +
          |         |          | ce_test_ro=rU/ce_test   +
          |         |          | ce_test_rw=rwU/ce_test
 ce_test  | ce_test | table    | ce_test=arwdDxt/ce_test +
          |         |          | ce_test_ro=r/ce_test    +
          |         |          | ce_test_rw=arwd/ce_test
 ce_test  | public  | sequence | ce_test=rwU/ce_test     +
          |         |          | ce_test_ro=rU/ce_test   +
          |         |          | ce_test_rw=rwU/ce_test
 ce_test  | public  | table    | ce_test=arwdDxt/ce_test +
          |         |          | ce_test_ro=r/ce_test    +
          |         |          | ce_test_rw=arwd/ce_test
 postgres | ce_test | sequence | ce_test=rwU/postgres    +
          |         |          | ce_test_ro=rU/postgres  +
          |         |          | ce_test_rw=rwU/postgres
 postgres | ce_test | table    | ce_test=arwdDxt/postgres+
          |         |          | ce_test_ro=r/postgres   +
          |         |          | ce_test_rw=arwd/postgres
 postgres | public  | sequence | ce_test=rwU/postgres    +
          |         |          | ce_test_ro=rU/postgres  +
          |         |          | ce_test_rw=rwU/postgres
 postgres | public  | table    | ce_test=arwdDxt/postgres+
          |         |          | ce_test_ro=r/postgres   +
          |         |          | ce_test_rw=arwd/postgres
(8 rows)

and similarly fixed the permissions on the live database on monsoon, so we should be good to go.