What is the difference between "separating access control by DB" and "separating access control by schema"?

Asked 2 years ago, Updated 2 years ago, 56 views

This is PostgreSQL12.
The PostgreSQL server uses AWS RDS.

legend)
User: SQL Executor
Group—Indicates the range of access.
Assume there are DBs, schemas, and tables under the group.

Group A: User 1, User 2
Group B: User 3, User 4

In a table under a group A, a user 1 and a user 2 can execute an SQL command, and a user 3 and a user 4 SQL command cannot be executed.
The table under Group B allows users 3 and 4 to execute SQL commands, and users 1 and 2 cannot execute SQL commands.

At this time, the PostgreSQL concept is to set the group to

  • Divide by multiple DBs
  • Separate one DB into multiple schemas

I think there are two ways.

I think each user can control the DB or schema setting for each group by GRANT, but I would like you to tell me the advantages and disadvantages of dividing by DB and schema.

For a single DATABASE, you have to choose between creating multiple schemas (in this case, group A and group B) and controlling access, or creating multiple DATABASE (in this case, group A and group B) and controlling access.

"Basically, ""Separating a DB into multiple schemas"" is easier to operate, but that's all I can think of, so I'd like you to give me a clear answer on the basis other than that."

postgresql

2022-09-30 20:22

1 Answers

DB segmentation and schema segmentation are not usually "either can meet the requirements", so for example,

  • "User 5" must run SQL with both "groups" JOINED
  • Both "groups" must be backed up in a consistent manner
  • Conversely, each "group" must be able to backup and restore independently
  • Consider compatibility with other RDBMS

It's just that the requirements that need to be considered are not being considered.

You can also set permissions for the table, so I think it would be good to review them.

All backup and other perspectives are covered by AWS RDS and do not need to be considered.

When you need to restore Group A data, you can't use Group B, you can't use it, you can't rewind the data together, or you have to rewind it, and RDS doesn't work for you.

I don't think this is enough consideration.

Also, if you really don't have to consider anything other than the conditions mentioned in the question, it doesn't make sense to discuss the advantages or disadvantages, so it doesn't matter.


2022-09-30 20:22

If you have any answers or tips


© 2024 OneMinuteCode. All rights reserved.