Re: public schema default ACL

On Wed, Mar 7, 2018 at 2:48 PM, Peter Eisentraut <
peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:

> On 3/7/18 10:05, Stephen Frost wrote:
> > I liken this to a well-known and well-trodden feature for auto creating
> > user home directories on Unix.
>
> I don't think likening schemas to home directories is really addressing
> the most typical use cases. Database contents are for the most part
> carefully constructed in a collaborative way.

​Databases intended to be deployed to production (hopefully) are, but not
necessarily those intend to evaluate PostgreSQL's capabilities.

> The fix is probably to not let them do that. What is
> being discussed here instead is to let them do whatever they want in
> their own non-shared spaces. That addresses the security concern, but
> it doesn't support the way people actually work right now.
>

Maybe not the majority of users, but the way DBA's work today is already
inherently secure (i.e., not using public)​ and requires a non-trivial
amount of DBA work (i.e., creating groups and users) to make happen. They
are not the target audience.

The target user profile for this discussion is one who does:

sudo apt install postgresql-10
sudo -U postgres createuser myosusername
psql myosusername postgres
> CREATE TABLE test_table (id serial primary key);
> insert into test_table;
> select * from test_table;

We want to avoid having the create table fail now whereas it worked before
we removed create permissions on public from PUBLIC.

Now, I'd argue that people aren't bothering to "createuser" in the above
but simply skipping to "psql" and then to "sudo -U postgres psql" when they
get the error that "user myosusername" doesn't exist...once they start
creating new users I'd agree that they likely benefit more from us being
conservative and "do only what I say" as opposed to being helpful and doing
more stuff in the name of usability.

I still feel like I want to mull this over more but auto-creating schemas
strikes me as being "spooky action at a distance".

David J.