Privileges for database owner; application user Quick version: 

What command should I issue to enable a database owner to allow it to access tables in this database and can this be done from that owner's account?



Longer Version:

I am creating a database on RDS.  I have a 'root' user that I have configured with Amazon.

Amazon automatically creates the group role 'rds_superuser' which is very privileged, but not actually a superuser.

I am creating a database and user for the application as follows:

create database master_integration;
CREATE ROLE master_application LOGIN ENCRYPTED PASSWORD '...' VALID UNTIL 'infinity';
GRANT ALL ON DATABASE master_integration TO GROUP rds_superuser WITH GRANT OPTION;
GRANT ALL ON DATABASE master_integration TO GROUP master_application;

\c master_integration;
ALTER DEFAULT PRIVILEGES GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES TO rds_superuser;


I updated this script to reflect suggestions by Craig Ringer regarding how I should be handling this.

When the app connects (with the master_application credentials) it creates  (and therefore owns) the tables.

My issue is that I cannot use my administrative (rootish) log in to run queries because that user has no privileges on the table.  

I have been able to solve this before by running the following from the application account:

GRANT ALL privileges ON ALL TABLES IN SCHEMA public to rds_superuser;


But it seems hacky to have a subordinate user grant privs back to an administrative user.

So...Is there a command that I can run before or after I create the tables from the application which will ensure that the owner of the database can access the tables within the database?



update after re-trying the alter default privilegs...

This still does not grant access to the tables; I see it being suggested elsewhere and it makes complete sense, but it is not working for me.  From a psql shell:

master_integration=&gt; \ddp
                           Default access privileges
      Owner       | Schema | Type  |             Access privileges             
------------------+--------+-------+-------------------------------------------
 integration_root |        | table | integration_root=arwdDxt/integration_root+
                  |        |       | rds_superuser=arwdDxt/integration_root
(1 row)

master_integration=&gt; \dp users
                           Access privileges
 Schema | Name  | Type  | Access privileges | Column access privileges 
--------+-------+-------+-------------------+--------------------------
 public | users | table |                   | 
(1 row)


integration_root is my superuser-ish user and users is a table within my database.



Update

I got a fairly useless response from someone at Amazon.  

They asked me to call ALTER DEFAULT PRIVILEGES from the master_application login.  While this would probably work, it would not answer my question (which is how do I make this happen solely from the rds_superuser account).  

I asked them to clarify this and they went away.
You want ALTER DEFAULT PRIVILEGES.

Give the rds_superuser default access rights to all new tables.

This only affects tables created after the ALTER. For existing tables you must GRANT rights.