On 07/01/2013 10:21 AM, Andrew Sullivan wrote:
>
> 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.
>
This is basically what we have on our real system, and is fine so far...
>> 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.
>
This is technically true, and is why the default permissions don't work
for groups. See below.
> 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.
>
Of course, and this works for any objects that exist when I do it. But
if someone creates a new table in the database, I want the other users
to be able to access it automatically, without me having to log in and
GRANT something. The initial GRANT doesn't do that.
There's something that sounds like it will work, ALTER DEFAULT
PRIVILEGES, but it only works for the one role that creates the new
object. So if the 'developers' role has default privileges set, and
alice is a developer, she can create a table and the default privileges
will *not* apply. They would only apply if 'developers' logged in and
created the table.
There was another post about this a while ago:
http://www.postgresql.org/message-id/CAG1_KcBFM0e2buUG=o7OjQ_KtadrzDGd45jU7Gke3dUZ0Sz92g@mail.gmail.com
that went unresolved.
The solution that I'm using right now is to ALTER DEFAULT PRIVILEGES for
alice and bob individually, but this is basically a pen-and-paper method
of access control, and I'm very wary of making a mistake.
Furthermore, it scales terribly if you have lots of users and databases.
I'd like to be able to manage their permissions as a group, rather than
having to keep track of the group members mentally.