I don't currently have easy access to a PG 9.5 box, but I think the
following would work:
CREATE TABLE customer_orders (customer_id integer, order_description text);
ALTER TABLE customer_orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY customer_policy ON customer_orders
USING (true)
WITH CHECK (myvars.customer_id = customer_orders.customer_id);
Then in a before_filter on your controllers you use something similar to
the following SQL to set the restriction:
SET myvars.customer_id = 10;
See: http://www.postgresql.org/docs/9.5/static/ddl-rowsecurity.html and
http://stackoverflow.com/questions/28047911/how-to-set-some-context-variable-for-a-user-connection
Also, pro tip: you're more likely to get help if you don't demand a premade
solution and and instead demonstrate that you've already researched this
yourself. Google is your friend, but you don't really seem to have used it.
On Sat, Dec 12, 2015 at 11:57 AM, <[email protected]> wrote:
> well of thats the case please explain me a practical proved implementation
>
> Sent from my iPhone
>
> On 12 Dec 2015, at 17:15, James Coleman <[email protected]> wrote:
>
> Allowing a connection pool per client is definitely a wrong way of do
> this, given the high overhead of PG backends and connection limits.
>
> I believe there are other ways to accomplish this even with row level
> security. For example, you could set a per-connection variable in PG on
> connection checkout and have your row security policies check that variable
> rather than the current DB user. Not only will this give you far greater
> performance, it will also be far more versatile.
>
> The only time you should have different DB users per customer is if you're
> running a separate app layer instance(s) per customer. Otherwise you're
> creating a flawed design that will bit you later on. Database users exist
> to restrict at a lower level than multi-tenant-per-app policies; they're
> about securing the database for different use cases (such as some apps only
> needing to write to certain tables, or a reporting user/app only having
> read access, etc.) I believe you're misunderstanding the purpose of
> database level users.
>
> On Sat, Dec 12, 2015 at 11:00 AM, <[email protected]> wrote:
>
>> Have you ever manage to get this this to work? Even when override from
>> the current connection handler i can't seem to get the right behavior oob.
>>
>> On Thursday, December 10, 2015 at 9:06:55 PM UTC+1, Olly Legg wrote:
>>>
>>> - Make it possible to make connection handler pluggable. I've tried to
>>> do this, but documentation is lacking and i can't seem to get it work.
>>>
>>>
>>> The connection handler is already pluggable. You can configure it like
>>> so:
>>>
>>> config.active_record.connection_handler = MyConnectionHandler.new
>>>
>>> - Make it possible to create connection pools based on specs.
>>>
>>>
>>> I believe that it’s already possible to create connection pools based
>>> with a ConnectionSpecification.
>>> https://github.com/rails/rails/blob/v4.2.3/activerecord/lib/active_record/connection_adapters/abstract/connection_pool.rb#L227-L252
>>>
>>>
>>> --
>> You received this message because you are subscribed to the Google Groups
>> "Ruby on Rails: Core" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to [email protected].
>> To post to this group, send email to [email protected].
>> Visit this group at https://groups.google.com/group/rubyonrails-core.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
> --
> You received this message because you are subscribed to a topic in the
> Google Groups "Ruby on Rails: Core" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/rubyonrails-core/X5JS8eV6Ddo/unsubscribe
> .
> To unsubscribe from this group and all its topics, send an email to
> [email protected].
> To post to this group, send email to [email protected].
> Visit this group at https://groups.google.com/group/rubyonrails-core.
> For more options, visit https://groups.google.com/d/optout.
>
> --
> You received this message because you are subscribed to the Google Groups
> "Ruby on Rails: Core" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected].
> To post to this group, send email to [email protected].
> Visit this group at https://groups.google.com/group/rubyonrails-core.
> For more options, visit https://groups.google.com/d/optout.
>
--
You received this message because you are subscribed to the Google Groups "Ruby
on Rails: Core" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/rubyonrails-core.
For more options, visit https://groups.google.com/d/optout.
