Re: public schema default ACL
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.
Re: public schema default ACL
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?
Re: public schema default ACL
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.
Re: public schema default ACL
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. Author: Noah Misch Commit: Noah Misch 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.gb4039...@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; By default, users cannot access any objects in schemas they do not own. To allow that, the owner of the schema must grant the -USAGE 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. +USAGE privilege on the schema. By default, everyone +has that privilege on the schema public. To allow +users to make use of the objects in a schema, additional privileges might +need to be granted, as appropriate for the object. -A user can also be allowed to create objects in someone else's -schema. To allow that, the CREATE privilege on -the schema needs to be granted. Note that by default, everyone -has CREATE and USAGE privileges on -the schema -public. This allows all users that are able to -connect to a given database to create objects in its -public schema. +A user can also be allowed to create objects in someone else's schema. To +allow that, the CREATE privilege on the schema needs to +be granted. In databases upgraded from +PostgreSQL 13 or earlier, everyone has that +privilege on the schema public. Some usage patterns call for revoking that privilege: @@ -3087,20 +3085,25 @@ REVOKE CREATE ON SCHEMA public FROM PUBLIC; database owner attack. --> Constrain ordinary users to user-private schemas. To implement this, - issue REVOKE CREATE ON SCHEMA public FROM PUBLIC, - and create a schema for each user with the same name as that user. - Recall that the default search path starts - with $user, 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 REVOKE CREATE ON SCHEMA public FROM + PUBLIC. 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 $user, which + resolves to the user name. Therefore, if each user has a separate + schema, they access their own schemas by
Re: public schema default ACL
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
Re: public schema default ACL
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. Author: Noah Misch Commit: Noah Misch 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; By default, users cannot access any objects in schemas they do not own. To allow that, the owner of the schema must grant the -USAGE 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. +USAGE privilege on the schema. By default, everyone +has that privilege on the schema public. To allow +users to make use of the objects in a schema, additional privileges might +need to be granted, as appropriate for the object. -A user can also be allowed to create objects in someone else's -schema. To allow that, the CREATE privilege on -the schema needs to be granted. Note that by default, everyone -has CREATE and USAGE privileges on -the schema -public. This allows all users that are able to -connect to a given database to create objects in its -public schema. +A user can also be allowed to create objects in someone else's schema. To +allow that, the CREATE privilege on the schema needs to +be granted. In databases upgraded from +PostgreSQL 13 or earlier, everyone has that +privilege on the schema public. Some usage patterns call for revoking that privilege: @@ -3062,20 +3060,25 @@ REVOKE CREATE ON SCHEMA public FROM PUBLIC; database owner attack. --> Constrain ordinary users to user-private schemas. To implement this, - issue REVOKE CREATE ON SCHEMA public FROM PUBLIC, - and create a schema for each user with the same name as that user. - Recall that the default search path starts - with $user, 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 REVOKE CREATE ON SCHEMA public FROM + PUBLIC. 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 $user, 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 pg_catalog. This pattern is a secure schema usage pattern unless an untrusted user is the database owner or holds the CREATEROLE privilege, in which case no secure schema usage pattern exists. + If the database originated in an upgrade + from PostgreSQL 13 or earlier, + the REVOKE is essential. Otherwise, the default + configuration follows this pattern; ordinary users can create only + temporary objects until a privileged user furnishes a schema. @@ -3084,10 +3087,10 @@ REVOKE CREATE ON SCHEMA public FROM PUBLIC; Remove the public schema from
Re: public schema default ACL
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: https://postgr.es/m/20201228043148.ga1053...@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: https://postgr.es/m/20201229134924.ga1431...@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 Author: Noah Misch Commit: Noah Misch 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:
Re: public schema default ACL
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 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. > > 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 https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee
Re: public schema default ACL
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 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. 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.
Re: public schema default ACL
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 https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee
Re: public schema default ACL
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 writes: > > > On Mon, Nov 2, 2020 at 5:51 AM Peter Eisentraut > > > 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 https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee
Re: public schema default ACL
On Mon, Nov 2, 2020 at 1:41 PM Stephen Frost 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
Re: public schema default ACL
On Mon, Nov 02, 2020 at 12:42:26PM -0500, Tom Lane wrote: > Robert Haas writes: > > On Mon, Nov 2, 2020 at 5:51 AM Peter Eisentraut > > 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?
Re: public schema default ACL
Greetings, * Tom Lane (t...@sss.pgh.pa.us) wrote: > Robert Haas writes: > > On Mon, Nov 2, 2020 at 5:51 AM Peter Eisentraut > > 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
Re: public schema default ACL
Robert Haas writes: > On Mon, Nov 2, 2020 at 5:51 AM Peter Eisentraut > 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
Re: public schema default ACL
On Mon, Nov 2, 2020 at 5:51 AM Peter Eisentraut 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
Re: public schema default ACL
Greetings, * Peter Eisentraut (peter.eisentr...@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 signature.asc Description: PGP signature
Re: public schema default ACL
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
Re: public schema default ACL
On Thu, Aug 06, 2020 at 12:48:17PM +0200, Magnus Hagander wrote: > On Mon, Aug 3, 2020 at 5:26 PM Stephen Frost wrote: > > * Noah Misch (n...@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. > > +. (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
Re: public schema default ACL
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 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?
Re: public schema default ACL
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.
Re: public schema default ACL
On Mon, Aug 10, 2020 at 10:21:06AM +0200, Magnus Hagander wrote: > On Thu, Aug 6, 2020 at 3:34 PM Stephen Frost 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.
Re: public schema default ACL
On Thu, Aug 6, 2020 at 3:34 PM Stephen Frost wrote: > Greetings, > > * Magnus Hagander (mag...@hagander.net) wrote: > > On Mon, Aug 3, 2020 at 5:26 PM Stephen Frost wrote: > > > * Noah Misch (n...@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. > > > > 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. > 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 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
Re: public schema default ACL
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?
Re: public schema default ACL
Greetings, * Magnus Hagander (mag...@hagander.net) wrote: > On Mon, Aug 3, 2020 at 5:26 PM Stephen Frost wrote: > > * Noah Misch (n...@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. > > 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
Re: public schema default ACL
On Mon, Aug 3, 2020 at 5:26 PM Stephen Frost wrote: > > * Noah Misch (n...@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. > 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 > 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
Re: public schema default ACL
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?
Re: public schema default ACL
On Mon, Aug 03, 2020 at 09:46:23AM -0400, Robert Haas wrote: > On Mon, Aug 3, 2020 at 2:30 AM Noah Misch 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?
Re: public schema default ACL
On Sun, Aug 2, 2020 at 11:30 PM Noah Misch 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.
Re: public schema default ACL
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
Re: public schema default ACL
Greetings, * Noah Misch (n...@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 signature.asc Description: PGP signature
Re: public schema default ACL
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 https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee
Re: public schema default ACL
On Mon, Aug 3, 2020 at 2:30 AM Noah Misch 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
Re: public schema default ACL
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). http://postgr.es/m/0e61bd66-07a2-255b-2b0f-7a8488ea1...@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
Re: public schema default ACL
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.
Re: public schema default ACL
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".
Re: public schema default ACL
On Wed, Mar 07, 2018 at 07:14:43AM -0500, Stephen Frost wrote: > * Noah Misch (n...@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?
Re: public schema default ACL
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 Mischwrote: > >> 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.
Re: public schema default ACL
On Wed, Mar 7, 2018 at 5:11 PM, David G. Johnstonwrote: > 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
Re: public schema default ACL
On 07/03/18 17:55, Stephen Frost wrote: > Greetings Petr, all, > > * Petr Jelinek (petr.jeli...@2ndquadrant.com) wrote: >> On 07/03/18 13:14, Stephen Frost wrote: >>> * Noah Misch (n...@leadboat.com) wrote: On Tue, Mar 06, 2018 at 09:28:21PM -0500, Stephen Frost wrote: > * Tom Lane (t...@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
Re: public schema default ACL
Greetings Petr, all, * Petr Jelinek (petr.jeli...@2ndquadrant.com) wrote: > On 07/03/18 13:14, Stephen Frost wrote: > > * Noah Misch (n...@leadboat.com) wrote: > >> On Tue, Mar 06, 2018 at 09:28:21PM -0500, Stephen Frost wrote: > >>> * Tom Lane (t...@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
Re: public schema default ACL
Greetings Petr, all, * Petr Jelinek (petr.jeli...@2ndquadrant.com) wrote: > On 07/03/18 16:26, Stephen Frost wrote: > > Greeting Petr, all, > > > > * Petr Jelinek (petr.jeli...@2ndquadrant.com) wrote: > >> On 07/03/18 13:18, Stephen Frost wrote: > >>> Greetings, > >>> > >>> * Petr Jelinek (petr.jeli...@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
Re: public schema default ACL
On 07/03/18 13:14, Stephen Frost wrote: > Greetings, > > * Noah Misch (n...@leadboat.com) wrote: >> On Tue, Mar 06, 2018 at 09:28:21PM -0500, Stephen Frost wrote: >>> * Tom Lane (t...@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
Re: public schema default ACL
Greeting Petr, all, * Petr Jelinek (petr.jeli...@2ndquadrant.com) wrote: > On 07/03/18 13:18, Stephen Frost wrote: > > Greetings, > > > > * Petr Jelinek (petr.jeli...@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
Re: public schema default ACL
On 07/03/18 13:18, Stephen Frost wrote: > Greetings, > > * Petr Jelinek (petr.jeli...@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
Re: public schema default ACL
Stephen Frost wrote: > * Noah Misch (n...@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 Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: public schema default ACL
On 3/6/18 15:20, Robert Haas wrote: > On Sat, Mar 3, 2018 at 4:56 AM, Noah Mischwrote: >> 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
Re: public schema default ACL
Greetings, * Noah Misch (n...@leadboat.com) wrote: > On Tue, Mar 06, 2018 at 09:28:21PM -0500, Stephen Frost wrote: > > * Tom Lane (t...@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 signature.asc Description: PGP signature
Re: public schema default ACL
Greetings Tom, all, * Tom Lane (t...@sss.pgh.pa.us) wrote: > Robert Haaswrites: > > On Sat, Mar 3, 2018 at 4:56 AM, Noah Misch 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 signature.asc Description: PGP signature
Re: public schema default ACL
Robert Haaswrites: > On Sat, Mar 3, 2018 at 4:56 AM, Noah Misch 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
Re: public schema default ACL
On Sat, Mar 3, 2018 at 4:56 AM, Noah Mischwrote: > 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
Re: public schema default ACL
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.
Re: public schema default ACL
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 signature.asc Description: OpenPGP digital signature