Link to home
Start Free TrialLog in
Avatar of yagnaramiyer
yagnaramiyer

asked on

Set up Postgresql role/ security with access to a schema

Hi,

I am trying to build an application using remote developers. I would like to protect certain portions of the database. They will be in separate schema categories.
I would like the schemas to be divided into two categories:
  • Semi-protected set of schemas: A schema (or set of schemas) to stored standard application tables used for authentication, role management etc. This will be accessible to external developers.
  • Protected set of schemas: A schema category where I want the data and code inside these schemas to be protected and not accessible to external developers. They will be accessible to in-house developers. The Db Api will expose data for the external developers. 
. I would like application access to these tables only to in-house developers who develop "Db Api". The Db Api will be available for the remote UI developers to consume. They should not have access to the tables and SPs/ Functions in these schemas.

The external developers will use "Db Api" to file and retrieve data and perform CRUD actions needed for UI and Api development. The UI developer will also have access to the "Semi-protected" set of schemas so they can use ORM tools if needed for faster development.

Have stated my intention, How do I accomplish this?

I was thinking of creating two roles for application: protected and semiprotected. The external users will use the semiprotected role and the in-house developers will use the protected role. I will then add users to these roles.
  • Is there a better way to accomplish this?
  • Are there any resources that show how to set up roles? I would like to remove public make these roles tighter.
I found the following:
https://wiki.postgresql.org/images/d/d1/Managing_rights_in_postgresql.pdf
https://stackoverflow.com/questions/15867175/postgresql-db-user-should-only-be-allowed-to-call-functions

Let me know if the experts have any valuable insights and suggestions.

User generated image

Any help would be greatly appreciated!
Avatar of ste5an
ste5an
Flag of Germany image

I am trying to build an application using remote developers.
Can you please elaborate your use-case and context here? Do you also have a certain development method in mind?

Normal development?

Then there is normally no need for what you do. Developers have in most scenarios their own dev copy of the all schemata. Your approach looks unnecessarily overly complex.
Avatar of yagnaramiyer
yagnaramiyer

ASKER

Hi Ste5an,

There is nothing fancy when it comes to development. We have data and code in few other schemas that we are not comfortable 3rd party developers making copies or having access to. What you mention  "most scenarios their own dev copy of the all schemata " is what we are trying to avoid. It is trying to implement "seperation of duties" and apply "need to know basis". Hope it makes sense.

Thanks,

Karthik
ASKER CERTIFIED SOLUTION
Avatar of lcohan
lcohan
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi Cohan,

What you described makes sense. What about permissions to SPs/ Functions? And any special permissions to be revoked on public schema?

Thanks!
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I asked about the kind of development. Cause there different areas or stages:

1) There is database development. Here developers need a separate copy of the schema to implement their work and do their own tests. No real data.

2) Then there is your API work. Here you have two possibilities:
a) You have a shared development database. No real data. The developers have their own services infrastructure, thus needed access for the CRUD operations. In other words, only the API credentials.
b) You have a shared service infrastructure for development. Here you need some kind of CI/CD for devs to deploy their changes.

3) Then there is mere UI work. The devs just have normal user accounts into the dev system for calling the services.

It is trying to implement "seperation of duties" [..]
In development there are only different, separate stages of development. There is no separation of duties as this is a business process aspect.

E.g. in the above scenario the following separation must exist:
UI work is only done against existing services. No service is touched, no schema is touched.
New services or changes are only written against existing schema. No schema is touched. Changes don't break UI (open-closed principle).
Schema is touched. Changes don't services. (open-closed principle).

This kind of separation is imho implied by agile methods like SCRUM, where sprints are isolated units of work.

[..] and apply "need to know basis".
The get only the schema (database developers) or access to a development system.
In a dev system there is no real data besides unproblematic master and invariant data.
Hi Ste5an,

I will use the terms more carefully. It is different in my head than how it is defined  in the industry. I agree with all you have said.

My intent is to not expose tables and code to developers when there is no need for them to know or make copies of...whether it has only test data or not. I know what I am asking is different from the way industry operates where developers typically have all rights to all schemas and code. I want to avoid that.

If you go back to my question, your #1 and #2 are done by internal developers who have access to all.

The "external developers" in my question refer typically to the #3 in you comment. Quoting you "3) Then there is mere UI work. The devs just have normal user accounts into the dev system for calling the services. " . My question is about going about setting up "normal user accounts". They sometimes use ORM and might need the permissions to create objects. That is why they might need elevated permissions. However I do want to separate this out as a separate role.

In short:

Your term and usage of "developers" is the problem. There a different roles in software engineering and development. These different roles have different requirements of access.

You're currently looking for a single "solves it all" solution. This does not really exists. You can implement solutions which look and feel like that for small projects, but keep in mind they are only in the class "it works", they are not a correct solution as the deny to realize that those role differences imply separate solutions

If you go back to my question, your #1 and #2 are done by internal developers who have access to all.
You mentioned "separation of duties": This is prohibited by SoD.

The "external developers" in my question refer typically to the #3 in you comment. Quoting you "3) Then there is mere UI work. The devs just have normal user accounts into the dev system for calling the services. " . My question is about going about setting up "normal user accounts". They sometimes use ORM and might need the permissions to create objects. That is why they might need elevated permissions. However I do want to separate this out as a separate role.
This means that your internal developers clone your test or QA system. Then PII is removed and replaced by generated data. The UI devs access the services layers by using normal user accounts. They simply don't work in the same system as your dev, test, QA, UAT or even production. Cause there are a lot sneaky ways, how this could affect the normal operations. Blocking resources or by hidden side channels.

3) Then there is mere UI work. The devs just have normal user accounts into the dev system for calling the services.
I wasn't as cleary as I wanted: This implied that a separate, most probably shared dev system exists for those UI works. "the dev system" is not the same dev system your interal devs use.
btw, the level of isolation you intend to get are implemented by mocking the API layer in your diagram.