CODEWITHSUNDEEP
postgresql
Tamar
Tamar

Reputation: 379

Separate and restrict data access using schemas

I am an Oracle DBA, new to PostgreSQL. I have a requirement to separate some modules data, so that one module will not have access to read the data of another module. I read about the use of schemas in PostgreSql which is somewhat different than the use of it in Oracle. However seems like it is recommended to use the method of different schemas for separation and management - just like in Oracle. However, when I create 2 schemas - connected to the same database and same user - I can do a select from the other schema's table. That is, for example, if I have schema A owning table a, and schema B owning table b, when I set the search path to schema B I can do a select on schema’s A table a without any problem. I couldn't find a way to revoke this privilege from schema B. The only I could find then to separate access to data, is using different roles, that is to create role A with schema A, and role B with schema B. then I can grant and revoke access from user B in order for it to see what I want from role's A tables.

Is this correct? Am I missing something?

Upvotes: 0

Views: 1110

Answers (1)

user330315
user330315

Reputation:

if I have schema A owning table a

A schema does not "own" a table in Postgres - a user does. This is the same as in Oracle - the difference (and maybe where your confusion arises) is that in Oracle in a regular user can't create tables outside of a schema that has the same name as the user account.

So if user arthur creates a table in schema_a and one in schema_b, both tables are owned by arthur - not "the schema".

If you used different schemas/users in Oracle to separate data and to prevent access to the other user's tables, then you need two users and two schemas in Postgres as well.

The default search_path in Postgres is setup in a way, that (unqualified) tables are always first searched (and created) in a schema with the same name as the user running the statement.

So if you create two users and a schema with the user's name for each user, you pretty much have the same setup as in Oracle:

create user arthur with password 'foobar';
create schema arthur authorization arthur; --<< this is what Oracle does "automatically"
create user bob with password 'verysecret';
create schema bob authorization bob;

Now, if bob creates a table, that table is created in the schema bob and is owned by the user bob. User arthur has not privileges to access that table.

If you never need to share data between those users (or schemas), then you can create two databases, create two users and let both users create everything in the public schema of "their" database.

Upvotes: 1

Related Questions