Обсуждение: Rationale for PUBLIC having CREATE and USAGE privileges on the schema"public" by default
Rationale for PUBLIC having CREATE and USAGE privileges on the schema"public" by default
От
Olegs Jeremejevs
Дата:
Hi,
I'm aware that these default privileges are documented:
However, I'm unable to find any reasoning behind their existence. Normally, one can just revoke them and move on, but they have caused me some trouble in a managed deployment (Compose, where you can't login as the owner of the schema or as a superuser, so it's impossible to do the revocation, so you're essentially forced to use a non-public schema), and I would like to understand the need for them.
I assume they make it a bit easier for new users to try out the database, and I see that they go at least as far as 7.3, but has there been any discussion of removing them? And if so, why was it decided to keep them?
Regards,
Olegs
Re: Rationale for PUBLIC having CREATE and USAGE privileges on theschema "public" by default
От
"David G. Johnston"
Дата:
Hi,I'm aware that these default privileges are documented:However, I'm unable to find any reasoning behind their existence. Normally, one can just revoke them and move on, but they have caused me some trouble in a managed deployment (Compose, where you can't login as the owner of the schema or as a superuser, so it's impossible to do the revocation, so you're essentially forced to use a non-public schema), and I would like to understand the need for them.I assume they make it a bit easier for new users to try out the database, and I see that they go at least as far as 7.3, but has there been any discussion of removing them? And if so, why was it decided to keep them?
No recent discussions that I recall.
I'm not sure whether you are really being limited/forced here or if you are thinking that having CREATE and USAGE on a schema is more powerful than it is...
In any case any solution at this point would require intervention on the part of Compose since PostgreSQL isn't going to be changing its default install and almost certainly break tons of scripts that rely upon it. No matter whether the original rationale still holds as strongly, whatever it may have been. It is a convenience feature by definition, and one that especially benefits new users but that nearly all users have come to rely upon (some may choose to immediately revoke public defaults on their systems but I'd assume many do not).
Given the above, you should probably ask Compose to add an option to their system where you can request disabling of certain default privileges (or removal of the public schema altogether) and their system will do so, as superuser, on your behalf.
I haven't had any issues with creating and using application-specific schemas and as such I'm not seeing where "essentially forced to use a non-public schema" is a hardship.
David J.
Re: Rationale for PUBLIC having CREATE and USAGE privileges on theschema "public" by default
От
Olegs Jeremejevs
Дата:
Thanks for the reply.
> I'm not sure whether you are really being limited/forced here or if you are thinking that having CREATE and USAGE on a schema is more powerful than it is...
As far as I know, having these permissions has a DoS potential, though, admittedly, negligible, if the rest of the database is secured properly. Just wanted to play safe and revoke them.
> ... PostgreSQL isn't going to be changing its default install and almost certainly break tons of scripts that rely upon it
>
> It is a convenience feature by definition ... that nearly all users have come to rely upon
That's understandable, but, if deemed necessary, can't PostgreSQL break something like this on a major release? Or is the benefit of doing so being weighed against the added friction to upgrading?
> ... any solution at this point would require intervention on the part of Compose
>
>
> Given the above, you should probably ask Compose
Have already done that. This was a bit over a year ago, actually, just never got around to reporting it here, until now. Seems like they still haven't addressed this.
> ... I'm not seeing where "essentially forced to use a non-public schema" is a hardship
Now that I have thought about it some more, there's no point in using an application-specific schema just because the "public" schema has these permissions, because the DoS angle is still there, as long as the permissions aren't revoked or the schema isn't deleted, so I take back the "essentially forced to" part.
Regards,
Olegs
On Sat, Feb 17, 2018 at 2:57 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:
Hi,I'm aware that these default privileges are documented:However, I'm unable to find any reasoning behind their existence. Normally, one can just revoke them and move on, but they have caused me some trouble in a managed deployment (Compose, where you can't login as the owner of the schema or as a superuser, so it's impossible to do the revocation, so you're essentially forced to use a non-public schema), and I would like to understand the need for them.I assume they make it a bit easier for new users to try out the database, and I see that they go at least as far as 7.3, but has there been any discussion of removing them? And if so, why was it decided to keep them?No recent discussions that I recall.I'm not sure whether you are really being limited/forced here or if you are thinking that having CREATE and USAGE on a schema is more powerful than it is...In any case any solution at this point would require intervention on the part of Compose since PostgreSQL isn't going to be changing its default install and almost certainly break tons of scripts that rely upon it. No matter whether the original rationale still holds as strongly, whatever it may have been. It is a convenience feature by definition, and one that especially benefits new users but that nearly all users have come to rely upon (some may choose to immediately revoke public defaults on their systems but I'd assume many do not).Given the above, you should probably ask Compose to add an option to their system where you can request disabling of certain default privileges (or removal of the public schema altogether) and their system will do so, as superuser, on your behalf.I haven't had any issues with creating and using application-specific schemas and as such I'm not seeing where "essentially forced to use a non-public schema" is a hardship.David J.
Re: Rationale for PUBLIC having CREATE and USAGE privileges on theschema "public" by default
От
"David G. Johnston"
Дата:
On Saturday, February 17, 2018, Olegs Jeremejevs <olegs@jeremejevs.com> wrote:
Thanks for the reply.> I'm not sure whether you are really being limited/forced here or if you are thinking that having CREATE and USAGE on a schema is more powerful than it is...As far as I know, having these permissions has a DoS potential, though, admittedly, negligible, if the rest of the database is secured properly. Just wanted to play safe and revoke them.
To an extent it is possible to DoS so long as you have a session and access to pg_catalog. Having create and usage on public doesn't meaningfully (if at all) expand the risk surface area. Default also provides for creating temporary tables.
David J.
Re: Rationale for PUBLIC having CREATE and USAGE privileges on theschema "public" by default
От
Olegs Jeremejevs
Дата:
Okay, in other words, there's no way to completely defend oneself from DoS attacks which require having a session? If so, is there a scenario where some bad actor can create a new user for themselves (to connect to the database with), and not be able to do anything more damaging than that? For example, if I can do an SQL injection, then I can do something more clever than running a CREATE ROLE. And if not, then there's no point in worrying about privileges in a single-tenant database? Beyond human error safeguards.
Olegs
On Sat, Feb 17, 2018 at 10:08 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Saturday, February 17, 2018, Olegs Jeremejevs <olegs@jeremejevs.com> wrote:Thanks for the reply.> I'm not sure whether you are really being limited/forced here or if you are thinking that having CREATE and USAGE on a schema is more powerful than it is...As far as I know, having these permissions has a DoS potential, though, admittedly, negligible, if the rest of the database is secured properly. Just wanted to play safe and revoke them.To an extent it is possible to DoS so long as you have a session and access to pg_catalog. Having create and usage on public doesn't meaningfully (if at all) expand the risk surface area. Default also provides for creating temporary tables.David J.
On 17/02/18 20:48, Olegs Jeremejevs wrote: > Okay, in other words, there's no way to completely defend oneself from > DoS attacks which require having a session? If so, is there a scenario > where some bad actor can create a new user for themselves (to connect > to the database with), and not be able to do anything more damaging > than that? For example, if I can do an SQL injection, then I can do > something more clever than running a CREATE ROLE. And if not, then > there's no point in worrying about privileges in a single-tenant > database? Beyond human error safeguards. > > Olegs How about execution limits, Olegs? Tim Clarke
Вложения
Re: Rationale for PUBLIC having CREATE and USAGE privileges on theschema "public" by default
От
"David G. Johnston"
Дата:
On Saturday, February 17, 2018, Olegs Jeremejevs <olegs@jeremejevs.com> wrote:
Okay, in other words, there's no way to completely defend oneself from DoS attacks which require having a session? If so, is there a scenario where some bad actor can create a new user for themselves (to connect to the database with), and not be able to do anything more damaging than that? For example, if I can do an SQL injection, then I can do something more clever than running a CREATE ROLE. And if not, then there's no point in worrying about privileges in a single-tenant database? Beyond human error safeguards.
Roles that applications use should not be superuser or given createrole so your example should not arise. But any logged user can do something like:
Select * from generate_series1,100000000) cross join generate_series(1,100000000)
Privileges are largely valuable for information privacy and security, and preventing subtle attacks.
David J.
Re: Rationale for PUBLIC having CREATE and USAGE privileges on theschema "public" by default
От
Olegs Jeremejevs
Дата:
Okay, thanks, I'll stop worrying about the defaults then. Have a nice evening!
Olegs
On Sat, Feb 17, 2018 at 11:49 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Saturday, February 17, 2018, Olegs Jeremejevs <olegs@jeremejevs.com> wrote:Okay, in other words, there's no way to completely defend oneself from DoS attacks which require having a session? If so, is there a scenario where some bad actor can create a new user for themselves (to connect to the database with), and not be able to do anything more damaging than that? For example, if I can do an SQL injection, then I can do something more clever than running a CREATE ROLE. And if not, then there's no point in worrying about privileges in a single-tenant database? Beyond human error safeguards.Roles that applications use should not be superuser or given createrole so your example should not arise. But any logged user can do something like:Select * from generate_series1,100000000) cross join generate_series(1,100000000)Privileges are largely valuable for information privacy and security, and preventing subtle attacks.David J.