[SQL] RPostgreSQL Win32 package
Hi! I'd like to use the statistics software GNU R (http://www.r-project.org/) as a PostgreSQL client. Currently I use RpgSQL but this package seems to be fitting better for smaller projects. As my project is about really big amounts of data, the package RPostgreSQL (http://code.google.com/p/rpostgresql/) seems to be a better choice. Unfortunately I only found the RPostgreSQL source but couldn't find a package for Win XP (32 bit). As my user rights are limited, I am also not able to compile the package myself. Therefore I wanted to kindly ask if somebody has already compiled RPostgreSQL for Win 32 and would be so nice to provide it as a an R package for me. Thank you in advance, Marcel Jira
Re: [SQL] RPostgreSQL Win32 package
En réponse à votre message, Jira, Marcel: Hi! 8>< Unfortunately I only found the RPostgreSQL source but couldn’t find a package for Win XP (32 bit). As my user rights are limited, I am also not able to compile the package myself. Therefore I wanted to kindly ask if somebody has already compiled RPostgreSQL for Win 32 and would be so nice to provide it as a an R package for me. Hi, I am not running Windows, but as you can see : http://cran.r-project.org/doc/manuals/R-admin.html#Windows-packages looks like you could build packages through this website: http://win-builder.r-project.org/ HTH, -- /\ /\ ( ''' ) (@) (@) >(·Y·)< __/`-'\__ (,,) T (,,) ===\[_]/=== «»-((_))-«» -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Handling mutliple clients access with views
Hello everyone, I am developping a web service where some tables are handling the data of different clients. Depending on configured rights, one client can have no access, or read access, or read and write access to other clients data. In order to handle that, I am using views and, to ensure that a client cannot access data outside the view, all clients info is stored in a table where its postgresql user name is also stored. So, in order to limit access of view, I am using the postgresql special function "current_user()" and I am retrieving the id of my client like this. My problem is that I am not sure this is a good idea in term of performance (which is a big issue in my case). My main fears are that : - The use of the"current_user()" function would slow the complete request - My method requests that each user has its own postgresql user. But, in this case, my web server needs to establish a postgresql connection for each user, which will maybe cause problems. So, I was guessing if I was not completely wrong by doing like that. Maybe is there a simpler way of doing what I try to do ? Or maybe am I a little bit too paranoïde, and maybe should I handle all my clients with a single postgresql user, handling all safety aspect in my php script ? The advantage of my method is that, even if there is a bug in my php script, if the db views are properly configured, a malicious client cannot alter the data ofanother client for which he does not have write access. But maybe is it not a good idea to handle this at db level ? Do you have any suggestion on those aspects? Regards, Brice André
Re: [SQL] new user on mac
On Thu, 2011-10-20 at 12:22 +0200, Guillaume Lelarge wrote: > On Wed, 2011-10-19 at 01:13 -0700, Basil Bourque wrote: > [...] > > I may have once had an inexplicable glitch, but after restart all was well. > > I've only been bitten by 2 recurring bugs: > > > > • (Cosmetic) Changing font size for use on projectors in a meeting makes > > fonts bigger, but the rows of the Output Pane in a SQL window fail to grow > > in height. > > > > Yeah, someone already reported this bug. It shouldn't be hard to fix. > You can always use the mousewheel in the query tool. The new setting > will be temporary but it should work for a meeting. > This is fixed. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] CTE or Subselect and outer joins not behaving as expected?
Hi all, I'm trying to enumerate a list of months between a date in the past and now and display a value from an existing table if there is one for the date or NULL if there isn't. I'm using this SQL to generate the months: select distinct date_trunc( 'month', '2011-06-01'::date + tally_day )::date as tally_mon from generate_series( 0, ( select current_date - '2011-06-01'::date ) ) as tally_day [[ produces ]] tally_mon 2011-06-01 2011-07-01 2011-08-01 2011-09-01 2011-10-01 (5 rows) and I am trying to use it as the left hand side of a left join against my data table: select tally_table. tally_mon, met.datum from ( select distinct date_trunc( 'month', '2011-06-01'::date + tally_day )::date as tally_mon from generate_series( 0, ( select current_date - '2011-06-01'::date ) ) as tally_day ) as tally_table full outer join my_existing_table as met on( tally_mon = met.month ) where met.series = 1; -- ** See SETUP below ** This produces rows only for those that exist in my_existing_table and no left join output which I'd expect for the rest of the tally_table rows. What am I missing? Many thanks, Joel SETUP: create temp table my_existing_table ( month date not null, series int not null, datum int not null ); insert into my_existing_table values ( '2011-08-01', 1, 5 ), ( '2011-10-01', 1, 4 ); -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] CTE or Subselect and outer joins not behaving as expected?
On Mon, Oct 24, 2011 at 5:46 PM, Joel Stevenson wrote: > Hi all, I'm trying to enumerate a list of months between a date in the past > and now and display a value from an existing table if there is one for the > date or NULL if there isn't. > > I'm using this SQL to generate the months: > > select distinct date_trunc( 'month', '2011-06-01'::date + tally_day )::date > as tally_mon > from generate_series( 0, ( select current_date - '2011-06-01'::date ) ) as > tally_day > > [[ produces ]] > tally_mon > > 2011-06-01 > 2011-07-01 > 2011-08-01 > 2011-09-01 > 2011-10-01 > (5 rows) > > and I am trying to use it as the left hand side of a left join against my > data table: > > select tally_table. tally_mon, met.datum > from ( > select distinct date_trunc( 'month', '2011-06-01'::date + tally_day )::date > as tally_mon > from generate_series( 0, ( select current_date - '2011-06-01'::date ) ) as > tally_day > ) as tally_table full outer join my_existing_table as met on( tally_mon = > met.month ) > where met.series = 1; > -- ** See SETUP below ** > > This produces rows only for those that exist in my_existing_table and no left > join output which I'd expect for the rest of the tally_table rows. > > What am I missing? > > Many thanks, > Joel > > SETUP: > create temp table my_existing_table ( month date not null, series int not > null, datum int not null ); > insert into my_existing_table values ( '2011-08-01', 1, 5 ), ( '2011-10-01', > 1, 4 ); UNION ALL should do it for you. Something along these lines should work select tally_mon as mon, max(datum) as datum from ( select distinct date_trunc( 'month', '2011-06-01'::date + tally_day )::date as tally_mon,0 as datum from generate_series( 0, ( select current_date - '2011-06-01'::date ) ) as tally_day union all select month as tally_mon,datum from my_existing_table ) as tally_table group by mon order by 1 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Handling mutliple clients access with views
On 25/10/11 03:23, Brice André wrote: > Hello everyone, > > I am developping a web service where some tables are handling the data > of different clients. Depending on configured rights, one client can > have no access, or read access, or read and write access to other > clients data. > > In order to handle that, I am using views and, to ensure that a client > cannot access data outside the view, all clients info is stored in a > table where its postgresql user name is also stored. So, in order to > limit access of view, I am using the postgresql special function > "current_user()" and I am retrieving the id of my client like this. That sounds ... roundabout. Why not use roles and role inheritance? You can use SET ROLE to temporarily change roles, log in as different roles, have one role be a member of other roles, have role access permissions on tables/views at the column or table level, etc. >- My method requests that each user has its own postgresql user. But, > in this case, my web server needs to establish a postgresql connection > for each user, which will maybe cause problems. Connect as a single user, then SET ROLE to the user you want in order to control access. Instead of using current_user() and programmatic security checking, use GRANT and REVOKE for declarative access checking where possible. > So, I was guessing if I was not completely wrong by doing like that. > Maybe is there a simpler way of doing what I try to do ? Or maybe am I a > little bit too paranoïde, and maybe should I handle all my clients with > a single postgresql user, handling all safety aspect in my php script ? Nope, I heartily approve of doing security in-database, especially if you can do it declaratively. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Handling mutliple clients access with views
On Oct 24, 2011, at 22:54, Craig Ringer wrote: > On 25/10/11 03:23, Brice André wrote: >> Hello everyone, >> >> I am developping a web service where some tables are handling the data >> of different clients. Depending on configured rights, one client can >> have no access, or read access, or read and write access to other >> clients data. >> >> In order to handle that, I am using views and, to ensure that a client >> cannot access data outside the view, all clients info is stored in a >> table where its postgresql user name is also stored. So, in order to >> limit access of view, I am using the postgresql special function >> "current_user()" and I am retrieving the id of my client like this. > > That sounds ... roundabout. > > Why not use roles and role inheritance? You can use SET ROLE to > temporarily change roles, log in as different roles, have one role be a > member of other roles, have role access permissions on tables/views at > the column or table level, etc. > >> - My method requests that each user has its own postgresql user. But, >> in this case, my web server needs to establish a postgresql connection >> for each user, which will maybe cause problems. > > Connect as a single user, then SET ROLE to the user you want in order to > control access. > > Instead of using current_user() and programmatic security checking, use > GRANT and REVOKE for declarative access checking where possible. > >> So, I was guessing if I was not completely wrong by doing like that. >> Maybe is there a simpler way of doing what I try to do ? Or maybe am I a >> little bit too paranoïde, and maybe should I handle all my clients with >> a single postgresql user, handling all safety aspect in my php script ? > > Nope, I heartily approve of doing security in-database, especially if > you can do it declaratively. > > -- > Craig Ringer > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql Except database roles cannot strictly enforce row-level security (i.e., multiple-tenant) which is the goal of this setup. Views are not fool-proof in providing row-level security, for that you need functions. While a view itself will not provide the protected data a function can be used to process data (via RAISE NOTICE) that would otherwise be filtered out in the end result. This is because views are simply re-write rules. David J. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Handling mutliple clients access with views
On 25/10/11 11:36, David Johnston wrote: > Except database roles cannot strictly enforce row-level security (i.e., > multiple-tenant) which is the goal of this setup. Ah, yes, if theyr'e relying on _row_ level security then that's very much the case. I misread their post as suggesting that they had different tables for different clients, rather than tables that mix different clients' data. Declarative row-level security (row ownership) would be really nice... here's hoping the SELinux work can be extended to support a simpler, OS-agnostic non-SELinux-based row-level RBAC mechanism. I'd still use SET ROLE where possible, and rely on SECURITY DEFINER stored procs in cases where clients' data is mixed in a table so you need to filter it programmatically. Then I'd wrap those procs in a view so they were transparent and looked like tables, so once row-level security is implemented you could switch to that without the app caring. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Handling mutliple clients access with views
On Mon, Oct 24, 2011 at 8:50 PM, Craig Ringer wrote: > > Declarative row-level security (row ownership) would be really nice... > here's hoping the SELinux work can be extended to support a simpler, > OS-agnostic non-SELinux-based row-level RBAC mechanism. > -- > Craig Ringer Veil might do what you mention above. http://veil.projects.postgresql.org/curdocs/index.html -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
