On Tue, Aug 12, 2014 at 8:33 AM, Bill Moran <wmo...@potentialtech.com> wrote:
> On Tue, 12 Aug 2014 16:57:32 +0200 > Jimmy Thrasibule <thrasibule.ji...@gmail.com> wrote: > > > > Is there a reason why hstore or json is not an option? That may work > a lot > > > better than this approach. > > > > I don't want to move away from SQL common features so I can have a > > test environment using SQLite and deploy on PostgreSQL. This approach > > looks elegant and simple to me. Using a new table per attribute type > > or even per attribute for the values, I can easily index them for > > quick lookups. > > Couple of things to keep in mind: > * SQLLite and PostgreSQL are not 100% compatable. It's cheap and easy to > set up PostgreSQL for testing/development, so it seems silly (to me) to > test > on something that might behave differently than the production environment. > > * Any setup where the application can execute DDL is a potential security > concern. If the code can manipulate tables then a bug in the code can > allow an accidental or malicious user to quickly and easily destroy data. > This is a good point I probably should have mentioned specifically. Making my solution secure means restricting access to the catalog and catalog to DDL features to a very small subset of trusted users (ideally not through a standard application connection). In this case, one might as well restrict it to those who are already db superusers unless there is a clear need to broaden it. BTW, a specific case in point... DDL can't be parameterized since it doesn't have a query plan. This means you are doing string concatenation to create your ddl queries. If you aren't careful someone can add an attribute like: '; DROP TABLE things; -- In practice this means a great deal of manual review and restriction on which users can access this feature. Best Wishes, Chris Travers > > -- > Bill Moran > I need your help to succeed: > http://gamesbybill.com > -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more
