Querying from the partition?

[mwildehahn]

architect is creating the partitions correctly, but how can I query the partition directly if I'm using Django?

Also, is there a way to delete the records from the parent table after inserting them into the child table?

[maxtepkeev]

I'm assuming you're talking about PostgreSQL, so:

how can I query the partition directly if I'm using Django?

You don't need to do that, just execute your query and specify a correct where clause and database will automatically query only the needed partition. But if, for some reason, you want to do that manually, you need to write a raw query to the database.

is there a way to delete the records from the parent table after inserting them into the child table

There are no records inside the parent table, you see them here because they are inherited from all the child tables, but if you inserted them here before the table was partitioned you can delete them using the DELETE FROM ONLY parent_table query.

[mwildehahn]

Yea, I closed the issue as I read up more on your code and how Postgres handles partitions. I want to restrict access to certain partitions and am looking at ways that I can do that.

Thanks! On Sun, Aug 2, 2015 at 2:15 AM Max Tepkeev notifications@github.com wrote:

I'm assuming you're talking about PostgreSQL, so:

how can I query the partition directly if I'm using Django?

You don't need to do that, just execute your query and specify a correct where clause and database will automatically query only the needed partition. But if, for some reason, you want to do that manually, you need to write a raw query to the database.

is there a way to delete the records from the parent table after inserting them into the child table

There are no records inside the parent table, you see them here because they are inherited from all the child tables, but if you inserted them here before the table was partitioned you can delete them using the DELETE FROM ONLY parent_table query.

— Reply to this email directly or view it on GitHub https://github.com/maxtepkeev/architect/issues/15#issuecomment-127002184 .

[maxtepkeev]

Can you elaborate a little bit on the restriction logic ? I mean, partitions are created automatically depending on the value of the partition and the settings you specified, so how would you know if this partition should be forbidden to access or not, also do you want to restrict access on the database level or Django level ?

I know you closed the issue, but this is an interesting use case, so this is why I'm curious about the details.

[mwildehahn]

I have the concept of an organization in my schema. I want to ensure that access to an organization's data is only available to someone with a token that contains the correct organization_id, so all relevant tables have an organization_id column. Currently I just make sure that all queries include the organization_id, but I was hoping for something at the database layer to avoid future human error. Ensuring that data never leaks between organizations is the priority.

One thing I was trying was if I could create a new partition per organization_id and then restrict access to that partition. I was hoping for something where if a read was attempted it would only read from an available partition, if not error. But I read up more on table inheritance and understand what queries against the parent table are doing.

I'd love to hear any ideas you have around this issue! On Sun, Aug 2, 2015 at 2:24 AM Max Tepkeev notifications@github.com wrote:

Can you elaborate a little bit on the restriction logic ? I mean, partitions are created automatically depending on the value of the partition and the settings you specified, so how would you know if this partition should be forbidden to access or not, also do you want to restrict access on the database level or Django level ?

I know you closed the issue, but this is an interesting use case, so this is why I'm curious about the details.

— Reply to this email directly or view it on GitHub https://github.com/maxtepkeev/architect/issues/15#issuecomment-127004866 .

[maxtepkeev]

I like your idea about creating a partition per organization id. Because a partition is just a table we can use one of the following approaches to restrict access to it on a database level:

1) Create a user/password per organization and only allow them to access their respective table (probably not very convenient if you will have thousands of organizations) 2) PostgreSQL 9.5. will introduce an ability to create a policy for a table which looks like exactly what you're looking for, see docs. But unfortunately it's still in alpha if I'm not mistaken.

These are the only things that come to mind.

You can also think about implementing your security layer in your application instead of a database because I don't think that you will give your organizations a direct access to your database. That would be a much simpler solution.

[mwildehahn]

Yea, those are both points I was considering.

I'm going to go with the application layer security until Postgres 9.5 is released.

Thanks! On Mon, Aug 3, 2015 at 7:01 AM Max Tepkeev notifications@github.com wrote:

I like your idea about creating a partition per organization id. Because a partition is just a table we can use one of the following approaches to restrict access to it on a database level:

1) Create a user/password per organization and only allow them to access their respective table (probably not very convenient if you will have thousands of organizations) 2) PostgreSQL 9.5. will introduce an ability to create a policy for a table which looks like exactly what you're looking for, see docs http://www.postgresql.org/docs/9.5/static/sql-createpolicy.html. But unfortunately it's still in alpha if I'm not mistaken.

These are the only things that comes to mind.

You can also think about implementing your security layer in your application instead of a database because I don't think that you will give your organizations a direct access to your database. That would be a much simpler solution.

— Reply to this email directly or view it on GitHub https://github.com/maxtepkeev/architect/issues/15#issuecomment-127248079 .