How can I create postgres users for employees that have limited access?

Question

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.

Answer 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.