Re: [SQL] Handling mutliple clients access with views
Thanks very much for those info.
>From what I read, I think Veil will do the trick.
I already use ROLES, but there are a few probems with them :
- first, as I mix the data from different users in the same table, ROLES
do not solve the complete problem.
- second, this forces me to use a dedicated postgresql connection for
each user, which on a large accessed database, could be a big problem.
If what I read from Veil is exact, it allows determining access rules from
SELECT statements, which is what I am currently performing with the
"current_user()". A typical view in my system is declared like this :
CREATE VIEW "AgendaCurrentlyDisplayedReadableView" AS
SELECT "AgendaAccessRights"."AgendaID" FROM "AgendaAccessRights" WHERE
(("AgendaAccessRights"."ClientID" = (SELECT "Clients"."ID" FROM "Clients"
WHERE "Clients"."Login" = "current_user"())) AND
("AgendaAccessRights"."IsDisplayed" = true));
My understanding is that veil will allow me to perform stuff like that, but
without using the "current_user()" stuff. And, if I still understand
correctly, the Veil serialisation functions will allow me to quickly switch
from one user to the other with keeping the same database connection.
Am I right or is there something I did not understood ?
Second question : does Veil has a big impact on database performances ?
Regards,
Brice
2011/10/25 bricklen
> 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
>
Re: [SQL] Problem with DROP ROLE
You can't do that in THAT way, but you can use dynamic SQL: test=# select * from drop_role ; t foobar (1 row) test=*# do $$ declare r text; begin for r in select t from drop_role loop execute 'drop role ' || quote_ident(r) || ';'; end loop; end; $$language plpgsql; DO "Brice André" hat am 19. Oktober 2011 um 12:11 geschrieben: > Hello everyone, > > I would want to implement an SQL query where I would be able to suppress all > information from a registered user. I am currenlty able to suppress everything > except the user role. The name of the role is present in a table and so, I > would want to perform something like this : > DROP ROLE (SELECT ...) > but this is not considered as valid, as DROP ROLE is expecting a name and not > a text field. So, I tried the following, but with no success : > DROP ROLE CAST((SELECT...) AS name) > > So, does someone knows how to handle this problem ? > > Regards, > Brice > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] How to write sql to access another odbc source.
I have a mumps database with an ODBC connection so I can write queries from other databases. How can I write a sql in Postgresql to access this table to use in my Postgresql reports?
[SQL] Question about Escaping text when calling pgplsql functions
I have a situation where data in a text column contains line breaks of the form \r\n if I run a select such as select count(1) from table where wordcol = 'word1\r\nword2' I get 0. Same if I run select count(1) from table where wordcol = 'word1\nword2'. I get values if I run select count(1) from table where wordcol=E'word1\r\nword2' I get a value (18 in this case). This is fine if I've got the literal string. The issue I have is that I'm calling a function: create or replace function escapereadertest(word text) returns int as $$ declare retval int; begin select count(1) from table where wordcol = word into retval; return retval; end;$$ language 'plpgsql'; I haven't found a way to escape the word variable. I'm calling this function from java via callable statement like this: call escapereadertest(?) and setting the parameter to a string that contains "word1\r\nword2"; Calling it this way gets me 0 as well. I'd like to get 18. Is there any way of escaping the word variable inside the function or of pre-escaping the value in java before sending it on or do I have to dynamically build the sql to get the E prefixed to the word as a quoted literal. I've looked at the string functions but haven't found what I'm looking for - I maybe looking in the wrong place. Thanks for any ideas. Matthias
Re: [SQL] advice on how to store variable attributes
On Oct 22, 2011, at 10:07, Pavel Stehule wrote: > 2011/10/22 David Johnston : >> On Oct 22, 2011, at 6:41, Linos wrote: >> >>> Hi all, >>>i need a little of advice on what could be the best way to store this >>> information. >>> >>> We need to calculate the difference in costs for our operations, we are >>> already >>> storing our vendor invoices in the database so calculate the monetary >>> change it >>> is a no-brainer but we need to store special attributes for any of the >>> invoices >>> that we need to compare too, for example: >>>-electric provider: total Kw. >>>-water provider: total m3. >>>-car maintenance: kilometers of the car. >>>-mobile phones provider: international call minutes, national minutes, >>> number >>> of sms, etc.. >>> >>> And much more types/variables, the number of variables can change, not >>> every day >>> but still can change, i would like that they can be defined/changed from our >>> application, so alter table to add columns don't seem the best way (still an >>> option though). We will have "generic" reports that will show us changes in >>> costs and specific reports for the types with "extended attributes" that we >>> want >>> to compare. >>> >>> To compare values from this "extended attributes" i think we have two ways: >>>1- have them in columns and use standard SQL. >>>2- create the columns with a function that reads this attrs and create >>> the columns. >>> >>> So far i thin we have this options: >>>1- a bunch of columns that would be null except when the type of the >>> invoice >>> uses them. >>>2- a table related with the vendor invoices table for every type of >>> invoice >>> with his specifics columns. >>>3- a key/value in a separate table related with the vendor invoices >>> table where >>> i store the extended attrs of every invoice that needs them. >>>4- use a hstore column in the vendor invoces table to store this attrs. >>> >>> The first two have the problem of probably changes to the number of >>> attributes >>> of every type and give a more closed solution, apart from that 1- seems to >>> be a >>> bit awkward and 2- would need the application that creates the query to know >>> with what table should join for every type (other point we will need to >>> change >>> if we want to create new invoices types). >>> >>> The last two have his own problems too, with 3 i will need to create a >>> function >>> that return rows as columns to compare them, with 4- given that i will >>> store the >>> attrs of every type in the database anyway i can use the operator -> (with a >>> CASE using operator ? returning 0 if the searched attr it is not in the >>> hstore) >>> but still don't seem a clean solution for me. >>> >>> For me it seems i am missing something, probably any of you have a much more >>> elegant (or correct) way to handle this situation, what would be your >>> advice? >>> Thanks. >>> >>> >> >> Create a table with a single numeric column and multiple category columns. >> >> ( amount_value, amount_unit, amount_category, vendor_id ) > > > This is EAV model - is good for smaller datasets, for larger datasets > is problematic. There is second possibility - using a "hstore" contrib > module - that emulates HASH table - It has better for larger datasets. > > Regards > > Pavel Stehule Store was mentioned by the OP. Any suggestions on where the line between small and large is drawn? Partitions could help in the larger cases. My personal first choice is to use separate tables. If going EAV route plan on eventually moving to the separate table route and at least try to make migration relatively easy. Since both models capture the same data the decision at least partially rests upon the immediate use-cases for the data. Lacking use-cases and size parameters recommendations are difficult to make. I have not used hstore so my ability to recommend it is limited. My main "concern" is the fact that the data is less visible and always stored as text. I see hstore as being useful for situations where the attributes are variable but this use case seems to have fairly well-defined attributes. David J. > >> >> If necessary each "amount_value" data type should have it's own table since >> the processing logic will vary (I.e., you cannot subtract text or Boolean >> values). >> >> You are , in effect, creating multiple tables but combining them into one >> and using the category column to distinguish between them. >> >> 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 >> > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sq
Re: [SQL] How to write sql to access another odbc source.
On 19/10/11 22:08, Rich wrote: > I have a mumps database with an ODBC connection so I can write queries > from other databases. How can I write a sql in Postgresql to access > this table to use in my Postgresql reports? Use dbi-link to make an ODBC connection to the other database. Either copy the data into a PostgreSQL table for processing, or make a view over the dbi-link function call to query the data each time. The latter option is slow, but requires no periodic refreshing of the copied table. If you're on PostgreSQL 9.1 I'd recommend copying the data from the other database via dbi-link into an unlogged table. See: http://pgfoundry.org/projects/dbi-link and the documentation for the dbi-link contrib module in your version of PostgreSQL. Hopefully in 9.2 the odbc foreign data wrapper will be working and ready for general use, in which case you'll be able to do it even more easily using ODBC FDW (SQL/MED). -- 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] CTE or Subselect and outer joins not behaving as expected?
In article , Joel Stevenson writes: > 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. Your WHERE clause turns the OUTER into an INNER JOIN. Try 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 left join my_existing_table as met on tally_mon = met.month and met.series = 1; -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
