Rationale for PUBLIC having CREATE and USAGE privileges on the schema "public" by default
Hi,
I'm aware that these default privileges are documented:
https://www.postgresql.org/docs/10/static/ddl-schemas.html#DDL-SCHEMAS-PRIV
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
On Fri, Feb 16, 2018 at 4:50 PM, Olegs Jeremejevs <olegs@jeremejevs.com>
wrote:
Hi,
I'm aware that these default privileges are documented:
https://www.postgresql.org/docs/10/static/ddl-schemas.
html#DDL-SCHEMAS-PRIVHowever, 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.
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:
Show quoted text
On Fri, Feb 16, 2018 at 4:50 PM, Olegs Jeremejevs <olegs@jeremejevs.com>
wrote:Hi,
I'm aware that these default privileges are documented:
https://www.postgresql.org/docs/10/static/ddl-schemas.html#
DDL-SCHEMAS-PRIVHowever, 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.
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.
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:
Show quoted text
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
Attachments:
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.
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:
Show quoted text
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.