Re: Migrating to postgresql from oracle

On Sat, Dec 23, 2017, at 21:05, Pavel Stehule wrote:
> Hi
>
> 2017-12-23 19:53 GMT+01:00 Timo Myyrä <timo(dot)myyra(at)bittivirhe(dot)fi>:
>> Hi,
>>
>> I'm preparing migration of our asset management system database from
>> Oracle 12c to>> PostgreSQL 10. I'm using ora2pg and a bit of sed to mangle the SQL
>> ready for>> import to pg but I've hit first problem:
>> ERROR: referenced relation "..." is not a table
>>
>> Our Oracle databases uses single 'admin' schema and dedicated schema
>> for each>> customer. For example usergroup mappings are held in admin.usergroup
>> table. Then>> admin schema has customer-specific view on this table
>> admin.usergroup_customer1>> view which limits the full view to just those of that customer. And
>> then each>> customers own schema has synonym to that view like
>> customer1.usergroup. The>> applications queries use the "usergroup" table to query the group
>> mappings.>>
>> Here's hopefully a bit more detailed description of the database
>> structure:>>
>> ADMIN SCHEMA:
>> TABLES:
>> usergroup
>> unit
>> user
>> ...
>> VIEWS:
>> usergroup_customer1
>> unit_customer1
>> user_customer1
>> ...
>> CUSTOMER1 SCHEMA:
>> TABLES:
>> resource
>> ...
>> SYNONYM:
>> usergroup (refers to admin.usergroup_customer1)
>> user (refers to admin.user_customer1)
>> unit (refers to admin.unit_customer1)
>> ...
>>
>> So it seems that postgresql doesn't support foreign keys in views
>> like Oracle.>> Would you have any suggestions how the above Oracle structure would
>> best be>> handled in PostgreSQL? I'm pretty new to PostgreSQL so I might
>> overlook>> something if I try to solve this by myself.
>
> try to ask on https://github.com/darold/ora2pg/issues
>
> Regard
>
> Pavel
>
>
>>
>> Timo

Well, I doubt postgresql limitations apply for ora2pg.

I looked this a bit more closely. Postgresql seems to have nice feature
which would apply to this quite nicely called row-level security. I
like to limit the 'admin' schema table rows so that each user sees only
their own rows.
So a bit more about the table structure is needed to understand the
situation.Lets take admin.unit table, this holds the business units of each
customer. The import rows are id and parent_id. Parent_id field might
refer to unit id in the same unit table so units can be nested. The top-
level unit doesn't have parent_id. The admin.unit.id field gives the top-
level id, then theres the admin.project_unit table which has 2 fields,
project_id, unit_id which gives mapping to project. Finally we have
project table which has the actual project id and name which map to
logged in project.
I was testing the row-level security with following query but it gives
syntax error for some reason:
CREATE POLICY unit_customer1 ON admin.unit
FOR ALL
TO customer1
USING ( with recursive e(id,parent_id) as (
select id, parent_id
from admin.bg_unit
where id in (select unit_id from admin.project_unit where
project_id = 'customer1') union all
select f.id, f.parent_id
from admin.bg_unit f, e
where e.id = f.parent_id
) select id from e order by id asc
);

When I run the above query I get just:
ERROR: syntax error at or near "with"
LINE 4: USING ( with recursive e(id,parent_id) as (

Can the above policy made to work for my use-case or am I doing
something fundamentally wrong?
Timo