Privileges for database owner; application user

Question

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=> \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=> \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.

Answer 1

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.

Answer 2

The public schema is supposed to be visible by all users. You should not restrict rights of public schema to just one group.

So, if you do not use:

GRANT ALL ON SCHEMA public TO GROUP rds_superuser WITH GRANT OPTION;

You may safely work with the public schema with all accounts.

If you do not want specific accounts to mess up your public schema tables, then create a new role (for users of your app) and revoke rights from this particular role inside the public schema. Something like:

CREATE USER mywebuser WITH PASSWORD '*****';
REVOKE ALL PRIVILEGES ON SCHEMA public FROM mywebuser;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO mywebuser; (or whatever rights you need to provide)

Not the other way around as you try to do it.