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@xxxxxxxxxxxxxx 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
