public schema default ACL
Commit 5770172 ("Document security implications of search_path and the public
schema.") is largely a workaround for the fact that the boot_val of
search_path contains "public" while template0 gets "GRANT CREATE, USAGE ON
SCHEMA public TO PUBLIC". It's like having world-writable /usr/bin. The
security team opted not to change that in released branches, but we thought to
revisit it later. I propose, for v11, switching to "GRANT USAGE ON SCHEMA
public TO PUBLIC" (omit CREATE). Concerns? An alternative is to change the
default search_path to "$user"; that would be break more applications, and I
don't see an advantage to compensate for that.
If we do that alone, databases reaching v11 via dump/reload or pg_upgrade will
get the new default ACL if they had not changed the ACL of schema public. If
they had GRANTed or REVOKEd on schema public, pg_dump will recreate the
resulting ACL. This is the standard pg_dump behavior for ACLs on system
objects. I think that's okay for the public schema, too, and I like
preserving that usual rule. However, if we wanted to minimize upgrade-time
surprises, we could make pg_dump include GRANT for schema public
unconditionally. That way, the default ACL change would apply to new
databases only. Does anyone want to argue for that?
Thanks,
nm
On 03/03/2018 01:56 AM, Noah Misch wrote:
Commit 5770172 ("Document security implications of search_path and the public
schema.") is largely a workaround for the fact that the boot_val of
search_path contains "public" while template0 gets "GRANT CREATE, USAGE ON
SCHEMA public TO PUBLIC". It's like having world-writable /usr/bin. The
security team opted not to change that in released branches, but we thought to
revisit it later. I propose, for v11, switching to "GRANT USAGE ON SCHEMA
public TO PUBLIC" (omit CREATE). Concerns?
+1. Doing this, or even revoking everything for schema public from
PUBLIC, is already common enough and good practice.
If we do that alone, databases reaching v11 via dump/reload or pg_upgrade will
get the new default ACL if they had not changed the ACL of schema public. If
they had GRANTed or REVOKEd on schema public, pg_dump will recreate the
resulting ACL. This is the standard pg_dump behavior for ACLs on system
objects. I think that's okay for the public schema, too, and I like
preserving that usual rule. However, if we wanted to minimize upgrade-time
surprises, we could make pg_dump include GRANT for schema public
unconditionally. That way, the default ACL change would apply to new
databases only. Does anyone want to argue for that?
What about a pg_dump option to do that and then a big note in the
release notes telling people why they might want to use it?
Joe
--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development
On Sat, Mar 03, 2018 at 02:31:58AM -0800, Joe Conway wrote:
On 03/03/2018 01:56 AM, Noah Misch wrote:
If we do that alone, databases reaching v11 via dump/reload or pg_upgrade will
get the new default ACL if they had not changed the ACL of schema public. If
they had GRANTed or REVOKEd on schema public, pg_dump will recreate the
resulting ACL. This is the standard pg_dump behavior for ACLs on system
objects. I think that's okay for the public schema, too, and I like
preserving that usual rule. However, if we wanted to minimize upgrade-time
surprises, we could make pg_dump include GRANT for schema public
unconditionally. That way, the default ACL change would apply to new
databases only. Does anyone want to argue for that?What about a pg_dump option to do that and then a big note in the
release notes telling people why they might want to use it?
I'd want any new pg_dump option to have use beyond this one case. That is,
not --old-public-schema-acl, but perhaps --old-system-acls-for=OBJECT-PATTERN.
But it's a simple task to loop over your databases and run a GRANT, so I
somewhat doubt that particular idea should win. Hmm.
On Sat, Mar 3, 2018 at 4:56 AM, Noah Misch <noah@leadboat.com> wrote:
Commit 5770172 ("Document security implications of search_path and the public
schema.") is largely a workaround for the fact that the boot_val of
search_path contains "public" while template0 gets "GRANT CREATE, USAGE ON
SCHEMA public TO PUBLIC". It's like having world-writable /usr/bin. The
security team opted not to change that in released branches, but we thought to
revisit it later. I propose, for v11, switching to "GRANT USAGE ON SCHEMA
public TO PUBLIC" (omit CREATE). Concerns? An alternative is to change the
default search_path to "$user"; that would be break more applications, and I
don't see an advantage to compensate for that.
Isn't this going to cause widespread breakage? Unprivileged users
will suddenly find that they can no longer create tables, because
$user doesn't exist and they don't have permission on public. That
seems quite unfriendly.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
On Sat, Mar 3, 2018 at 4:56 AM, Noah Misch <noah@leadboat.com> wrote:
I propose, for v11, switching to "GRANT USAGE ON SCHEMA
public TO PUBLIC" (omit CREATE). Concerns? An alternative is to change the
default search_path to "$user"; that would be break more applications, and I
don't see an advantage to compensate for that.
Isn't this going to cause widespread breakage? Unprivileged users
will suddenly find that they can no longer create tables, because
$user doesn't exist and they don't have permission on public. That
seems quite unfriendly.
Well, the fundamental problem here is that the arrangements around schema
public were set up to allow a smooth transition from the pre-7.3
no-schemas world, not to provide any kind of security. If we want to use
schemas for security then we're going to have to do *something* that's not
compatible. Or we can continue to ship an insecure default configuration,
but I recall many people arguing against that sort of choice in the past.
I wonder whether it'd be sensible for CREATE USER --- or at least the
createuser script --- to automatically make a matching schema. Or we
could just recommend that DBAs do so. Either way, we'd be pushing people
towards the design where "$user" does exist for most/all users. Our docs
comment (section 5.8.7) that "the concepts of schema and user are nearly
equivalent in a database system that implements only the basic schema
support specified in the standard", so the idea of automatically making
a schema per user doesn't seem ridiculous on its face. (Now, where'd I
put my flameproof long johns ...)
regards, tom lane
Greetings Tom, all,
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
Robert Haas <robertmhaas@gmail.com> writes:
On Sat, Mar 3, 2018 at 4:56 AM, Noah Misch <noah@leadboat.com> wrote:
I propose, for v11, switching to "GRANT USAGE ON SCHEMA
public TO PUBLIC" (omit CREATE). Concerns? An alternative is to change the
default search_path to "$user"; that would be break more applications, and I
don't see an advantage to compensate for that.Isn't this going to cause widespread breakage? Unprivileged users
will suddenly find that they can no longer create tables, because
$user doesn't exist and they don't have permission on public. That
seems quite unfriendly.Well, the fundamental problem here is that the arrangements around schema
public were set up to allow a smooth transition from the pre-7.3
no-schemas world, not to provide any kind of security. If we want to use
schemas for security then we're going to have to do *something* that's not
compatible. Or we can continue to ship an insecure default configuration,
but I recall many people arguing against that sort of choice in the past.
I concur that this is the fundamental issue and that the privilege
system around schemas weren't considered due to the desire to provide a
smooth transition, but we are quite a long way from 7.3 and there's
abundent evidence that the current defaults are insecure by default.
I'll point out that a number of our *other* defaults are also insecure
(pg_hba.conf entries with 'trust' being particulalrly bad). Those have
been worked around by packagers, but that really isn't ideal. I'd love
to see us ship an actually secure (or even just reasonable, frankly...)
default configuration.
I wonder whether it'd be sensible for CREATE USER --- or at least the
createuser script --- to automatically make a matching schema. Or we
could just recommend that DBAs do so. Either way, we'd be pushing people
towards the design where "$user" does exist for most/all users. Our docs
comment (section 5.8.7) that "the concepts of schema and user are nearly
equivalent in a database system that implements only the basic schema
support specified in the standard", so the idea of automatically making
a schema per user doesn't seem ridiculous on its face. (Now, where'd I
put my flameproof long johns ...)
You are not the first to think of this in recent days, and I'm hopeful
to see others comment in support of this idea. For my 2c, I'd suggest
that what we actually do is have a new role attribute which is "when
this user connects to a database, if they don't have a schema named
after their role, then create one." Creating the role at CREATE ROLE
time would only work for the current database, after all (barring some
other magic that allows us to create schemas in all current and future
databases...).
Thanks!
Stephen
On Tue, Mar 06, 2018 at 09:28:21PM -0500, Stephen Frost wrote:
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
Robert Haas <robertmhaas@gmail.com> writes:
On Sat, Mar 3, 2018 at 4:56 AM, Noah Misch <noah@leadboat.com> wrote:
I propose, for v11, switching to "GRANT USAGE ON SCHEMA
public TO PUBLIC" (omit CREATE). Concerns? An alternative is to change the
default search_path to "$user"; that would be break more applications, and I
don't see an advantage to compensate for that.Isn't this going to cause widespread breakage? Unprivileged users
will suddenly find that they can no longer create tables, because
$user doesn't exist and they don't have permission on public. That
seems quite unfriendly.
It will, but the level of breakage seems similar to that from removing
PGC_SIGHUP GUCs, which we've done in major releases without great harm.
I wonder whether it'd be sensible for CREATE USER --- or at least the
createuser script --- to automatically make a matching schema. Or we
could just recommend that DBAs do so. Either way, we'd be pushing people
towards the design where "$user" does exist for most/all users. Our docs
comment (section 5.8.7) that "the concepts of schema and user are nearly
equivalent in a database system that implements only the basic schema
support specified in the standard", so the idea of automatically making
a schema per user doesn't seem ridiculous on its face. (Now, where'd I
put my flameproof long johns ...)You are not the first to think of this in recent days, and I'm hopeful
to see others comment in support of this idea. For my 2c, I'd suggest
that what we actually do is have a new role attribute which is "when
this user connects to a database, if they don't have a schema named
after their role, then create one." Creating the role at CREATE ROLE
time would only work for the current database, after all (barring some
other magic that allows us to create schemas in all current and future
databases...).
I like the idea of getting more SQL-compatible, if this presents a distinct
opportunity to do so. I do think it would be too weird to create the schema
in one database only. Creating it on demand might work. What would be the
procedure, if any, for database owners who want to deny object creation in
their databases?
On 07/03/18 08:23, Noah Misch wrote:
On Tue, Mar 06, 2018 at 09:28:21PM -0500, Stephen Frost wrote:
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
Robert Haas <robertmhaas@gmail.com> writes:
On Sat, Mar 3, 2018 at 4:56 AM, Noah Misch <noah@leadboat.com> wrote:
I propose, for v11, switching to "GRANT USAGE ON SCHEMA
public TO PUBLIC" (omit CREATE). Concerns? An alternative is to change the
default search_path to "$user"; that would be break more applications, and I
don't see an advantage to compensate for that.Isn't this going to cause widespread breakage? Unprivileged users
will suddenly find that they can no longer create tables, because
$user doesn't exist and they don't have permission on public. That
seems quite unfriendly.It will, but the level of breakage seems similar to that from removing
PGC_SIGHUP GUCs, which we've done in major releases without great harm.I wonder whether it'd be sensible for CREATE USER --- or at least the
createuser script --- to automatically make a matching schema. Or we
could just recommend that DBAs do so. Either way, we'd be pushing people
towards the design where "$user" does exist for most/all users. Our docs
comment (section 5.8.7) that "the concepts of schema and user are nearly
equivalent in a database system that implements only the basic schema
support specified in the standard", so the idea of automatically making
a schema per user doesn't seem ridiculous on its face. (Now, where'd I
put my flameproof long johns ...)You are not the first to think of this in recent days, and I'm hopeful
to see others comment in support of this idea. For my 2c, I'd suggest
that what we actually do is have a new role attribute which is "when
this user connects to a database, if they don't have a schema named
after their role, then create one." Creating the role at CREATE ROLE
time would only work for the current database, after all (barring some
other magic that allows us to create schemas in all current and future
databases...).I like the idea of getting more SQL-compatible, if this presents a distinct
Certain "market leader" database behaves this way as well. I just hope
we won't go as far as them and also create users for schemas (so that
the analogy of user=schema would be complete and working both ways).
Because that's one of the main reasons their users depend on packages so
much, there is no other way to create a namespace without having to deal
with another user which needs to be secured.
One thing we could do to limit impact of any of this is having
DEFAULT_SCHEMA option for roles which would then be the first one in the
search_path (it could default to the role name), that way making public
schema work again for everybody would be just about tweaking the roles a
bit which can be easily scripted.
TBH I would personally prefer if we got rid of search_path as GUC
completely because it makes certain aspects of DDL logical replication
and connection pooling much more complex, but that does not seem to be a
realistic change.
opportunity to do so. I do think it would be too weird to create the schema
in one database only. Creating it on demand might work. What would be the
procedure, if any, for database owners who want to deny object creation in
their databases?
Well, REVOKE CREATE ON DATABASE already exists.
--
Petr Jelinek http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Greetings,
* Noah Misch (noah@leadboat.com) wrote:
On Tue, Mar 06, 2018 at 09:28:21PM -0500, Stephen Frost wrote:
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
I wonder whether it'd be sensible for CREATE USER --- or at least the
createuser script --- to automatically make a matching schema. Or we
could just recommend that DBAs do so. Either way, we'd be pushing people
towards the design where "$user" does exist for most/all users. Our docs
comment (section 5.8.7) that "the concepts of schema and user are nearly
equivalent in a database system that implements only the basic schema
support specified in the standard", so the idea of automatically making
a schema per user doesn't seem ridiculous on its face. (Now, where'd I
put my flameproof long johns ...)You are not the first to think of this in recent days, and I'm hopeful
to see others comment in support of this idea. For my 2c, I'd suggest
that what we actually do is have a new role attribute which is "when
this user connects to a database, if they don't have a schema named
after their role, then create one." Creating the role at CREATE ROLE
time would only work for the current database, after all (barring some
other magic that allows us to create schemas in all current and future
databases...).I like the idea of getting more SQL-compatible, if this presents a distinct
opportunity to do so. I do think it would be too weird to create the schema
in one database only. Creating it on demand might work. What would be the
procedure, if any, for database owners who want to deny object creation in
their databases?
My suggestion was that this would be a role attribute. If an
administrator doesn't wish for that role to have a schema created
on-demand at login time, they would set the 'SCHEMA_CREATE' (or whatever
we name it) role attribute to false.
Thanks!
Stephen
Greetings,
* Petr Jelinek (petr.jelinek@2ndquadrant.com) wrote:
Certain "market leader" database behaves this way as well. I just hope
we won't go as far as them and also create users for schemas (so that
the analogy of user=schema would be complete and working both ways).
Because that's one of the main reasons their users depend on packages so
much, there is no other way to create a namespace without having to deal
with another user which needs to be secured.
I agree that we do *not* want to force role creation on schema creation.
One thing we could do to limit impact of any of this is having
DEFAULT_SCHEMA option for roles which would then be the first one in the
search_path (it could default to the role name), that way making public
schema work again for everybody would be just about tweaking the roles a
bit which can be easily scripted.
I don't entirely get what you're suggesting here considering we already
have $user, and it is the first in the search_path..?
TBH I would personally prefer if we got rid of search_path as GUC
completely because it makes certain aspects of DDL logical replication
and connection pooling much more complex, but that does not seem to be a
realistic change.
No, I don't think we're going to get rid of it.
opportunity to do so. I do think it would be too weird to create the schema
in one database only. Creating it on demand might work. What would be the
procedure, if any, for database owners who want to deny object creation in
their databases?Well, REVOKE CREATE ON DATABASE already exists.
That really isn't the same.. In this approach, regular roles are *not*
given the CREATE right on the database, the system would just create the
schema for them on login automatically if the role attribute says to do
so.
Thanks!
Stephen
On 3/6/18 15:20, Robert Haas wrote:
On Sat, Mar 3, 2018 at 4:56 AM, Noah Misch <noah@leadboat.com> wrote:
I propose, for v11, switching to "GRANT USAGE ON SCHEMA
public TO PUBLIC" (omit CREATE). Concerns? An alternative is to change the
default search_path to "$user"; that would be break more applications, and I
don't see an advantage to compensate for that.Isn't this going to cause widespread breakage? Unprivileged users
will suddenly find that they can no longer create tables, because
$user doesn't exist and they don't have permission on public. That
seems quite unfriendly.
Moreover, the problem is that if you have database owners that are not
superusers, they can't easily fix the issue themselves. Since the
public schema is owned by postgres, they database owner can't just go in
and run GRANT CREATE ON SCHEMA PUBLIC TO whomever to restore the old
behavior or grant specific access. It would be simpler if we didn't
install a public schema by default at all.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Stephen Frost wrote:
* Noah Misch (noah@leadboat.com) wrote:
I like the idea of getting more SQL-compatible, if this presents a distinct
opportunity to do so. I do think it would be too weird to create the schema
in one database only. Creating it on demand might work. What would be the
procedure, if any, for database owners who want to deny object creation in
their databases?My suggestion was that this would be a role attribute. If an
administrator doesn't wish for that role to have a schema created
on-demand at login time, they would set the 'SCHEMA_CREATE' (or whatever
we name it) role attribute to false.
Is a single attribute enough? I think we need two: one would authorize
to create the schema $user to the user themselves (maybe
SELF_SCHEMA_CREATE); another would automatically do so when connecting
to a database that does not have it (perhaps AUTO_CREATE_SCHEMA).
Now, maybe the idea of creating it as soon as a connection is
established is not great. What about creating it only when the first
object creation is attempted and there is no other schema to create in?
This avoid pointless proliferation of empty user schemas, as well as
avoid the overhead of checking existence of schem $user on each
connection.
--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
Now, maybe the idea of creating it as soon as a connection is
established is not great. What about creating it only when the first
object creation is attempted and there is no other schema to create in?
This avoid pointless proliferation of empty user schemas, as well as
avoid the overhead of checking existence of schem $user on each
connection.
Hmm. On first glance that sounds bizarre, but we do something pretty
similar for the pg_temp schemas, so it could likely be made to work.
One issue to think about is exactly which $user we intend to make the
schema for, if we've executed SET SESSION AUTHORIZATION, or are inside
a SECURITY DEFINER function, etc etc. I'd argue that only the original
connection username should get this treatment, which may mean that object
creation can fail in those contexts.
regards, tom lane
Greetings,
* Alvaro Herrera (alvherre@alvh.no-ip.org) wrote:
Stephen Frost wrote:
* Noah Misch (noah@leadboat.com) wrote:
I like the idea of getting more SQL-compatible, if this presents a distinct
opportunity to do so. I do think it would be too weird to create the schema
in one database only. Creating it on demand might work. What would be the
procedure, if any, for database owners who want to deny object creation in
their databases?My suggestion was that this would be a role attribute. If an
administrator doesn't wish for that role to have a schema created
on-demand at login time, they would set the 'SCHEMA_CREATE' (or whatever
we name it) role attribute to false.Is a single attribute enough? I think we need two: one would authorize
to create the schema $user to the user themselves (maybe
SELF_SCHEMA_CREATE); another would automatically do so when connecting
to a database that does not have it (perhaps AUTO_CREATE_SCHEMA).
I don't see a use-case for this SELF_SCHEMA_CREATE attribute and it
seems more likely to cause confusion than to be helpful. If the admin
sets AUTO_CREATE_SCHEMA for a user then that's what we should do.
Now, maybe the idea of creating it as soon as a connection is
established is not great. What about creating it only when the first
object creation is attempted and there is no other schema to create in?
This avoid pointless proliferation of empty user schemas, as well as
avoid the overhead of checking existence of schem $user on each
connection.
I don't see how creating schemas for roles which the admin has created
with the AUTO_CREATE_SCHEMA option would be pointless. To not do so
would be confusing, imo. Consider the user who logs in and doesn't
realize that they're allowed to create a schema and doesn't see a schema
of their own in the list- they aren't going to think "I should just try
to create an object and see if a schema appears", they're going to ask
the admin why they don't have a schema.
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
Hmm. On first glance that sounds bizarre, but we do something pretty
similar for the pg_temp schemas, so it could likely be made to work.
While I agree that it might not be that hard to make the code do it,
since we do this for temp schemas, I still don't see real value in it
and instead just a confusing system where schemas "appear" at some
arbitrary point when the user happens to try to create an object without
qualification.
I liken this to a well-known and well-trodden feature for auto creating
user home directories on Unix. Being different from that for, at best,
rare use-cases which could be handled in other ways is going against
POLA. If an admin is concerned about too many empty schemas or about
having $user in a search_path and needing to search it, then those are
entirely fixable rather easily, but those are the uncommon cases in my
experience.
One issue to think about is exactly which $user we intend to make the
schema for, if we've executed SET SESSION AUTHORIZATION, or are inside
a SECURITY DEFINER function, etc etc. I'd argue that only the original
connection username should get this treatment, which may mean that object
creation can fail in those contexts.
This just strengthens the "this will be confusing to our users" argument,
imv.
Thanks!
Stephen
Import Notes
Reply to msg id not found: 19853.1520433890@sss.pgh.pa.us20180307143610.u6jb4tlzx2n64x22@alvherre.pgsql | Resolved by subject fallback
On 07/03/18 13:18, Stephen Frost wrote:
Greetings,
* Petr Jelinek (petr.jelinek@2ndquadrant.com) wrote:
Certain "market leader" database behaves this way as well. I just hope
we won't go as far as them and also create users for schemas (so that
the analogy of user=schema would be complete and working both ways).
Because that's one of the main reasons their users depend on packages so
much, there is no other way to create a namespace without having to deal
with another user which needs to be secured.I agree that we do *not* want to force role creation on schema creation.
One thing we could do to limit impact of any of this is having
DEFAULT_SCHEMA option for roles which would then be the first one in the
search_path (it could default to the role name), that way making public
schema work again for everybody would be just about tweaking the roles a
bit which can be easily scripted.I don't entirely get what you're suggesting here considering we already
have $user, and it is the first in the search_path..?
What I am suggesting is that we add option to set user's default schema
to something other than user name so that if people don't want the
schema with the name of the user auto-created, it won't be.
opportunity to do so. I do think it would be too weird to create the schema
in one database only. Creating it on demand might work. What would be the
procedure, if any, for database owners who want to deny object creation in
their databases?Well, REVOKE CREATE ON DATABASE already exists.
That really isn't the same.. In this approach, regular roles are *not*
given the CREATE right on the database, the system would just create the
schema for them on login automatically if the role attribute says to do
so.
What's the point of creating schema for them if they don't have CREATE
privilege?
--
Petr Jelinek http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Greeting Petr, all,
* Petr Jelinek (petr.jelinek@2ndquadrant.com) wrote:
On 07/03/18 13:18, Stephen Frost wrote:
Greetings,
* Petr Jelinek (petr.jelinek@2ndquadrant.com) wrote:
Certain "market leader" database behaves this way as well. I just hope
we won't go as far as them and also create users for schemas (so that
the analogy of user=schema would be complete and working both ways).
Because that's one of the main reasons their users depend on packages so
much, there is no other way to create a namespace without having to deal
with another user which needs to be secured.I agree that we do *not* want to force role creation on schema creation.
One thing we could do to limit impact of any of this is having
DEFAULT_SCHEMA option for roles which would then be the first one in the
search_path (it could default to the role name), that way making public
schema work again for everybody would be just about tweaking the roles a
bit which can be easily scripted.I don't entirely get what you're suggesting here considering we already
have $user, and it is the first in the search_path..?What I am suggesting is that we add option to set user's default schema
to something other than user name so that if people don't want the
schema with the name of the user auto-created, it won't be.
We have ALTER USER joe SET search_path already though..? And ALTER
DATABASE, and in postgresql.conf? What are we missing?
opportunity to do so. I do think it would be too weird to create the schema
in one database only. Creating it on demand might work. What would be the
procedure, if any, for database owners who want to deny object creation in
their databases?Well, REVOKE CREATE ON DATABASE already exists.
That really isn't the same.. In this approach, regular roles are *not*
given the CREATE right on the database, the system would just create the
schema for them on login automatically if the role attribute says to do
so.What's the point of creating schema for them if they don't have CREATE
privilege?
They would own the schema and therefore have CREATE and USAGE rights on
the schema itself. Creating objects checks for schema rights, it
doesn't check for database rights- that's only if you're creating
schemas.
Thanks!
Stephen
On 07/03/18 16:26, Stephen Frost wrote:
Greeting Petr, all,
* Petr Jelinek (petr.jelinek@2ndquadrant.com) wrote:
On 07/03/18 13:18, Stephen Frost wrote:
Greetings,
* Petr Jelinek (petr.jelinek@2ndquadrant.com) wrote:
Certain "market leader" database behaves this way as well. I just hope
we won't go as far as them and also create users for schemas (so that
the analogy of user=schema would be complete and working both ways).
Because that's one of the main reasons their users depend on packages so
much, there is no other way to create a namespace without having to deal
with another user which needs to be secured.I agree that we do *not* want to force role creation on schema creation.
One thing we could do to limit impact of any of this is having
DEFAULT_SCHEMA option for roles which would then be the first one in the
search_path (it could default to the role name), that way making public
schema work again for everybody would be just about tweaking the roles a
bit which can be easily scripted.I don't entirely get what you're suggesting here considering we already
have $user, and it is the first in the search_path..?What I am suggesting is that we add option to set user's default schema
to something other than user name so that if people don't want the
schema with the name of the user auto-created, it won't be.We have ALTER USER joe SET search_path already though..? And ALTER
DATABASE, and in postgresql.conf? What are we missing?
That will not change the fact that we have created schema joe for that
user though. While something like CREATE USER joe DEFAULT_SCHEMA foobar
would.
My point is that I don't mind if we create schemas for users by default,
but I want simple way to opt out.
opportunity to do so. I do think it would be too weird to create the schema
in one database only. Creating it on demand might work. What would be the
procedure, if any, for database owners who want to deny object creation in
their databases?Well, REVOKE CREATE ON DATABASE already exists.
That really isn't the same.. In this approach, regular roles are *not*
given the CREATE right on the database, the system would just create the
schema for them on login automatically if the role attribute says to do
so.What's the point of creating schema for them if they don't have CREATE
privilege?They would own the schema and therefore have CREATE and USAGE rights on
the schema itself. Creating objects checks for schema rights, it
doesn't check for database rights- that's only if you're creating
schemas.
Yes, but should the schema for them be created at all if they don't have
CREATE privilege on the database? If yes then I have same question as
Noah, how does dba prevent object creation in their databases?
--
Petr Jelinek http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
On 07/03/18 13:14, Stephen Frost wrote:
Greetings,
* Noah Misch (noah@leadboat.com) wrote:
On Tue, Mar 06, 2018 at 09:28:21PM -0500, Stephen Frost wrote:
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
I wonder whether it'd be sensible for CREATE USER --- or at least the
createuser script --- to automatically make a matching schema. Or we
could just recommend that DBAs do so. Either way, we'd be pushing people
towards the design where "$user" does exist for most/all users. Our docs
comment (section 5.8.7) that "the concepts of schema and user are nearly
equivalent in a database system that implements only the basic schema
support specified in the standard", so the idea of automatically making
a schema per user doesn't seem ridiculous on its face. (Now, where'd I
put my flameproof long johns ...)You are not the first to think of this in recent days, and I'm hopeful
to see others comment in support of this idea. For my 2c, I'd suggest
that what we actually do is have a new role attribute which is "when
this user connects to a database, if they don't have a schema named
after their role, then create one." Creating the role at CREATE ROLE
time would only work for the current database, after all (barring some
other magic that allows us to create schemas in all current and future
databases...).I like the idea of getting more SQL-compatible, if this presents a distinct
opportunity to do so. I do think it would be too weird to create the schema
in one database only. Creating it on demand might work. What would be the
procedure, if any, for database owners who want to deny object creation in
their databases?My suggestion was that this would be a role attribute. If an
administrator doesn't wish for that role to have a schema created
on-demand at login time, they would set the 'SCHEMA_CREATE' (or whatever
we name it) role attribute to false.
Yeah I think role attribute makes sense, it's why I suggested something
like DEFAULT_SCHEMA, that seems to address both schema creation (dba can
point the schema to public for example) and also the fact that $user
schema which is first in search_path might or might not exist.
Question would be what happens if schema is then explicitly dropper (in
either case).
--
Petr Jelinek http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Greetings Petr, all,
* Petr Jelinek (petr.jelinek@2ndquadrant.com) wrote:
On 07/03/18 16:26, Stephen Frost wrote:
Greeting Petr, all,
* Petr Jelinek (petr.jelinek@2ndquadrant.com) wrote:
On 07/03/18 13:18, Stephen Frost wrote:
Greetings,
* Petr Jelinek (petr.jelinek@2ndquadrant.com) wrote:
Certain "market leader" database behaves this way as well. I just hope
we won't go as far as them and also create users for schemas (so that
the analogy of user=schema would be complete and working both ways).
Because that's one of the main reasons their users depend on packages so
much, there is no other way to create a namespace without having to deal
with another user which needs to be secured.I agree that we do *not* want to force role creation on schema creation.
One thing we could do to limit impact of any of this is having
DEFAULT_SCHEMA option for roles which would then be the first one in the
search_path (it could default to the role name), that way making public
schema work again for everybody would be just about tweaking the roles a
bit which can be easily scripted.I don't entirely get what you're suggesting here considering we already
have $user, and it is the first in the search_path..?What I am suggesting is that we add option to set user's default schema
to something other than user name so that if people don't want the
schema with the name of the user auto-created, it won't be.We have ALTER USER joe SET search_path already though..? And ALTER
DATABASE, and in postgresql.conf? What are we missing?That will not change the fact that we have created schema joe for that
user though. While something like CREATE USER joe DEFAULT_SCHEMA foobar
would.My point is that I don't mind if we create schemas for users by default,
but I want simple way to opt out.
Oh, yes, we would definitely need an opt-out mechanism. It's unclear to
me what adding a 'default schema' role option would do though that's
different from setting the search_path for a user. I certainly wouldn't
expect it to create a new schema....
opportunity to do so. I do think it would be too weird to create the schema
in one database only. Creating it on demand might work. What would be the
procedure, if any, for database owners who want to deny object creation in
their databases?Well, REVOKE CREATE ON DATABASE already exists.
That really isn't the same.. In this approach, regular roles are *not*
given the CREATE right on the database, the system would just create the
schema for them on login automatically if the role attribute says to do
so.What's the point of creating schema for them if they don't have CREATE
privilege?They would own the schema and therefore have CREATE and USAGE rights on
the schema itself. Creating objects checks for schema rights, it
doesn't check for database rights- that's only if you're creating
schemas.Yes, but should the schema for them be created at all if they don't have
CREATE privilege on the database? If yes then I have same question as
Noah, how does dba prevent object creation in their databases?
Yes, the schema would be created regardless of the rights of the user on
the database, because the admin set the flag on the role saying 'create
a schema for this user when they log in.'
If we think there is a use-case for saying "this user should only have
schemas in these databases, not all databases" then I could see having
the role attribute be a list of databases or "all", instead. In the
end, I do think this is something which is controlled at the role level
and not something an individual database owner could override or
prevent, though perhaps there is some room for discussion there.
What I don't want is for this feature to *depend* on the users having
CREATE rights on the database, as that would allow them to create other
schemas (perhaps even one which is named the same as a likely new user
whose account hasn't been created yet or they haven't logged in yet...).
Thanks!
Stephen
Greetings Petr, all,
* Petr Jelinek (petr.jelinek@2ndquadrant.com) wrote:
On 07/03/18 13:14, Stephen Frost wrote:
* Noah Misch (noah@leadboat.com) wrote:
On Tue, Mar 06, 2018 at 09:28:21PM -0500, Stephen Frost wrote:
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
I wonder whether it'd be sensible for CREATE USER --- or at least the
createuser script --- to automatically make a matching schema. Or we
could just recommend that DBAs do so. Either way, we'd be pushing people
towards the design where "$user" does exist for most/all users. Our docs
comment (section 5.8.7) that "the concepts of schema and user are nearly
equivalent in a database system that implements only the basic schema
support specified in the standard", so the idea of automatically making
a schema per user doesn't seem ridiculous on its face. (Now, where'd I
put my flameproof long johns ...)You are not the first to think of this in recent days, and I'm hopeful
to see others comment in support of this idea. For my 2c, I'd suggest
that what we actually do is have a new role attribute which is "when
this user connects to a database, if they don't have a schema named
after their role, then create one." Creating the role at CREATE ROLE
time would only work for the current database, after all (barring some
other magic that allows us to create schemas in all current and future
databases...).I like the idea of getting more SQL-compatible, if this presents a distinct
opportunity to do so. I do think it would be too weird to create the schema
in one database only. Creating it on demand might work. What would be the
procedure, if any, for database owners who want to deny object creation in
their databases?My suggestion was that this would be a role attribute. If an
administrator doesn't wish for that role to have a schema created
on-demand at login time, they would set the 'SCHEMA_CREATE' (or whatever
we name it) role attribute to false.Yeah I think role attribute makes sense, it's why I suggested something
like DEFAULT_SCHEMA, that seems to address both schema creation (dba can
point the schema to public for example) and also the fact that $user
schema which is first in search_path might or might not exist.
What I dislike about this proposal is that it seems to conflate two
things- if the schema will be created for the user automatically or not,
and what the search_path setting is. Those are two different things and
I don't think we should mix them.
Question would be what happens if schema is then explicitly dropper (in
either case).
I'm not sure that I see an issue with that- if it's dropped then it gets
recreated when that user logs back in. The systems I'm aware of, as
best as I can recall, didn't have any particular check or explicit
additional behavior for such a case.
Thanks!
Stephen
On 07/03/18 17:55, Stephen Frost wrote:
Greetings Petr, all,
* Petr Jelinek (petr.jelinek@2ndquadrant.com) wrote:
On 07/03/18 13:14, Stephen Frost wrote:
* Noah Misch (noah@leadboat.com) wrote:
On Tue, Mar 06, 2018 at 09:28:21PM -0500, Stephen Frost wrote:
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
I wonder whether it'd be sensible for CREATE USER --- or at least the
createuser script --- to automatically make a matching schema. Or we
could just recommend that DBAs do so. Either way, we'd be pushing people
towards the design where "$user" does exist for most/all users. Our docs
comment (section 5.8.7) that "the concepts of schema and user are nearly
equivalent in a database system that implements only the basic schema
support specified in the standard", so the idea of automatically making
a schema per user doesn't seem ridiculous on its face. (Now, where'd I
put my flameproof long johns ...)You are not the first to think of this in recent days, and I'm hopeful
to see others comment in support of this idea. For my 2c, I'd suggest
that what we actually do is have a new role attribute which is "when
this user connects to a database, if they don't have a schema named
after their role, then create one." Creating the role at CREATE ROLE
time would only work for the current database, after all (barring some
other magic that allows us to create schemas in all current and future
databases...).I like the idea of getting more SQL-compatible, if this presents a distinct
opportunity to do so. I do think it would be too weird to create the schema
in one database only. Creating it on demand might work. What would be the
procedure, if any, for database owners who want to deny object creation in
their databases?My suggestion was that this would be a role attribute. If an
administrator doesn't wish for that role to have a schema created
on-demand at login time, they would set the 'SCHEMA_CREATE' (or whatever
we name it) role attribute to false.Yeah I think role attribute makes sense, it's why I suggested something
like DEFAULT_SCHEMA, that seems to address both schema creation (dba can
point the schema to public for example) and also the fact that $user
schema which is first in search_path might or might not exist.What I dislike about this proposal is that it seems to conflate two
things- if the schema will be created for the user automatically or not,
and what the search_path setting is.
Well, what $user in search_path resolves to rather than what search_path is.
Those are two different things and
I don't think we should mix them.
I guess I am missing the point of the schema creation for user then if
it's not also automatically the default schema for that user.
--
Petr Jelinek http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Greetings Petr,
* Petr Jelinek (petr.jelinek@2ndquadrant.com) wrote:
On 07/03/18 17:55, Stephen Frost wrote:
Greetings Petr, all,
* Petr Jelinek (petr.jelinek@2ndquadrant.com) wrote:
On 07/03/18 13:14, Stephen Frost wrote:
* Noah Misch (noah@leadboat.com) wrote:
On Tue, Mar 06, 2018 at 09:28:21PM -0500, Stephen Frost wrote:
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
I wonder whether it'd be sensible for CREATE USER --- or at least the
createuser script --- to automatically make a matching schema. Or we
could just recommend that DBAs do so. Either way, we'd be pushing people
towards the design where "$user" does exist for most/all users. Our docs
comment (section 5.8.7) that "the concepts of schema and user are nearly
equivalent in a database system that implements only the basic schema
support specified in the standard", so the idea of automatically making
a schema per user doesn't seem ridiculous on its face. (Now, where'd I
put my flameproof long johns ...)You are not the first to think of this in recent days, and I'm hopeful
to see others comment in support of this idea. For my 2c, I'd suggest
that what we actually do is have a new role attribute which is "when
this user connects to a database, if they don't have a schema named
after their role, then create one." Creating the role at CREATE ROLE
time would only work for the current database, after all (barring some
other magic that allows us to create schemas in all current and future
databases...).I like the idea of getting more SQL-compatible, if this presents a distinct
opportunity to do so. I do think it would be too weird to create the schema
in one database only. Creating it on demand might work. What would be the
procedure, if any, for database owners who want to deny object creation in
their databases?My suggestion was that this would be a role attribute. If an
administrator doesn't wish for that role to have a schema created
on-demand at login time, they would set the 'SCHEMA_CREATE' (or whatever
we name it) role attribute to false.Yeah I think role attribute makes sense, it's why I suggested something
like DEFAULT_SCHEMA, that seems to address both schema creation (dba can
point the schema to public for example) and also the fact that $user
schema which is first in search_path might or might not exist.What I dislike about this proposal is that it seems to conflate two
things- if the schema will be created for the user automatically or not,
and what the search_path setting is.Well, what $user in search_path resolves to rather than what search_path is.
Alright, that makes a bit more sense to me. I had thought you were
suggesting we would just combine these two pieces to make up the "real"
search path, which I didn't like.
Having it replace what $user is in the search_path would be a bit
confusing, I think. Perhaps having a new '$default' would be alright
though I'm still having a bit of trouble imagining the use-case and it
seems like we'd probably still keep the "wil this schema be created
automatically or not" seperate from this new search path variable.
Those are two different things and
I don't think we should mix them.I guess I am missing the point of the schema creation for user then if
it's not also automatically the default schema for that user.
With our default search path being $user, public, it would be...
Thanks!
Stephen
On 3/7/18 10:05, Stephen Frost wrote:
I liken this to a well-known and well-trodden feature for auto creating
user home directories on Unix.
I don't think likening schemas to home directories is really addressing
the most typical use cases. Database contents are for the most part
carefully constructed in a collaborative way. If your organization has
three DBAs foo, bar, and baz, it's quite unlikely that they will want to
create or look at objects in schemas named foo, bar, or baz. More
likely, they will be interested in the schemas myapp or myotherapp. Or
they don't care about schemas and will want the database to behave as if
there wasn't a schema layer between the database and the tables.
The existing structures are not bad. They work for a lot of users. The
problem is just that by default everyone can do whatever they want in a
shared space. The fix is probably to not let them do that. What is
being discussed here instead is to let them do whatever they want in
their own non-shared spaces. That addresses the security concern, but
it doesn't support the way people actually work right now.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Wed, Mar 7, 2018 at 2:48 PM, Peter Eisentraut <
peter.eisentraut@2ndquadrant.com> wrote:
On 3/7/18 10:05, Stephen Frost wrote:
I liken this to a well-known and well-trodden feature for auto creating
user home directories on Unix.I don't think likening schemas to home directories is really addressing
the most typical use cases. Database contents are for the most part
carefully constructed in a collaborative way.
Databases intended to be deployed to production (hopefully) are, but not
necessarily those intend to evaluate PostgreSQL's capabilities.
The fix is probably to not let them do that. What is
being discussed here instead is to let them do whatever they want in
their own non-shared spaces. That addresses the security concern, but
it doesn't support the way people actually work right now.
Maybe not the majority of users, but the way DBA's work today is already
inherently secure (i.e., not using public) and requires a non-trivial
amount of DBA work (i.e., creating groups and users) to make happen. They
are not the target audience.
The target user profile for this discussion is one who does:
sudo apt install postgresql-10
sudo -U postgres createuser myosusername
psql myosusername postgres
CREATE TABLE test_table (id serial primary key);
insert into test_table;
select * from test_table;
We want to avoid having the create table fail now whereas it worked before
we removed create permissions on public from PUBLIC.
Now, I'd argue that people aren't bothering to "createuser" in the above
but simply skipping to "psql" and then to "sudo -U postgres psql" when they
get the error that "user myosusername" doesn't exist...once they start
creating new users I'd agree that they likely benefit more from us being
conservative and "do only what I say" as opposed to being helpful and doing
more stuff in the name of usability.
I still feel like I want to mull this over more but auto-creating schemas
strikes me as being "spooky action at a distance".
David J.
On Wed, Mar 7, 2018 at 5:11 PM, David G. Johnston
<david.g.johnston@gmail.com> wrote:
I still feel like I want to mull this over more but auto-creating schemas
strikes me as being "spooky action at a distance".
I don't think that it's a terrible proposal, but I don't see it as
fixing the real issue. If we do something even as simple as removing
'public' from the default search_path, then I predict that a very
significant number of people will run pg_upgrade (or dump and restore,
or logically replicate the database), restart their application, and
find that it no longer works and they have absolutely no idea what has
gone wrong or how to fix it. That seems like a major usability fail
to me, and auto-creating per-user schemas does absolutely nothing to
improve the situation. That's not to say that it's a bad idea;
honestly, I think it's kind of nifty, and it certainly makes things a
lot nicer if there's no public schema any more because it makes CREATE
TABLE work out of the box again, something that we certainly want.
But if we don't have some solution to the problem of upgrade =>
everything breaks, then I don't think we really have a good solution
here.
I also wonder why we're all convinced that this urgently needs to be
changed. I agree that the default configuration we ship is not the
most secure configuration that we could ship. However, I think it's a
big step from saying that the configuration is not as secure as it
could be to saying that we absolutely must change it for v11. We have
shipped tons of releases with sslmode=prefer and a wide-open
pg_hba.conf, and those aren't the most secure default configurations
either. And changing either of those things would probably break a
lot fewer users than the changes being proposed on this thread. This
issue isn't something that is brand new in a recent release of
PostgreSQL, and a lot of users are unaffected by it. People need to
be aware that having the Donald Trump campaign and the Hilary Clinton
campaign share access to the same public schema, to which both
campaigns have CREATE and USAGE access, is probably asking for
trouble, but to be honest I suspect a fair number of users had figured
that out well before this security release went out the door.
It's certainly worth considering ideas for improving PostgreSQL's
security out-of-the-box, but the sky isn't falling, and it appears to
me that the risk of collateral damage from changes in this area is
pretty high.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On Wed, Mar 07, 2018 at 09:22:16AM -0500, Peter Eisentraut wrote:
On 3/6/18 15:20, Robert Haas wrote:
On Sat, Mar 3, 2018 at 4:56 AM, Noah Misch <noah@leadboat.com> wrote:
I propose, for v11, switching to "GRANT USAGE ON SCHEMA
public TO PUBLIC" (omit CREATE). Concerns? An alternative is to change the
default search_path to "$user"; that would be break more applications, and I
don't see an advantage to compensate for that.Isn't this going to cause widespread breakage? Unprivileged users
will suddenly find that they can no longer create tables, because
$user doesn't exist and they don't have permission on public. That
seems quite unfriendly.Moreover, the problem is that if you have database owners that are not
superusers, they can't easily fix the issue themselves. Since the
public schema is owned by postgres, they database owner can't just go in
and run GRANT CREATE ON SCHEMA PUBLIC TO whomever to restore the old
behavior or grant specific access. It would be simpler if we didn't
install a public schema by default at all.
That's a good point. Worse, a user with CREATEDB privilege would be able to
create new databases and immediately create and use any schema _except_
public. That is rather silly.
On Wed, Mar 07, 2018 at 07:14:43AM -0500, Stephen Frost wrote:
* Noah Misch (noah@leadboat.com) wrote:
I like the idea of getting more SQL-compatible, if this presents a distinct
opportunity to do so. I do think it would be too weird to create the schema
in one database only. Creating it on demand might work. What would be the
procedure, if any, for database owners who want to deny object creation in
their databases?My suggestion was that this would be a role attribute. If an
administrator doesn't wish for that role to have a schema created
on-demand at login time, they would set the 'SCHEMA_CREATE' (or whatever
we name it) role attribute to false.
I had in mind a site with diverse database owners, where the administrators
(folks with CREATEROLE or superuser) don't know every database owner
preference. If we had a SCHEMA_CREATE like you describe, I expect its
documentation would say something like this:
Since SCHEMA_CREATE provides the user one writable schema in each database,
this allows the user to create permanent objects in any database that
permits them to connect. The database owner can prevent that by creating
the schema in advance of the user's first login. However, once the user has
connected once, a non-superuser database owner cannot modify or drop it.
Is that good enough?
On Thu, Mar 08, 2018 at 02:00:23PM -0500, Robert Haas wrote:
I also wonder why we're all convinced that this urgently needs to be
changed. I agree that the default configuration we ship is not the
most secure configuration that we could ship. However, I think it's a
big step from saying that the configuration is not as secure as it
could be to saying that we absolutely must change it for v11.
Did someone say that? I, for one, wanted to change it but didn't intend to
present it as a "must change".
On Thu, Mar 08, 2018 at 11:14:59PM -0800, Noah Misch wrote:
On Thu, Mar 08, 2018 at 02:00:23PM -0500, Robert Haas wrote:
I also wonder why we're all convinced that this urgently needs to be
changed. I agree that the default configuration we ship is not the
most secure configuration that we could ship. However, I think it's a
big step from saying that the configuration is not as secure as it
could be to saying that we absolutely must change it for v11.Did someone say that? I, for one, wanted to change it but didn't intend to
present it as a "must change".
In light of the mixed reception, I am withdrawing this proposal.
On Fri, Mar 23, 2018 at 07:47:39PM -0700, Noah Misch wrote:
In light of the mixed reception, I am withdrawing this proposal.
I'd like to reopen this. Reception was mixed, but more in favor than against.
Also, variations on the idea trade some problems for others and may be more
attractive. The taxonomy of variations has three important dimensions:
Interaction with dump/restore (including pg_upgrade) options:
a. If the schema has a non-default ACL, dump/restore reproduces it.
Otherwise, the new default prevails.
b. Dump/restore always reproduces the schema ACL.
Initial ownership of schema "public" options:
1. Bootstrap superuser owns it. (Without superuser cooperation, database
owners can't drop it or create objects in it.)
2. Don't create the schema during initdb. Database owners can create it or
any other schema. (A superuser could create it in template1, which
converts an installation to option (1).)
3. Database owner owns it. (One might implement this by offering ALTER SCHEMA
x OWNER TO DATABASE_OWNER, which sets nspowner to a system OID meaning
"refer to pg_database.datdba". A superuser could issue DDL to convert to
option (1) or (2).)
Automatic creation of $user schemas options:
X. Automatic schema creation doesn't exist.
Y. Create $user schemas on-demand (at login time or CREATE TABLE/CREATE
FUNCTION/etc. time) if the DBA specified a "SCHEMA_CREATE" option in the
CREATE ROLE statement.
Z. Like (Y), but SCHEMA_CREATE is the default.
I started the thread by proposing (a)(1)(X) and mentioning (b)(1)(X) as an
alternative. Given the compatibility concerns, I now propose ruling out (a)
in favor of (b).
/messages/by-id/0e61bd66-07a2-255b-2b0f-7a8488ea1647@2ndquadrant.com
identified (b)(2)(X) and identified the problem with (1).
I dislike (Z), because it requires updating security guidelines to specify
NOSCHEMA_CREATE; I think it would be better to leave $SUBJECT unchanged than
to adopt (Z). I like (Y) from an SQL standard perspective, but I don't think
it resolves the ease-of-first-use objections raised against (a)(1)(X). (If
changing the public schema ACL is too much of an obstacle for a DBA, adopting
SCHEMA_CREATE is no easier.) Hence, I propose ruling out (Y) and (Z).
That leaves the choice between (2) and (3). Under (b)(2)(X), first-use guides
would need to add some CREATE SCHEMA. While (3) avoids that, some users may
find themselves setting ownership back to the bootstrap superuser. (3) also
makes the system more complex overall.
Between (b)(2)(X) and (b)(3)(X), what are folks' preferences? Does anyone
strongly favor some other option (including the option of changing nothing)
over both of those two?
Thanks,
nm
On Mon, Aug 3, 2020 at 2:30 AM Noah Misch <noah@leadboat.com> wrote:
Between (b)(2)(X) and (b)(3)(X), what are folks' preferences? Does anyone
strongly favor some other option (including the option of changing nothing)
over both of those two?
I don't think we have any options here that are secure but do not
break backward compatibility. The present situation, with a writable
public schema, is equivalent to a UNIX system in which /usr/bin is
drwxrwxrwt. Nobody would seriously propose that such a system design
is secure, not so much because it's intrinsically broken if everyone
is careful not to execute any executables they don't know to have been
deposited by people they trust, but because it's quite easy to
accidentally execute one that isn't. However, if people are used to
being able to deposit stuff in /usr/bin and you tell them that they
now can't (because the permissions will henceforth be drwxr-xr-x or
the directly won't exist at all) then some of them are going to
complain. I don't know what to do about that: it's a straightforward
trade-off between security and backward compatibility, and you can't
have both.
I support the idea of having an automatic schema creation option. I
think that would be quite a cool thing to have, whether it's the
default (Y) or not (Z). But I don't know how to choose between (1),
(2), and (3).
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On Sun, Aug 2, 2020 at 11:30:50PM -0700, Noah Misch wrote:
On Fri, Mar 23, 2018 at 07:47:39PM -0700, Noah Misch wrote:
In light of the mixed reception, I am withdrawing this proposal.
I'd like to reopen this. Reception was mixed, but more in favor than against.
Also, variations on the idea trade some problems for others and may be more
attractive. The taxonomy of variations has three important dimensions:Interaction with dump/restore (including pg_upgrade) options:
a. If the schema has a non-default ACL, dump/restore reproduces it.
Otherwise, the new default prevails.
b. Dump/restore always reproduces the schema ACL.
I am worried that someone _slightly_ modifies the ACL permissions on the
schema, and we reproduce it, and they think they are secure, but they
are not. I guess for the public, and change would be to make it more
secure, so maybe this works, but it seems tricky.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com
The usefulness of a cup is in its emptiness, Bruce Lee
Greetings,
* Noah Misch (noah@leadboat.com) wrote:
I'd like to reopen this. Reception was mixed, but more in favor than against.
Also, variations on the idea trade some problems for others and may be more
attractive. The taxonomy of variations has three important dimensions:Interaction with dump/restore (including pg_upgrade) options:
a. If the schema has a non-default ACL, dump/restore reproduces it.
Otherwise, the new default prevails.
b. Dump/restore always reproduces the schema ACL.Initial ownership of schema "public" options:
1. Bootstrap superuser owns it. (Without superuser cooperation, database
owners can't drop it or create objects in it.)
2. Don't create the schema during initdb. Database owners can create it or
any other schema. (A superuser could create it in template1, which
converts an installation to option (1).)
3. Database owner owns it. (One might implement this by offering ALTER SCHEMA
x OWNER TO DATABASE_OWNER, which sets nspowner to a system OID meaning
"refer to pg_database.datdba". A superuser could issue DDL to convert to
option (1) or (2).)Automatic creation of $user schemas options:
X. Automatic schema creation doesn't exist.
Y. Create $user schemas on-demand (at login time or CREATE TABLE/CREATE
FUNCTION/etc. time) if the DBA specified a "SCHEMA_CREATE" option in the
CREATE ROLE statement.
Z. Like (Y), but SCHEMA_CREATE is the default.I started the thread by proposing (a)(1)(X) and mentioning (b)(1)(X) as an
alternative. Given the compatibility concerns, I now propose ruling out (a)
in favor of (b).
I agree that we don't want to effectively change these privileges on a
dump/restore or pg_upgrade.
I dislike (Z), because it requires updating security guidelines to specify
NOSCHEMA_CREATE; I think it would be better to leave $SUBJECT unchanged than
to adopt (Z). I like (Y) from an SQL standard perspective, but I don't think
it resolves the ease-of-first-use objections raised against (a)(1)(X). (If
changing the public schema ACL is too much of an obstacle for a DBA, adopting
SCHEMA_CREATE is no easier.) Hence, I propose ruling out (Y) and (Z).
I'm also in favor of having some flavor of automatic schema creation,
but I view that as something independent from this discussion and which
this change shouldn't depend on.
That leaves the choice between (2) and (3). Under (b)(2)(X), first-use guides
would need to add some CREATE SCHEMA. While (3) avoids that, some users may
find themselves setting ownership back to the bootstrap superuser. (3) also
makes the system more complex overall.Between (b)(2)(X) and (b)(3)(X), what are folks' preferences? Does anyone
strongly favor some other option (including the option of changing nothing)
over both of those two?
Having the database owner own the public schema makes the most sense to
me- that this doesn't happen today has always seemed a bit odd to me as,
notionally, you'd imagine the "owner" of a database as, well, owning the
objects in that database (clearly they shouldn't actually own system
catalogs or functions or such, but the public schema isn't some internal
thing like the system catalogs and such). Having the database owner not
have to jump through hoops to create objects immediately upon connection
to a new database also seems like it reduces the compatibility impact
that this will have.
In general, I'm still in favor of the overall change and moving to
better and more secure defaults.
Thanks,
Stephen
On 2020-08-03 15:46, Robert Haas wrote:
However, if people are used to
being able to deposit stuff in /usr/bin and you tell them that they
now can't (because the permissions will henceforth be drwxr-xr-x or
the directly won't exist at all) then some of them are going to
complain. I don't know what to do about that: it's a straightforward
trade-off between security and backward compatibility, and you can't
have both.
File system conventions, permissions, and restrictions have been changed
many times in the history of Unix, Linux, and the like. Recent examples
are /usr/bin and /bin unification and that /tmp is changing to a
per-user mount. There are of course always a few complaints and some
breakage, but generally this has been going well and is usually
appreciated overall.
The important things in my mind are that you keep an easy onboarding
experience (you can do SQL things without having to create and unlock a
bunch of things first) and that advanced users can do the things they
want to do *somehow*.
As an example, per-user /tmp is not hardcoded into the kernel, it's just
a run-time configuration. If you want it to behave differently, you can
set that up.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Sun, Aug 2, 2020 at 11:30 PM Noah Misch <noah@leadboat.com> wrote:
Interaction with dump/restore (including pg_upgrade) options:
a. If the schema has a non-default ACL, dump/restore reproduces it.
Otherwise, the new default prevails.
b. Dump/restore always reproduces the schema ACL.Initial ownership of schema "public" options:
1. Bootstrap superuser owns it. (Without superuser cooperation, database
owners can't drop it or create objects in it.)
2. Don't create the schema during initdb. Database owners can create it or
any other schema. (A superuser could create it in template1, which
converts an installation to option (1).)
3. Database owner owns it. (One might implement this by offering ALTER
SCHEMA
x OWNER TO DATABASE_OWNER, which sets nspowner to a system OID meaning
"refer to pg_database.datdba". A superuser could issue DDL to convert
to
option (1) or (2).)Automatic creation of $user schemas options:
X. Automatic schema creation doesn't exist.
Y. Create $user schemas on-demand (at login time or CREATE TABLE/CREATE
FUNCTION/etc. time) if the DBA specified a "SCHEMA_CREATE" option in the
CREATE ROLE statement.
Z. Like (Y), but SCHEMA_CREATE is the default.Between (b)(2)(X) and (b)(3)(X), what are folks' preferences? Does anyone
strongly favor some other option (including the option of changing nothing)
over both of those two?
Both, as well as a reconsideration of not providing an escape hatch to the
search_path change as part of dump/restore in response to a number of
emails to these lists.
I like an option 2 that simply and quickly allows a DBA to setup a system
with zero-trust and have all grants be made explicitly. This would go
beyond just the public schema and basically remove the concept of grants to
the built-in PUBLIC group.
I like option 3 for the user-friendly default option that has as few
compatibility issues compared to today as possible.
David J.
On Mon, Aug 03, 2020 at 09:46:23AM -0400, Robert Haas wrote:
On Mon, Aug 3, 2020 at 2:30 AM Noah Misch <noah@leadboat.com> wrote:
Between (b)(2)(X) and (b)(3)(X), what are folks' preferences? Does anyone
strongly favor some other option (including the option of changing nothing)
over both of those two?I don't think we have any options here that are secure but do not
break backward compatibility.
I agree, but compatibility breaks vary in pain caused. I want to offer a
simple exit to a backward-compatible configuration, and I want a $NEW_DEFAULT
pleasing enough that a decent fraction of deployments keep $NEW_DEFAULT (forgo
the exit). The move to default standard_conforming_strings=on is an example
to follow (editing postgresql.conf was the simple exit).
I don't know how to choose between (1), (2), and (3).
One way is to envision deployments you know and think about a couple of
questions in the context of those deployments. If $EACH_OPTION happened,
would this deployment keep $NEW_DEFAULT, override $NEW_DEFAULT to some other
secure configuration, or exit to $v13_DEFAULT? Where the answer is "exit",
would those deployments rate the exit recipe easy, medium, or difficult?
On Mon, Aug 03, 2020 at 07:46:02PM +0200, Peter Eisentraut wrote:
The important things in my mind are that you keep an easy onboarding
experience (you can do SQL things without having to create and unlock a
bunch of things first) and that advanced users can do the things they want
to do *somehow*.
Makes sense. How do these options differ in ease of onboarding? In that
light, what option would you pick?
On Mon, Aug 3, 2020 at 5:26 PM Stephen Frost <sfrost@snowman.net> wrote:
* Noah Misch (noah@leadboat.com) wrote:
I'd like to reopen this. Reception was mixed, but more in favor than
against.
Also, variations on the idea trade some problems for others and may be
more
attractive. The taxonomy of variations has three important dimensions:
Interaction with dump/restore (including pg_upgrade) options:
a. If the schema has a non-default ACL, dump/restore reproduces it.
Otherwise, the new default prevails.
b. Dump/restore always reproduces the schema ACL.Initial ownership of schema "public" options:
1. Bootstrap superuser owns it. (Without superuser cooperation, database
owners can't drop it or create objects in it.)
2. Don't create the schema during initdb. Database owners can create itor
any other schema. (A superuser could create it in template1, which
converts an installation to option (1).)
3. Database owner owns it. (One might implement this by offering ALTERSCHEMA
x OWNER TO DATABASE_OWNER, which sets nspowner to a system OID meaning
"refer to pg_database.datdba". A superuser could issue DDL toconvert to
option (1) or (2).)
Automatic creation of $user schemas options:
X. Automatic schema creation doesn't exist.
Y. Create $user schemas on-demand (at login time or CREATE TABLE/CREATE
FUNCTION/etc. time) if the DBA specified a "SCHEMA_CREATE" option inthe
CREATE ROLE statement.
Z. Like (Y), but SCHEMA_CREATE is the default.I started the thread by proposing (a)(1)(X) and mentioning (b)(1)(X) as
an
alternative. Given the compatibility concerns, I now propose ruling out
(a)
in favor of (b).
I agree that we don't want to effectively change these privileges on a
dump/restore or pg_upgrade.
Agreed. But it might be worthwhile having pg_dump spit out something like
"current defaults are insecure, pass in parameter --update-default-acls to
migrate to new defaults" when it detects the old default ones. (Or even
specifically look for known insecure ones, like people who just added
things to the acl which already had public with create -- obviously there's
a limit how far one can go there)
I dislike (Z), because it requires updating security guidelines to specify
NOSCHEMA_CREATE; I think it would be better to leave $SUBJECT unchanged
than
to adopt (Z). I like (Y) from an SQL standard perspective, but I don't
think
it resolves the ease-of-first-use objections raised against (a)(1)(X).
(If
changing the public schema ACL is too much of an obstacle for a DBA,
adopting
SCHEMA_CREATE is no easier.) Hence, I propose ruling out (Y) and (Z).
I'm also in favor of having some flavor of automatic schema creation,
but I view that as something independent from this discussion and which
this change shouldn't depend on.
I'm a bit torn on this one.
Because, in the end, how many people *actually* want the "user<->schema"
tie-in? While I've seen some people actually use it, they are very few and
far apart, and mostly only connected with migrating over from
$BIG_DATABASE_VENDOR. I think we'd find a lot more people who are annoyed
by "I just created a table, and now I have to go clean up this weird schema
that got auto-created for me".
So on that, I'd definitely say Y over Z. Having it as an option would
certainly find useful scenarios, but I think having it on by default would
be annoying.
And it would also question whether $user should actually be in the default
search_path at all, or not.
In the comparison with filesystems, people are used to creating directories
before placing files in them... (except those that put all their files
directly on their desktop, but those are not likely going to be the ones
creating objects in the database)
That leaves the choice between (2) and (3). Under (b)(2)(X), first-use
guideswould need to add some CREATE SCHEMA. While (3) avoids that, some users
may
find themselves setting ownership back to the bootstrap superuser. (3)
also
makes the system more complex overall.
Between (b)(2)(X) and (b)(3)(X), what are folks' preferences? Does
anyone
strongly favor some other option (including the option of changing
nothing)
over both of those two?
Having the database owner own the public schema makes the most sense to
me- that this doesn't happen today has always seemed a bit odd to me as,
notionally, you'd imagine the "owner" of a database as, well, owning the
objects in that database (clearly they shouldn't actually own system
catalogs or functions or such, but the public schema isn't some internal
thing like the system catalogs and such). Having the database owner not
have to jump through hoops to create objects immediately upon connection
to a new database also seems like it reduces the compatibility impact
that this will have.
+1. This feels mostly like a weird quirk in the current system. Having the
database owner own it would feel a lot more logical.
In general, I'm still in favor of the overall change and moving to
better and more secure defaults.
+<many>.
--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>
Greetings,
* Magnus Hagander (magnus@hagander.net) wrote:
On Mon, Aug 3, 2020 at 5:26 PM Stephen Frost <sfrost@snowman.net> wrote:
* Noah Misch (noah@leadboat.com) wrote:
I'd like to reopen this. Reception was mixed, but more in favor than
against.
Also, variations on the idea trade some problems for others and may be
more
attractive. The taxonomy of variations has three important dimensions:
Interaction with dump/restore (including pg_upgrade) options:
a. If the schema has a non-default ACL, dump/restore reproduces it.
Otherwise, the new default prevails.
b. Dump/restore always reproduces the schema ACL.Initial ownership of schema "public" options:
1. Bootstrap superuser owns it. (Without superuser cooperation, database
owners can't drop it or create objects in it.)
2. Don't create the schema during initdb. Database owners can create itor
any other schema. (A superuser could create it in template1, which
converts an installation to option (1).)
3. Database owner owns it. (One might implement this by offering ALTERSCHEMA
x OWNER TO DATABASE_OWNER, which sets nspowner to a system OID meaning
"refer to pg_database.datdba". A superuser could issue DDL toconvert to
option (1) or (2).)
Automatic creation of $user schemas options:
X. Automatic schema creation doesn't exist.
Y. Create $user schemas on-demand (at login time or CREATE TABLE/CREATE
FUNCTION/etc. time) if the DBA specified a "SCHEMA_CREATE" option inthe
CREATE ROLE statement.
Z. Like (Y), but SCHEMA_CREATE is the default.I started the thread by proposing (a)(1)(X) and mentioning (b)(1)(X) as
an
alternative. Given the compatibility concerns, I now propose ruling out
(a)
in favor of (b).
I agree that we don't want to effectively change these privileges on a
dump/restore or pg_upgrade.Agreed. But it might be worthwhile having pg_dump spit out something like
"current defaults are insecure, pass in parameter --update-default-acls to
migrate to new defaults" when it detects the old default ones. (Or even
specifically look for known insecure ones, like people who just added
things to the acl which already had public with create -- obviously there's
a limit how far one can go there)
Interesting idea, though that seems like it would be an extremely useful
*independent* tool from pg_dump (but, sure, we could run it as part of
pg_dump too). Indeed, such tools already exist and having one of our
own would be nice.
I wonder if we should also consider having a tool for post-release
updates/fixes (eg: catalog changes). Today we currently "deploy" such
fixes through the release notes, which isn't great. Not sure why I
thought of that as being related but maybe it's not crazy to have the
same tool for both..?
pg_checkdb
-- catalog updates
-- security
-- other stuff?
I dislike (Z), because it requires updating security guidelines to specify
NOSCHEMA_CREATE; I think it would be better to leave $SUBJECT unchanged
than
to adopt (Z). I like (Y) from an SQL standard perspective, but I don't
think
it resolves the ease-of-first-use objections raised against (a)(1)(X).
(If
changing the public schema ACL is too much of an obstacle for a DBA,
adopting
SCHEMA_CREATE is no easier.) Hence, I propose ruling out (Y) and (Z).
I'm also in favor of having some flavor of automatic schema creation,
but I view that as something independent from this discussion and which
this change shouldn't depend on.I'm a bit torn on this one.
Because, in the end, how many people *actually* want the "user<->schema"
tie-in? While I've seen some people actually use it, they are very few and
far apart, and mostly only connected with migrating over from
$BIG_DATABASE_VENDOR. I think we'd find a lot more people who are annoyed
by "I just created a table, and now I have to go clean up this weird schema
that got auto-created for me".So on that, I'd definitely say Y over Z. Having it as an option would
certainly find useful scenarios, but I think having it on by default would
be annoying.
I tend to agree with this also.
And it would also question whether $user should actually be in the default
search_path at all, or not.
That's certainly an interesting question.
In the comparison with filesystems, people are used to creating directories
before placing files in them... (except those that put all their files
directly on their desktop, but those are not likely going to be the ones
creating objects in the database)
Not sure how much it happens in these days of docker and containers, but
certainly it was common at one point to have home directories
automatically created on login. There's one particularly large
difference here though- home directories go in /home/ (or whatever) and
have a specific namespace, which our schemas don't. That is to say, if
someone has CREATE rights on the database they can create an 'sfrost'
schema that they own, dump whatever they want into it, and then it's in
my default search_path when I log in, even if this feature to
auto-create role schemas exists. Sure, you could argue that in the unix
case, that would have been an 'admin' user to be able to make a
directory in /home/, but we haven't got any other way to make
'directories', so perhaps the analogy just doesn't fit close enough.
Thanks,
Stephen
On Mon, Aug 03, 2020 at 11:22:48AM -0400, Bruce Momjian wrote:
On Sun, Aug 2, 2020 at 11:30:50PM -0700, Noah Misch wrote:
On Fri, Mar 23, 2018 at 07:47:39PM -0700, Noah Misch wrote:
In light of the mixed reception, I am withdrawing this proposal.
I'd like to reopen this. Reception was mixed, but more in favor than against.
Also, variations on the idea trade some problems for others and may be more
attractive. The taxonomy of variations has three important dimensions:Interaction with dump/restore (including pg_upgrade) options:
a. If the schema has a non-default ACL, dump/restore reproduces it.
Otherwise, the new default prevails.
b. Dump/restore always reproduces the schema ACL.I am worried that someone _slightly_ modifies the ACL permissions on the
schema, and we reproduce it, and they think they are secure, but they
are not. I guess for the public, and change would be to make it more
secure, so maybe this works, but it seems tricky.
Unless someone advocates for (a), we have dodged that problem, right?
On Thu, Aug 6, 2020 at 3:34 PM Stephen Frost <sfrost@snowman.net> wrote:
Greetings,
* Magnus Hagander (magnus@hagander.net) wrote:
On Mon, Aug 3, 2020 at 5:26 PM Stephen Frost <sfrost@snowman.net> wrote:
* Noah Misch (noah@leadboat.com) wrote:
I'd like to reopen this. Reception was mixed, but more in favor than
against.
Also, variations on the idea trade some problems for others and may
be
more
attractive. The taxonomy of variations has three important
dimensions:
Interaction with dump/restore (including pg_upgrade) options:
a. If the schema has a non-default ACL, dump/restore reproduces it.
Otherwise, the new default prevails.
b. Dump/restore always reproduces the schema ACL.Initial ownership of schema "public" options:
1. Bootstrap superuser owns it. (Without superuser cooperation,database
owners can't drop it or create objects in it.)
2. Don't create the schema during initdb. Database owners cancreate it
or
any other schema. (A superuser could create it in template1,
which
converts an installation to option (1).)
3. Database owner owns it. (One might implement this by offeringALTER
SCHEMA
x OWNER TO DATABASE_OWNER, which sets nspowner to a system OID
meaning
"refer to pg_database.datdba". A superuser could issue DDL to
convert to
option (1) or (2).)
Automatic creation of $user schemas options:
X. Automatic schema creation doesn't exist.
Y. Create $user schemas on-demand (at login time or CREATETABLE/CREATE
FUNCTION/etc. time) if the DBA specified a "SCHEMA_CREATE" option
in
the
CREATE ROLE statement.
Z. Like (Y), but SCHEMA_CREATE is the default.I started the thread by proposing (a)(1)(X) and mentioning (b)(1)(X)
as
an
alternative. Given the compatibility concerns, I now propose ruling
out
(a)
in favor of (b).
I agree that we don't want to effectively change these privileges on a
dump/restore or pg_upgrade.Agreed. But it might be worthwhile having pg_dump spit out something like
"current defaults are insecure, pass in parameter --update-default-aclsto
migrate to new defaults" when it detects the old default ones. (Or even
specifically look for known insecure ones, like people who just added
things to the acl which already had public with create -- obviouslythere's
a limit how far one can go there)
Interesting idea, though that seems like it would be an extremely useful
*independent* tool from pg_dump (but, sure, we could run it as part of
pg_dump too). Indeed, such tools already exist and having one of our
own would be nice.
Agreed. But I think it would get extra value from also being run on every
pg_dump at least to throw "important warnings".
I wonder if we should also consider having a tool for post-release
updates/fixes (eg: catalog changes). Today we currently "deploy" such
fixes through the release notes, which isn't great. Not sure why I
thought of that as being related but maybe it's not crazy to have the
same tool for both..?
pg_checkdb
-- catalog updates
-- security
-- other stuff?
That'd certainly be useful, but we'd have to be careful about the potential
for feature creep :) In theory there is no limitation at all on what such a
tool would do :) But for example limiting it to explicitly the things that
we have covered in release notes or side-effects of upgrades would be a
reasonable limitation. In which case you might not need the switches?
I dislike (Z), because it requires updating security guidelines to
specify
NOSCHEMA_CREATE; I think it would be better to leave $SUBJECT
unchanged
than
to adopt (Z). I like (Y) from an SQL standard perspective, but I
don't
think
it resolves the ease-of-first-use objections raised against
(a)(1)(X).
(If
changing the public schema ACL is too much of an obstacle for a DBA,
adopting
SCHEMA_CREATE is no easier.) Hence, I propose ruling out (Y) and
(Z).
I'm also in favor of having some flavor of automatic schema creation,
but I view that as something independent from this discussion and which
this change shouldn't depend on.I'm a bit torn on this one.
Because, in the end, how many people *actually* want the "user<->schema"
tie-in? While I've seen some people actually use it, they are very fewand
far apart, and mostly only connected with migrating over from
$BIG_DATABASE_VENDOR. I think we'd find a lot more people who are annoyed
by "I just created a table, and now I have to go clean up this weirdschema
that got auto-created for me".
So on that, I'd definitely say Y over Z. Having it as an option would
certainly find useful scenarios, but I think having it on by defaultwould
be annoying.
I tend to agree with this also.
And it would also question whether $user should actually be in the
default
search_path at all, or not.
That's certainly an interesting question.
In the comparison with filesystems, people are used to creating
directories
before placing files in them... (except those that put all their files
directly on their desktop, but those are not likely going to be the ones
creating objects in the database)Not sure how much it happens in these days of docker and containers, but
certainly it was common at one point to have home directories
automatically created on login. There's one particularly large
difference here though- home directories go in /home/ (or whatever) and
have a specific namespace, which our schemas don't. That is to say, if
someone has CREATE rights on the database they can create an 'sfrost'
schema that they own, dump whatever they want into it, and then it's in
my default search_path when I log in, even if this feature to
auto-create role schemas exists. Sure, you could argue that in the unix
case, that would have been an 'admin' user to be able to make a
directory in /home/, but we haven't got any other way to make
'directories', so perhaps the analogy just doesn't fit close enough.
Yeah, the fact that a owner can just create a schema called "postgres" and
thereby sticking things in the search path of postgres is not great. And
that's not fixed by changing how "public" works, per any of the suggested
methods I think. Only the database owner can do mean things there, but
database owner != superuser (at least in theory).
--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>
On Mon, Aug 10, 2020 at 10:21:06AM +0200, Magnus Hagander wrote:
On Thu, Aug 6, 2020 at 3:34 PM Stephen Frost <sfrost@snowman.net> wrote:
Not sure how much it happens in these days of docker and containers, but
certainly it was common at one point to have home directories
automatically created on login. There's one particularly large
difference here though- home directories go in /home/ (or whatever) and
have a specific namespace, which our schemas don't. That is to say, if
someone has CREATE rights on the database they can create an 'sfrost'
schema that they own, dump whatever they want into it, and then it's in
my default search_path when I log in, even if this feature to
auto-create role schemas exists. Sure, you could argue that in the unix
case, that would have been an 'admin' user to be able to make a
directory in /home/, but we haven't got any other way to make
'directories', so perhaps the analogy just doesn't fit close enough.Yeah, the fact that a owner can just create a schema called "postgres" and
thereby sticking things in the search path of postgres is not great. And
that's not fixed by changing how "public" works, per any of the suggested
methods I think. Only the database owner can do mean things there, but
database owner != superuser (at least in theory).
https://www.postgresql.org/docs/devel/ddl-schemas.html#DDL-SCHEMAS-PATTERNS
does document the power of untrusted database owners. Unfortunately, I've not
seen or thought of a specification of database owner powers that included
enough power to be useful yet not enough power to cause mischief.
On Wed, Aug 05, 2020 at 10:05:28PM -0700, Noah Misch wrote:
On Mon, Aug 03, 2020 at 07:46:02PM +0200, Peter Eisentraut wrote:
The important things in my mind are that you keep an easy onboarding
experience (you can do SQL things without having to create and unlock a
bunch of things first) and that advanced users can do the things they want
to do *somehow*.Makes sense. How do these options differ in ease of onboarding? In that
light, what option would you pick?
Ping. Your statement above seems to suggest one of the options more than
others, but I'd rather not assume you see it the same way.
On Wed, Aug 05, 2020 at 10:00:02PM -0700, Noah Misch wrote:
On Mon, Aug 03, 2020 at 09:46:23AM -0400, Robert Haas wrote:
On Mon, Aug 3, 2020 at 2:30 AM Noah Misch <noah@leadboat.com> wrote:
Between (b)(2)(X) and (b)(3)(X), what are folks' preferences? Does anyone
strongly favor some other option (including the option of changing nothing)
over both of those two?I don't think we have any options here that are secure but do not
break backward compatibility.I agree, but compatibility breaks vary in pain caused. I want to offer a
simple exit to a backward-compatible configuration, and I want a $NEW_DEFAULT
pleasing enough that a decent fraction of deployments keep $NEW_DEFAULT (forgo
the exit). The move to default standard_conforming_strings=on is an example
to follow (editing postgresql.conf was the simple exit).I don't know how to choose between (1), (2), and (3).
One way is to envision deployments you know and think about a couple of
questions in the context of those deployments. If $EACH_OPTION happened,
would this deployment keep $NEW_DEFAULT, override $NEW_DEFAULT to some other
secure configuration, or exit to $v13_DEFAULT? Where the answer is "exit",
would those deployments rate the exit recipe easy, medium, or difficult?
It sounds like you might prefer to wait for better ideas and not change
$SUBJECT for now. Is that right?
On Thu, Aug 06, 2020 at 12:48:17PM +0200, Magnus Hagander wrote:
On Mon, Aug 3, 2020 at 5:26 PM Stephen Frost <sfrost@snowman.net> wrote:
* Noah Misch (noah@leadboat.com) wrote:
Between (b)(2)(X) and (b)(3)(X), what are folks' preferences?� Does anyone
strongly favor some other option (including the option of changing nothing)
over both of those two?Having the database owner own the public schema makes the most sense to
me- that this doesn't happen today has always seemed a bit odd to me as,
notionally, you'd imagine the "owner" of a database as, well, owning the
objects in that database (clearly they shouldn't actually own system
catalogs or functions or such, but the public schema isn't some internal
thing like the system catalogs and such).� Having the database owner not
have to jump through hoops to create objects immediately upon connection
to a new database also seems like it reduces the compatibility impact
that this will have.+1. This feels mostly like a weird quirk in the current system. Having the database owner own it would feel a lot more logical.
In general, I'm still in favor of the overall change and moving to
better and more secure defaults.+<many>.
(b)(2)(X) got no votes.
(b)(3)(X) got votes from Stephen Frost and Magnus Hagander. I'll pick it,
too. Peter Eisentraut did not vote, but I'm counting him as +0.2 for it in
light of this comment:
On Mon, Aug 03, 2020 at 07:46:02PM +0200, Peter Eisentraut wrote:
The important things in my mind are that you keep an easy onboarding
experience (you can do SQL things without having to create and unlock a
bunch of things first) and that advanced users can do the things they want
to do *somehow*.
Robert Haas did not vote, but this seems more consistent with a request to
wait for better ideas and change nothing for now:
On Mon, Aug 03, 2020 at 09:46:23AM -0400, Robert Haas wrote:
I don't think we have any options here that are secure but do not
break backward compatibility.
Overall, that's 3.2 votes for (b)(3)(X) and 0.0 to 1.0 votes for changing
nothing. That suffices to proceed with (b)(3)(X). However, given the few
votes and the conspicuous non-responses, work in this area has a high risk of
failure. Hence, I will place it at a low-priority position in my queue.
Would anyone else would like to take over implementation?
More details on the semantics I'll use:
1. initdb will change like this:
@@ -1721 +1721 @@ setup_privileges(FILE *cmdfd)
- "GRANT CREATE, USAGE ON SCHEMA public TO PUBLIC;\n\n",
+ "GRANT USAGE ON SCHEMA public TO PUBLIC;\n\n",
+ "ALTER SCHEMA public OWNER TO DATABASE_OWNER;\n\n",2. If schema public does not exist, pg_dump will emit nothing about it. This
is what happens today. (I suspect it would be better for pg_dump to emit
DROP SCHEMA public RESTRICT, but that is drifting offtopic for $SUBJECT.)
Otherwise, when dumping from v13 or earlier, pg_dump will always emit
REVOKE and/or GRANT statements to reproduce the old ACL. When dumping from
v14 or later, pg_dump will use pg_init_privs to compute GRANT and REVOKE
statements, as it does today. (This may interfere with cross-version
pg_upgrade testing. I haven't looked at how best to fix that. Perhaps add
more fix_sql in test.sh.)
3. pg_upgrade from v13 to later versions will transfer template1's ACL for
schema public, even if that ACL was unchanged since v13 initdb. (This is
purely a consequence of the pg_dump behavior decision.) template0 will
keep the new default.
4. OWNER TO DATABASE_OWNER will likely be available for schemas only, though I
might propose it for all object classes if class-specific complexity proves
negligible.
5. ALTER DATABASE OWNER TO changes access control decisions involving
nspowner==DATABASE_OWNER. Speed of nspacl checks is more important than
reacting swiftly to ALTER DATABASE OWNER TO. Sessions running concurrently
will be eventually-consistent with respect to the ALTER DATABASE.
(Existing access control decisions, too, allow this sort of anomaly.)
6. pg_dump hasn't been reproducing ALTER SCHEMA public OWNER TO. That's a
mild defect today, but it wouldn't be mild anymore. We'll need pg_dump of
v13 databases to emit "ALTER SCHEMA public OWNER TO postgres" and for a v14
=> v15 upgrade to propagate that. This project can stand by itself; would
anyone else like to own it?
Thanks,
nm
On 2020-10-31 17:35, Noah Misch wrote:
Overall, that's 3.2 votes for (b)(3)(X) and 0.0 to 1.0 votes for changing
nothing. That suffices to proceed with (b)(3)(X). However, given the few
votes and the conspicuous non-responses, work in this area has a high risk of
failure. Hence, I will place it at a low-priority position in my queue.
My vote would also be (b)(3)(X). Allowing the database owner to manage
the public schema within their database makes a lot of sense,
independent of any overarching goals.
I'm not convinced, however, that this would would really move the needle
in terms of the general security-uneasiness about the public schema and
search paths. AFAICT, in any of your proposals, the default would still
be to have the public schema world-writable and in the path.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Greetings,
* Peter Eisentraut (peter.eisentraut@2ndquadrant.com) wrote:
On 2020-10-31 17:35, Noah Misch wrote:
Overall, that's 3.2 votes for (b)(3)(X) and 0.0 to 1.0 votes for changing
nothing. That suffices to proceed with (b)(3)(X). However, given the few
votes and the conspicuous non-responses, work in this area has a high risk of
failure. Hence, I will place it at a low-priority position in my queue.My vote would also be (b)(3)(X). Allowing the database owner to manage the
public schema within their database makes a lot of sense, independent of any
overarching goals.
Agreed.
I'm not convinced, however, that this would would really move the needle in
terms of the general security-uneasiness about the public schema and search
paths. AFAICT, in any of your proposals, the default would still be to have
the public schema world-writable and in the path.
Looks like the proposal wasn't explicitly clear on this point and I, at
least, took the proposal to implicitly also be saying that the public
schema's ACL would be the default- meaning that the owner would be able
to create objects in the schema and to use it, but other users wouldn't
be able to (or, perhaps, that USAGE rights would be GRANT'd to public,
but not CREATE).
Seems we probably need another round of votes where it's made very clear
what the default ACL (not from a dump/reload) on the public schema would
be.
Thanks,
Stephen
On Mon, Nov 2, 2020 at 5:51 AM Peter Eisentraut
<peter.eisentraut@2ndquadrant.com> wrote:
I'm not convinced, however, that this would would really move the needle
in terms of the general security-uneasiness about the public schema and
search paths. AFAICT, in any of your proposals, the default would still
be to have the public schema world-writable and in the path.
Noah's proposed change to initdb appears to involve removing CREATE
permission by default, so I don't think this is true.
It's hard to predict how many users that might inconvenience, but I
suppose it's probably a big number. On the other hand, the only
alternative is to continue shipping a configuration that, by default,
is potentially insecure. It's hard to decide which thing we should
care more about.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
On Mon, Nov 2, 2020 at 5:51 AM Peter Eisentraut
<peter.eisentraut@2ndquadrant.com> wrote:I'm not convinced, however, that this would would really move the needle
in terms of the general security-uneasiness about the public schema and
search paths. AFAICT, in any of your proposals, the default would still
be to have the public schema world-writable and in the path.
Noah's proposed change to initdb appears to involve removing CREATE
permission by default, so I don't think this is true.
I assume that means removing *public* CREATE permissions, not the
owner's (which'd be the DB owner with the proposed changes).
It's hard to predict how many users that might inconvenience, but I
suppose it's probably a big number. On the other hand, the only
alternative is to continue shipping a configuration that, by default,
is potentially insecure. It's hard to decide which thing we should
care more about.
Yeah. The thing is, if we make it harder to create stuff in "public",
that's going to result in the path-of-least-resistance being to run
everything as the DB owner. Which is better than running everything as
superuser (at least if DB owner != postgres), but still not exactly great.
Second least difficult thing is to re-grant public CREATE permissions,
putting things right back where they were.
I'm not sure how far we can expect to move things without creating a
bad on-boarding experience. The folks who actually need cross-user
security already know what they have to do (or if not, that's a docs
problem not a code problem). I'm inclined to think that first-time
users do not need that, though.
What potentially could move the needle is separate search paths for
relation lookup and function/operator lookup. We have sort of stuck
our toe in that pond already by discriminating against pg_temp for
function/operator lookup, but we could make that more formalized and
controllable if there were distinct settings. I'm not sure offhand
how much of a compatibility problem that produces.
regards, tom lane
Greetings,
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
Robert Haas <robertmhaas@gmail.com> writes:
On Mon, Nov 2, 2020 at 5:51 AM Peter Eisentraut
<peter.eisentraut@2ndquadrant.com> wrote:I'm not convinced, however, that this would would really move the needle
in terms of the general security-uneasiness about the public schema and
search paths. AFAICT, in any of your proposals, the default would still
be to have the public schema world-writable and in the path.Noah's proposed change to initdb appears to involve removing CREATE
permission by default, so I don't think this is true.
The original proposal didn't include that change (I don't think anyway,
the change you're referring to seems to have come after most of the
folks had voiced opinions..?), so I can understand someone being unclear
on this point. Admittedly, I suspect most folks on this thread assumed,
as I did, that Noah was proposing to remove CREATE permission from
public on the public schema, and Peter was actually responding to the
email which included Noah's suggest initdb change, so it should have
been clear at that point anyway. The only other relevant vote, I
believe, was from Magnus, so might be good to just make sure he's also
in favor of (b)(3)(X) with the understanding that it also involves
removing CREATE rights from the public schema from the public role.
(there are definitely days when I wish we didn't have a public schema,
simply because it would result in 'public' only ever meaning 'the
special role called public' ...)
I assume that means removing *public* CREATE permissions, not the
owner's (which'd be the DB owner with the proposed changes).
Yes, that's correct.
It's hard to predict how many users that might inconvenience, but I
suppose it's probably a big number. On the other hand, the only
alternative is to continue shipping a configuration that, by default,
is potentially insecure. It's hard to decide which thing we should
care more about.Yeah. The thing is, if we make it harder to create stuff in "public",
that's going to result in the path-of-least-resistance being to run
everything as the DB owner. Which is better than running everything as
superuser (at least if DB owner != postgres), but still not exactly great.
Second least difficult thing is to re-grant public CREATE permissions,
putting things right back where they were.I'm not sure how far we can expect to move things without creating a
bad on-boarding experience. The folks who actually need cross-user
security already know what they have to do (or if not, that's a docs
problem not a code problem). I'm inclined to think that first-time
users do not need that, though.
This proposal strikes me as the right balance between having a decent
on-boarding experience for new users, who are likely to be using
superuser or DB owner from the start because they just want to get in
and look at things and play with PG, while still meaningfully moving us
away from having a world-writable schema in the default search path.
At least from seeing the users that start out with PG and then come to
the Slack or IRC channel asking questions, the on-boarding experience
today typically consists of 'apt install postgresql' and then complaints
that they aren't able to figure out how to log into PG (often asking
about what the default password is to log in as 'postgres', or why the
system is saying 'role "root" does not exist'). Once a user gets to the
point of understanding or wanting to create other roles in the system,
saying they need to create a schema for that role if they want it to be
able to create objects (just like a user needing a home directory)
doesn't seem likely to be all that unexpected.
Where we could possibly help in this regard might be to add some syntax
to CREATE ROLE to have it create a schema for the role too- this would
help in a couple of ways: we could give new users a single command to
get going with being able to create objects in a safe way, for their
user, and we would get information about schemas included in the
CREATE ROLE documentation, which doesn't say anything about schemas
currently.
What potentially could move the needle is separate search paths for
relation lookup and function/operator lookup. We have sort of stuck
our toe in that pond already by discriminating against pg_temp for
function/operator lookup, but we could make that more formalized and
controllable if there were distinct settings. I'm not sure offhand
how much of a compatibility problem that produces.
While I agree with the general idea of giving users more granularity
when it comes to what objects are allowed to be created by users, and
where, and how objects are looked up, I really don't think this would
end up being a sufficiently complete answer to a world-writable public
schema. You don't have to be able to create functions or operators in
the public schema to make things dangerous for some other user poking
around at the tables or views that you are allowed to create there.
Thanks,
Stephen
On Mon, Nov 02, 2020 at 12:42:26PM -0500, Tom Lane wrote:
Robert Haas <robertmhaas@gmail.com> writes:
On Mon, Nov 2, 2020 at 5:51 AM Peter Eisentraut
<peter.eisentraut@2ndquadrant.com> wrote:I'm not convinced, however, that this would would really move the needle
in terms of the general security-uneasiness about the public schema and
search paths. AFAICT, in any of your proposals, the default would still
be to have the public schema world-writable and in the path.Noah's proposed change to initdb appears to involve removing CREATE
permission by default, so I don't think this is true.I assume that means removing *public* CREATE permissions, not the
owner's (which'd be the DB owner with the proposed changes).
My plan is for the default to become:
GRANT USAGE ON SCHEMA public TO PUBLIC;
ALTER SCHEMA public OWNER TO DATABASE_OWNER; -- new syntax
Hence, the dbowner can create objects in the schema or grant that ability to
others. Anyone can e.g. SELECT/UPDATE tables in the schema or call functions
in the schema, subject to per-table/per-function ACLs. ACK that this wasn't
explicit on the thread until a few days ago. I kept universal USAGE because
the schema wouldn't be very "public" without that.
It's hard to predict how many users that might inconvenience, but I
suppose it's probably a big number. On the other hand, the only
alternative is to continue shipping a configuration that, by default,
is potentially insecure. It's hard to decide which thing we should
care more about.Yeah. The thing is, if we make it harder to create stuff in "public",
that's going to result in the path-of-least-resistance being to run
everything as the DB owner. Which is better than running everything as
superuser (at least if DB owner != postgres), but still not exactly great.
Second least difficult thing is to re-grant public CREATE permissions,
putting things right back where they were.
That is factual; whenever a strategy is easier to start than its alternatives,
folks will overconsume that strategy. One can mitigate that by introducing
artificial obstacles to use of the discouraged strategy, but that will tend to
harm the onboarding experience. Option (b)(2)(X) would have done that.
When folks end up creating all objects as the database owner, we still get the
win for roles that don't create permanent objects. It's decently common to
have an app run as a user that queries existing permanent objects, not issuing
permanent-object DDL. That works under both v13 and future defaults.
What potentially could move the needle is separate search paths for
relation lookup and function/operator lookup. We have sort of stuck
our toe in that pond already by discriminating against pg_temp for
function/operator lookup, but we could make that more formalized and
controllable if there were distinct settings. I'm not sure offhand
how much of a compatibility problem that produces.
Stephen raised a good point about this. Separately, regarding compatibility,
suppose a v13 database has:
CREATE FUNCTION f() RETURNS int LANGUAGE sql SECURITY DEFINER
AS 'SELECT inner_f()' SET search_path = a, b;
For compatibility, no value of the function-search-path setting should break
this function's ability to find a.inner_f(void). Which definition of
function-search-path achieves this?
On Mon, Nov 2, 2020 at 1:41 PM Stephen Frost <sfrost@snowman.net> wrote:
What potentially could move the needle is separate search paths for
relation lookup and function/operator lookup. We have sort of stuck
our toe in that pond already by discriminating against pg_temp for
function/operator lookup, but we could make that more formalized and
controllable if there were distinct settings. I'm not sure offhand
how much of a compatibility problem that produces.While I agree with the general idea of giving users more granularity
when it comes to what objects are allowed to be created by users, and
where, and how objects are looked up, I really don't think this would
end up being a sufficiently complete answer to a world-writable public
schema. You don't have to be able to create functions or operators in
the public schema to make things dangerous for some other user poking
around at the tables or views that you are allowed to create there.
I agree. Everything that can execute code is a risk, which also
includes things like triggers and RLS policies. Noah's certainly right
about the compatibility hazard, too.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On Mon, Nov 2, 2020 at 11:05:15PM -0800, Noah Misch wrote:
On Mon, Nov 02, 2020 at 12:42:26PM -0500, Tom Lane wrote:
Robert Haas <robertmhaas@gmail.com> writes:
On Mon, Nov 2, 2020 at 5:51 AM Peter Eisentraut
<peter.eisentraut@2ndquadrant.com> wrote:I'm not convinced, however, that this would would really move the needle
in terms of the general security-uneasiness about the public schema and
search paths. AFAICT, in any of your proposals, the default would still
be to have the public schema world-writable and in the path.Noah's proposed change to initdb appears to involve removing CREATE
permission by default, so I don't think this is true.I assume that means removing *public* CREATE permissions, not the
owner's (which'd be the DB owner with the proposed changes).My plan is for the default to become:
GRANT USAGE ON SCHEMA public TO PUBLIC;
ALTER SCHEMA public OWNER TO DATABASE_OWNER; -- new syntax
Seems it would be better to create a predefined role that owns the
public schema, or at least has create permission for the public schema
--- that way, when you are creating a role, you can decide if the role
should have creation permissions in the public schema, rather than
having people always using the database owner for this purpose.--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com
The usefulness of a cup is in its emptiness, Bruce Lee
On Mon, Nov 2, 2020 at 01:41:09PM -0500, Stephen Frost wrote:
At least from seeing the users that start out with PG and then come to
the Slack or IRC channel asking questions, the on-boarding experience
today typically consists of 'apt install postgresql' and then complaints
that they aren't able to figure out how to log into PG (often asking
about what the default password is to log in as 'postgres', or why the
system is saying 'role "root" does not exist'). Once a user gets to the
point of understanding or wanting to create other roles in the system,
saying they need to create a schema for that role if they want it to be
able to create objects (just like a user needing a home directory)
doesn't seem likely to be all that unexpected.
It is a good point that the user has to create another user before this
becomes a usability issue. It seems at the time the first user is
created (non-postgres), the admin needs to decide how the public schema
should behave.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com
The usefulness of a cup is in its emptiness, Bruce Lee
On Mon, Nov 09, 2020 at 02:56:53PM -0500, Bruce Momjian wrote:
On Mon, Nov 2, 2020 at 11:05:15PM -0800, Noah Misch wrote:
My plan is for the default to become:
GRANT USAGE ON SCHEMA public TO PUBLIC;
ALTER SCHEMA public OWNER TO DATABASE_OWNER; -- new syntaxSeems it would be better to create a predefined role that owns the public schema, or at least has create permission for the public schema --- that way, when you are creating a role, you can decide if the role should have creation permissions in the public schema, rather than having people always using the database owner for this purpose.
Defaulting to a specific predefined role empowers the role's members in all
databases simultaneously. Folks who want it like that can create a role and
issue "ALTER SCHEMA public OWNER TO that_role" in template1. What's the
better default? I think that depends on whether you regard this schema as a
per-database phenomenon or a per-cluster phenomenon.
On Thu, Nov 12, 2020 at 06:36:39PM -0800, Noah Misch wrote:
On Mon, Nov 09, 2020 at 02:56:53PM -0500, Bruce Momjian wrote:
On Mon, Nov 2, 2020 at 11:05:15PM -0800, Noah Misch wrote:
My plan is for the default to become:
GRANT USAGE ON SCHEMA public TO PUBLIC;
ALTER SCHEMA public OWNER TO DATABASE_OWNER; -- new syntaxSeems it would be better to create a predefined role that owns the public schema, or at least has create permission for the public schema --- that way, when you are creating a role, you can decide if the role should have creation permissions in the public schema, rather than having people always using the database owner for this purpose.Defaulting to a specific predefined role empowers the role's members in all
databases simultaneously. Folks who want it like that can create a role and
issue "ALTER SCHEMA public OWNER TO that_role" in template1. What's the
better default? I think that depends on whether you regard this schema as a
per-database phenomenon or a per-cluster phenomenon.
Ah, I see your point. I was just thinking we don't want everyone
logging in as the db user, or given super-user permissions, so haveing a
non-login role would help, but we can just document how to do it.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com
The usefulness of a cup is in its emptiness, Bruce Lee
I'm attaching the patch for $SUBJECT, which applies atop the four patches from
the two other threads below. For convenience of testing, I've included a
rollup patch, equivalent to applying all five patches.
On Sat, Oct 31, 2020 at 09:35:18AM -0700, Noah Misch wrote:
More details on the semantics I'll use:
1. initdb will change like this: @@ -1721 +1721 @@ setup_privileges(FILE *cmdfd) - "GRANT CREATE, USAGE ON SCHEMA public TO PUBLIC;\n\n", + "GRANT USAGE ON SCHEMA public TO PUBLIC;\n\n", + "ALTER SCHEMA public OWNER TO DATABASE_OWNER;\n\n",
(I ended up assigning the ownership via pg_namespace.dat, not here.)
2. If schema public does not exist, pg_dump will emit nothing about it. This
is what happens today. (I suspect it would be better for pg_dump to emit
DROP SCHEMA public RESTRICT, but that is drifting offtopic for $SUBJECT.)
Otherwise, when dumping from v13 or earlier, pg_dump will always emit
REVOKE and/or GRANT statements to reproduce the old ACL.
More precisely, it diffs the source database ownership and ACL to the v14
defaults, then emits ALTER, GRANT, and/or REVOKE as needed. That yields no
GRANT or REVOKE if the source database is an early adopter of the new default.
When dumping from
v14 or later, pg_dump will use pg_init_privs to compute GRANT and REVOKE
statements, as it does today.
(It doesn't actually use pg_init_privs, but the effect is similar.)
(This may interfere with cross-version
pg_upgrade testing. I haven't looked at how best to fix that. Perhaps add
more fix_sql in test.sh.)
src/bin/pg_upgrade/test.sh doesn't need changes. Upgrades from 9.6 (the first
version having pg_init_privs) or later get no new diffs. Upgrades from v8.4
or v9.5 to v14 have a relevant diff before or after this change. In master:
-REVOKE ALL ON SCHEMA public FROM PUBLIC;
-REVOKE ALL ON SCHEMA public FROM nm;
-GRANT ALL ON SCHEMA public TO nm;
-GRANT ALL ON SCHEMA public TO PUBLIC;
After $SUBJECT:
-REVOKE ALL ON SCHEMA public FROM PUBLIC;
-REVOKE ALL ON SCHEMA public FROM nm;
-GRANT ALL ON SCHEMA public TO nm;
+REVOKE USAGE ON SCHEMA public FROM PUBLIC;
GRANT ALL ON SCHEMA public TO PUBLIC;3. pg_upgrade from v13 to later versions will transfer template1's ACL for
schema public, even if that ACL was unchanged since v13 initdb. (This is
purely a consequence of the pg_dump behavior decision.) template0 will
keep the new default.4. OWNER TO DATABASE_OWNER will likely be available for schemas only, though I
might propose it for all object classes if class-specific complexity proves
negligible.
Class-specific complexity was negligible, so I made it available for all
objects. The syntax is "OWNER TO pg_database_owner", because it's a special
predefined role. That patch has its own thread:
/messages/by-id/20201228043148.GA1053024@rfd.leadboat.com
5. ALTER DATABASE OWNER TO changes access control decisions involving
nspowner==DATABASE_OWNER. Speed of nspacl checks is more important than
reacting swiftly to ALTER DATABASE OWNER TO. Sessions running concurrently
will be eventually-consistent with respect to the ALTER DATABASE.
(Existing access control decisions, too, allow this sort of anomaly.)6. pg_dump hasn't been reproducing ALTER SCHEMA public OWNER TO. That's a
mild defect today, but it wouldn't be mild anymore. We'll need pg_dump of
v13 databases to emit "ALTER SCHEMA public OWNER TO postgres" and for a v14
=> v15 upgrade to propagate that. This project can stand by itself; would
anyone else like to own it?
That patch has its own thread:
/messages/by-id/20201229134924.GA1431748@rfd.leadboat.com
Changing this ACL caused 13 of 202 tests to fail in "make check". I first
intended to modify tests as needed for that suite to keep the default ACL.
For complicated cases, my strategy was to make a test create a schema and
change search_path. However, that created large expected output diffs
(e.g. ~120 lines in updatable_views.out), mostly in EXPLAIN and \d output
bearing the schema name. I didn't want that kind of obstacle to future
back-patched test updates, so I did make the first test install the old ACL.
All other in-tree suites do test the new default.
Thanks,
nm
Attachments:
public-default-acl-v1.patch_no_cfbottext/plain; charset=us-asciiDownload
Author: Noah Misch <noah@leadboat.com>
Commit: Noah Misch <noah@leadboat.com>
Revoke PUBLIC CREATE from public schema, now owned by pg_database_owner.
This switches the default ACL to what the documentation has recommended
since CVE-2018-1058. Upgrades will carry forward any old ownership and
ACL. Sites that declined the 2018 recommendation should take a fresh
look. Recipes for commissioning a new database cluster from scratch may
need to create a schema, grant more privileges, etc. Out-of-tree test
suites may require such updates.
Reviewed by FIXME.
Discussion: https://postgr.es/m/20201031163518.GB4039133@rfd.leadboat.com
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 60c7e11..8b3b37b 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -8925,7 +8925,7 @@ $d$;
-- But creation of user mappings for non-superusers should fail
CREATE USER MAPPING FOR public SERVER loopback_nopw;
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback_nopw;
-CREATE FOREIGN TABLE ft1_nopw (
+CREATE FOREIGN TABLE pg_temp.ft1_nopw (
c1 int NOT NULL,
c2 int NOT NULL,
c3 text,
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 151f4f1..bf35097 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -2595,7 +2595,7 @@ $d$;
CREATE USER MAPPING FOR public SERVER loopback_nopw;
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback_nopw;
-CREATE FOREIGN TABLE ft1_nopw (
+CREATE FOREIGN TABLE pg_temp.ft1_nopw (
c1 int NOT NULL,
c2 int NOT NULL,
c3 text,
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 1e9a462..1b30a0d 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -2982,20 +2982,18 @@ SELECT 3 OPERATOR(pg_catalog.+) 4;
<para>
By default, users cannot access any objects in schemas they do not
own. To allow that, the owner of the schema must grant the
- <literal>USAGE</literal> privilege on the schema. To allow users
- to make use of the objects in the schema, additional privileges
- might need to be granted, as appropriate for the object.
+ <literal>USAGE</literal> privilege on the schema. By default, everyone
+ has that privilege on the schema <literal>public</literal>. To allow
+ users to make use of the objects in a schema, additional privileges might
+ need to be granted, as appropriate for the object.
</para>
<para>
- A user can also be allowed to create objects in someone else's
- schema. To allow that, the <literal>CREATE</literal> privilege on
- the schema needs to be granted. Note that by default, everyone
- has <literal>CREATE</literal> and <literal>USAGE</literal> privileges on
- the schema
- <literal>public</literal>. This allows all users that are able to
- connect to a given database to create objects in its
- <literal>public</literal> schema.
+ A user can also be allowed to create objects in someone else's schema. To
+ allow that, the <literal>CREATE</literal> privilege on the schema needs to
+ be granted. In databases upgraded from
+ <productname>PostgreSQL</productname> 13 or earlier, everyone has that
+ privilege on the schema <literal>public</literal>.
Some <link linkend="ddl-schemas-patterns">usage patterns</link> call for
revoking that privilege:
<programlisting>
@@ -3068,20 +3066,25 @@ REVOKE CREATE ON SCHEMA public FROM PUBLIC;
database owner attack. -->
<para>
Constrain ordinary users to user-private schemas. To implement this,
- issue <literal>REVOKE CREATE ON SCHEMA public FROM PUBLIC</literal>,
- and create a schema for each user with the same name as that user.
- Recall that the default search path starts
- with <literal>$user</literal>, which resolves to the user name.
- Therefore, if each user has a separate schema, they access their own
- schemas by default. After adopting this pattern in a database where
- untrusted users had already logged in, consider auditing the public
- schema for objects named like objects in
+ first issue <literal>REVOKE CREATE ON SCHEMA public FROM
+ PUBLIC</literal>. Then, for every user needing to create non-temporary
+ objects, create a schema with the same name as that user. Recall that
+ the default search path starts with <literal>$user</literal>, which
+ resolves to the user name. Therefore, if each user has a separate
+ schema, they access their own schemas by default. After adopting this
+ pattern in a database where untrusted users had already logged in,
+ consider auditing the public schema for objects named like objects in
schema <literal>pg_catalog</literal>. This pattern is a secure schema
usage pattern unless an untrusted user is the database owner or holds
the <literal>CREATEROLE</literal> privilege, in which case no secure
schema usage pattern exists.
</para>
<para>
+ If the database originated in an upgrade
+ from <productname>PostgreSQL</productname> 13 or earlier,
+ the <literal>REVOKE</literal> is essential. Otherwise, the default
+ configuration follows this pattern; ordinary users can create only
+ temporary objects until a privileged user furnishes a schema.
</para>
</listitem>
@@ -3090,10 +3093,10 @@ REVOKE CREATE ON SCHEMA public FROM PUBLIC;
Remove the public schema from the default search path, by modifying
<link linkend="config-setting-configuration-file"><filename>postgresql.conf</filename></link>
or by issuing <literal>ALTER ROLE ALL SET search_path =
- "$user"</literal>. Everyone retains the ability to create objects in
- the public schema, but only qualified names will choose those objects.
- While qualified table references are fine, calls to functions in the
- public schema <link linkend="typeconv-func">will be unsafe or
+ "$user"</literal>. Then, grant privileges to create in the public
+ schema. Only qualified names will choose public schema objects. While
+ qualified table references are fine, calls to functions in the public
+ schema <link linkend="typeconv-func">will be unsafe or
unreliable</link>. If you create functions or extensions in the public
schema, use the first pattern instead. Otherwise, like the first
pattern, this is secure unless an untrusted user is the database owner
@@ -3103,11 +3106,14 @@ REVOKE CREATE ON SCHEMA public FROM PUBLIC;
<listitem>
<para>
- Keep the default. All users access the public schema implicitly. This
+ Keep the default search path, and grant privileges to create in the
+ public schema. All users access the public schema implicitly. This
simulates the situation where schemas are not available at all, giving
a smooth transition from the non-schema-aware world. However, this is
never a secure pattern. It is acceptable only when the database has a
- single user or a few mutually-trusting users.
+ single user or a few mutually-trusting users. In databases upgraded
+ from <productname>PostgreSQL</productname> 13 or earlier, this is the
+ default.
</para>
</listitem>
</itemizedlist>
diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml
index 758493c..768a37f 100644
--- a/doc/src/sgml/user-manag.sgml
+++ b/doc/src/sgml/user-manag.sgml
@@ -577,13 +577,14 @@ DROP ROLE doomed_role;
<para>
The <literal>pg_database_owner</literal> role has one implicit,
- situation-dependent member, namely the owner of the current database. The
- role conveys no rights at first. Like any role, it can own objects or
- receive grants of access privileges. Consequently, once
- <literal>pg_database_owner</literal> has rights within a template database,
- each owner of a database instantiated from that template will exercise those
- rights. <literal>pg_database_owner</literal> cannot be a member of any
- role, and it cannot have non-implicit members.
+ situation-dependent member, namely the owner of the current database. Like
+ any role, it can own objects or receive grants of access privileges.
+ Consequently, once <literal>pg_database_owner</literal> has rights within a
+ template database, each owner of a database instantiated from that template
+ will exercise those rights. <literal>pg_database_owner</literal> cannot be
+ a member of any role, and it cannot have non-implicit members. Initially,
+ this role owns the <literal>public</literal> schema, so each database owner
+ governs local use of the schema.
</para>
<para>
@@ -632,8 +633,8 @@ GRANT pg_signal_backend TO admin_user;
horse</quote> others with relative ease. The strongest protection is tight
control over who can define objects. Where that is infeasible, write
queries referring only to objects having trusted owners. Remove
- from <varname>search_path</varname> the public schema and any other schemas
- that permit untrusted users to create objects.
+ from <varname>search_path</varname> any schemas that permit untrusted users
+ to create objects.
</para>
<para>
diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c
index 62540a1..3a72fbb 100644
--- a/src/bin/initdb/initdb.c
+++ b/src/bin/initdb/initdb.c
@@ -1696,8 +1696,7 @@ setup_privileges(FILE *cmdfd)
CppAsString2(RELKIND_VIEW) ", " CppAsString2(RELKIND_MATVIEW) ", "
CppAsString2(RELKIND_SEQUENCE) ")"
" AND relacl IS NULL;\n\n",
- "GRANT USAGE ON SCHEMA pg_catalog TO PUBLIC;\n\n",
- "GRANT CREATE, USAGE ON SCHEMA public TO PUBLIC;\n\n",
+ "GRANT USAGE ON SCHEMA pg_catalog, public TO PUBLIC;\n\n",
"REVOKE ALL ON pg_largeobject FROM PUBLIC;\n\n",
"INSERT INTO pg_init_privs "
" (objoid, classoid, objsubid, initprivs, privtype)"
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 0a3f40d..d59e063 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -1568,10 +1568,11 @@ selectDumpableNamespace(NamespaceInfo *nsinfo, Archive *fout)
* no-mans-land between being a system object and a user object.
* CREATE SCHEMA would fail, so its DUMP_COMPONENT_DEFINITION is just
* a comment and an indication of ownership. If the owner is the
- * default, that DUMP_COMPONENT_DEFINITION is superfluous.
+ * default, omit that superfluous DUMP_COMPONENT_DEFINITION. Before
+ * v14, the default owner was BOOTSTRAP_SUPERUSERID.
*/
nsinfo->dobj.dump = DUMP_COMPONENT_ALL;
- if (nsinfo->nspowner == BOOTSTRAP_SUPERUSERID)
+ if (nsinfo->nspowner == DEFAULT_ROLE_DATABASE_OWNER)
nsinfo->dobj.dump &= ~DUMP_COMPONENT_DEFINITION;
nsinfo->dobj.dump_contains = DUMP_COMPONENT_ALL;
}
@@ -4777,21 +4778,26 @@ getNamespaces(Archive *fout, int *numNamespaces)
PQExpBuffer init_racl_subquery = createPQExpBuffer();
/*
- * Bypass pg_init_privs.initprivs for the public schema. Dropping and
- * recreating the schema detaches it from its pg_init_privs row, but
- * an empty destination database starts with this ACL nonetheless.
- * Also, we support dump/reload of public schema ownership changes.
- * ALTER SCHEMA OWNER filters nspacl through aclnewowner(), but
- * initprivs continues to reflect the initial owner (the bootstrap
- * superuser). Hence, synthesize the value that nspacl will have
- * after the restore's ALTER SCHEMA OWNER.
+ * Bypass pg_init_privs.initprivs for the public schema, for several
+ * reasons. First, dropping and recreating the schema detaches it
+ * from its pg_init_privs row, but an empty destination database
+ * starts with this ACL nonetheless. Second, we support dump/reload
+ * of public schema ownership changes. ALTER SCHEMA OWNER filters
+ * nspacl through aclnewowner(), but initprivs continues to reflect
+ * the initial owner. Hence, synthesize the value that nspacl will
+ * have after the restore's ALTER SCHEMA OWNER. Third, this makes the
+ * destination database match the source's ACL, even if the latter was
+ * an initdb-default ACL, which changed in v14. An upgrade pulls in
+ * changes to most system object ACLs that the DBA had not customized.
+ * We've made the public schema depart from that, because changing its
+ * ACL so easily breaks applications.
*/
buildACLQueries(acl_subquery, racl_subquery, init_acl_subquery,
init_racl_subquery, "n.nspacl", "n.nspowner",
"CASE WHEN n.nspname = 'public' THEN array["
" format('%s=UC/%s', "
" n.nspowner::regrole, n.nspowner::regrole),"
- " format('=UC/%s', n.nspowner::regrole)]::aclitem[] "
+ " format('=U/%s', n.nspowner::regrole)]::aclitem[] "
"ELSE pip.initprivs END",
"'n'", dopt->binary_upgrade);
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index 4693292..02b4418 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -614,7 +614,9 @@ my %tests = (
},
'ALTER SCHEMA public OWNER TO' => {
- # see test "REVOKE CREATE ON SCHEMA public" for causative create_sql
+ create_order => 15,
+ create_sql =>
+ 'ALTER SCHEMA public OWNER TO "regress_quoted \"" role";',
regexp => qr/^ALTER SCHEMA public OWNER TO .+;/m,
like => {
%full_runs, section_pre_data => 1,
@@ -3342,16 +3344,12 @@ my %tests = (
unlike => { no_privs => 1, },
},
- 'REVOKE CREATE ON SCHEMA public FROM public' => {
+ 'REVOKE ALL ON SCHEMA public' => {
create_order => 16,
- create_sql => '
- REVOKE CREATE ON SCHEMA public FROM public;
- ALTER SCHEMA public OWNER TO "regress_quoted \"" role";
- REVOKE ALL ON SCHEMA public FROM "regress_quoted \"" role";',
+ create_sql =>
+ 'REVOKE ALL ON SCHEMA public FROM "regress_quoted \"" role";',
regexp => qr/^
\QREVOKE ALL ON SCHEMA public FROM "regress_quoted \"" role";\E
- \n\QREVOKE ALL ON SCHEMA public FROM PUBLIC;\E
- \n\QGRANT USAGE ON SCHEMA public TO PUBLIC;\E
/xm,
like => { %full_runs, section_pre_data => 1, },
unlike => { no_privs => 1, },
diff --git a/src/include/catalog/pg_namespace.dat b/src/include/catalog/pg_namespace.dat
index 988f1c4..7932aa6 100644
--- a/src/include/catalog/pg_namespace.dat
+++ b/src/include/catalog/pg_namespace.dat
@@ -21,6 +21,6 @@
# update dumpComment() if changing this descr
{ oid => '2200', oid_symbol => 'PG_PUBLIC_NAMESPACE',
descr => 'standard public schema',
- nspname => 'public', nspacl => '_null_' },
+ nspname => 'public', nspowner => 'pg_database_owner', nspacl => '_null_' },
]
diff --git a/src/pl/plperl/expected/plperl_setup.out b/src/pl/plperl/expected/plperl_setup.out
index a1a24df..5234feb 100644
--- a/src/pl/plperl/expected/plperl_setup.out
+++ b/src/pl/plperl/expected/plperl_setup.out
@@ -25,6 +25,9 @@ CREATE EXTENSION plperl;
CREATE EXTENSION plperlu; -- fail
ERROR: permission denied to create extension "plperlu"
HINT: Must be superuser to create this extension.
+CREATE SCHEMA plperl_setup_scratch;
+SET search_path = plperl_setup_scratch;
+GRANT ALL ON SCHEMA plperl_setup_scratch TO regress_user2;
CREATE FUNCTION foo1() returns int language plperl as '1;';
SELECT foo1();
foo1
@@ -34,6 +37,7 @@ SELECT foo1();
-- Must reconnect to avoid failure with non-MULTIPLICITY Perl interpreters
\c -
+SET search_path = plperl_setup_scratch;
SET ROLE regress_user1;
-- Should be able to change privileges on the language
revoke all on language plperl from public;
diff --git a/src/pl/plperl/sql/plperl_setup.sql b/src/pl/plperl/sql/plperl_setup.sql
index 7484478..a89cf56 100644
--- a/src/pl/plperl/sql/plperl_setup.sql
+++ b/src/pl/plperl/sql/plperl_setup.sql
@@ -27,12 +27,16 @@ SET ROLE regress_user1;
CREATE EXTENSION plperl;
CREATE EXTENSION plperlu; -- fail
+CREATE SCHEMA plperl_setup_scratch;
+SET search_path = plperl_setup_scratch;
+GRANT ALL ON SCHEMA plperl_setup_scratch TO regress_user2;
CREATE FUNCTION foo1() returns int language plperl as '1;';
SELECT foo1();
-- Must reconnect to avoid failure with non-MULTIPLICITY Perl interpreters
\c -
+SET search_path = plperl_setup_scratch;
SET ROLE regress_user1;
diff --git a/src/test/regress/input/tablespace.source b/src/test/regress/input/tablespace.source
index c133e73..cb9774e 100644
--- a/src/test/regress/input/tablespace.source
+++ b/src/test/regress/input/tablespace.source
@@ -388,7 +388,7 @@ CREATE INDEX k ON testschema.tablespace_acl (c) TABLESPACE regress_tblspace;
ALTER TABLE testschema.tablespace_acl OWNER TO regress_tablespace_user2;
SET SESSION ROLE regress_tablespace_user2;
-CREATE TABLE tablespace_table (i int) TABLESPACE regress_tblspace; -- fail
+CREATE TEMP TABLE tablespace_table (i int) TABLESPACE regress_tblspace; -- fail
ALTER TABLE testschema.tablespace_acl ALTER c TYPE bigint;
REINDEX (TABLESPACE regress_tblspace) TABLE tablespace_table; -- fail
REINDEX (TABLESPACE regress_tblspace, CONCURRENTLY) TABLE tablespace_table; -- fail
@@ -409,3 +409,6 @@ DROP SCHEMA testschema CASCADE;
DROP ROLE regress_tablespace_user1;
DROP ROLE regress_tablespace_user2;
+
+-- Rest of this suite can use the public schema freely.
+GRANT ALL ON SCHEMA public TO public;
diff --git a/src/test/regress/output/tablespace.source b/src/test/regress/output/tablespace.source
index 1bbe7e0..e7629d4 100644
--- a/src/test/regress/output/tablespace.source
+++ b/src/test/regress/output/tablespace.source
@@ -908,7 +908,7 @@ CREATE TABLE testschema.tablespace_acl (c int);
CREATE INDEX k ON testschema.tablespace_acl (c) TABLESPACE regress_tblspace;
ALTER TABLE testschema.tablespace_acl OWNER TO regress_tablespace_user2;
SET SESSION ROLE regress_tablespace_user2;
-CREATE TABLE tablespace_table (i int) TABLESPACE regress_tblspace; -- fail
+CREATE TEMP TABLE tablespace_table (i int) TABLESPACE regress_tblspace; -- fail
ERROR: permission denied for tablespace regress_tblspace
ALTER TABLE testschema.tablespace_acl ALTER c TYPE bigint;
REINDEX (TABLESPACE regress_tblspace) TABLE tablespace_table; -- fail
@@ -934,3 +934,5 @@ drop cascades to table testschema.atable
drop cascades to table testschema.tablespace_acl
DROP ROLE regress_tablespace_user1;
DROP ROLE regress_tablespace_user2;
+-- Rest of this suite can use the public schema freely.
+GRANT ALL ON SCHEMA public TO public;
public-default-acl-v1-rollup.patchtext/plain; charset=us-asciiDownload
Author: Noah Misch <noah@leadboat.com>
Commit: Noah Misch <noah@leadboat.com>
Rollup of five patches, for easy testing.
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 60c7e11..8b3b37b 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -8925,7 +8925,7 @@ $d$;
-- But creation of user mappings for non-superusers should fail
CREATE USER MAPPING FOR public SERVER loopback_nopw;
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback_nopw;
-CREATE FOREIGN TABLE ft1_nopw (
+CREATE FOREIGN TABLE pg_temp.ft1_nopw (
c1 int NOT NULL,
c2 int NOT NULL,
c3 text,
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 151f4f1..bf35097 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -2595,7 +2595,7 @@ $d$;
CREATE USER MAPPING FOR public SERVER loopback_nopw;
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback_nopw;
-CREATE FOREIGN TABLE ft1_nopw (
+CREATE FOREIGN TABLE pg_temp.ft1_nopw (
c1 int NOT NULL,
c2 int NOT NULL,
c3 text,
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index ea222c0..8080047 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -10105,6 +10105,9 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
<primary>pg_group</primary>
</indexterm>
+ <!-- Unlike information_schema.applicable_roles, this shows no members for
+ pg_database_owner. The v8.1 catalog would have shown no members if
+ that role had existed at the time. -->
<para>
The view <structname>pg_group</structname> exists for backwards
compatibility: it emulates a catalog that existed in
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 1e9a462..1b30a0d 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -2982,20 +2982,18 @@ SELECT 3 OPERATOR(pg_catalog.+) 4;
<para>
By default, users cannot access any objects in schemas they do not
own. To allow that, the owner of the schema must grant the
- <literal>USAGE</literal> privilege on the schema. To allow users
- to make use of the objects in the schema, additional privileges
- might need to be granted, as appropriate for the object.
+ <literal>USAGE</literal> privilege on the schema. By default, everyone
+ has that privilege on the schema <literal>public</literal>. To allow
+ users to make use of the objects in a schema, additional privileges might
+ need to be granted, as appropriate for the object.
</para>
<para>
- A user can also be allowed to create objects in someone else's
- schema. To allow that, the <literal>CREATE</literal> privilege on
- the schema needs to be granted. Note that by default, everyone
- has <literal>CREATE</literal> and <literal>USAGE</literal> privileges on
- the schema
- <literal>public</literal>. This allows all users that are able to
- connect to a given database to create objects in its
- <literal>public</literal> schema.
+ A user can also be allowed to create objects in someone else's schema. To
+ allow that, the <literal>CREATE</literal> privilege on the schema needs to
+ be granted. In databases upgraded from
+ <productname>PostgreSQL</productname> 13 or earlier, everyone has that
+ privilege on the schema <literal>public</literal>.
Some <link linkend="ddl-schemas-patterns">usage patterns</link> call for
revoking that privilege:
<programlisting>
@@ -3068,20 +3066,25 @@ REVOKE CREATE ON SCHEMA public FROM PUBLIC;
database owner attack. -->
<para>
Constrain ordinary users to user-private schemas. To implement this,
- issue <literal>REVOKE CREATE ON SCHEMA public FROM PUBLIC</literal>,
- and create a schema for each user with the same name as that user.
- Recall that the default search path starts
- with <literal>$user</literal>, which resolves to the user name.
- Therefore, if each user has a separate schema, they access their own
- schemas by default. After adopting this pattern in a database where
- untrusted users had already logged in, consider auditing the public
- schema for objects named like objects in
+ first issue <literal>REVOKE CREATE ON SCHEMA public FROM
+ PUBLIC</literal>. Then, for every user needing to create non-temporary
+ objects, create a schema with the same name as that user. Recall that
+ the default search path starts with <literal>$user</literal>, which
+ resolves to the user name. Therefore, if each user has a separate
+ schema, they access their own schemas by default. After adopting this
+ pattern in a database where untrusted users had already logged in,
+ consider auditing the public schema for objects named like objects in
schema <literal>pg_catalog</literal>. This pattern is a secure schema
usage pattern unless an untrusted user is the database owner or holds
the <literal>CREATEROLE</literal> privilege, in which case no secure
schema usage pattern exists.
</para>
<para>
+ If the database originated in an upgrade
+ from <productname>PostgreSQL</productname> 13 or earlier,
+ the <literal>REVOKE</literal> is essential. Otherwise, the default
+ configuration follows this pattern; ordinary users can create only
+ temporary objects until a privileged user furnishes a schema.
</para>
</listitem>
@@ -3090,10 +3093,10 @@ REVOKE CREATE ON SCHEMA public FROM PUBLIC;
Remove the public schema from the default search path, by modifying
<link linkend="config-setting-configuration-file"><filename>postgresql.conf</filename></link>
or by issuing <literal>ALTER ROLE ALL SET search_path =
- "$user"</literal>. Everyone retains the ability to create objects in
- the public schema, but only qualified names will choose those objects.
- While qualified table references are fine, calls to functions in the
- public schema <link linkend="typeconv-func">will be unsafe or
+ "$user"</literal>. Then, grant privileges to create in the public
+ schema. Only qualified names will choose public schema objects. While
+ qualified table references are fine, calls to functions in the public
+ schema <link linkend="typeconv-func">will be unsafe or
unreliable</link>. If you create functions or extensions in the public
schema, use the first pattern instead. Otherwise, like the first
pattern, this is secure unless an untrusted user is the database owner
@@ -3103,11 +3106,14 @@ REVOKE CREATE ON SCHEMA public FROM PUBLIC;
<listitem>
<para>
- Keep the default. All users access the public schema implicitly. This
+ Keep the default search path, and grant privileges to create in the
+ public schema. All users access the public schema implicitly. This
simulates the situation where schemas are not available at all, giving
a smooth transition from the non-schema-aware world. However, this is
never a secure pattern. It is acceptable only when the database has a
- single user or a few mutually-trusting users.
+ single user or a few mutually-trusting users. In databases upgraded
+ from <productname>PostgreSQL</productname> 13 or earlier, this is the
+ default.
</para>
</listitem>
</itemizedlist>
diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml
index cc08252..768a37f 100644
--- a/doc/src/sgml/user-manag.sgml
+++ b/doc/src/sgml/user-manag.sgml
@@ -540,6 +540,10 @@ DROP ROLE doomed_role;
<literal>pg_stat_scan_tables</literal>.</entry>
</row>
<row>
+ <entry>pg_database_owner</entry>
+ <entry>None. Membership consists, implicitly, of the current database owner.</entry>
+ </row>
+ <row>
<entry>pg_signal_backend</entry>
<entry>Signal another backend to cancel a query or terminate its session.</entry>
</row>
@@ -572,6 +576,18 @@ DROP ROLE doomed_role;
</para>
<para>
+ The <literal>pg_database_owner</literal> role has one implicit,
+ situation-dependent member, namely the owner of the current database. Like
+ any role, it can own objects or receive grants of access privileges.
+ Consequently, once <literal>pg_database_owner</literal> has rights within a
+ template database, each owner of a database instantiated from that template
+ will exercise those rights. <literal>pg_database_owner</literal> cannot be
+ a member of any role, and it cannot have non-implicit members. Initially,
+ this role owns the <literal>public</literal> schema, so each database owner
+ governs local use of the schema.
+ </para>
+
+ <para>
The <literal>pg_signal_backend</literal> role is intended to allow
administrators to enable trusted, but non-superuser, roles to send signals
to other backends. Currently this role enables sending of signals for
@@ -617,8 +633,8 @@ GRANT pg_signal_backend TO admin_user;
horse</quote> others with relative ease. The strongest protection is tight
control over who can define objects. Where that is infeasible, write
queries referring only to objects having trusted owners. Remove
- from <varname>search_path</varname> the public schema and any other schemas
- that permit untrusted users to create objects.
+ from <varname>search_path</varname> any schemas that permit untrusted users
+ to create objects.
</para>
<para>
diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql
index 4907855..9a0169b 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -255,7 +255,14 @@ CREATE VIEW applicable_roles AS
SELECT CAST(a.rolname AS sql_identifier) AS grantee,
CAST(b.rolname AS sql_identifier) AS role_name,
CAST(CASE WHEN m.admin_option THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
- FROM pg_auth_members m
+ FROM (SELECT member, roleid, admin_option FROM pg_auth_members
+ -- This UNION could be UNION ALL, but UNION works even if we start
+ -- to allow explicit pg_database_owner membership.
+ UNION
+ SELECT datdba, pg_authid.oid, false
+ FROM pg_database, pg_authid
+ WHERE datname = current_database() AND rolname = 'pg_database_owner'
+ ) m
JOIN pg_authid a ON (m.member = a.oid)
JOIN pg_authid b ON (m.roleid = b.oid)
WHERE pg_has_role(a.oid, 'USAGE');
diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c
index ed243e3..74be182 100644
--- a/src/backend/commands/user.c
+++ b/src/backend/commands/user.c
@@ -1497,6 +1497,18 @@ AddRoleMems(const char *rolename, Oid roleid,
}
/*
+ * The charter of pg_database_owner is to have exactly one, implicit,
+ * situation-dependent member. There's no technical need for this
+ * restriction. (One could lift it and take the further step of making
+ * pg_database_ownercheck() equivalent to has_privs_of_role(roleid,
+ * DEFAULT_ROLE_DATABASE_OWNER), in which case explicit,
+ * situation-independent members could act as the owner of any database.)
+ */
+ if (roleid == DEFAULT_ROLE_DATABASE_OWNER)
+ ereport(ERROR,
+ errmsg("role \"%s\" cannot have explicit members", rolename));
+
+ /*
* The role membership grantor of record has little significance at
* present. Nonetheless, inasmuch as users might look to it for a crude
* audit trail, let only superusers impute the grant to a third party.
@@ -1525,6 +1537,22 @@ AddRoleMems(const char *rolename, Oid roleid,
bool new_record_repl[Natts_pg_auth_members];
/*
+ * pg_database_owner is never a role member. Lifting this restriction
+ * would require a policy decision about membership loops. One could
+ * prevent loops, which would include making "ALTER DATABASE x OWNER
+ * TO proposed_datdba" fail if is_member_of_role(pg_database_owner,
+ * proposed_datdba). Hence, gaining a membership could reduce what a
+ * role could do. Alternately, one could allow these memberships to
+ * complete loops. A role could then have actual WITH ADMIN OPTION on
+ * itself, prompting a decision about is_admin_of_role() treatment of
+ * the case.
+ */
+ if (memberid == DEFAULT_ROLE_DATABASE_OWNER)
+ ereport(ERROR,
+ errmsg("role \"%s\" cannot be a member of any role",
+ get_rolespec_name(memberRole)));
+
+ /*
* Refuse creation of membership loops, including the trivial case
* where a role is made a member of itself. We do this by checking to
* see if the target role is already a member of the proposed member
diff --git a/src/backend/utils/adt/acl.c b/src/backend/utils/adt/acl.c
index c7f029e..34975ee 100644
--- a/src/backend/utils/adt/acl.c
+++ b/src/backend/utils/adt/acl.c
@@ -22,6 +22,7 @@
#include "catalog/pg_auth_members.h"
#include "catalog/pg_authid.h"
#include "catalog/pg_class.h"
+#include "catalog/pg_database.h"
#include "catalog/pg_type.h"
#include "commands/dbcommands.h"
#include "commands/proclang.h"
@@ -50,32 +51,25 @@ typedef struct
/*
* We frequently need to test whether a given role is a member of some other
* role. In most of these tests the "given role" is the same, namely the
- * active current user. So we can optimize it by keeping a cached list of
- * all the roles the "given role" is a member of, directly or indirectly.
- *
- * There are actually two caches, one computed under "has_privs" rules
- * (do not recurse where rolinherit isn't true) and one computed under
- * "is_member" rules (recurse regardless of rolinherit).
+ * active current user. So we can optimize it by keeping cached lists of all
+ * the roles the "given role" is a member of, directly or indirectly.
*
* Possibly this mechanism should be generalized to allow caching membership
* info for multiple roles?
*
- * The has_privs cache is:
- * cached_privs_role is the role OID the cache is for.
- * cached_privs_roles is an OID list of roles that cached_privs_role
- * has the privileges of (always including itself).
- * The cache is valid if cached_privs_role is not InvalidOid.
- *
- * The is_member cache is similarly:
- * cached_member_role is the role OID the cache is for.
- * cached_membership_roles is an OID list of roles that cached_member_role
- * is a member of (always including itself).
- * The cache is valid if cached_member_role is not InvalidOid.
+ * Each element of cached_roles is an OID list of constituent roles for the
+ * corresponding element of cached_role (always including the cached_role
+ * itself). One cache has ROLERECURSE_PRIVS semantics, and the other has
+ * ROLERECURSE_MEMBERS semantics.
*/
-static Oid cached_privs_role = InvalidOid;
-static List *cached_privs_roles = NIL;
-static Oid cached_member_role = InvalidOid;
-static List *cached_membership_roles = NIL;
+enum RoleRecurseType
+{
+ ROLERECURSE_PRIVS = 0, /* recurse if rolinherit */
+ ROLERECURSE_MEMBERS = 1 /* recurse unconditionally */
+};
+static Oid cached_role[] = {InvalidOid, InvalidOid};
+static List *cached_roles[] = {NIL, NIL};
+static uint32 cached_db_hash;
static const char *getid(const char *s, char *n);
@@ -4673,10 +4667,14 @@ initialize_acl(void)
{
if (!IsBootstrapProcessingMode())
{
+ cached_db_hash =
+ GetSysCacheHashValue1(DATABASEOID,
+ ObjectIdGetDatum(MyDatabaseId));
+
/*
* In normal mode, set a callback on any syscache invalidation of rows
- * of pg_auth_members (for each AUTHMEM search in this file) or
- * pg_authid (for has_rolinherit())
+ * of pg_auth_members (for roles_is_member_of()), pg_authid (for
+ * has_rolinherit()), or pg_database (for roles_is_member_of())
*/
CacheRegisterSyscacheCallback(AUTHMEMROLEMEM,
RoleMembershipCacheCallback,
@@ -4684,6 +4682,9 @@ initialize_acl(void)
CacheRegisterSyscacheCallback(AUTHOID,
RoleMembershipCacheCallback,
(Datum) 0);
+ CacheRegisterSyscacheCallback(DATABASEOID,
+ RoleMembershipCacheCallback,
+ (Datum) 0);
}
}
@@ -4694,9 +4695,16 @@ initialize_acl(void)
static void
RoleMembershipCacheCallback(Datum arg, int cacheid, uint32 hashvalue)
{
+ if (cacheid == DATABASEOID &&
+ hashvalue != cached_db_hash &&
+ hashvalue != 0)
+ {
+ return; /* ignore pg_database changes for other DBs */
+ }
+
/* Force membership caches to be recomputed on next use */
- cached_privs_role = InvalidOid;
- cached_member_role = InvalidOid;
+ cached_role[ROLERECURSE_PRIVS] = InvalidOid;
+ cached_role[ROLERECURSE_MEMBERS] = InvalidOid;
}
@@ -4718,115 +4726,54 @@ has_rolinherit(Oid roleid)
/*
- * Get a list of roles that the specified roleid has the privileges of
+ * Get a list of roles that the specified roleid is a member of
*
- * This is defined not to recurse through roles that don't have rolinherit
- * set; for such roles, membership implies the ability to do SET ROLE, but
- * the privileges are not available until you've done so.
+ * Type ROLERECURSE_PRIVS recurses only through roles that have rolinherit
+ * set, while ROLERECURSE_MEMBERS recurses through all roles. This sets
+ * *is_admin==true if and only if role "roleid" has an ADMIN OPTION membership
+ * in role "admin_of".
*
* Since indirect membership testing is relatively expensive, we cache
* a list of memberships. Hence, the result is only guaranteed good until
- * the next call of roles_has_privs_of()!
+ * the next call of roles_is_member_of()!
*
* For the benefit of select_best_grantor, the result is defined to be
* in breadth-first order, ie, closer relationships earlier.
*/
static List *
-roles_has_privs_of(Oid roleid)
+roles_is_member_of(Oid roleid, enum RoleRecurseType type,
+ Oid admin_of, bool *is_admin)
{
+ Oid dba;
List *roles_list;
ListCell *l;
- List *new_cached_privs_roles;
+ List *new_cached_roles;
MemoryContext oldctx;
- /* If cache is already valid, just return the list */
- if (OidIsValid(cached_privs_role) && cached_privs_role == roleid)
- return cached_privs_roles;
+ /* If cache is valid and ADMIN OPTION not sought, just return the list */
+ if (cached_role[type] == roleid && !OidIsValid(admin_of) &&
+ OidIsValid(cached_role[type]))
+ return cached_roles[type];
/*
- * Find all the roles that roleid is a member of, including multi-level
- * recursion. The role itself will always be the first element of the
- * resulting list.
- *
- * Each element of the list is scanned to see if it adds any indirect
- * memberships. We can use a single list as both the record of
- * already-found memberships and the agenda of roles yet to be scanned.
- * This is a bit tricky but works because the foreach() macro doesn't
- * fetch the next list element until the bottom of the loop.
+ * Role expansion happens in a non-database backend when guc.c checks
+ * DEFAULT_ROLE_READ_ALL_SETTINGS for a physical walsender SHOW command.
+ * In that case, no role gets pg_database_owner.
*/
- roles_list = list_make1_oid(roleid);
-
- foreach(l, roles_list)
+ if (!OidIsValid(MyDatabaseId))
+ dba = InvalidOid;
+ else
{
- Oid memberid = lfirst_oid(l);
- CatCList *memlist;
- int i;
-
- /* Ignore non-inheriting roles */
- if (!has_rolinherit(memberid))
- continue;
+ HeapTuple dbtup;
- /* Find roles that memberid is directly a member of */
- memlist = SearchSysCacheList1(AUTHMEMMEMROLE,
- ObjectIdGetDatum(memberid));
- for (i = 0; i < memlist->n_members; i++)
- {
- HeapTuple tup = &memlist->members[i]->tuple;
- Oid otherid = ((Form_pg_auth_members) GETSTRUCT(tup))->roleid;
-
- /*
- * Even though there shouldn't be any loops in the membership
- * graph, we must test for having already seen this role. It is
- * legal for instance to have both A->B and A->C->B.
- */
- roles_list = list_append_unique_oid(roles_list, otherid);
- }
- ReleaseSysCacheList(memlist);
+ dbtup = SearchSysCache1(DATABASEOID, ObjectIdGetDatum(MyDatabaseId));
+ if (!HeapTupleIsValid(dbtup))
+ elog(ERROR, "cache lookup failed for database %u", MyDatabaseId);
+ dba = ((Form_pg_database) GETSTRUCT(dbtup))->datdba;
+ ReleaseSysCache(dbtup);
}
/*
- * Copy the completed list into TopMemoryContext so it will persist.
- */
- oldctx = MemoryContextSwitchTo(TopMemoryContext);
- new_cached_privs_roles = list_copy(roles_list);
- MemoryContextSwitchTo(oldctx);
- list_free(roles_list);
-
- /*
- * Now safe to assign to state variable
- */
- cached_privs_role = InvalidOid; /* just paranoia */
- list_free(cached_privs_roles);
- cached_privs_roles = new_cached_privs_roles;
- cached_privs_role = roleid;
-
- /* And now we can return the answer */
- return cached_privs_roles;
-}
-
-
-/*
- * Get a list of roles that the specified roleid is a member of
- *
- * This is defined to recurse through roles regardless of rolinherit.
- *
- * Since indirect membership testing is relatively expensive, we cache
- * a list of memberships. Hence, the result is only guaranteed good until
- * the next call of roles_is_member_of()!
- */
-static List *
-roles_is_member_of(Oid roleid)
-{
- List *roles_list;
- ListCell *l;
- List *new_cached_membership_roles;
- MemoryContext oldctx;
-
- /* If cache is already valid, just return the list */
- if (OidIsValid(cached_member_role) && cached_member_role == roleid)
- return cached_membership_roles;
-
- /*
* Find all the roles that roleid is a member of, including multi-level
* recursion. The role itself will always be the first element of the
* resulting list.
@@ -4845,6 +4792,9 @@ roles_is_member_of(Oid roleid)
CatCList *memlist;
int i;
+ if (type == ROLERECURSE_PRIVS && !has_rolinherit(memberid))
+ continue; /* ignore non-inheriting roles */
+
/* Find roles that memberid is directly a member of */
memlist = SearchSysCacheList1(AUTHMEMMEMROLE,
ObjectIdGetDatum(memberid));
@@ -4854,6 +4804,15 @@ roles_is_member_of(Oid roleid)
Oid otherid = ((Form_pg_auth_members) GETSTRUCT(tup))->roleid;
/*
+ * While otherid==InvalidOid shouldn't appear in the catalog, the
+ * OidIsValid() avoids crashing if that arises.
+ */
+ if (otherid == admin_of &&
+ ((Form_pg_auth_members) GETSTRUCT(tup))->admin_option &&
+ OidIsValid(admin_of))
+ *is_admin = true;
+
+ /*
* Even though there shouldn't be any loops in the membership
* graph, we must test for having already seen this role. It is
* legal for instance to have both A->B and A->C->B.
@@ -4861,26 +4820,31 @@ roles_is_member_of(Oid roleid)
roles_list = list_append_unique_oid(roles_list, otherid);
}
ReleaseSysCacheList(memlist);
+
+ /* implement pg_database_owner implicit membership */
+ if (memberid == dba && OidIsValid(dba))
+ roles_list = list_append_unique_oid(roles_list,
+ DEFAULT_ROLE_DATABASE_OWNER);
}
/*
* Copy the completed list into TopMemoryContext so it will persist.
*/
oldctx = MemoryContextSwitchTo(TopMemoryContext);
- new_cached_membership_roles = list_copy(roles_list);
+ new_cached_roles = list_copy(roles_list);
MemoryContextSwitchTo(oldctx);
list_free(roles_list);
/*
* Now safe to assign to state variable
*/
- cached_member_role = InvalidOid; /* just paranoia */
- list_free(cached_membership_roles);
- cached_membership_roles = new_cached_membership_roles;
- cached_member_role = roleid;
+ cached_role[type] = InvalidOid; /* just paranoia */
+ list_free(cached_roles[type]);
+ cached_roles[type] = new_cached_roles;
+ cached_role[type] = roleid;
/* And now we can return the answer */
- return cached_membership_roles;
+ return cached_roles[type];
}
@@ -4906,7 +4870,9 @@ has_privs_of_role(Oid member, Oid role)
* Find all the roles that member has the privileges of, including
* multi-level recursion, then see if target role is any one of them.
*/
- return list_member_oid(roles_has_privs_of(member), role);
+ return list_member_oid(roles_is_member_of(member, ROLERECURSE_PRIVS,
+ InvalidOid, NULL),
+ role);
}
@@ -4930,7 +4896,9 @@ is_member_of_role(Oid member, Oid role)
* Find all the roles that member is a member of, including multi-level
* recursion, then see if target role is any one of them.
*/
- return list_member_oid(roles_is_member_of(member), role);
+ return list_member_oid(roles_is_member_of(member, ROLERECURSE_MEMBERS,
+ InvalidOid, NULL),
+ role);
}
/*
@@ -4964,7 +4932,9 @@ is_member_of_role_nosuper(Oid member, Oid role)
* Find all the roles that member is a member of, including multi-level
* recursion, then see if target role is any one of them.
*/
- return list_member_oid(roles_is_member_of(member), role);
+ return list_member_oid(roles_is_member_of(member, ROLERECURSE_MEMBERS,
+ InvalidOid, NULL),
+ role);
}
@@ -4977,8 +4947,6 @@ bool
is_admin_of_role(Oid member, Oid role)
{
bool result = false;
- List *roles_list;
- ListCell *l;
if (superuser_arg(member))
return true;
@@ -5016,44 +4984,7 @@ is_admin_of_role(Oid member, Oid role)
return member == GetSessionUserId() &&
!InLocalUserIdChange() && !InSecurityRestrictedOperation();
- /*
- * Find all the roles that member is a member of, including multi-level
- * recursion. We build a list in the same way that is_member_of_role does
- * to track visited and unvisited roles.
- */
- roles_list = list_make1_oid(member);
-
- foreach(l, roles_list)
- {
- Oid memberid = lfirst_oid(l);
- CatCList *memlist;
- int i;
-
- /* Find roles that memberid is directly a member of */
- memlist = SearchSysCacheList1(AUTHMEMMEMROLE,
- ObjectIdGetDatum(memberid));
- for (i = 0; i < memlist->n_members; i++)
- {
- HeapTuple tup = &memlist->members[i]->tuple;
- Oid otherid = ((Form_pg_auth_members) GETSTRUCT(tup))->roleid;
-
- if (otherid == role &&
- ((Form_pg_auth_members) GETSTRUCT(tup))->admin_option)
- {
- /* Found what we came for, so can stop searching */
- result = true;
- break;
- }
-
- roles_list = list_append_unique_oid(roles_list, otherid);
- }
- ReleaseSysCacheList(memlist);
- if (result)
- break;
- }
-
- list_free(roles_list);
-
+ (void) roles_is_member_of(member, ROLERECURSE_MEMBERS, role, &result);
return result;
}
@@ -5128,7 +5059,8 @@ select_best_grantor(Oid roleId, AclMode privileges,
* roles_has_privs_of() throughout this loop, because aclmask_direct()
* doesn't query any role memberships.
*/
- roles_list = roles_has_privs_of(roleId);
+ roles_list = roles_is_member_of(roleId, ROLERECURSE_PRIVS,
+ InvalidOid, NULL);
/* initialize candidate result as default */
*grantorId = roleId;
diff --git a/src/backend/utils/cache/catcache.c b/src/backend/utils/cache/catcache.c
index fa2b49c..19926f9 100644
--- a/src/backend/utils/cache/catcache.c
+++ b/src/backend/utils/cache/catcache.c
@@ -1076,8 +1076,9 @@ InitCatCachePhase2(CatCache *cache, bool touch_index)
* criticalRelcachesBuilt), we don't have to worry anymore.
*
* Similarly, during backend startup we have to be able to use the
- * pg_authid and pg_auth_members syscaches for authentication even if
- * we don't yet have relcache entries for those catalogs' indexes.
+ * pg_authid, pg_auth_members and pg_database syscaches for
+ * authentication even if we don't yet have relcache entries for those
+ * catalogs' indexes.
*/
static bool
IndexScanOK(CatCache *cache, ScanKey cur_skey)
@@ -1110,6 +1111,7 @@ IndexScanOK(CatCache *cache, ScanKey cur_skey)
case AUTHNAME:
case AUTHOID:
case AUTHMEMMEMROLE:
+ case DATABASEOID:
/*
* Protect authentication lookups occurring before relcache has
diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c
index 62540a1..3a72fbb 100644
--- a/src/bin/initdb/initdb.c
+++ b/src/bin/initdb/initdb.c
@@ -1696,8 +1696,7 @@ setup_privileges(FILE *cmdfd)
CppAsString2(RELKIND_VIEW) ", " CppAsString2(RELKIND_MATVIEW) ", "
CppAsString2(RELKIND_SEQUENCE) ")"
" AND relacl IS NULL;\n\n",
- "GRANT USAGE ON SCHEMA pg_catalog TO PUBLIC;\n\n",
- "GRANT CREATE, USAGE ON SCHEMA public TO PUBLIC;\n\n",
+ "GRANT USAGE ON SCHEMA pg_catalog, public TO PUBLIC;\n\n",
"REVOKE ALL ON pg_largeobject FROM PUBLIC;\n\n",
"INSERT INTO pg_init_privs "
" (objoid, classoid, objsubid, initprivs, privtype)"
diff --git a/src/bin/pg_dump/dumputils.c b/src/bin/pg_dump/dumputils.c
index 60d306e..ea67e52 100644
--- a/src/bin/pg_dump/dumputils.c
+++ b/src/bin/pg_dump/dumputils.c
@@ -725,6 +725,7 @@ void
buildACLQueries(PQExpBuffer acl_subquery, PQExpBuffer racl_subquery,
PQExpBuffer init_acl_subquery, PQExpBuffer init_racl_subquery,
const char *acl_column, const char *acl_owner,
+ const char *initprivs_expr,
const char *obj_kind, bool binary_upgrade)
{
/*
@@ -765,23 +766,25 @@ buildACLQueries(PQExpBuffer acl_subquery, PQExpBuffer racl_subquery,
"WITH ORDINALITY AS perm(acl,row_n) "
"WHERE NOT EXISTS ( "
"SELECT 1 FROM "
- "pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault(%s,%s))) "
+ "pg_catalog.unnest(coalesce(%s,pg_catalog.acldefault(%s,%s))) "
"AS init(init_acl) WHERE acl = init_acl)) as foo)",
acl_column,
obj_kind,
acl_owner,
+ initprivs_expr,
obj_kind,
acl_owner);
printfPQExpBuffer(racl_subquery,
"(SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM "
"(SELECT acl, row_n FROM "
- "pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault(%s,%s))) "
+ "pg_catalog.unnest(coalesce(%s,pg_catalog.acldefault(%s,%s))) "
"WITH ORDINALITY AS initp(acl,row_n) "
"WHERE NOT EXISTS ( "
"SELECT 1 FROM "
"pg_catalog.unnest(coalesce(%s,pg_catalog.acldefault(%s,%s))) "
"AS permp(orig_acl) WHERE acl = orig_acl)) as foo)",
+ initprivs_expr,
obj_kind,
acl_owner,
acl_column,
@@ -807,12 +810,13 @@ buildACLQueries(PQExpBuffer acl_subquery, PQExpBuffer racl_subquery,
printfPQExpBuffer(init_acl_subquery,
"CASE WHEN privtype = 'e' THEN "
"(SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM "
- "(SELECT acl, row_n FROM pg_catalog.unnest(pip.initprivs) "
+ "(SELECT acl, row_n FROM pg_catalog.unnest(%s) "
"WITH ORDINALITY AS initp(acl,row_n) "
"WHERE NOT EXISTS ( "
"SELECT 1 FROM "
"pg_catalog.unnest(pg_catalog.acldefault(%s,%s)) "
"AS privm(orig_acl) WHERE acl = orig_acl)) as foo) END",
+ initprivs_expr,
obj_kind,
acl_owner);
@@ -823,10 +827,11 @@ buildACLQueries(PQExpBuffer acl_subquery, PQExpBuffer racl_subquery,
"pg_catalog.unnest(pg_catalog.acldefault(%s,%s)) "
"WITH ORDINALITY AS privp(acl,row_n) "
"WHERE NOT EXISTS ( "
- "SELECT 1 FROM pg_catalog.unnest(pip.initprivs) "
+ "SELECT 1 FROM pg_catalog.unnest(%s) "
"AS initp(init_acl) WHERE acl = init_acl)) as foo) END",
obj_kind,
- acl_owner);
+ acl_owner,
+ initprivs_expr);
}
else
{
diff --git a/src/bin/pg_dump/dumputils.h b/src/bin/pg_dump/dumputils.h
index 6e97da7..f5465f1 100644
--- a/src/bin/pg_dump/dumputils.h
+++ b/src/bin/pg_dump/dumputils.h
@@ -54,6 +54,7 @@ extern void emitShSecLabels(PGconn *conn, PGresult *res,
extern void buildACLQueries(PQExpBuffer acl_subquery, PQExpBuffer racl_subquery,
PQExpBuffer init_acl_subquery, PQExpBuffer init_racl_subquery,
const char *acl_column, const char *acl_owner,
+ const char *initprivs_expr,
const char *obj_kind, bool binary_upgrade);
extern bool variable_is_guc_list_quote(const char *name);
diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c
index 1f82c64..a16d149 100644
--- a/src/bin/pg_dump/pg_backup_archiver.c
+++ b/src/bin/pg_dump/pg_backup_archiver.c
@@ -3597,10 +3597,13 @@ _printTocEntry(ArchiveHandle *AH, TocEntry *te, bool isData)
* Actually print the definition.
*
* Really crude hack for suppressing AUTHORIZATION clause that old pg_dump
- * versions put into CREATE SCHEMA. We have to do this when --no-owner
- * mode is selected. This is ugly, but I see no other good way ...
+ * versions put into CREATE SCHEMA. Don't mutate the modern definition
+ * for schema "public", which consists of a comment. We have to do this
+ * when --no-owner mode is selected. This is ugly, but I see no other
+ * good way ...
*/
- if (ropt->noOwner && strcmp(te->desc, "SCHEMA") == 0)
+ if (ropt->noOwner &&
+ strcmp(te->desc, "SCHEMA") == 0 && strncmp(te->defn, "--", 2) != 0)
{
ahprintf(AH, "CREATE SCHEMA %s;\n\n\n", fmtId(te->tag));
}
@@ -3612,11 +3615,16 @@ _printTocEntry(ArchiveHandle *AH, TocEntry *te, bool isData)
/*
* If we aren't using SET SESSION AUTH to determine ownership, we must
- * instead issue an ALTER OWNER command. We assume that anything without
- * a DROP command is not a separately ownable object. All the categories
- * with DROP commands must appear in one list or the other.
+ * instead issue an ALTER OWNER command. Schema "public" is special; a
+ * dump never creates it, so we use ALTER OWNER even when using SET
+ * SESSION for all other objects. We assume that anything without a DROP
+ * command is not a separately ownable object. All the categories with
+ * DROP commands must appear in one list or the other.
*/
- if (!ropt->noOwner && !ropt->use_setsessauth &&
+ if (!ropt->noOwner &&
+ (!ropt->use_setsessauth ||
+ (strcmp(te->tag, "public") == 0
+ && strcmp(te->desc, "SCHEMA") == 0)) &&
te->owner && strlen(te->owner) > 0 &&
te->dropStmt && strlen(te->dropStmt) > 0)
{
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index d99b61e..d59e063 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -44,6 +44,7 @@
#include "catalog/pg_aggregate_d.h"
#include "catalog/pg_am_d.h"
#include "catalog/pg_attribute_d.h"
+#include "catalog/pg_authid_d.h"
#include "catalog/pg_cast_d.h"
#include "catalog/pg_class_d.h"
#include "catalog/pg_collation_d.h"
@@ -1564,13 +1565,15 @@ selectDumpableNamespace(NamespaceInfo *nsinfo, Archive *fout)
{
/*
* The public schema is a strange beast that sits in a sort of
- * no-mans-land between being a system object and a user object. We
- * don't want to dump creation or comment commands for it, because
- * that complicates matters for non-superuser use of pg_dump. But we
- * should dump any ACL changes that have occurred for it, and of
- * course we should dump contained objects.
+ * no-mans-land between being a system object and a user object.
+ * CREATE SCHEMA would fail, so its DUMP_COMPONENT_DEFINITION is just
+ * a comment and an indication of ownership. If the owner is the
+ * default, omit that superfluous DUMP_COMPONENT_DEFINITION. Before
+ * v14, the default owner was BOOTSTRAP_SUPERUSERID.
*/
- nsinfo->dobj.dump = DUMP_COMPONENT_ACL;
+ nsinfo->dobj.dump = DUMP_COMPONENT_ALL;
+ if (nsinfo->nspowner == DEFAULT_ROLE_DATABASE_OWNER)
+ nsinfo->dobj.dump &= ~DUMP_COMPONENT_DEFINITION;
nsinfo->dobj.dump_contains = DUMP_COMPONENT_ALL;
}
else
@@ -3352,8 +3355,8 @@ getBlobs(Archive *fout)
PQExpBuffer init_racl_subquery = createPQExpBuffer();
buildACLQueries(acl_subquery, racl_subquery, init_acl_subquery,
- init_racl_subquery, "l.lomacl", "l.lomowner", "'L'",
- dopt->binary_upgrade);
+ init_racl_subquery, "l.lomacl", "l.lomowner",
+ "pip.initprivs", "'L'", dopt->binary_upgrade);
appendPQExpBuffer(blobQry,
"SELECT l.oid, (%s l.lomowner) AS rolname, "
@@ -4754,6 +4757,7 @@ getNamespaces(Archive *fout, int *numNamespaces)
int i_tableoid;
int i_oid;
int i_nspname;
+ int i_nspowner;
int i_rolname;
int i_nspacl;
int i_rnspacl;
@@ -4773,11 +4777,32 @@ getNamespaces(Archive *fout, int *numNamespaces)
PQExpBuffer init_acl_subquery = createPQExpBuffer();
PQExpBuffer init_racl_subquery = createPQExpBuffer();
+ /*
+ * Bypass pg_init_privs.initprivs for the public schema, for several
+ * reasons. First, dropping and recreating the schema detaches it
+ * from its pg_init_privs row, but an empty destination database
+ * starts with this ACL nonetheless. Second, we support dump/reload
+ * of public schema ownership changes. ALTER SCHEMA OWNER filters
+ * nspacl through aclnewowner(), but initprivs continues to reflect
+ * the initial owner. Hence, synthesize the value that nspacl will
+ * have after the restore's ALTER SCHEMA OWNER. Third, this makes the
+ * destination database match the source's ACL, even if the latter was
+ * an initdb-default ACL, which changed in v14. An upgrade pulls in
+ * changes to most system object ACLs that the DBA had not customized.
+ * We've made the public schema depart from that, because changing its
+ * ACL so easily breaks applications.
+ */
buildACLQueries(acl_subquery, racl_subquery, init_acl_subquery,
- init_racl_subquery, "n.nspacl", "n.nspowner", "'n'",
- dopt->binary_upgrade);
+ init_racl_subquery, "n.nspacl", "n.nspowner",
+ "CASE WHEN n.nspname = 'public' THEN array["
+ " format('%s=UC/%s', "
+ " n.nspowner::regrole, n.nspowner::regrole),"
+ " format('=U/%s', n.nspowner::regrole)]::aclitem[] "
+ "ELSE pip.initprivs END",
+ "'n'", dopt->binary_upgrade);
appendPQExpBuffer(query, "SELECT n.tableoid, n.oid, n.nspname, "
+ "n.nspowner, "
"(%s nspowner) AS rolname, "
"%s as nspacl, "
"%s as rnspacl, "
@@ -4802,7 +4827,7 @@ getNamespaces(Archive *fout, int *numNamespaces)
destroyPQExpBuffer(init_racl_subquery);
}
else
- appendPQExpBuffer(query, "SELECT tableoid, oid, nspname, "
+ appendPQExpBuffer(query, "SELECT tableoid, oid, nspname, nspowner, "
"(%s nspowner) AS rolname, "
"nspacl, NULL as rnspacl, "
"NULL AS initnspacl, NULL as initrnspacl "
@@ -4818,6 +4843,7 @@ getNamespaces(Archive *fout, int *numNamespaces)
i_tableoid = PQfnumber(res, "tableoid");
i_oid = PQfnumber(res, "oid");
i_nspname = PQfnumber(res, "nspname");
+ i_nspowner = PQfnumber(res, "nspowner");
i_rolname = PQfnumber(res, "rolname");
i_nspacl = PQfnumber(res, "nspacl");
i_rnspacl = PQfnumber(res, "rnspacl");
@@ -4831,6 +4857,7 @@ getNamespaces(Archive *fout, int *numNamespaces)
nsinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
AssignDumpId(&nsinfo[i].dobj);
nsinfo[i].dobj.name = pg_strdup(PQgetvalue(res, i, i_nspname));
+ nsinfo[i].nspowner = atooid(PQgetvalue(res, i, i_nspowner));
nsinfo[i].rolname = pg_strdup(PQgetvalue(res, i, i_rolname));
nsinfo[i].nspacl = pg_strdup(PQgetvalue(res, i, i_nspacl));
nsinfo[i].rnspacl = pg_strdup(PQgetvalue(res, i, i_rnspacl));
@@ -5022,8 +5049,8 @@ getTypes(Archive *fout, int *numTypes)
PQExpBuffer initracl_subquery = createPQExpBuffer();
buildACLQueries(acl_subquery, racl_subquery, initacl_subquery,
- initracl_subquery, "t.typacl", "t.typowner", "'T'",
- dopt->binary_upgrade);
+ initracl_subquery, "t.typacl", "t.typowner",
+ "pip.initprivs", "'T'", dopt->binary_upgrade);
appendPQExpBuffer(query, "SELECT t.tableoid, t.oid, t.typname, "
"t.typnamespace, "
@@ -5724,8 +5751,8 @@ getAggregates(Archive *fout, int *numAggs)
const char *agg_check;
buildACLQueries(acl_subquery, racl_subquery, initacl_subquery,
- initracl_subquery, "p.proacl", "p.proowner", "'f'",
- dopt->binary_upgrade);
+ initracl_subquery, "p.proacl", "p.proowner",
+ "pip.initprivs", "'f'", dopt->binary_upgrade);
agg_check = (fout->remoteVersion >= 110000 ? "p.prokind = 'a'"
: "p.proisagg");
@@ -5937,8 +5964,8 @@ getFuncs(Archive *fout, int *numFuncs)
const char *not_agg_check;
buildACLQueries(acl_subquery, racl_subquery, initacl_subquery,
- initracl_subquery, "p.proacl", "p.proowner", "'f'",
- dopt->binary_upgrade);
+ initracl_subquery, "p.proacl", "p.proowner",
+ "pip.initprivs", "'f'", dopt->binary_upgrade);
not_agg_check = (fout->remoteVersion >= 110000 ? "p.prokind <> 'a'"
: "NOT p.proisagg");
@@ -6234,13 +6261,14 @@ getTables(Archive *fout, int *numTables)
buildACLQueries(acl_subquery, racl_subquery, initacl_subquery,
initracl_subquery, "c.relacl", "c.relowner",
+ "pip.initprivs",
"CASE WHEN c.relkind = " CppAsString2(RELKIND_SEQUENCE)
" THEN 's' ELSE 'r' END::\"char\"",
dopt->binary_upgrade);
buildACLQueries(attacl_subquery, attracl_subquery, attinitacl_subquery,
- attinitracl_subquery, "at.attacl", "c.relowner", "'c'",
- dopt->binary_upgrade);
+ attinitracl_subquery, "at.attacl", "c.relowner",
+ "pip.initprivs", "'c'", dopt->binary_upgrade);
appendPQExpBuffer(query,
"SELECT c.tableoid, c.oid, c.relname, "
@@ -8238,8 +8266,8 @@ getProcLangs(Archive *fout, int *numProcLangs)
PQExpBuffer initracl_subquery = createPQExpBuffer();
buildACLQueries(acl_subquery, racl_subquery, initacl_subquery,
- initracl_subquery, "l.lanacl", "l.lanowner", "'l'",
- dopt->binary_upgrade);
+ initracl_subquery, "l.lanacl", "l.lanowner",
+ "pip.initprivs", "'l'", dopt->binary_upgrade);
/* pg_language has a laninline column */
appendPQExpBuffer(query, "SELECT l.tableoid, l.oid, "
@@ -9420,8 +9448,8 @@ getForeignDataWrappers(Archive *fout, int *numForeignDataWrappers)
PQExpBuffer initracl_subquery = createPQExpBuffer();
buildACLQueries(acl_subquery, racl_subquery, initacl_subquery,
- initracl_subquery, "f.fdwacl", "f.fdwowner", "'F'",
- dopt->binary_upgrade);
+ initracl_subquery, "f.fdwacl", "f.fdwowner",
+ "pip.initprivs", "'F'", dopt->binary_upgrade);
appendPQExpBuffer(query, "SELECT f.tableoid, f.oid, f.fdwname, "
"(%s f.fdwowner) AS rolname, "
@@ -9587,8 +9615,8 @@ getForeignServers(Archive *fout, int *numForeignServers)
PQExpBuffer initracl_subquery = createPQExpBuffer();
buildACLQueries(acl_subquery, racl_subquery, initacl_subquery,
- initracl_subquery, "f.srvacl", "f.srvowner", "'S'",
- dopt->binary_upgrade);
+ initracl_subquery, "f.srvacl", "f.srvowner",
+ "pip.initprivs", "'S'", dopt->binary_upgrade);
appendPQExpBuffer(query, "SELECT f.tableoid, f.oid, f.srvname, "
"(%s f.srvowner) AS rolname, "
@@ -9734,6 +9762,7 @@ getDefaultACLs(Archive *fout, int *numDefaultACLs)
buildACLQueries(acl_subquery, racl_subquery, initacl_subquery,
initracl_subquery, "defaclacl", "defaclrole",
+ "pip.initprivs",
"CASE WHEN defaclobjtype = 'S' THEN 's' ELSE defaclobjtype END::\"char\"",
dopt->binary_upgrade);
@@ -9888,6 +9917,28 @@ dumpComment(Archive *fout, const char *type, const char *name,
ncomments--;
}
+ /*
+ * Skip dumping the initdb-provided public schema comment, which would
+ * complicate matters for non-superuser use of pg_dump. When the DBA has
+ * removed initdb's comment, replicate that.
+ */
+ if (strcmp(type, "SCHEMA") == 0 && strcmp(name, "public") == 0)
+ {
+ static CommentItem empty_comment = {.descr = ""};
+
+ if (ncomments == 0)
+ {
+ comments = &empty_comment;
+ ncomments = 1;
+ }
+ else if (strcmp(comments->descr, (fout->remoteVersion >= 80300 ?
+ "standard public schema" :
+ "Standard public schema")) == 0)
+ {
+ ncomments = 0;
+ }
+ }
+
/* If a comment exists, build COMMENT ON statement */
if (ncomments > 0)
{
@@ -10351,9 +10402,19 @@ dumpNamespace(Archive *fout, NamespaceInfo *nspinfo)
qnspname = pg_strdup(fmtId(nspinfo->dobj.name));
- appendPQExpBuffer(delq, "DROP SCHEMA %s;\n", qnspname);
-
- appendPQExpBuffer(q, "CREATE SCHEMA %s;\n", qnspname);
+ if (strcmp(nspinfo->dobj.name, "public") == 0)
+ {
+ /* see selectDumpableNamespace() */
+ appendPQExpBufferStr(delq,
+ "-- *not* dropping schema, since initdb creates it\n");
+ appendPQExpBufferStr(q,
+ "-- *not* creating schema, since initdb creates it\n");
+ }
+ else
+ {
+ appendPQExpBuffer(delq, "DROP SCHEMA %s;\n", qnspname);
+ appendPQExpBuffer(q, "CREATE SCHEMA %s;\n", qnspname);
+ }
if (dopt->binary_upgrade)
binary_upgrade_extension_member(q, &nspinfo->dobj,
@@ -15501,8 +15562,8 @@ dumpTable(Archive *fout, TableInfo *tbinfo)
PQExpBuffer initracl_subquery = createPQExpBuffer();
buildACLQueries(acl_subquery, racl_subquery, initacl_subquery,
- initracl_subquery, "at.attacl", "c.relowner", "'c'",
- dopt->binary_upgrade);
+ initracl_subquery, "at.attacl", "c.relowner",
+ "pip.initprivs", "'c'", dopt->binary_upgrade);
appendPQExpBuffer(query,
"SELECT at.attname, "
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index 1290f96..51f5c03 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -142,6 +142,7 @@ typedef struct _dumpableObject
typedef struct _namespaceInfo
{
DumpableObject dobj;
+ Oid nspowner;
char *rolname; /* name of owner, or empty string */
char *nspacl;
char *rnspacl;
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index 737e464..02b4418 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -125,6 +125,14 @@ my %pgdump_runs = (
'regress_pg_dump_test',
],
},
+ defaults_public_owner => {
+ database => 'regress_public_owner',
+ dump_cmd => [
+ 'pg_dump', '--no-sync', '-f',
+ "$tempdir/defaults_public_owner.sql",
+ 'regress_public_owner',
+ ],
+ },
# Do not use --no-sync to give test coverage for data sync.
defaults_custom_format => {
@@ -605,6 +613,26 @@ my %tests = (
unlike => { no_owner => 1, },
},
+ 'ALTER SCHEMA public OWNER TO' => {
+ create_order => 15,
+ create_sql =>
+ 'ALTER SCHEMA public OWNER TO "regress_quoted \"" role";',
+ regexp => qr/^ALTER SCHEMA public OWNER TO .+;/m,
+ like => {
+ %full_runs, section_pre_data => 1,
+ },
+ unlike => { no_owner => 1, },
+ },
+
+ 'ALTER SCHEMA public OWNER TO (w/o ACL changes)' => {
+ database => 'regress_public_owner',
+ create_order => 100,
+ create_sql =>
+ 'ALTER SCHEMA public OWNER TO "regress_quoted \"" role";',
+ regexp => qr/^(GRANT|REVOKE)/m,
+ unlike => { defaults_public_owner => 1 },
+ },
+
'ALTER SEQUENCE test_table_col1_seq' => {
regexp => qr/^
\QALTER SEQUENCE dump_test.test_table_col1_seq OWNED BY dump_test.test_table.col1;\E
@@ -940,6 +968,23 @@ my %tests = (
like => {},
},
+ 'COMMENT ON SCHEMA public' => {
+ regexp => qr/^COMMENT ON SCHEMA public IS .+;/m,
+ # regress_public_owner emits this, due to create_sql of next test
+ like => {
+ pg_dumpall_dbprivs => 1,
+ pg_dumpall_exclude => 1,
+ },
+ },
+
+ 'COMMENT ON SCHEMA public IS NULL' => {
+ database => 'regress_public_owner',
+ create_order => 100,
+ create_sql => 'COMMENT ON SCHEMA public IS NULL;',
+ regexp => qr/^COMMENT ON SCHEMA public IS '';/m,
+ like => { defaults_public_owner => 1 },
+ },
+
'COMMENT ON TABLE dump_test.test_table' => {
create_order => 36,
create_sql => 'COMMENT ON TABLE dump_test.test_table
@@ -1370,6 +1415,18 @@ my %tests = (
},
},
+ 'CREATE ROLE regress_quoted...' => {
+ create_order => 1,
+ create_sql => 'CREATE ROLE "regress_quoted \"" role";',
+ regexp => qr/^\QCREATE ROLE "regress_quoted \"" role";\E/m,
+ like => {
+ pg_dumpall_dbprivs => 1,
+ pg_dumpall_exclude => 1,
+ pg_dumpall_globals => 1,
+ pg_dumpall_globals_clean => 1,
+ },
+ },
+
'CREATE ACCESS METHOD gist2' => {
create_order => 52,
create_sql =>
@@ -3261,6 +3318,23 @@ my %tests = (
unlike => { no_privs => 1, },
},
+ # With the exception of the public schema, we don't dump ownership changes
+ # for objects originating at initdb. Hence, any GRANT or REVOKE affecting
+ # owner privileges for those objects should reference the bootstrap
+ # superuser, not the dump-time owner.
+ 'REVOKE EXECUTE ON FUNCTION pg_stat_reset FROM regress_dump_test_role' =>
+ {
+ create_order => 15,
+ create_sql => '
+ ALTER FUNCTION pg_stat_reset OWNER TO regress_dump_test_role;
+ REVOKE EXECUTE ON FUNCTION pg_stat_reset
+ FROM regress_dump_test_role;',
+ regexp => qr/^[^-].*pg_stat_reset.* regress_dump_test_role/m,
+
+ # this shouldn't ever get emitted
+ like => {},
+ },
+
'REVOKE SELECT ON TABLE pg_proc FROM public' => {
create_order => 45,
create_sql => 'REVOKE SELECT ON TABLE pg_proc FROM public;',
@@ -3270,12 +3344,12 @@ my %tests = (
unlike => { no_privs => 1, },
},
- 'REVOKE CREATE ON SCHEMA public FROM public' => {
+ 'REVOKE ALL ON SCHEMA public' => {
create_order => 16,
- create_sql => 'REVOKE CREATE ON SCHEMA public FROM public;',
- regexp => qr/^
- \QREVOKE ALL ON SCHEMA public FROM PUBLIC;\E
- \n\QGRANT USAGE ON SCHEMA public TO PUBLIC;\E
+ create_sql =>
+ 'REVOKE ALL ON SCHEMA public FROM "regress_quoted \"" role";',
+ regexp => qr/^
+ \QREVOKE ALL ON SCHEMA public FROM "regress_quoted \"" role";\E
/xm,
like => { %full_runs, section_pre_data => 1, },
unlike => { no_privs => 1, },
@@ -3376,8 +3450,9 @@ if ($collation_check_stderr !~ /ERROR: /)
$collation_support = 1;
}
-# Create a second database for certain tests to work against
+# Create additional databases for mutations of schema public
$node->psql('postgres', 'create database regress_pg_dump_test;');
+$node->psql('postgres', 'create database regress_public_owner;');
# Start with number of command_fails_like()*2 tests below (each
# command_fails_like is actually 2 tests)
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 20af5a9..c8e64b1 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3508,6 +3508,7 @@ describeRoles(const char *pattern, bool verbose, bool showSystem)
printTableAddHeader(&cont, gettext_noop("Role name"), true, align);
printTableAddHeader(&cont, gettext_noop("Attributes"), true, align);
+ /* ignores implicit memberships from superuser & pg_database_owner */
printTableAddHeader(&cont, gettext_noop("Member of"), true, align);
if (verbose && pset.sversion >= 80200)
diff --git a/src/include/catalog/pg_authid.dat b/src/include/catalog/pg_authid.dat
index 87d917f..4c2bf97 100644
--- a/src/include/catalog/pg_authid.dat
+++ b/src/include/catalog/pg_authid.dat
@@ -24,6 +24,11 @@
rolcreaterole => 't', rolcreatedb => 't', rolcanlogin => 't',
rolreplication => 't', rolbypassrls => 't', rolconnlimit => '-1',
rolpassword => '_null_', rolvaliduntil => '_null_' },
+{ oid => '8778', oid_symbol => 'DEFAULT_ROLE_DATABASE_OWNER',
+ rolname => 'pg_database_owner', rolsuper => 'f', rolinherit => 't',
+ rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
+ rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
+ rolpassword => '_null_', rolvaliduntil => '_null_' },
{ oid => '3373', oid_symbol => 'DEFAULT_ROLE_MONITOR',
rolname => 'pg_monitor', rolsuper => 'f', rolinherit => 't',
rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
diff --git a/src/include/catalog/pg_namespace.dat b/src/include/catalog/pg_namespace.dat
index 2ed136b..7932aa6 100644
--- a/src/include/catalog/pg_namespace.dat
+++ b/src/include/catalog/pg_namespace.dat
@@ -18,8 +18,9 @@
{ oid => '99', oid_symbol => 'PG_TOAST_NAMESPACE',
descr => 'reserved schema for TOAST tables',
nspname => 'pg_toast', nspacl => '_null_' },
+# update dumpComment() if changing this descr
{ oid => '2200', oid_symbol => 'PG_PUBLIC_NAMESPACE',
descr => 'standard public schema',
- nspname => 'public', nspacl => '_null_' },
+ nspname => 'public', nspowner => 'pg_database_owner', nspacl => '_null_' },
]
diff --git a/src/pl/plperl/expected/plperl_setup.out b/src/pl/plperl/expected/plperl_setup.out
index a1a24df..5234feb 100644
--- a/src/pl/plperl/expected/plperl_setup.out
+++ b/src/pl/plperl/expected/plperl_setup.out
@@ -25,6 +25,9 @@ CREATE EXTENSION plperl;
CREATE EXTENSION plperlu; -- fail
ERROR: permission denied to create extension "plperlu"
HINT: Must be superuser to create this extension.
+CREATE SCHEMA plperl_setup_scratch;
+SET search_path = plperl_setup_scratch;
+GRANT ALL ON SCHEMA plperl_setup_scratch TO regress_user2;
CREATE FUNCTION foo1() returns int language plperl as '1;';
SELECT foo1();
foo1
@@ -34,6 +37,7 @@ SELECT foo1();
-- Must reconnect to avoid failure with non-MULTIPLICITY Perl interpreters
\c -
+SET search_path = plperl_setup_scratch;
SET ROLE regress_user1;
-- Should be able to change privileges on the language
revoke all on language plperl from public;
diff --git a/src/pl/plperl/sql/plperl_setup.sql b/src/pl/plperl/sql/plperl_setup.sql
index 7484478..a89cf56 100644
--- a/src/pl/plperl/sql/plperl_setup.sql
+++ b/src/pl/plperl/sql/plperl_setup.sql
@@ -27,12 +27,16 @@ SET ROLE regress_user1;
CREATE EXTENSION plperl;
CREATE EXTENSION plperlu; -- fail
+CREATE SCHEMA plperl_setup_scratch;
+SET search_path = plperl_setup_scratch;
+GRANT ALL ON SCHEMA plperl_setup_scratch TO regress_user2;
CREATE FUNCTION foo1() returns int language plperl as '1;';
SELECT foo1();
-- Must reconnect to avoid failure with non-MULTIPLICITY Perl interpreters
\c -
+SET search_path = plperl_setup_scratch;
SET ROLE regress_user1;
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index 46a69fc..bad4eb0 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -1719,6 +1719,67 @@ SELECT * FROM pg_largeobject LIMIT 0;
SET SESSION AUTHORIZATION regress_priv_user1;
SELECT * FROM pg_largeobject LIMIT 0; -- to be denied
ERROR: permission denied for table pg_largeobject
+-- test pg_database_owner
+RESET SESSION AUTHORIZATION;
+GRANT pg_database_owner TO regress_priv_user1;
+ERROR: role "pg_database_owner" cannot have explicit members
+GRANT regress_priv_user1 TO pg_database_owner;
+ERROR: role "pg_database_owner" cannot be a member of any role
+CREATE TABLE datdba_only ();
+ALTER TABLE datdba_only OWNER TO pg_database_owner;
+REVOKE DELETE ON datdba_only FROM pg_database_owner;
+SELECT
+ pg_has_role('regress_priv_user1', 'pg_database_owner', 'USAGE') as priv,
+ pg_has_role('regress_priv_user1', 'pg_database_owner', 'MEMBER') as mem,
+ pg_has_role('regress_priv_user1', 'pg_database_owner',
+ 'MEMBER WITH ADMIN OPTION') as admin;
+ priv | mem | admin
+------+-----+-------
+ f | f | f
+(1 row)
+
+BEGIN;
+DO $$BEGIN EXECUTE format(
+ 'ALTER DATABASE %I OWNER TO regress_priv_group2', current_catalog); END$$;
+SELECT
+ pg_has_role('regress_priv_user1', 'pg_database_owner', 'USAGE') as priv,
+ pg_has_role('regress_priv_user1', 'pg_database_owner', 'MEMBER') as mem,
+ pg_has_role('regress_priv_user1', 'pg_database_owner',
+ 'MEMBER WITH ADMIN OPTION') as admin;
+ priv | mem | admin
+------+-----+-------
+ t | t | f
+(1 row)
+
+SET SESSION AUTHORIZATION regress_priv_user1;
+TABLE information_schema.enabled_roles;
+ role_name
+---------------------
+ pg_database_owner
+ regress_priv_user1
+ regress_priv_group2
+(3 rows)
+
+TABLE information_schema.applicable_roles;
+ grantee | role_name | is_grantable
+---------------------+---------------------+--------------
+ regress_priv_group2 | pg_database_owner | NO
+ regress_priv_user1 | regress_priv_group2 | NO
+(2 rows)
+
+INSERT INTO datdba_only DEFAULT VALUES;
+SAVEPOINT q; DELETE FROM datdba_only; ROLLBACK TO q;
+ERROR: permission denied for table datdba_only
+SET SESSION AUTHORIZATION regress_priv_user2;
+TABLE information_schema.enabled_roles;
+ role_name
+--------------------
+ regress_priv_user2
+(1 row)
+
+INSERT INTO datdba_only DEFAULT VALUES;
+ERROR: permission denied for table datdba_only
+ROLLBACK;
-- test default ACLs
\c -
CREATE SCHEMA testns;
diff --git a/src/test/regress/input/tablespace.source b/src/test/regress/input/tablespace.source
index c133e73..cb9774e 100644
--- a/src/test/regress/input/tablespace.source
+++ b/src/test/regress/input/tablespace.source
@@ -388,7 +388,7 @@ CREATE INDEX k ON testschema.tablespace_acl (c) TABLESPACE regress_tblspace;
ALTER TABLE testschema.tablespace_acl OWNER TO regress_tablespace_user2;
SET SESSION ROLE regress_tablespace_user2;
-CREATE TABLE tablespace_table (i int) TABLESPACE regress_tblspace; -- fail
+CREATE TEMP TABLE tablespace_table (i int) TABLESPACE regress_tblspace; -- fail
ALTER TABLE testschema.tablespace_acl ALTER c TYPE bigint;
REINDEX (TABLESPACE regress_tblspace) TABLE tablespace_table; -- fail
REINDEX (TABLESPACE regress_tblspace, CONCURRENTLY) TABLE tablespace_table; -- fail
@@ -409,3 +409,6 @@ DROP SCHEMA testschema CASCADE;
DROP ROLE regress_tablespace_user1;
DROP ROLE regress_tablespace_user2;
+
+-- Rest of this suite can use the public schema freely.
+GRANT ALL ON SCHEMA public TO public;
diff --git a/src/test/regress/output/tablespace.source b/src/test/regress/output/tablespace.source
index 1bbe7e0..e7629d4 100644
--- a/src/test/regress/output/tablespace.source
+++ b/src/test/regress/output/tablespace.source
@@ -908,7 +908,7 @@ CREATE TABLE testschema.tablespace_acl (c int);
CREATE INDEX k ON testschema.tablespace_acl (c) TABLESPACE regress_tblspace;
ALTER TABLE testschema.tablespace_acl OWNER TO regress_tablespace_user2;
SET SESSION ROLE regress_tablespace_user2;
-CREATE TABLE tablespace_table (i int) TABLESPACE regress_tblspace; -- fail
+CREATE TEMP TABLE tablespace_table (i int) TABLESPACE regress_tblspace; -- fail
ERROR: permission denied for tablespace regress_tblspace
ALTER TABLE testschema.tablespace_acl ALTER c TYPE bigint;
REINDEX (TABLESPACE regress_tblspace) TABLE tablespace_table; -- fail
@@ -934,3 +934,5 @@ drop cascades to table testschema.atable
drop cascades to table testschema.tablespace_acl
DROP ROLE regress_tablespace_user1;
DROP ROLE regress_tablespace_user2;
+-- Rest of this suite can use the public schema freely.
+GRANT ALL ON SCHEMA public TO public;
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index 6277140..b45719c 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -1034,6 +1034,37 @@ SELECT * FROM pg_largeobject LIMIT 0;
SET SESSION AUTHORIZATION regress_priv_user1;
SELECT * FROM pg_largeobject LIMIT 0; -- to be denied
+-- test pg_database_owner
+RESET SESSION AUTHORIZATION;
+GRANT pg_database_owner TO regress_priv_user1;
+GRANT regress_priv_user1 TO pg_database_owner;
+CREATE TABLE datdba_only ();
+ALTER TABLE datdba_only OWNER TO pg_database_owner;
+REVOKE DELETE ON datdba_only FROM pg_database_owner;
+SELECT
+ pg_has_role('regress_priv_user1', 'pg_database_owner', 'USAGE') as priv,
+ pg_has_role('regress_priv_user1', 'pg_database_owner', 'MEMBER') as mem,
+ pg_has_role('regress_priv_user1', 'pg_database_owner',
+ 'MEMBER WITH ADMIN OPTION') as admin;
+
+BEGIN;
+DO $$BEGIN EXECUTE format(
+ 'ALTER DATABASE %I OWNER TO regress_priv_group2', current_catalog); END$$;
+SELECT
+ pg_has_role('regress_priv_user1', 'pg_database_owner', 'USAGE') as priv,
+ pg_has_role('regress_priv_user1', 'pg_database_owner', 'MEMBER') as mem,
+ pg_has_role('regress_priv_user1', 'pg_database_owner',
+ 'MEMBER WITH ADMIN OPTION') as admin;
+SET SESSION AUTHORIZATION regress_priv_user1;
+TABLE information_schema.enabled_roles;
+TABLE information_schema.applicable_roles;
+INSERT INTO datdba_only DEFAULT VALUES;
+SAVEPOINT q; DELETE FROM datdba_only; ROLLBACK TO q;
+SET SESSION AUTHORIZATION regress_priv_user2;
+TABLE information_schema.enabled_roles;
+INSERT INTO datdba_only DEFAULT VALUES;
+ROLLBACK;
+
-- test default ACLs
\c -
On Sat, Feb 13, 2021 at 04:56:29AM -0800, Noah Misch wrote:
I'm attaching the patch for $SUBJECT, which applies atop the four patches from
the two other threads below. For convenience of testing, I've included a
rollup patch, equivalent to applying all five patches.
I committed prerequisites from one thread, so here's a rebased rollup patch.
Attachments:
public-default-acl-v1-rollup2.patchtext/plain; charset=us-asciiDownload
Author: Noah Misch <noah@leadboat.com>
Commit: Noah Misch <noah@leadboat.com>
Rollup of three patches, for easy testing.
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 0649b6b..1bcc29b 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -8925,7 +8925,7 @@ $d$;
-- But creation of user mappings for non-superusers should fail
CREATE USER MAPPING FOR public SERVER loopback_nopw;
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback_nopw;
-CREATE FOREIGN TABLE ft1_nopw (
+CREATE FOREIGN TABLE pg_temp.ft1_nopw (
c1 int NOT NULL,
c2 int NOT NULL,
c3 text,
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 2b525ea..803e288 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -2595,7 +2595,7 @@ $d$;
CREATE USER MAPPING FOR public SERVER loopback_nopw;
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback_nopw;
-CREATE FOREIGN TABLE ft1_nopw (
+CREATE FOREIGN TABLE pg_temp.ft1_nopw (
c1 int NOT NULL,
c2 int NOT NULL,
c3 text,
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index f073fba..550b3d0 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -2976,20 +2976,18 @@ SELECT 3 OPERATOR(pg_catalog.+) 4;
<para>
By default, users cannot access any objects in schemas they do not
own. To allow that, the owner of the schema must grant the
- <literal>USAGE</literal> privilege on the schema. To allow users
- to make use of the objects in the schema, additional privileges
- might need to be granted, as appropriate for the object.
+ <literal>USAGE</literal> privilege on the schema. By default, everyone
+ has that privilege on the schema <literal>public</literal>. To allow
+ users to make use of the objects in a schema, additional privileges might
+ need to be granted, as appropriate for the object.
</para>
<para>
- A user can also be allowed to create objects in someone else's
- schema. To allow that, the <literal>CREATE</literal> privilege on
- the schema needs to be granted. Note that by default, everyone
- has <literal>CREATE</literal> and <literal>USAGE</literal> privileges on
- the schema
- <literal>public</literal>. This allows all users that are able to
- connect to a given database to create objects in its
- <literal>public</literal> schema.
+ A user can also be allowed to create objects in someone else's schema. To
+ allow that, the <literal>CREATE</literal> privilege on the schema needs to
+ be granted. In databases upgraded from
+ <productname>PostgreSQL</productname> 13 or earlier, everyone has that
+ privilege on the schema <literal>public</literal>.
Some <link linkend="ddl-schemas-patterns">usage patterns</link> call for
revoking that privilege:
<programlisting>
@@ -3062,20 +3060,25 @@ REVOKE CREATE ON SCHEMA public FROM PUBLIC;
database owner attack. -->
<para>
Constrain ordinary users to user-private schemas. To implement this,
- issue <literal>REVOKE CREATE ON SCHEMA public FROM PUBLIC</literal>,
- and create a schema for each user with the same name as that user.
- Recall that the default search path starts
- with <literal>$user</literal>, which resolves to the user name.
- Therefore, if each user has a separate schema, they access their own
- schemas by default. After adopting this pattern in a database where
- untrusted users had already logged in, consider auditing the public
- schema for objects named like objects in
+ first issue <literal>REVOKE CREATE ON SCHEMA public FROM
+ PUBLIC</literal>. Then, for every user needing to create non-temporary
+ objects, create a schema with the same name as that user. Recall that
+ the default search path starts with <literal>$user</literal>, which
+ resolves to the user name. Therefore, if each user has a separate
+ schema, they access their own schemas by default. After adopting this
+ pattern in a database where untrusted users had already logged in,
+ consider auditing the public schema for objects named like objects in
schema <literal>pg_catalog</literal>. This pattern is a secure schema
usage pattern unless an untrusted user is the database owner or holds
the <literal>CREATEROLE</literal> privilege, in which case no secure
schema usage pattern exists.
</para>
<para>
+ If the database originated in an upgrade
+ from <productname>PostgreSQL</productname> 13 or earlier,
+ the <literal>REVOKE</literal> is essential. Otherwise, the default
+ configuration follows this pattern; ordinary users can create only
+ temporary objects until a privileged user furnishes a schema.
</para>
</listitem>
@@ -3084,10 +3087,10 @@ REVOKE CREATE ON SCHEMA public FROM PUBLIC;
Remove the public schema from the default search path, by modifying
<link linkend="config-setting-configuration-file"><filename>postgresql.conf</filename></link>
or by issuing <literal>ALTER ROLE ALL SET search_path =
- "$user"</literal>. Everyone retains the ability to create objects in
- the public schema, but only qualified names will choose those objects.
- While qualified table references are fine, calls to functions in the
- public schema <link linkend="typeconv-func">will be unsafe or
+ "$user"</literal>. Then, grant privileges to create in the public
+ schema. Only qualified names will choose public schema objects. While
+ qualified table references are fine, calls to functions in the public
+ schema <link linkend="typeconv-func">will be unsafe or
unreliable</link>. If you create functions or extensions in the public
schema, use the first pattern instead. Otherwise, like the first
pattern, this is secure unless an untrusted user is the database owner
@@ -3097,11 +3100,14 @@ REVOKE CREATE ON SCHEMA public FROM PUBLIC;
<listitem>
<para>
- Keep the default. All users access the public schema implicitly. This
+ Keep the default search path, and grant privileges to create in the
+ public schema. All users access the public schema implicitly. This
simulates the situation where schemas are not available at all, giving
a smooth transition from the non-schema-aware world. However, this is
never a secure pattern. It is acceptable only when the database has a
- single user or a few mutually-trusting users.
+ single user or a few mutually-trusting users. In databases upgraded
+ from <productname>PostgreSQL</productname> 13 or earlier, this is the
+ default.
</para>
</listitem>
</itemizedlist>
diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml
index 6920f2d..9dc8ab2 100644
--- a/doc/src/sgml/user-manag.sgml
+++ b/doc/src/sgml/user-manag.sgml
@@ -578,13 +578,14 @@ DROP ROLE doomed_role;
<para>
The <literal>pg_database_owner</literal> role has one implicit,
- situation-dependent member, namely the owner of the current database. The
- role conveys no rights at first. Like any role, it can own objects or
- receive grants of access privileges. Consequently, once
- <literal>pg_database_owner</literal> has rights within a template database,
- each owner of a database instantiated from that template will exercise those
- rights. <literal>pg_database_owner</literal> cannot be a member of any
- role, and it cannot have non-implicit members.
+ situation-dependent member, namely the owner of the current database. Like
+ any role, it can own objects or receive grants of access privileges.
+ Consequently, once <literal>pg_database_owner</literal> has rights within a
+ template database, each owner of a database instantiated from that template
+ will exercise those rights. <literal>pg_database_owner</literal> cannot be
+ a member of any role, and it cannot have non-implicit members. Initially,
+ this role owns the <literal>public</literal> schema, so each database owner
+ governs local use of the schema.
</para>
<para>
@@ -633,8 +634,8 @@ GRANT pg_signal_backend TO admin_user;
horse</quote> others with relative ease. The strongest protection is tight
control over who can define objects. Where that is infeasible, write
queries referring only to objects having trusted owners. Remove
- from <varname>search_path</varname> the public schema and any other schemas
- that permit untrusted users to create objects.
+ from <varname>search_path</varname> any schemas that permit untrusted users
+ to create objects.
</para>
<para>
diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c
index 3c1cf78..738b2cf 100644
--- a/src/bin/initdb/initdb.c
+++ b/src/bin/initdb/initdb.c
@@ -1702,8 +1702,7 @@ setup_privileges(FILE *cmdfd)
CppAsString2(RELKIND_VIEW) ", " CppAsString2(RELKIND_MATVIEW) ", "
CppAsString2(RELKIND_SEQUENCE) ")"
" AND relacl IS NULL;\n\n",
- "GRANT USAGE ON SCHEMA pg_catalog TO PUBLIC;\n\n",
- "GRANT CREATE, USAGE ON SCHEMA public TO PUBLIC;\n\n",
+ "GRANT USAGE ON SCHEMA pg_catalog, public TO PUBLIC;\n\n",
"REVOKE ALL ON pg_largeobject FROM PUBLIC;\n\n",
"INSERT INTO pg_init_privs "
" (objoid, classoid, objsubid, initprivs, privtype)"
diff --git a/src/bin/pg_dump/dumputils.c b/src/bin/pg_dump/dumputils.c
index 60d306e..ea67e52 100644
--- a/src/bin/pg_dump/dumputils.c
+++ b/src/bin/pg_dump/dumputils.c
@@ -725,6 +725,7 @@ void
buildACLQueries(PQExpBuffer acl_subquery, PQExpBuffer racl_subquery,
PQExpBuffer init_acl_subquery, PQExpBuffer init_racl_subquery,
const char *acl_column, const char *acl_owner,
+ const char *initprivs_expr,
const char *obj_kind, bool binary_upgrade)
{
/*
@@ -765,23 +766,25 @@ buildACLQueries(PQExpBuffer acl_subquery, PQExpBuffer racl_subquery,
"WITH ORDINALITY AS perm(acl,row_n) "
"WHERE NOT EXISTS ( "
"SELECT 1 FROM "
- "pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault(%s,%s))) "
+ "pg_catalog.unnest(coalesce(%s,pg_catalog.acldefault(%s,%s))) "
"AS init(init_acl) WHERE acl = init_acl)) as foo)",
acl_column,
obj_kind,
acl_owner,
+ initprivs_expr,
obj_kind,
acl_owner);
printfPQExpBuffer(racl_subquery,
"(SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM "
"(SELECT acl, row_n FROM "
- "pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault(%s,%s))) "
+ "pg_catalog.unnest(coalesce(%s,pg_catalog.acldefault(%s,%s))) "
"WITH ORDINALITY AS initp(acl,row_n) "
"WHERE NOT EXISTS ( "
"SELECT 1 FROM "
"pg_catalog.unnest(coalesce(%s,pg_catalog.acldefault(%s,%s))) "
"AS permp(orig_acl) WHERE acl = orig_acl)) as foo)",
+ initprivs_expr,
obj_kind,
acl_owner,
acl_column,
@@ -807,12 +810,13 @@ buildACLQueries(PQExpBuffer acl_subquery, PQExpBuffer racl_subquery,
printfPQExpBuffer(init_acl_subquery,
"CASE WHEN privtype = 'e' THEN "
"(SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM "
- "(SELECT acl, row_n FROM pg_catalog.unnest(pip.initprivs) "
+ "(SELECT acl, row_n FROM pg_catalog.unnest(%s) "
"WITH ORDINALITY AS initp(acl,row_n) "
"WHERE NOT EXISTS ( "
"SELECT 1 FROM "
"pg_catalog.unnest(pg_catalog.acldefault(%s,%s)) "
"AS privm(orig_acl) WHERE acl = orig_acl)) as foo) END",
+ initprivs_expr,
obj_kind,
acl_owner);
@@ -823,10 +827,11 @@ buildACLQueries(PQExpBuffer acl_subquery, PQExpBuffer racl_subquery,
"pg_catalog.unnest(pg_catalog.acldefault(%s,%s)) "
"WITH ORDINALITY AS privp(acl,row_n) "
"WHERE NOT EXISTS ( "
- "SELECT 1 FROM pg_catalog.unnest(pip.initprivs) "
+ "SELECT 1 FROM pg_catalog.unnest(%s) "
"AS initp(init_acl) WHERE acl = init_acl)) as foo) END",
obj_kind,
- acl_owner);
+ acl_owner,
+ initprivs_expr);
}
else
{
diff --git a/src/bin/pg_dump/dumputils.h b/src/bin/pg_dump/dumputils.h
index 6e97da7..f5465f1 100644
--- a/src/bin/pg_dump/dumputils.h
+++ b/src/bin/pg_dump/dumputils.h
@@ -54,6 +54,7 @@ extern void emitShSecLabels(PGconn *conn, PGresult *res,
extern void buildACLQueries(PQExpBuffer acl_subquery, PQExpBuffer racl_subquery,
PQExpBuffer init_acl_subquery, PQExpBuffer init_racl_subquery,
const char *acl_column, const char *acl_owner,
+ const char *initprivs_expr,
const char *obj_kind, bool binary_upgrade);
extern bool variable_is_guc_list_quote(const char *name);
diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c
index 0ac8b18..1d00fdc 100644
--- a/src/bin/pg_dump/pg_backup_archiver.c
+++ b/src/bin/pg_dump/pg_backup_archiver.c
@@ -3629,10 +3629,13 @@ _printTocEntry(ArchiveHandle *AH, TocEntry *te, bool isData)
* Actually print the definition.
*
* Really crude hack for suppressing AUTHORIZATION clause that old pg_dump
- * versions put into CREATE SCHEMA. We have to do this when --no-owner
- * mode is selected. This is ugly, but I see no other good way ...
+ * versions put into CREATE SCHEMA. Don't mutate the modern definition
+ * for schema "public", which consists of a comment. We have to do this
+ * when --no-owner mode is selected. This is ugly, but I see no other
+ * good way ...
*/
- if (ropt->noOwner && strcmp(te->desc, "SCHEMA") == 0)
+ if (ropt->noOwner &&
+ strcmp(te->desc, "SCHEMA") == 0 && strncmp(te->defn, "--", 2) != 0)
{
ahprintf(AH, "CREATE SCHEMA %s;\n\n\n", fmtId(te->tag));
}
@@ -3644,11 +3647,16 @@ _printTocEntry(ArchiveHandle *AH, TocEntry *te, bool isData)
/*
* If we aren't using SET SESSION AUTH to determine ownership, we must
- * instead issue an ALTER OWNER command. We assume that anything without
- * a DROP command is not a separately ownable object. All the categories
- * with DROP commands must appear in one list or the other.
+ * instead issue an ALTER OWNER command. Schema "public" is special; a
+ * dump never creates it, so we use ALTER OWNER even when using SET
+ * SESSION for all other objects. We assume that anything without a DROP
+ * command is not a separately ownable object. All the categories with
+ * DROP commands must appear in one list or the other.
*/
- if (!ropt->noOwner && !ropt->use_setsessauth &&
+ if (!ropt->noOwner &&
+ (!ropt->use_setsessauth ||
+ (strcmp(te->tag, "public") == 0
+ && strcmp(te->desc, "SCHEMA") == 0)) &&
te->owner && strlen(te->owner) > 0 &&
te->dropStmt && strlen(te->dropStmt) > 0)
{
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index da6cc05..c4f3bd2 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -44,6 +44,7 @@
#include "catalog/pg_aggregate_d.h"
#include "catalog/pg_am_d.h"
#include "catalog/pg_attribute_d.h"
+#include "catalog/pg_authid_d.h"
#include "catalog/pg_cast_d.h"
#include "catalog/pg_class_d.h"
#include "catalog/pg_collation_d.h"
@@ -1571,13 +1572,15 @@ selectDumpableNamespace(NamespaceInfo *nsinfo, Archive *fout)
{
/*
* The public schema is a strange beast that sits in a sort of
- * no-mans-land between being a system object and a user object. We
- * don't want to dump creation or comment commands for it, because
- * that complicates matters for non-superuser use of pg_dump. But we
- * should dump any ACL changes that have occurred for it, and of
- * course we should dump contained objects.
+ * no-mans-land between being a system object and a user object.
+ * CREATE SCHEMA would fail, so its DUMP_COMPONENT_DEFINITION is just
+ * a comment and an indication of ownership. If the owner is the
+ * default, omit that superfluous DUMP_COMPONENT_DEFINITION. Before
+ * v14, the default owner was BOOTSTRAP_SUPERUSERID.
*/
- nsinfo->dobj.dump = DUMP_COMPONENT_ACL;
+ nsinfo->dobj.dump = DUMP_COMPONENT_ALL;
+ if (nsinfo->nspowner == DEFAULT_ROLE_DATABASE_OWNER)
+ nsinfo->dobj.dump &= ~DUMP_COMPONENT_DEFINITION;
nsinfo->dobj.dump_contains = DUMP_COMPONENT_ALL;
}
else
@@ -3402,8 +3405,8 @@ getBlobs(Archive *fout)
PQExpBuffer init_racl_subquery = createPQExpBuffer();
buildACLQueries(acl_subquery, racl_subquery, init_acl_subquery,
- init_racl_subquery, "l.lomacl", "l.lomowner", "'L'",
- dopt->binary_upgrade);
+ init_racl_subquery, "l.lomacl", "l.lomowner",
+ "pip.initprivs", "'L'", dopt->binary_upgrade);
appendPQExpBuffer(blobQry,
"SELECT l.oid, (%s l.lomowner) AS rolname, "
@@ -4804,6 +4807,7 @@ getNamespaces(Archive *fout, int *numNamespaces)
int i_tableoid;
int i_oid;
int i_nspname;
+ int i_nspowner;
int i_rolname;
int i_nspacl;
int i_rnspacl;
@@ -4823,11 +4827,32 @@ getNamespaces(Archive *fout, int *numNamespaces)
PQExpBuffer init_acl_subquery = createPQExpBuffer();
PQExpBuffer init_racl_subquery = createPQExpBuffer();
+ /*
+ * Bypass pg_init_privs.initprivs for the public schema, for several
+ * reasons. First, dropping and recreating the schema detaches it
+ * from its pg_init_privs row, but an empty destination database
+ * starts with this ACL nonetheless. Second, we support dump/reload
+ * of public schema ownership changes. ALTER SCHEMA OWNER filters
+ * nspacl through aclnewowner(), but initprivs continues to reflect
+ * the initial owner. Hence, synthesize the value that nspacl will
+ * have after the restore's ALTER SCHEMA OWNER. Third, this makes the
+ * destination database match the source's ACL, even if the latter was
+ * an initdb-default ACL, which changed in v14. An upgrade pulls in
+ * changes to most system object ACLs that the DBA had not customized.
+ * We've made the public schema depart from that, because changing its
+ * ACL so easily breaks applications.
+ */
buildACLQueries(acl_subquery, racl_subquery, init_acl_subquery,
- init_racl_subquery, "n.nspacl", "n.nspowner", "'n'",
- dopt->binary_upgrade);
+ init_racl_subquery, "n.nspacl", "n.nspowner",
+ "CASE WHEN n.nspname = 'public' THEN array["
+ " format('%s=UC/%s', "
+ " n.nspowner::regrole, n.nspowner::regrole),"
+ " format('=U/%s', n.nspowner::regrole)]::aclitem[] "
+ "ELSE pip.initprivs END",
+ "'n'", dopt->binary_upgrade);
appendPQExpBuffer(query, "SELECT n.tableoid, n.oid, n.nspname, "
+ "n.nspowner, "
"(%s nspowner) AS rolname, "
"%s as nspacl, "
"%s as rnspacl, "
@@ -4852,7 +4877,7 @@ getNamespaces(Archive *fout, int *numNamespaces)
destroyPQExpBuffer(init_racl_subquery);
}
else
- appendPQExpBuffer(query, "SELECT tableoid, oid, nspname, "
+ appendPQExpBuffer(query, "SELECT tableoid, oid, nspname, nspowner, "
"(%s nspowner) AS rolname, "
"nspacl, NULL as rnspacl, "
"NULL AS initnspacl, NULL as initrnspacl "
@@ -4868,6 +4893,7 @@ getNamespaces(Archive *fout, int *numNamespaces)
i_tableoid = PQfnumber(res, "tableoid");
i_oid = PQfnumber(res, "oid");
i_nspname = PQfnumber(res, "nspname");
+ i_nspowner = PQfnumber(res, "nspowner");
i_rolname = PQfnumber(res, "rolname");
i_nspacl = PQfnumber(res, "nspacl");
i_rnspacl = PQfnumber(res, "rnspacl");
@@ -4881,6 +4907,7 @@ getNamespaces(Archive *fout, int *numNamespaces)
nsinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
AssignDumpId(&nsinfo[i].dobj);
nsinfo[i].dobj.name = pg_strdup(PQgetvalue(res, i, i_nspname));
+ nsinfo[i].nspowner = atooid(PQgetvalue(res, i, i_nspowner));
nsinfo[i].rolname = pg_strdup(PQgetvalue(res, i, i_rolname));
nsinfo[i].nspacl = pg_strdup(PQgetvalue(res, i, i_nspacl));
nsinfo[i].rnspacl = pg_strdup(PQgetvalue(res, i, i_rnspacl));
@@ -5072,8 +5099,8 @@ getTypes(Archive *fout, int *numTypes)
PQExpBuffer initracl_subquery = createPQExpBuffer();
buildACLQueries(acl_subquery, racl_subquery, initacl_subquery,
- initracl_subquery, "t.typacl", "t.typowner", "'T'",
- dopt->binary_upgrade);
+ initracl_subquery, "t.typacl", "t.typowner",
+ "pip.initprivs", "'T'", dopt->binary_upgrade);
appendPQExpBuffer(query, "SELECT t.tableoid, t.oid, t.typname, "
"t.typnamespace, "
@@ -5774,8 +5801,8 @@ getAggregates(Archive *fout, int *numAggs)
const char *agg_check;
buildACLQueries(acl_subquery, racl_subquery, initacl_subquery,
- initracl_subquery, "p.proacl", "p.proowner", "'f'",
- dopt->binary_upgrade);
+ initracl_subquery, "p.proacl", "p.proowner",
+ "pip.initprivs", "'f'", dopt->binary_upgrade);
agg_check = (fout->remoteVersion >= 110000 ? "p.prokind = 'a'"
: "p.proisagg");
@@ -5987,8 +6014,8 @@ getFuncs(Archive *fout, int *numFuncs)
const char *not_agg_check;
buildACLQueries(acl_subquery, racl_subquery, initacl_subquery,
- initracl_subquery, "p.proacl", "p.proowner", "'f'",
- dopt->binary_upgrade);
+ initracl_subquery, "p.proacl", "p.proowner",
+ "pip.initprivs", "'f'", dopt->binary_upgrade);
not_agg_check = (fout->remoteVersion >= 110000 ? "p.prokind <> 'a'"
: "NOT p.proisagg");
@@ -6284,13 +6311,14 @@ getTables(Archive *fout, int *numTables)
buildACLQueries(acl_subquery, racl_subquery, initacl_subquery,
initracl_subquery, "c.relacl", "c.relowner",
+ "pip.initprivs",
"CASE WHEN c.relkind = " CppAsString2(RELKIND_SEQUENCE)
" THEN 's' ELSE 'r' END::\"char\"",
dopt->binary_upgrade);
buildACLQueries(attacl_subquery, attracl_subquery, attinitacl_subquery,
- attinitracl_subquery, "at.attacl", "c.relowner", "'c'",
- dopt->binary_upgrade);
+ attinitracl_subquery, "at.attacl", "c.relowner",
+ "pip.initprivs", "'c'", dopt->binary_upgrade);
appendPQExpBuffer(query,
"SELECT c.tableoid, c.oid, c.relname, "
@@ -8288,8 +8316,8 @@ getProcLangs(Archive *fout, int *numProcLangs)
PQExpBuffer initracl_subquery = createPQExpBuffer();
buildACLQueries(acl_subquery, racl_subquery, initacl_subquery,
- initracl_subquery, "l.lanacl", "l.lanowner", "'l'",
- dopt->binary_upgrade);
+ initracl_subquery, "l.lanacl", "l.lanowner",
+ "pip.initprivs", "'l'", dopt->binary_upgrade);
/* pg_language has a laninline column */
appendPQExpBuffer(query, "SELECT l.tableoid, l.oid, "
@@ -9479,8 +9507,8 @@ getForeignDataWrappers(Archive *fout, int *numForeignDataWrappers)
PQExpBuffer initracl_subquery = createPQExpBuffer();
buildACLQueries(acl_subquery, racl_subquery, initacl_subquery,
- initracl_subquery, "f.fdwacl", "f.fdwowner", "'F'",
- dopt->binary_upgrade);
+ initracl_subquery, "f.fdwacl", "f.fdwowner",
+ "pip.initprivs", "'F'", dopt->binary_upgrade);
appendPQExpBuffer(query, "SELECT f.tableoid, f.oid, f.fdwname, "
"(%s f.fdwowner) AS rolname, "
@@ -9646,8 +9674,8 @@ getForeignServers(Archive *fout, int *numForeignServers)
PQExpBuffer initracl_subquery = createPQExpBuffer();
buildACLQueries(acl_subquery, racl_subquery, initacl_subquery,
- initracl_subquery, "f.srvacl", "f.srvowner", "'S'",
- dopt->binary_upgrade);
+ initracl_subquery, "f.srvacl", "f.srvowner",
+ "pip.initprivs", "'S'", dopt->binary_upgrade);
appendPQExpBuffer(query, "SELECT f.tableoid, f.oid, f.srvname, "
"(%s f.srvowner) AS rolname, "
@@ -9793,6 +9821,7 @@ getDefaultACLs(Archive *fout, int *numDefaultACLs)
buildACLQueries(acl_subquery, racl_subquery, initacl_subquery,
initracl_subquery, "defaclacl", "defaclrole",
+ "pip.initprivs",
"CASE WHEN defaclobjtype = 'S' THEN 's' ELSE defaclobjtype END::\"char\"",
dopt->binary_upgrade);
@@ -9947,6 +9976,28 @@ dumpComment(Archive *fout, const char *type, const char *name,
ncomments--;
}
+ /*
+ * Skip dumping the initdb-provided public schema comment, which would
+ * complicate matters for non-superuser use of pg_dump. When the DBA has
+ * removed initdb's comment, replicate that.
+ */
+ if (strcmp(type, "SCHEMA") == 0 && strcmp(name, "public") == 0)
+ {
+ static CommentItem empty_comment = {.descr = ""};
+
+ if (ncomments == 0)
+ {
+ comments = &empty_comment;
+ ncomments = 1;
+ }
+ else if (strcmp(comments->descr, (fout->remoteVersion >= 80300 ?
+ "standard public schema" :
+ "Standard public schema")) == 0)
+ {
+ ncomments = 0;
+ }
+ }
+
/* If a comment exists, build COMMENT ON statement */
if (ncomments > 0)
{
@@ -10410,9 +10461,19 @@ dumpNamespace(Archive *fout, const NamespaceInfo *nspinfo)
qnspname = pg_strdup(fmtId(nspinfo->dobj.name));
- appendPQExpBuffer(delq, "DROP SCHEMA %s;\n", qnspname);
-
- appendPQExpBuffer(q, "CREATE SCHEMA %s;\n", qnspname);
+ if (strcmp(nspinfo->dobj.name, "public") == 0)
+ {
+ /* see selectDumpableNamespace() */
+ appendPQExpBufferStr(delq,
+ "-- *not* dropping schema, since initdb creates it\n");
+ appendPQExpBufferStr(q,
+ "-- *not* creating schema, since initdb creates it\n");
+ }
+ else
+ {
+ appendPQExpBuffer(delq, "DROP SCHEMA %s;\n", qnspname);
+ appendPQExpBuffer(q, "CREATE SCHEMA %s;\n", qnspname);
+ }
if (dopt->binary_upgrade)
binary_upgrade_extension_member(q, &nspinfo->dobj,
@@ -15560,8 +15621,8 @@ dumpTable(Archive *fout, const TableInfo *tbinfo)
PQExpBuffer initracl_subquery = createPQExpBuffer();
buildACLQueries(acl_subquery, racl_subquery, initacl_subquery,
- initracl_subquery, "at.attacl", "c.relowner", "'c'",
- dopt->binary_upgrade);
+ initracl_subquery, "at.attacl", "c.relowner",
+ "pip.initprivs", "'c'", dopt->binary_upgrade);
appendPQExpBuffer(query,
"SELECT at.attname, "
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index 5340843..e4256fa 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -142,6 +142,7 @@ typedef struct _dumpableObject
typedef struct _namespaceInfo
{
DumpableObject dobj;
+ Oid nspowner;
char *rolname; /* name of owner, or empty string */
char *nspacl;
char *rnspacl;
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index 737e464..02b4418 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -125,6 +125,14 @@ my %pgdump_runs = (
'regress_pg_dump_test',
],
},
+ defaults_public_owner => {
+ database => 'regress_public_owner',
+ dump_cmd => [
+ 'pg_dump', '--no-sync', '-f',
+ "$tempdir/defaults_public_owner.sql",
+ 'regress_public_owner',
+ ],
+ },
# Do not use --no-sync to give test coverage for data sync.
defaults_custom_format => {
@@ -605,6 +613,26 @@ my %tests = (
unlike => { no_owner => 1, },
},
+ 'ALTER SCHEMA public OWNER TO' => {
+ create_order => 15,
+ create_sql =>
+ 'ALTER SCHEMA public OWNER TO "regress_quoted \"" role";',
+ regexp => qr/^ALTER SCHEMA public OWNER TO .+;/m,
+ like => {
+ %full_runs, section_pre_data => 1,
+ },
+ unlike => { no_owner => 1, },
+ },
+
+ 'ALTER SCHEMA public OWNER TO (w/o ACL changes)' => {
+ database => 'regress_public_owner',
+ create_order => 100,
+ create_sql =>
+ 'ALTER SCHEMA public OWNER TO "regress_quoted \"" role";',
+ regexp => qr/^(GRANT|REVOKE)/m,
+ unlike => { defaults_public_owner => 1 },
+ },
+
'ALTER SEQUENCE test_table_col1_seq' => {
regexp => qr/^
\QALTER SEQUENCE dump_test.test_table_col1_seq OWNED BY dump_test.test_table.col1;\E
@@ -940,6 +968,23 @@ my %tests = (
like => {},
},
+ 'COMMENT ON SCHEMA public' => {
+ regexp => qr/^COMMENT ON SCHEMA public IS .+;/m,
+ # regress_public_owner emits this, due to create_sql of next test
+ like => {
+ pg_dumpall_dbprivs => 1,
+ pg_dumpall_exclude => 1,
+ },
+ },
+
+ 'COMMENT ON SCHEMA public IS NULL' => {
+ database => 'regress_public_owner',
+ create_order => 100,
+ create_sql => 'COMMENT ON SCHEMA public IS NULL;',
+ regexp => qr/^COMMENT ON SCHEMA public IS '';/m,
+ like => { defaults_public_owner => 1 },
+ },
+
'COMMENT ON TABLE dump_test.test_table' => {
create_order => 36,
create_sql => 'COMMENT ON TABLE dump_test.test_table
@@ -1370,6 +1415,18 @@ my %tests = (
},
},
+ 'CREATE ROLE regress_quoted...' => {
+ create_order => 1,
+ create_sql => 'CREATE ROLE "regress_quoted \"" role";',
+ regexp => qr/^\QCREATE ROLE "regress_quoted \"" role";\E/m,
+ like => {
+ pg_dumpall_dbprivs => 1,
+ pg_dumpall_exclude => 1,
+ pg_dumpall_globals => 1,
+ pg_dumpall_globals_clean => 1,
+ },
+ },
+
'CREATE ACCESS METHOD gist2' => {
create_order => 52,
create_sql =>
@@ -3261,6 +3318,23 @@ my %tests = (
unlike => { no_privs => 1, },
},
+ # With the exception of the public schema, we don't dump ownership changes
+ # for objects originating at initdb. Hence, any GRANT or REVOKE affecting
+ # owner privileges for those objects should reference the bootstrap
+ # superuser, not the dump-time owner.
+ 'REVOKE EXECUTE ON FUNCTION pg_stat_reset FROM regress_dump_test_role' =>
+ {
+ create_order => 15,
+ create_sql => '
+ ALTER FUNCTION pg_stat_reset OWNER TO regress_dump_test_role;
+ REVOKE EXECUTE ON FUNCTION pg_stat_reset
+ FROM regress_dump_test_role;',
+ regexp => qr/^[^-].*pg_stat_reset.* regress_dump_test_role/m,
+
+ # this shouldn't ever get emitted
+ like => {},
+ },
+
'REVOKE SELECT ON TABLE pg_proc FROM public' => {
create_order => 45,
create_sql => 'REVOKE SELECT ON TABLE pg_proc FROM public;',
@@ -3270,12 +3344,12 @@ my %tests = (
unlike => { no_privs => 1, },
},
- 'REVOKE CREATE ON SCHEMA public FROM public' => {
+ 'REVOKE ALL ON SCHEMA public' => {
create_order => 16,
- create_sql => 'REVOKE CREATE ON SCHEMA public FROM public;',
- regexp => qr/^
- \QREVOKE ALL ON SCHEMA public FROM PUBLIC;\E
- \n\QGRANT USAGE ON SCHEMA public TO PUBLIC;\E
+ create_sql =>
+ 'REVOKE ALL ON SCHEMA public FROM "regress_quoted \"" role";',
+ regexp => qr/^
+ \QREVOKE ALL ON SCHEMA public FROM "regress_quoted \"" role";\E
/xm,
like => { %full_runs, section_pre_data => 1, },
unlike => { no_privs => 1, },
@@ -3376,8 +3450,9 @@ if ($collation_check_stderr !~ /ERROR: /)
$collation_support = 1;
}
-# Create a second database for certain tests to work against
+# Create additional databases for mutations of schema public
$node->psql('postgres', 'create database regress_pg_dump_test;');
+$node->psql('postgres', 'create database regress_public_owner;');
# Start with number of command_fails_like()*2 tests below (each
# command_fails_like is actually 2 tests)
diff --git a/src/include/catalog/pg_namespace.dat b/src/include/catalog/pg_namespace.dat
index 2ed136b..7932aa6 100644
--- a/src/include/catalog/pg_namespace.dat
+++ b/src/include/catalog/pg_namespace.dat
@@ -18,8 +18,9 @@
{ oid => '99', oid_symbol => 'PG_TOAST_NAMESPACE',
descr => 'reserved schema for TOAST tables',
nspname => 'pg_toast', nspacl => '_null_' },
+# update dumpComment() if changing this descr
{ oid => '2200', oid_symbol => 'PG_PUBLIC_NAMESPACE',
descr => 'standard public schema',
- nspname => 'public', nspacl => '_null_' },
+ nspname => 'public', nspowner => 'pg_database_owner', nspacl => '_null_' },
]
diff --git a/src/pl/plperl/expected/plperl_setup.out b/src/pl/plperl/expected/plperl_setup.out
index a1a24df..5234feb 100644
--- a/src/pl/plperl/expected/plperl_setup.out
+++ b/src/pl/plperl/expected/plperl_setup.out
@@ -25,6 +25,9 @@ CREATE EXTENSION plperl;
CREATE EXTENSION plperlu; -- fail
ERROR: permission denied to create extension "plperlu"
HINT: Must be superuser to create this extension.
+CREATE SCHEMA plperl_setup_scratch;
+SET search_path = plperl_setup_scratch;
+GRANT ALL ON SCHEMA plperl_setup_scratch TO regress_user2;
CREATE FUNCTION foo1() returns int language plperl as '1;';
SELECT foo1();
foo1
@@ -34,6 +37,7 @@ SELECT foo1();
-- Must reconnect to avoid failure with non-MULTIPLICITY Perl interpreters
\c -
+SET search_path = plperl_setup_scratch;
SET ROLE regress_user1;
-- Should be able to change privileges on the language
revoke all on language plperl from public;
diff --git a/src/pl/plperl/sql/plperl_setup.sql b/src/pl/plperl/sql/plperl_setup.sql
index 7484478..a89cf56 100644
--- a/src/pl/plperl/sql/plperl_setup.sql
+++ b/src/pl/plperl/sql/plperl_setup.sql
@@ -27,12 +27,16 @@ SET ROLE regress_user1;
CREATE EXTENSION plperl;
CREATE EXTENSION plperlu; -- fail
+CREATE SCHEMA plperl_setup_scratch;
+SET search_path = plperl_setup_scratch;
+GRANT ALL ON SCHEMA plperl_setup_scratch TO regress_user2;
CREATE FUNCTION foo1() returns int language plperl as '1;';
SELECT foo1();
-- Must reconnect to avoid failure with non-MULTIPLICITY Perl interpreters
\c -
+SET search_path = plperl_setup_scratch;
SET ROLE regress_user1;
diff --git a/src/test/regress/input/tablespace.source b/src/test/regress/input/tablespace.source
index c133e73..cb9774e 100644
--- a/src/test/regress/input/tablespace.source
+++ b/src/test/regress/input/tablespace.source
@@ -388,7 +388,7 @@ CREATE INDEX k ON testschema.tablespace_acl (c) TABLESPACE regress_tblspace;
ALTER TABLE testschema.tablespace_acl OWNER TO regress_tablespace_user2;
SET SESSION ROLE regress_tablespace_user2;
-CREATE TABLE tablespace_table (i int) TABLESPACE regress_tblspace; -- fail
+CREATE TEMP TABLE tablespace_table (i int) TABLESPACE regress_tblspace; -- fail
ALTER TABLE testschema.tablespace_acl ALTER c TYPE bigint;
REINDEX (TABLESPACE regress_tblspace) TABLE tablespace_table; -- fail
REINDEX (TABLESPACE regress_tblspace, CONCURRENTLY) TABLE tablespace_table; -- fail
@@ -409,3 +409,6 @@ DROP SCHEMA testschema CASCADE;
DROP ROLE regress_tablespace_user1;
DROP ROLE regress_tablespace_user2;
+
+-- Rest of this suite can use the public schema freely.
+GRANT ALL ON SCHEMA public TO public;
diff --git a/src/test/regress/output/tablespace.source b/src/test/regress/output/tablespace.source
index 1bbe7e0..e7629d4 100644
--- a/src/test/regress/output/tablespace.source
+++ b/src/test/regress/output/tablespace.source
@@ -908,7 +908,7 @@ CREATE TABLE testschema.tablespace_acl (c int);
CREATE INDEX k ON testschema.tablespace_acl (c) TABLESPACE regress_tblspace;
ALTER TABLE testschema.tablespace_acl OWNER TO regress_tablespace_user2;
SET SESSION ROLE regress_tablespace_user2;
-CREATE TABLE tablespace_table (i int) TABLESPACE regress_tblspace; -- fail
+CREATE TEMP TABLE tablespace_table (i int) TABLESPACE regress_tblspace; -- fail
ERROR: permission denied for tablespace regress_tblspace
ALTER TABLE testschema.tablespace_acl ALTER c TYPE bigint;
REINDEX (TABLESPACE regress_tblspace) TABLE tablespace_table; -- fail
@@ -934,3 +934,5 @@ drop cascades to table testschema.atable
drop cascades to table testschema.tablespace_acl
DROP ROLE regress_tablespace_user1;
DROP ROLE regress_tablespace_user2;
+-- Rest of this suite can use the public schema freely.
+GRANT ALL ON SCHEMA public TO public;
On Sat, 2021-03-27 at 00:50 -0700, Noah Misch wrote:
On Sat, Feb 13, 2021 at 04:56:29AM -0800, Noah Misch wrote:
I'm attaching the patch for $SUBJECT, which applies atop the four patches from
the two other threads below. For convenience of testing, I've included a
rollup patch, equivalent to applying all five patches.I committed prerequisites from one thread, so here's a rebased rollup patch.
I am happy to see this problem tackled!
Yours,
Laurenz Albe
On Sat, Mar 27, 2021 at 11:41:07AM +0100, Laurenz Albe wrote:
On Sat, 2021-03-27 at 00:50 -0700, Noah Misch wrote:
On Sat, Feb 13, 2021 at 04:56:29AM -0800, Noah Misch wrote:
I'm attaching the patch for $SUBJECT, which applies atop the four patches from
the two other threads below. For convenience of testing, I've included a
rollup patch, equivalent to applying all five patches.I committed prerequisites from one thread, so here's a rebased rollup patch.
I am happy to see this problem tackled!
Rebased. I've pushed all prerequisites, so there's no longer a distinct
rollup patch.
Attachments:
public-default-acl-v2.patchtext/plain; charset=us-asciiDownload
Author: Noah Misch <noah@leadboat.com>
Commit: Noah Misch <noah@leadboat.com>
Revoke PUBLIC CREATE from public schema, now owned by pg_database_owner.
This switches the default ACL to what the documentation has recommended
since CVE-2018-1058. Upgrades will carry forward any old ownership and
ACL. Sites that declined the 2018 recommendation should take a fresh
look. Recipes for commissioning a new database cluster from scratch may
need to create a schema, grant more privileges, etc. Out-of-tree test
suites may require such updates.
Reviewed by FIXME.
Discussion: https://postgr.es/m/20201031163518.GB4039133@rfd.leadboat.com
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 31b5de9..3d77cea 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -9215,7 +9215,7 @@ $d$;
-- But creation of user mappings for non-superusers should fail
CREATE USER MAPPING FOR public SERVER loopback_nopw;
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback_nopw;
-CREATE FOREIGN TABLE ft1_nopw (
+CREATE FOREIGN TABLE pg_temp.ft1_nopw (
c1 int NOT NULL,
c2 int NOT NULL,
c3 text,
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 286dd99..36db65f 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -2735,7 +2735,7 @@ $d$;
CREATE USER MAPPING FOR public SERVER loopback_nopw;
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback_nopw;
-CREATE FOREIGN TABLE ft1_nopw (
+CREATE FOREIGN TABLE pg_temp.ft1_nopw (
c1 int NOT NULL,
c2 int NOT NULL,
c3 text,
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 4986548..e84c41a 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3001,20 +3001,18 @@ SELECT 3 OPERATOR(pg_catalog.+) 4;
<para>
By default, users cannot access any objects in schemas they do not
own. To allow that, the owner of the schema must grant the
- <literal>USAGE</literal> privilege on the schema. To allow users
- to make use of the objects in the schema, additional privileges
- might need to be granted, as appropriate for the object.
+ <literal>USAGE</literal> privilege on the schema. By default, everyone
+ has that privilege on the schema <literal>public</literal>. To allow
+ users to make use of the objects in a schema, additional privileges might
+ need to be granted, as appropriate for the object.
</para>
<para>
- A user can also be allowed to create objects in someone else's
- schema. To allow that, the <literal>CREATE</literal> privilege on
- the schema needs to be granted. Note that by default, everyone
- has <literal>CREATE</literal> and <literal>USAGE</literal> privileges on
- the schema
- <literal>public</literal>. This allows all users that are able to
- connect to a given database to create objects in its
- <literal>public</literal> schema.
+ A user can also be allowed to create objects in someone else's schema. To
+ allow that, the <literal>CREATE</literal> privilege on the schema needs to
+ be granted. In databases upgraded from
+ <productname>PostgreSQL</productname> 13 or earlier, everyone has that
+ privilege on the schema <literal>public</literal>.
Some <link linkend="ddl-schemas-patterns">usage patterns</link> call for
revoking that privilege:
<programlisting>
@@ -3087,20 +3085,25 @@ REVOKE CREATE ON SCHEMA public FROM PUBLIC;
database owner attack. -->
<para>
Constrain ordinary users to user-private schemas. To implement this,
- issue <literal>REVOKE CREATE ON SCHEMA public FROM PUBLIC</literal>,
- and create a schema for each user with the same name as that user.
- Recall that the default search path starts
- with <literal>$user</literal>, which resolves to the user name.
- Therefore, if each user has a separate schema, they access their own
- schemas by default. After adopting this pattern in a database where
- untrusted users had already logged in, consider auditing the public
- schema for objects named like objects in
+ first issue <literal>REVOKE CREATE ON SCHEMA public FROM
+ PUBLIC</literal>. Then, for every user needing to create non-temporary
+ objects, create a schema with the same name as that user. Recall that
+ the default search path starts with <literal>$user</literal>, which
+ resolves to the user name. Therefore, if each user has a separate
+ schema, they access their own schemas by default. After adopting this
+ pattern in a database where untrusted users had already logged in,
+ consider auditing the public schema for objects named like objects in
schema <literal>pg_catalog</literal>. This pattern is a secure schema
usage pattern unless an untrusted user is the database owner or holds
the <literal>CREATEROLE</literal> privilege, in which case no secure
schema usage pattern exists.
</para>
<para>
+ If the database originated in an upgrade
+ from <productname>PostgreSQL</productname> 13 or earlier,
+ the <literal>REVOKE</literal> is essential. Otherwise, the default
+ configuration follows this pattern; ordinary users can create only
+ temporary objects until a privileged user furnishes a schema.
</para>
</listitem>
@@ -3109,10 +3112,10 @@ REVOKE CREATE ON SCHEMA public FROM PUBLIC;
Remove the public schema from the default search path, by modifying
<link linkend="config-setting-configuration-file"><filename>postgresql.conf</filename></link>
or by issuing <literal>ALTER ROLE ALL SET search_path =
- "$user"</literal>. Everyone retains the ability to create objects in
- the public schema, but only qualified names will choose those objects.
- While qualified table references are fine, calls to functions in the
- public schema <link linkend="typeconv-func">will be unsafe or
+ "$user"</literal>. Then, grant privileges to create in the public
+ schema. Only qualified names will choose public schema objects. While
+ qualified table references are fine, calls to functions in the public
+ schema <link linkend="typeconv-func">will be unsafe or
unreliable</link>. If you create functions or extensions in the public
schema, use the first pattern instead. Otherwise, like the first
pattern, this is secure unless an untrusted user is the database owner
@@ -3122,11 +3125,14 @@ REVOKE CREATE ON SCHEMA public FROM PUBLIC;
<listitem>
<para>
- Keep the default. All users access the public schema implicitly. This
+ Keep the default search path, and grant privileges to create in the
+ public schema. All users access the public schema implicitly. This
simulates the situation where schemas are not available at all, giving
a smooth transition from the non-schema-aware world. However, this is
never a secure pattern. It is acceptable only when the database has a
- single user or a few mutually-trusting users.
+ single user or a few mutually-trusting users. In databases upgraded
+ from <productname>PostgreSQL</productname> 13 or earlier, this is the
+ default.
</para>
</listitem>
</itemizedlist>
diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml
index fe0bdb7..7b7c488 100644
--- a/doc/src/sgml/user-manag.sgml
+++ b/doc/src/sgml/user-manag.sgml
@@ -596,13 +596,14 @@ DROP ROLE doomed_role;
<para>
The <literal>pg_database_owner</literal> role has one implicit,
- situation-dependent member, namely the owner of the current database. The
- role conveys no rights at first. Like any role, it can own objects or
- receive grants of access privileges. Consequently, once
- <literal>pg_database_owner</literal> has rights within a template database,
- each owner of a database instantiated from that template will exercise those
- rights. <literal>pg_database_owner</literal> cannot be a member of any
- role, and it cannot have non-implicit members.
+ situation-dependent member, namely the owner of the current database. Like
+ any role, it can own objects or receive grants of access privileges.
+ Consequently, once <literal>pg_database_owner</literal> has rights within a
+ template database, each owner of a database instantiated from that template
+ will exercise those rights. <literal>pg_database_owner</literal> cannot be
+ a member of any role, and it cannot have non-implicit members. Initially,
+ this role owns the <literal>public</literal> schema, so each database owner
+ governs local use of the schema.
</para>
<para>
@@ -651,8 +652,8 @@ GRANT pg_signal_backend TO admin_user;
horse</quote> others with relative ease. The strongest protection is tight
control over who can define objects. Where that is infeasible, write
queries referring only to objects having trusted owners. Remove
- from <varname>search_path</varname> the public schema and any other schemas
- that permit untrusted users to create objects.
+ from <varname>search_path</varname> any schemas that permit untrusted users
+ to create objects.
</para>
<para>
diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c
index 152d21e..eba3385 100644
--- a/src/bin/initdb/initdb.c
+++ b/src/bin/initdb/initdb.c
@@ -1703,8 +1703,7 @@ setup_privileges(FILE *cmdfd)
CppAsString2(RELKIND_VIEW) ", " CppAsString2(RELKIND_MATVIEW) ", "
CppAsString2(RELKIND_SEQUENCE) ")"
" AND relacl IS NULL;\n\n",
- "GRANT USAGE ON SCHEMA pg_catalog TO PUBLIC;\n\n",
- "GRANT CREATE, USAGE ON SCHEMA public TO PUBLIC;\n\n",
+ "GRANT USAGE ON SCHEMA pg_catalog, public TO PUBLIC;\n\n",
"REVOKE ALL ON pg_largeobject FROM PUBLIC;\n\n",
"INSERT INTO pg_init_privs "
" (objoid, classoid, objsubid, initprivs, privtype)"
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 3211521..37e6be7 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -1647,11 +1647,12 @@ selectDumpableNamespace(NamespaceInfo *nsinfo, Archive *fout)
* no-mans-land between being a system object and a user object.
* CREATE SCHEMA would fail, so its DUMP_COMPONENT_DEFINITION is just
* a comment and an indication of ownership. If the owner is the
- * default, that DUMP_COMPONENT_DEFINITION is superfluous.
+ * default, omit that superfluous DUMP_COMPONENT_DEFINITION. Before
+ * v14, the default owner was BOOTSTRAP_SUPERUSERID.
*/
nsinfo->create = false;
nsinfo->dobj.dump = DUMP_COMPONENT_ALL;
- if (nsinfo->nspowner == BOOTSTRAP_SUPERUSERID)
+ if (nsinfo->nspowner == ROLE_PG_DATABASE_OWNER)
nsinfo->dobj.dump &= ~DUMP_COMPONENT_DEFINITION;
nsinfo->dobj.dump_contains = DUMP_COMPONENT_ALL;
}
@@ -4867,21 +4868,26 @@ getNamespaces(Archive *fout, int *numNamespaces)
PQExpBuffer init_racl_subquery = createPQExpBuffer();
/*
- * Bypass pg_init_privs.initprivs for the public schema. Dropping and
- * recreating the schema detaches it from its pg_init_privs row, but
- * an empty destination database starts with this ACL nonetheless.
- * Also, we support dump/reload of public schema ownership changes.
- * ALTER SCHEMA OWNER filters nspacl through aclnewowner(), but
- * initprivs continues to reflect the initial owner (the bootstrap
- * superuser). Hence, synthesize the value that nspacl will have
- * after the restore's ALTER SCHEMA OWNER.
+ * Bypass pg_init_privs.initprivs for the public schema, for several
+ * reasons. First, dropping and recreating the schema detaches it
+ * from its pg_init_privs row, but an empty destination database
+ * starts with this ACL nonetheless. Second, we support dump/reload
+ * of public schema ownership changes. ALTER SCHEMA OWNER filters
+ * nspacl through aclnewowner(), but initprivs continues to reflect
+ * the initial owner. Hence, synthesize the value that nspacl will
+ * have after the restore's ALTER SCHEMA OWNER. Third, this makes the
+ * destination database match the source's ACL, even if the latter was
+ * an initdb-default ACL, which changed in v14. An upgrade pulls in
+ * changes to most system object ACLs that the DBA had not customized.
+ * We've made the public schema depart from that, because changing its
+ * ACL so easily breaks applications.
*/
buildACLQueries(acl_subquery, racl_subquery, init_acl_subquery,
init_racl_subquery, "n.nspacl", "n.nspowner",
"CASE WHEN n.nspname = 'public' THEN array["
" format('%s=UC/%s', "
" n.nspowner::regrole, n.nspowner::regrole),"
- " format('=UC/%s', n.nspowner::regrole)]::aclitem[] "
+ " format('=U/%s', n.nspowner::regrole)]::aclitem[] "
"ELSE pip.initprivs END",
"'n'", dopt->binary_upgrade);
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index 448b1be2..edfbe41 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -629,7 +629,9 @@ my %tests = (
},
'ALTER SCHEMA public OWNER TO' => {
- # see test "REVOKE CREATE ON SCHEMA public" for causative create_sql
+ create_order => 15,
+ create_sql =>
+ 'ALTER SCHEMA public OWNER TO "regress_quoted \"" role";',
regexp => qr/^ALTER SCHEMA public OWNER TO .+;/m,
like => {
%full_runs, section_pre_data => 1,
@@ -3414,17 +3416,12 @@ my %tests = (
unlike => { no_privs => 1, },
},
- 'REVOKE CREATE ON SCHEMA public FROM public' => {
+ 'REVOKE ALL ON SCHEMA public' => {
create_order => 16,
- create_sql => '
- REVOKE CREATE ON SCHEMA public FROM public;
- ALTER SCHEMA public OWNER TO "regress_quoted \"" role";
- REVOKE ALL ON SCHEMA public FROM "regress_quoted \"" role";',
- regexp => qr/^
- \QREVOKE ALL ON SCHEMA public FROM "regress_quoted \E\\""\ role";
- \n\QREVOKE ALL ON SCHEMA public FROM PUBLIC;\E
- \n\QGRANT USAGE ON SCHEMA public TO PUBLIC;\E
- /xm,
+ create_sql =>
+ 'REVOKE ALL ON SCHEMA public FROM "regress_quoted \"" role";',
+ regexp =>
+ qr/^REVOKE ALL ON SCHEMA public FROM "regress_quoted \\"" role";/m,
like => { %full_runs, section_pre_data => 1, },
unlike => { no_privs => 1, },
},
diff --git a/src/include/catalog/pg_namespace.dat b/src/include/catalog/pg_namespace.dat
index 33992af..9a23975 100644
--- a/src/include/catalog/pg_namespace.dat
+++ b/src/include/catalog/pg_namespace.dat
@@ -21,6 +21,6 @@
# update dumpNamespace() if changing this descr
{ oid => '2200', oid_symbol => 'PG_PUBLIC_NAMESPACE',
descr => 'standard public schema',
- nspname => 'public', nspacl => '_null_' },
+ nspname => 'public', nspowner => 'pg_database_owner', nspacl => '_null_' },
]
diff --git a/src/pl/plperl/expected/plperl_setup.out b/src/pl/plperl/expected/plperl_setup.out
index a1a24df..5234feb 100644
--- a/src/pl/plperl/expected/plperl_setup.out
+++ b/src/pl/plperl/expected/plperl_setup.out
@@ -25,6 +25,9 @@ CREATE EXTENSION plperl;
CREATE EXTENSION plperlu; -- fail
ERROR: permission denied to create extension "plperlu"
HINT: Must be superuser to create this extension.
+CREATE SCHEMA plperl_setup_scratch;
+SET search_path = plperl_setup_scratch;
+GRANT ALL ON SCHEMA plperl_setup_scratch TO regress_user2;
CREATE FUNCTION foo1() returns int language plperl as '1;';
SELECT foo1();
foo1
@@ -34,6 +37,7 @@ SELECT foo1();
-- Must reconnect to avoid failure with non-MULTIPLICITY Perl interpreters
\c -
+SET search_path = plperl_setup_scratch;
SET ROLE regress_user1;
-- Should be able to change privileges on the language
revoke all on language plperl from public;
diff --git a/src/pl/plperl/sql/plperl_setup.sql b/src/pl/plperl/sql/plperl_setup.sql
index 7484478..a89cf56 100644
--- a/src/pl/plperl/sql/plperl_setup.sql
+++ b/src/pl/plperl/sql/plperl_setup.sql
@@ -27,12 +27,16 @@ SET ROLE regress_user1;
CREATE EXTENSION plperl;
CREATE EXTENSION plperlu; -- fail
+CREATE SCHEMA plperl_setup_scratch;
+SET search_path = plperl_setup_scratch;
+GRANT ALL ON SCHEMA plperl_setup_scratch TO regress_user2;
CREATE FUNCTION foo1() returns int language plperl as '1;';
SELECT foo1();
-- Must reconnect to avoid failure with non-MULTIPLICITY Perl interpreters
\c -
+SET search_path = plperl_setup_scratch;
SET ROLE regress_user1;
diff --git a/src/test/regress/input/tablespace.source b/src/test/regress/input/tablespace.source
index c133e73..cb9774e 100644
--- a/src/test/regress/input/tablespace.source
+++ b/src/test/regress/input/tablespace.source
@@ -388,7 +388,7 @@ CREATE INDEX k ON testschema.tablespace_acl (c) TABLESPACE regress_tblspace;
ALTER TABLE testschema.tablespace_acl OWNER TO regress_tablespace_user2;
SET SESSION ROLE regress_tablespace_user2;
-CREATE TABLE tablespace_table (i int) TABLESPACE regress_tblspace; -- fail
+CREATE TEMP TABLE tablespace_table (i int) TABLESPACE regress_tblspace; -- fail
ALTER TABLE testschema.tablespace_acl ALTER c TYPE bigint;
REINDEX (TABLESPACE regress_tblspace) TABLE tablespace_table; -- fail
REINDEX (TABLESPACE regress_tblspace, CONCURRENTLY) TABLE tablespace_table; -- fail
@@ -409,3 +409,6 @@ DROP SCHEMA testschema CASCADE;
DROP ROLE regress_tablespace_user1;
DROP ROLE regress_tablespace_user2;
+
+-- Rest of this suite can use the public schema freely.
+GRANT ALL ON SCHEMA public TO public;
diff --git a/src/test/regress/output/tablespace.source b/src/test/regress/output/tablespace.source
index 1bbe7e0..e7629d4 100644
--- a/src/test/regress/output/tablespace.source
+++ b/src/test/regress/output/tablespace.source
@@ -908,7 +908,7 @@ CREATE TABLE testschema.tablespace_acl (c int);
CREATE INDEX k ON testschema.tablespace_acl (c) TABLESPACE regress_tblspace;
ALTER TABLE testschema.tablespace_acl OWNER TO regress_tablespace_user2;
SET SESSION ROLE regress_tablespace_user2;
-CREATE TABLE tablespace_table (i int) TABLESPACE regress_tblspace; -- fail
+CREATE TEMP TABLE tablespace_table (i int) TABLESPACE regress_tblspace; -- fail
ERROR: permission denied for tablespace regress_tblspace
ALTER TABLE testschema.tablespace_acl ALTER c TYPE bigint;
REINDEX (TABLESPACE regress_tblspace) TABLE tablespace_table; -- fail
@@ -934,3 +934,5 @@ drop cascades to table testschema.atable
drop cascades to table testschema.tablespace_acl
DROP ROLE regress_tablespace_user1;
DROP ROLE regress_tablespace_user2;
+-- Rest of this suite can use the public schema freely.
+GRANT ALL ON SCHEMA public TO public;
On 30.06.21 03:37, Noah Misch wrote:
On Sat, Mar 27, 2021 at 11:41:07AM +0100, Laurenz Albe wrote:
On Sat, 2021-03-27 at 00:50 -0700, Noah Misch wrote:
On Sat, Feb 13, 2021 at 04:56:29AM -0800, Noah Misch wrote:
I'm attaching the patch for $SUBJECT, which applies atop the four patches from
the two other threads below. For convenience of testing, I've included a
rollup patch, equivalent to applying all five patches.I committed prerequisites from one thread, so here's a rebased rollup patch.
I am happy to see this problem tackled!
Rebased. I've pushed all prerequisites, so there's no longer a distinct
rollup patch.
I think this patch represents the consensus.
The documentation looks okay. Some places still refer to PostgreSQL 13,
which should now be changed to 14.
I tried a couple of upgrade scenarios and it appeared to do the right thing.
This patch is actually two separate changes: First, change the owner of
the public schema to "pg_database_owner"; second, change the default
privileges set on the public schema by initdb. I was a bit surprised
that the former hadn't already be done in PG14. In any case, if there
is still any doubt about the latter part, the former can surely go ahead
separately if needed.
On Thu, Sep 02, 2021 at 12:36:51PM +0200, Peter Eisentraut wrote:
I think this patch represents the consensus.
The documentation looks okay. Some places still refer to PostgreSQL 13,
which should now be changed to 14.
Thanks. I'll update s/13/14/ and/or s/14/15/ before the next step.
I tried a couple of upgrade scenarios and it appeared to do the right thing.
This patch is actually two separate changes: First, change the owner of the
public schema to "pg_database_owner"; second, change the default privileges
set on the public schema by initdb. I was a bit surprised that the former
hadn't already be done in PG14.
Interesting. That change requires a7a7be1, which is also not in v14.
Do you plan to change the CF entry, or should it remain in Needs Review with
no assigned reviewer?
On 04.09.21 18:18, Noah Misch wrote:
I tried a couple of upgrade scenarios and it appeared to do the right thing.
This patch is actually two separate changes: First, change the owner of the
public schema to "pg_database_owner"; second, change the default privileges
set on the public schema by initdb. I was a bit surprised that the former
hadn't already be done in PG14.Interesting. That change requires a7a7be1, which is also not in v14.
Do you plan to change the CF entry, or should it remain in Needs Review with
no assigned reviewer?
I've set it to ready for committer now.