One database and several users with different access rights

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

One database and several users with different access rights

Ralph Shnelvar
I have a database with a large table.

I have several users each of whom should have read-only rights to the large table but has read/wrtie rights to their own table(s) but not to each others table(s).

Adding to the complication, I want to give each RoR user the ability to write their own SQL statements against the large table as well as their own table(s).  I have successfully implemented being able to have them enter sql statements and create results they can view and/or download.  Doing that is not my question.

I want to make sure each of my "readonly" users can't modify any tables they are not authorized to see and/or change.

So,I guess, I want to change Postgres roles within Rails.  Any guidance would, of course, be appreciated.


--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/c46745f6-a835-440a-bfa7-447c03223dcd%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: One database and several users with different access rights

David Gleba

I use devise and cancancan for login and roles.

In my little rails app generator project: https://github.com/dgleba/bashrail  there are scripts for this. Even if you don't use the scripts, they document exactly how to implement it in that case.

look at:  

When I am learning something like this I strip the scripts down to just what is needed to create an app with the feature I am studing.

I am not sure about the  "give each RoR user the ability to write their own SQL statements ".
Is that just a model/view/controller in your app? If so, the above should work for that too.

Can you share how you did the "give each RoR user the ability to write their own SQL statements "? I may want to do the same.



On Tuesday, July 11, 2017 at 11:39:13 PM UTC-4, Ralph Shnelvar wrote:
I have a database with a large table.

I have several users each of whom should have read-only rights to the large table but has read/wrtie rights to their own table(s) but not to each others table(s).

Adding to the complication, I want to give each RoR user the ability to write their own SQL statements against the large table as well as their own table(s).  I have successfully implemented being able to have them enter sql statements and create results they can view and/or download.  Doing that is not my question.

I want to make sure each of my "readonly" users can't modify any tables they are not authorized to see and/or change.

So,I guess, I want to change Postgres roles within Rails.  Any guidance would, of course, be appreciated.


--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/d00f4681-e13c-4d0f-af07-bc4835467849%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: One database and several users with different access rights

Ralph Shnelvar
David,

I looked through your code and I'm not sure you have answered my question.

Let me try to clarify.  There is a difference between a Devise role and a Postgres role.  A devise role will control access to Rails functionality.  For example, one can restrict access to a Rails controller method using Devise.

A Postgres role ( https://www.postgresql.org/docs/current/static/database-roles.html ) is a completely different animal. I quote and modify what is written there:
Database roles are conceptually completely separate from operating system [and Devise!] users.

I _think_ I need to do something like "sudo -i -u SomeOtherPostgresUserName" but I want to do it inside Rails so I can connect to databases as user SomeOtherPostgresUserName.


Can you share how you did the "give each RoR user the ability to write their own SQL statements "? I may want to do the same.

Here is the code I use.  RalphSql.exec_sql is what you want.  RalphSql.get_column_names_from_table is a function I find useful for other obvious purposes.
class RalphSql
 
def self.exec_sql(sql_text)
   
begin
     
# Return an array of records
     
return ActiveRecord::Base.connection.execute(sql_text)
   
rescue Exception => e
      byebug
if ralph_test_byebug
     
raise e
   
end
 
end

 
# See http://www.rubydoc.info/gems/pg/PG/Result
 
def self.get_column_names_from_table(postgres_table_name)
    sql
= %Q[SELECT column_name FROM information_schema.columns WHERE table_name = '#{postgres_table_name}';]
   
# byebug if ralph_test_byebug
    pg_result
= RalphSql.exec_sql(sql)  # pg_result.class == PG::Result
    ret
= pg_result.values
   
# byebug if ralph_test_byebug
    ret
 
end


end


I use devise and cancancan for login and roles.

I use pundit



On Wednesday, July 12, 2017 at 4:51:32 AM UTC-6, David Gleba wrote:

I use devise and cancancan for login and roles.

In my little rails app generator project: <a href="https://github.com/dgleba/bashrail" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fdgleba%2Fbashrail\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHNUqqxmlMrAnHeDDhN0Sm_ooe61Q&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fdgleba%2Fbashrail\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHNUqqxmlMrAnHeDDhN0Sm_ooe61Q&#39;;return true;">https://github.com/dgleba/bashrail  there are scripts for this. Even if you don't use the scripts, they document exactly how to implement it in that case.

look at:  
<a href="https://github.com/dgleba/bashrail/blob/master/devise1.sh" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fdgleba%2Fbashrail%2Fblob%2Fmaster%2Fdevise1.sh\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHwiX-nv56tiC3K1xeWOf2o2UfXEA&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fdgleba%2Fbashrail%2Fblob%2Fmaster%2Fdevise1.sh\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHwiX-nv56tiC3K1xeWOf2o2UfXEA&#39;;return true;">https://github.com/dgleba/bashrail/blob/master/devise1.sh
<a href="https://github.com/dgleba/bashrail/blob/master/cancan1.sh" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fdgleba%2Fbashrail%2Fblob%2Fmaster%2Fcancan1.sh\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFfOhAS5SYDJm1TCxilErDCTcXQ0w&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fdgleba%2Fbashrail%2Fblob%2Fmaster%2Fcancan1.sh\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFfOhAS5SYDJm1TCxilErDCTcXQ0w&#39;;return true;">https://github.com/dgleba/bashrail/blob/master/cancan1.sh

When I am learning something like this I strip the scripts down to just what is needed to create an app with the feature I am studing.

I am not sure about the  "give each RoR user the ability to write their own SQL statements ".
Is that just a model/view/controller in your app? If so, the above should work for that too.

Can you share how you did the "give each RoR user the ability to write their own SQL statements "? I may want to do the same.



On Tuesday, July 11, 2017 at 11:39:13 PM UTC-4, Ralph Shnelvar wrote:
I have a database with a large table.

I have several users each of whom should have read-only rights to the large table but has read/wrtie rights to their own table(s) but not to each others table(s).

Adding to the complication, I want to give each RoR user the ability to write their own SQL statements against the large table as well as their own table(s).  I have successfully implemented being able to have them enter sql statements and create results they can view and/or download.  Doing that is not my question.

I want to make sure each of my "readonly" users can't modify any tables they are not authorized to see and/or change.

So,I guess, I want to change Postgres roles within Rails.  Any guidance would, of course, be appreciated.


--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/91a23720-7b73-4349-94aa-25d9d9da1e5b%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: One database and several users with different access rights

Hassan Schroeder-2
On Wed, Jul 12, 2017 at 6:33 AM, Ralph Shnelvar <[hidden email]> wrote:

> I _think_ I need to do something like "sudo -i -u SomeOtherPostgresUserName"
> but I want to do it inside Rails so I can connect to databases as user
> SomeOtherPostgresUserName.

I would look at

  ActiveRecord::Base.establish_connection()

which accepts either an atom representing an entry in your
config/database.yml or a hash with DB login credentials.

Aside:

> class RalphSql
>   def self.exec_sql(sql_text)
>     begin
>       # Return an array of records
>       return ActiveRecord::Base.connection.execute(sql_text)

I hope you really really REALLY trust the input from your users
and do very frequent backups :-)

HTH!
--
Hassan Schroeder ------------------------ [hidden email]
twitter: @hassan
Consulting Availability : Silicon Valley or remote

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/CACmC4yAVE1xS9wrdULocr-aebsGqEqq_-nqXzQ_eY7s0aEsbAw%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: One database and several users with different access rights

Ralph Shnelvar
What's the convention here: top posting or bottom posting?

Ralph


On Wednesday, July 12, 2017 at 7:52:06 AM UTC-6, Hassan Schroeder wrote:
On Wed, Jul 12, 2017 at 6:33 AM, Ralph Shnelvar <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="jkFbrt3PBgAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">ral...@...> wrote:

> I _think_ I need to do something like "sudo -i -u SomeOtherPostgresUserName"
> but I want to do it inside Rails so I can connect to databases as user
> SomeOtherPostgresUserName.

I would look at

  ActiveRecord::Base.establish_connection()

which accepts either an atom representing an entry in your
config/database.yml or a hash with DB login credentials.

Aside:

> class RalphSql
>   def self.exec_sql(sql_text)
>     begin
>       # Return an array of records
>       return ActiveRecord::Base.connection.execute(sql_text)

I hope you really really REALLY trust the input from your users
and do very frequent backups :-)

HTH!
--
Hassan Schroeder ------------------------ <a href="javascript:" target="_blank" gdf-obfuscated-mailto="jkFbrt3PBgAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">hassan.s...@...
twitter: @hassan
Consulting Availability : Silicon Valley or remote

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/a2db5d66-4ff1-4eb1-ba42-8ebf11954004%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: One database and several users with different access rights

Colin Law
On 12 July 2017 at 15:13, Ralph Shnelvar <[hidden email]> wrote:
> What's the convention here: top posting or bottom posting?

Whatever is appropriate for the message at hand. When making a number
of points wrt previous post then obviously inline posting is
preferable.  Well it's obvious to me anyway.

Colin

>
> Ralph
>
>
> On Wednesday, July 12, 2017 at 7:52:06 AM UTC-6, Hassan Schroeder wrote:
>>
>> On Wed, Jul 12, 2017 at 6:33 AM, Ralph Shnelvar <[hidden email]> wrote:
>>
>> > I _think_ I need to do something like "sudo -i -u
>> > SomeOtherPostgresUserName"
>> > but I want to do it inside Rails so I can connect to databases as user
>> > SomeOtherPostgresUserName.
>>
>> I would look at
>>
>>   ActiveRecord::Base.establish_connection()
>>
>> which accepts either an atom representing an entry in your
>> config/database.yml or a hash with DB login credentials.
>>
>> Aside:
>>
>> > class RalphSql
>> >   def self.exec_sql(sql_text)
>> >     begin
>> >       # Return an array of records
>> >       return ActiveRecord::Base.connection.execute(sql_text)
>>
>> I hope you really really REALLY trust the input from your users
>> and do very frequent backups :-)
>>
>> HTH!
>> --
>> Hassan Schroeder ------------------------ [hidden email]
>> twitter: @hassan
>> Consulting Availability : Silicon Valley or remote
>
> --
> You received this message because you are subscribed to the Google Groups
> "Ruby on Rails: Talk" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [hidden email].
> To post to this group, send email to [hidden email].
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/rubyonrails-talk/a2db5d66-4ff1-4eb1-ba42-8ebf11954004%40googlegroups.com.
>
> 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: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/CAL%3D0gLttndt3DFhL9fBtwXXzx-9PsXQwBgLepEiGCffEgQPEyA%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: One database and several users with different access rights

Matt Jones-15
In reply to this post by Ralph Shnelvar


On Tuesday, 11 July 2017 23:39:13 UTC-4, Ralph Shnelvar wrote:
I have a database with a large table.

I have several users each of whom should have read-only rights to the large table but has read/wrtie rights to their own table(s) but not to each others table(s).

Adding to the complication, I want to give each RoR user the ability to write their own SQL statements against the large table as well as their own table(s).  I have successfully implemented being able to have them enter sql statements and create results they can view and/or download.  Doing that is not my question.

I want to make sure each of my "readonly" users can't modify any tables they are not authorized to see and/or change.

So,I guess, I want to change Postgres roles within Rails.  Any guidance would, of course, be appreciated.

I've never used it, but something like "SET SESSION AUTHORIZATION"  looks like it would do what you want, mostly:

https://www.postgresql.org/docs/current/static/sql-set-session-authorization.html

You'd set up a "superuser" in config/database.yml and then change to the lower-privileged specific user per-request.

Some potential problems:

* the lower-privileged users *must* not have sufficient permissions to call "SET SESSION AUTHORIZATION" themselves or the security is an illusion

* you'll want to make 100% certain that connections get a "RESET SESSION AUTHORIZATION", or a selected user's authorization will leak into the next request (and fail, see the previous point)

* you'll need to somehow sanitize the incoming SQL to remove queries like "RESET SESSION AUTHORIZATION; DROP TABLE all_the_things" or the security is an illusion

I noticed you mentioned "their own tables" above; if you're already committed to solutions where adding users is complex, you might want to think about separating things further. You could use a tool like pglogical:

https://www.2ndquadrant.com/en/resources/pglogical/

To replicate only the large table to per-user Postgres DBs. Definitely NOT an appropriate solution for multi-tenancy with lots of users, but neither is table-per-user.

--Matt Jones

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/af2f5066-1ac6-4971-98e8-2acbfeb0be31%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Loading...