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