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