Обсуждение: GRANT CREATE or ALTER SCHEMA?
Hello,
When considering our mitigation strategy for the recently-announced CVE-2018-1058, we’ve been trying to choose between:
- ALTER SCHEMA public OWNER TO <db-owner>
- GRANT CREATE ON SCHEMA public TO <db-owner>
This is of course after the REVOKE CREATE ON SCHEMA public FROM PUBLIC.
We understand why the public schema is owned by the “postgres” account to start with, i.e. because CREATE DATABASE copies from the template1 database. But this does mean that we need a post-createdb action to allow an application account to use the public schema to create its objects (which is our most typical configuration).
Changing the owner of the public schema to the database owner after database creation (i.e. #1 above) seems to be the simplest approach, but we’re wondering if there’s a reason for the public schema to be owned by the postgres account, i.e. beyond just “this is how it happens by default”. We can’t come up with one, and neither can our Google-fu. :-)
Thanks in advance for your insights,
Kav Moradhassel | R&D Tools and Metrics | Ciena
kmoradha@ciena.com | 385 Terry Fox Drive | Ottawa, ON, K2K 0L1 Canada
"Moradhassel, Kavian" <kmoradha@ciena.com> writes:
> We understand why the public schema is owned by the "postgres" account
> to start with, i.e. because CREATE DATABASE copies from the template1
> database.
Right.
> Changing the owner of the public schema to the database owner after
> database creation (i.e. #1 above) seems to be the simplest approach, but
> we're wondering if there's a reason for the public schema to be owned by
> the postgres account, i.e. beyond just "this is how it happens by
> default". We can't come up with one, and neither can our Google-fu. :-)
No, there isn't another reason, really. There have actually been
proposals to change the behavior of CREATE DATABASE to adjust the
ownership of that schema automatically. But it's not simple to do
from within the creating session, and there'd be backwards-compatibility
complaints anyway.
regards, tom lane
Thanks for the quick response, Tom!
Kav Moradhassel | R&D Tools and Metrics | Ciena
kmoradha@ciena.com | 385 Terry Fox Drive | Ottawa, ON, K2K 0L1 Canada
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: March 22, 2018 11:01 AM
To: Moradhassel, Kavian <kmoradha@ciena.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: GRANT CREATE or ALTER SCHEMA?
"Moradhassel, Kavian" <kmoradha@ciena.com> writes:
> We understand why the public schema is owned by the "postgres" account
> to start with, i.e. because CREATE DATABASE copies from the template1
> database.
Right.
> Changing the owner of the public schema to the database owner after
> database creation (i.e. #1 above) seems to be the simplest approach, but
> we're wondering if there's a reason for the public schema to be owned by
> the postgres account, i.e. beyond just "this is how it happens by
> default". We can't come up with one, and neither can our Google-fu. :-)
No, there isn't another reason, really. There have actually been
proposals to change the behavior of CREATE DATABASE to adjust the
ownership of that schema automatically. But it's not simple to do
from within the creating session, and there'd be backwards-compatibility
complaints anyway.
regards, tom lane