2

I want to create postgres users for our staging + production databases that allow the users limited access to the tables.

I want the users to have read only access to ALL tables except messages & credit_cards. I want to give them update access for the access_tokens & I want them to have read access to newly added tables. How can I do this?

Example DB:

users:

  • id
  • name

messages:

  • user_id FK
  • message
  • to_user_id FK

credit_cards:

  • user_id FK
  • card info...

access_tokens:

  • token
  • user_id FK
  • expired_date

... Other tables

I tried:

grant USAGE on schema public to james;
grant select on all sequences in schema public to james;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO james;

ALTER DEFAULT PRIVILEGES 
    FOR ROLE james
    IN SCHEMA public
    GRANT SELECT ON TABLES TO james;

Update 1:

Note that running:

ALTER DEFAULT PRIVILEGES 
    FOR ROLE james
    IN SCHEMA public
    GRANT SELECT ON TABLES TO james;

Will throw the following error from postgres (rds):

ERROR: must be member of role "james"

However running the grant commands worked. It seems I cannot alter the default privileges for the postgres rds database.

  • 2
    what happened when you tried it? the only thing you should need is the revoke for the two tables, and grant update on the access_tokens. – Evan Carroll Jun 6 '17 at 3:44
  • When I run the alter default privileges command, I get an error: ERROR: must be member of role "james". Note that I'm using AWS RDS. @EvanCarroll – James111 Jun 6 '17 at 4:36
  • are you a database superuser? – Evan Carroll Jun 6 '17 at 4:37
  • Yeah I am @EvanCarroll I am logged in as the root user that AWS RDS provides. This adds me (the user I'm logged in as) as a member of {rds_superuser} – James111 Jun 6 '17 at 4:39
  • @EvanCarroll I added an update – James111 Jun 8 '17 at 4:37
1

ALTER DEFAULT PRIVILEGES FOR ROLE james IN SCHEMA public GRANT SELECT ON TABLES TO james;

This grants select on tables created by 'james', to 'james'. Which is generally unnecessary, as the creator will usually be owner and will automatically have access. You need to change first occurrence of 'james' to whoever it is that will be creating the new tables.

Also, you should probably change the last occurrence of 'james' to something like 'readonly' or 'developer', and than grant that role to 'james', rather than naming 'james' explicitly.

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.