15

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.

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.

  • 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 '14 at 2:05
  • It only affects tables created after the ALTER. When'd you do it? For existing tables you must GRANT rights. – Craig Ringer Apr 3 '14 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 '14 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 '14 at 13:38
  • Awfully odd. Can you reproduce the problem in a regular (non-RDS) PostgreSQL? – Craig Ringer Apr 4 '14 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.

  • 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. – Craig Ringer Apr 3 '14 at 1:52
  • I did finally get to try this out and it does not help the situation. – Andy Davis Apr 3 '14 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 '14 at 13:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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