PostgreSQL Default Permissions

While I was trying to setup PostgREST (will make a post on that later), I noticed that there were a lot of issues with the default permissions for PostgreSQL Databases.

template1=# \dn+
                          List of schemas
  Name  |  Owner   |  Access privileges   |      Description       
--------+----------+----------------------+------------------------
 public | postgres | postgres=UC/postgres+| standard public schema
        |          | =UC/postgres         | 
(1 row)

The main thing that was worrying here was =UC/postgres. According to the docs, this means that the postgres user is granting all other users in PostgreSQL USAGE and CREATE permissions on the database.

Due to the nature of the data I’m storing in Postgres and access control settings in place, I was still very confident about the security of my data. Regardless, I have changed permissions on all data in Postgres.

Apart from the pubdata database, all databases have permissions restricted to the owner of that DB.

I also noticed a couple of issues with Nextcloud’s usage of PostgreSQL (I had to manually delete entries which Nextcloud forgot about), but I don’t have enough notes on what I did to make a post about it.