We're brand new to postgres and are looking to use it for some small software projects we're working on. Consequently, I'm trying to set up a somewhat secure, relatively easy-to-manage, small multiuser development environment. My goals are:
- Have a non-superuser administrator for standard database management (create databases and users, manage privileges, etc.).
- Allow other developers login to and work on the various project databases we set up (including being able to edit other people's work).
It's actually turned out to be much more difficult to achieve this than I thought it would be. Here's what I think needs to be done, but I'm hoping someone with more experience might be able to impart some knowledge, advice, and/or best practices.
1) Create a non-login, non-superuser 'admin' role with CREATEDB and CREATEROLE.
2) Grant individual users membership to the 'admin' role who can actually login and perform maintenance.
3) Create a corresponding "user" role with each database.
4) Revoke CREATE privileges on the public schema to everyone except the role in step 3.
5) Grant individual developers NOINHERIT membership to the role in step 3.
I've found the whole schemas thing somewhat confusing. I was initially surprised to find that anyone who can connect to the database can just start creating tables (presumably because every database by default has the public schema with public CREATE privileges -- removing this privilege achieved the desired behavior).
The schemas documentation says: "there is no concept of a public schema in the SQL standard. For maximum conformance to the standard, you should not use (perhaps even remove) the public schema." This seems to suggest dropping the public schema. But the CREATE privilege on a database simply "allows new schemas to be created within the database", so it looks like I'll need to create a schema regardless to hold and control access to individual tables. It seems like the public schema is essentially transparent anyway, so I really don't understand what this is trying to tell me. With CREATE privileges revoked on the database, no one can create new schemas (so everything will default to the public schema), and with CREATE privileges on the schema restricted to one role, everything will be commonly owned (which will allow editing of each other's work).
Finally, I wrestled with having everything created under one role without giving everyone the same, project-wide login. I figured using NOINHERIT will allow developers to login individually and then switch to the correct role to do work.
So, I think it's a start. Hopefully not too convoluted!