-1

I need to achieve the below goal in postgresql. There is a database "textdb" with 4 schema's that are public, ds, fin, viz.

We need to create a script that can be used to provide access to the role. So, I need to create 3 roles, one for analyst, another for data scientist, and last one for visualization people.

  CREATE ROLE analyst WITH LOGIN;

  CREATE ROLE ds WITH LOGIN;

  CREATE ROLE vi WITH LOGIN;

The respective team members will be added to their respective roles so that we need not to get access to individual users. Analyst is required to get complete access of the database "testdb", ds role will have access to all the data in the schema "ds" and "fin" and vi role will have access to schema "viz".

Also, whenever new table/view/procedure/function is added, then automatically users should have the required access.

halfer
  • 19,824
  • 17
  • 99
  • 186
user3222101
  • 1,270
  • 2
  • 24
  • 43

1 Answers1

0
  1. If you plan to grant those roles to users they don't need login I believe. This is exact difference between "USER that can connect" and "just ROLE"
  2. for new relations grants use ALTER DEFAULT PRIVILEGES
  3. to grant role to user just grant ds to user_mike; if you \du+ user_mike you will see roles granted in "Menber of" section
Vao Tsun
  • 47,234
  • 13
  • 100
  • 132