18

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.

2 Answers 2

9

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.

11
  • I had tried to do this. I worded this poorly in the original question, I have edited it to show exactly the command that I was executing. Did I get something wrong?
    – Andy Davis
    Apr 3, 2014 at 2:05
  • It only affects tables created after the ALTER. When'd you do it? For existing tables you must GRANT rights. Apr 3, 2014 at 2:09
  • I altered the default privs, then created the tables. I am going to try it again, perhaps I made a mistake.
    – Andy Davis
    Apr 3, 2014 at 12:37
  • Still no luck with this, although it absolutely seems like the command that should solve the problem. I updated the command to include the output from \ddp and \dp from one of the tables.
    – Andy Davis
    Apr 3, 2014 at 13:38
  • Awfully odd. Can you reproduce the problem in a regular (non-RDS) PostgreSQL? Apr 4, 2014 at 1:36
0

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.

3
  • Huh? A GRANT should never be able to reduce access rights. I'm not convinced. Note that rights on a schema don't get inherited by new tables in that schema, either, so having access to the public schema doesn't mean you can use tables within it. Apr 3, 2014 at 1:52
  • I did finally get to try this out and it does not help the situation.
    – Andy Davis
    Apr 3, 2014 at 1:59
  • I have edited the question to eliminate the Grant that @Aleandros was referring to. It didn't seem to be the problem, but I think it was a distraction.
    – Andy Davis
    Apr 3, 2014 at 13:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.