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

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.

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

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

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,

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

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

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; > > >

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

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

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 > >

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,

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

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 > >>

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

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

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

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

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

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

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

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.

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

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.

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

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

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

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

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

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

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: > >

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

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

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.

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

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

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.

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 Misch wrote: > >> I propose, for v11, switching to "GRANT USAGE ON SCHEMA > >> public TO PUBLIC" (omit CREATE). Concerns? An

Re: public schema default ACL

On Wed, Mar 7, 2018 at 5:11 PM, David G. Johnston wrote: > I still feel like I want to mull this over more but auto-creating schemas > strikes me as being "spooky action at a distance". I don't think that it's a terrible proposal, but I don't see it as fixing the real

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

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

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

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

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

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

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

Re: public schema default ACL

On 3/6/18 15:20, Robert Haas wrote: > 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

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

Re: public schema default ACL

Greetings Tom, all, * Tom Lane (t...@sss.pgh.pa.us) wrote: > Robert Haas writes: > > 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

Re: public schema default ACL

Robert Haas writes: > 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

Re: public schema default ACL

On Sat, Mar 3, 2018 at 4: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 >

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

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".