PostgreSQL – How to Set Default Privileges on Schemas

This is currently not supported in PostgreSQL out of the box. Perhaps you should send in a feature request (or a patch).

The documentation about ALTER DEFAULT PRIVILEGES tells you a possible reason - it is not that clearly described, though.

Let's see, what is said:

You can change default privileges only for objects that will be created by yourself or by roles that you are a member of.

This means that the default privileges defined by this statement applies only to objects that are created by you (or the role you are a member of). Let's see this in action!

alice's schema

First, we are logged in as alice. Then, in a newly created schema, we create a table and grant some rights to bob:

SELECT current_user;
 current_user 
──────────────
 alice


SHOW search_path ;
 search_path  
──────────────
 test, public

CREATE SCHEMA alicetest;

ALTER DEFAULT PRIVILEGES 
    FOR ROLE alice 
    IN SCHEMA alicetest 
    GRANT ALL ON TABLES TO alice;

GRANT SELECT ON alicetest.a TO bob; 
-- this I do only for showing the privileges - 
-- the owner has by default ALL and is not shown by \dp

\dp alicetest.a
                             Access privileges
  Schema   │ Name │ Type  │  Access privileges  │ Column access privileges 
───────────┼──────┼───────┼─────────────────────┼──────────────────────────
 alicetest │ a    │ table │ alice=arwdDxt/alice↵│ 
           │      │       │ bob=r/alice         │ 

alice now has all rights on her table, as expected.

bob' table in the same schema

Now, after obtaining access to this schema, bob tries to create a table:

SELECT current_user;
 current_user 
──────────────
 bob

CREATE TABLE alicetest.b (id integer);

GRANT SELECT ON alicetest.b TO alice;

\dp alicetest.b
                            Access privileges
  Schema   │ Name │ Type  │ Access privileges │ Column access privileges 
───────────┼──────┼───────┼───────────────────┼──────────────────────────
 alicetest │ b    │ table │ bob=arwdDxt/bob  ↵│ 
           │      │       │ alice=r/bob       │ 

As you can see, despite creating the table in alice's schema where she set the default privileges, bob's table doesn't have all those permissions. This happens because alice is not a member of bob.

Let's check this membership thing, too, and try to define default privileges by alice again, this time for another role:

ALTER DEFAULT PRIVILEGES 
    FOR ROLE charlie 
    IN SCHEMA alicetest 
    GRANT ALL ON TABLES TO alice;
ERROR:  must be member of role "charlie"

So, some mighty enough user grants her a membership in charlie, then she tries again, with success:

ALTER DEFAULT PRIVILEGES 
    FOR ROLE charlie 
    IN SCHEMA alicetest 
    GRANT ALL ON TABLES TO alice;
ALTER DEFAULT PRIVILEGES

charlie's round

Then charlie creates a new table:

CREATE TABLE alicetest.c (id integer);

And the privileges:

\dp alicetest.c
                               Access privileges
  Schema   │ Name │ Type  │    Access privileges    │ Column access privileges 
───────────┼──────┼───────┼─────────────────────────┼──────────────────────────
 alicetest │ c    │ table │ charlie=arwdDxt/charlie↵│ 
           │      │       │ alice=arwdDxt/charlie   │ 

As you see, alice, as a member of charlie, gets her access to this table.

To answer your question,

I guess you defined the default privileges for yourself (alice in the example), but you developers act as a bunch of bobs here, not getting the necessary privileges. One way to get around this (as we do it at work) is to do a

SET ROLE TO schema_owner;

every time before creating a new object in the schema. This should be a role that all developers are a member of (otherwise you'd get an error).

NOTE that \dp is a psql command.