Обсуждение: "REVOKE ... ON DATABASE template1 ..." has no effect
Hi all, I am trying to properly harden my postgres installations with the goal of users having no access whatsoever unless explicitly granted. (TBH I was quite shocked to learn that this is something I need to even do, I am used to more secure defaults.) So, following <https://wiki.postgresql.org/wiki/Shared_Database_Hosting>, I ran these commands in template1: REVOKE ALL ON DATABASE template1 FROM public; REVOKE ALL ON SCHEMA public FROM public; The default access to the public schema was successfully revoked, but the "REVOKE ... ON DATABASE" seems to have no effect: I continued doing "CREATE DATABASE test" (as the postgres user), and then tried to connect to "test" with my test user, which worked. I would have expected a "REVOKE ALL ON DATABASE template1" to have the effect of changing the default permissions for new databases. I am not even alone in this expectation, see e.g. <https://dba.stackexchange.com/a/17792>. Because the actual behavior differs from the expected behavior, I am reporting this as a bug. This is using PostgreSQL 10.3 on Debian testing. Kind regards, Ralf
Ralf Jung <post@ralfj.de> writes: > I would have expected a "REVOKE ALL ON DATABASE template1" to have the effect of > changing the default permissions for new databases. This is not a bug, and I don't think it's a reasonable expectation either. There's certainly plenty of reasons why you might wish to lock people out of template1, but that doesn't equate to supposing that people should be locked out of every new database. Nor do we copy most other database-level attributes when cloning a database (the exceptions are things that affect the database contents, such as encoding). There might be an argument for extending ALTER DEFAULT PRIVILEGES so that it can control the initial default privileges for new databases. That's certainly a feature request not a bug though. regards, tom lane
On 15/05/18 03:43, Tom Lane wrote: > Ralf Jung <post@ralfj.de> writes: >> I would have expected a "REVOKE ALL ON DATABASE template1" to have the effect of >> changing the default permissions for new databases. > This is not a bug, and I don't think it's a reasonable expectation either. > There's certainly plenty of reasons why you might wish to lock people out > of template1, but that doesn't equate to supposing that people should be > locked out of every new database. Nor do we copy most other > database-level attributes when cloning a database (the exceptions are > things that affect the database contents, such as encoding). > > There might be an argument for extending ALTER DEFAULT PRIVILEGES so that > it can control the initial default privileges for new databases. That's > certainly a feature request not a bug though. > > regards, tom lane > Would definitely agree with Tom on both points! Cheers, Gavin
Hi Tom, thanks for your fast response! On 14.05.2018 17:43, Tom Lane wrote: > Ralf Jung <post@ralfj.de> writes: >> I would have expected a "REVOKE ALL ON DATABASE template1" to have the effect of >> changing the default permissions for new databases. > > This is not a bug, and I don't think it's a reasonable expectation either. > There's certainly plenty of reasons why you might wish to lock people out > of template1, but that doesn't equate to supposing that people should be > locked out of every new database. Nor do we copy most other > database-level attributes when cloning a database (the exceptions are > things that affect the database contents, such as encoding). Being entirely new to PostgreSQL, I don't really understand why e.g. schema properties of the template are preserved but database properties are not. I thought that's the entire point of this template DB. I did not even think of this "REVOKE ALL" as "lock people out of the template"; just like "REVOKE ALL ON SCHEMA public" (executed in DB template1) conceptually doesn't mean to me "lock people out of the public schema in this template DB", it means "lock people out of the public schema of all DBs created in the future". But then I also don't even know what other database properties there are, so this misunderstanding probably stems from my lack of experience. > There might be an argument for extending ALTER DEFAULT PRIVILEGES so that > it can control the initial default privileges for new databases. That's > certainly a feature request not a bug though. I haven't yet fully understood the interaction between defining default privileges in template1 vs. defining them with "ALTER DEFAULT PRIVILEGES", but any way to make sure that new databases are properly locked down would be useful. Kind regards, Ralf