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.
ERROR: must be member of role "james"
. Note that I'm using AWS RDS. @EvanCarroll