On Mon, Jul 01, 2013 at 09:34:24AM -0400, Michael Orlitzky wrote:
> >> # Admins can do anything.
> >
> > You've been able to create this situation with the superuser flag for
> > as long as I can remember (I started with Postgres in the 6.5.x era,
> > but I won't claim my memory goes back that far).
> >
>
> I'm not giving root to people who don't need it. They need to be able to
> read/write any database.
So not "can do anything", but can read and write any database. Looks
to me to be something like
CREATE ROLE adminuser NOSUPERUSER NOCREATEDB NOCREATEROLE
NOCREATEUSER INHERIT LOGIN NOREPLICATION ADMIN;
Whenever a database is created, you need to GRANT ALL PRIVILEGES ON
database TO adminuser.
> In one database. The example.com user should be able to read the
> example.com database. If you can come up with a way to grant permissions
> automatically, I'd like to hear it. You can do it for a user but not for
> a group, which is the whole problem I'm trying to describe.
There's no distinction between users and groups. They're all ROLEs.
According to the manual, that's been true since PostgreSQL 8.1.
You need to create your users with certain ROLEs, is all. For instance,
'CREATE ROLE newuser LOGIN IN ROLE examplereader;' Then you GRANT the
various privileges you want on the database.
It's true that the role system feels a little odd if you're used to
the user handling in some other systems (like UNIX). But it isn't
impossible to do what you appear to want.
Best,
A
--
Andrew Sullivan
ajs@crankycanuck.ca