Re: [HACKERS] proposal: session server side variables
2017-02-06 21:36 GMT+01:00 Fabien COELHO : > > Hello, > > I'll work on my proposal in v11 time. Maybe in this time Postgres will >> support autonomous transactions. >> > > Maybe. > > The variables syntax should be better integrated to core - it should be >> implemented without getter/setter functions. >> > > Yes, a nicer syntax would be great. > > Note that setter/getter could be useful for some use case, eg with queries > built dynamically? There is not any problem for usage in dynamic sql. Some generic access is done already. > > > I am not sure If statement SET can be enhanced to allows the work with >> session variables without some conflicts, but we will see. >> > > If so, maybe some kind of prefix could provide a workaround. any other database objects has not prefix. But we can identify a ambiguous situation and in this case we can require qualified identifier. Regards Pavel > > > -- > Fabien. >
Re: [HACKERS] proposal: session server side variables
Hello, I'll work on my proposal in v11 time. Maybe in this time Postgres will support autonomous transactions. Maybe. The variables syntax should be better integrated to core - it should be implemented without getter/setter functions. Yes, a nicer syntax would be great. Note that setter/getter could be useful for some use case, eg with queries built dynamically? I am not sure If statement SET can be enhanced to allows the work with session variables without some conflicts, but we will see. If so, maybe some kind of prefix could provide a workaround. -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
2017-02-03 11:18 GMT+01:00 Fabien COELHO : > > We can implement XA support for variables, ale I don't think so default >> should be XA. >> > > I was answering your question, which is what you can do about the > feedback: take the one hard/strong point into account in your proposal. > > You do not want to do that. Too bad. > > The argument that you keep on repeating about "other software do it like > that so it is the good way" do not work because these software (Oracle, > DB2, ...) have features unavailable to postgres which mitigate the issue > I'm raising, and there is no such mitigation in postgres. > > Note that you can proceed and simply ignore my negative opinion, which > will stay negative till these "secure" variables are transactional by > default, or till nested/autonomous transactions are provided by postgres. I'll work on my proposal in v11 time. Maybe in this time Postgres will support autonomous transactions. The variables syntax should be better integrated to core - it should be implemented without getter/setter functions. I am not sure If statement SET can be enhanced to allows the work with session variables without some conflicts, but we will see. Regards Pavel > > > -- > Fabien. >
Re: [HACKERS] proposal: session server side variables
2017-02-03 11:18 GMT+01:00 Fabien COELHO : > > We can implement XA support for variables, ale I don't think so default >> should be XA. >> > > I was answering your question, which is what you can do about the > feedback: take the one hard/strong point into account in your proposal. > > You do not want to do that. Too bad. > > The argument that you keep on repeating about "other software do it like > that so it is the good way" do not work because these software (Oracle, > DB2, ...) have features unavailable to postgres which mitigate the issue > I'm raising, and there is no such mitigation in postgres. > > Note that you can proceed and simply ignore my negative opinion, which > will stay negative till these "secure" variables are transactional by > default, or till nested/autonomous transactions are provided by postgres. ok Regards Pavel > > > -- > Fabien. >
Re: [HACKERS] proposal: session server side variables
>
>
>
>
>>
>> My "hard" opinion is that providing an unsafe by default feature (i.e.
>> which works as in some particular cases, but may fail silently if the
>> transaction fails), especially for a security related use case which
>> motivates the whole feature addition, is a very bad idea for the product.
>> If a committer likes it anyway, good for you.
>>
>
> I respect to your opinion and I understand - I have a similar strong
> opinion on packages in Postgres. In this case I prefer a common
> implementation - and common expectation.
>
> When some feature is PostgreSQL original, then we can design how we can.
> But when we implement some feature that exists already, then we should to
> respect some previous, older major implementation.
>
> The great example is our implementation of OUT parameters in PL. The idea
> is great - modern languages use it Golang, Rust. But in PL area is unique,
> different. One from significant issues migrations to Postgres, Postgres
> adoptions is this small feature.
>
> The people who is working with stored procedures doesn't expect XA behave,
> overhead when they working with some objects named "variables". We can
> implement XA support for variables, ale I don't think so default should be
> XA. Only few cases where variables can be used are are XA sensitive.
>
A syntax can be designed very verbose, so anybody can see and should to
choose expected behave of variables
CREATE [ { TEMPORARY | TEMP } ] [ { TRANSACTIONAL | XA } ] VARIABLE [ IF
NOT EXISTS ] varname datatype [ DEFAULT default_expression ] ;
Regards
Pavel
>
> Regards
>
> Pavel
>
>
>>
>> Other opinions I expressed on the thread are somehow "softer", i.e. even
>> if I think that there are better (simpler, easier) alternatives, these are
>> only alternatives.
>>
>> --
>> Fabien.
>>
>
>
Re: [HACKERS] proposal: session server side variables
We can implement XA support for variables, ale I don't think so default should be XA. I was answering your question, which is what you can do about the feedback: take the one hard/strong point into account in your proposal. You do not want to do that. Too bad. The argument that you keep on repeating about "other software do it like that so it is the good way" do not work because these software (Oracle, DB2, ...) have features unavailable to postgres which mitigate the issue I'm raising, and there is no such mitigation in postgres. Note that you can proceed and simply ignore my negative opinion, which will stay negative till these "secure" variables are transactional by default, or till nested/autonomous transactions are provided by postgres. -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
2017-02-03 7:25 GMT+01:00 Fabien COELHO : > > Hello Pavel, > > The @1 area is partially solved by psql session variables or by pgAdmin >> scripting functionality. @2 is partially solved by GUC but without >> possibility to set a access rights. >> >> I didn't found any implementation of XA variables [...] >> > > I did: GUCs in PostgreSQL are an implementation of transactional session > variables. > GUC was not designed for usage in stored procedures. > > As I wrote on the thread, given the "security check" use case, the safe > alternative to transactional session variables is to have nested > transactions. This seems like a far away prospect for pg, but is a reality > for Oracle, DB2 and some others that have untransactional session > variables, so at least it is safe in their case, if not elegant. > You have everywhere the fence between transactional/untransactional - and you can see some unwanted artefacts there. The world "secure" just means - a possibility to set access rights - nothing more, nothing less. > > My "hard" opinion is that providing an unsafe by default feature (i.e. > which works as in some particular cases, but may fail silently if the > transaction fails), especially for a security related use case which > motivates the whole feature addition, is a very bad idea for the product. > If a committer likes it anyway, good for you. > I respect to your opinion and I understand - I have a similar strong opinion on packages in Postgres. In this case I prefer a common implementation - and common expectation. When some feature is PostgreSQL original, then we can design how we can. But when we implement some feature that exists already, then we should to respect some previous, older major implementation. The great example is our implementation of OUT parameters in PL. The idea is great - modern languages use it Golang, Rust. But in PL area is unique, different. One from significant issues migrations to Postgres, Postgres adoptions is this small feature. The people who is working with stored procedures doesn't expect XA behave, overhead when they working with some objects named "variables". We can implement XA support for variables, ale I don't think so default should be XA. Only few cases where variables can be used are are XA sensitive. Regards Pavel > > Other opinions I expressed on the thread are somehow "softer", i.e. even > if I think that there are better (simpler, easier) alternatives, these are > only alternatives. > > -- > Fabien. >
Re: [HACKERS] proposal: session server side variables
Hello Pavel, The @1 area is partially solved by psql session variables or by pgAdmin scripting functionality. @2 is partially solved by GUC but without possibility to set a access rights. I didn't found any implementation of XA variables [...] I did: GUCs in PostgreSQL are an implementation of transactional session variables. As I wrote on the thread, given the "security check" use case, the safe alternative to transactional session variables is to have nested transactions. This seems like a far away prospect for pg, but is a reality for Oracle, DB2 and some others that have untransactional session variables, so at least it is safe in their case, if not elegant. My "hard" opinion is that providing an unsafe by default feature (i.e. which works as in some particular cases, but may fail silently if the transaction fails), especially for a security related use case which motivates the whole feature addition, is a very bad idea for the product. If a committer likes it anyway, good for you. Other opinions I expressed on the thread are somehow "softer", i.e. even if I think that there are better (simpler, easier) alternatives, these are only alternatives. -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
On Fri, Feb 3, 2017 at 2:56 PM, Pavel Stehule wrote: > My patch was marked as "returned with feedback". Personally, I had not a > idea what can be next step and what is preferred design, if some preferred > design exists. I don't know what I have to change on my proposal. Perhaps this was not adapted, sorry about that. Now the latest patch is 2-month old and does not apply. If you think that the approach you are taking is worth it, you can of course submit again a new version and make the discussion move on. Finding a consensus is the difficult part though. -- Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
There is a link - comparation Oracle package variables and DB2 global variables https://www.ibm.com/developerworks/data/library/techarticle/dm-0711zubiri/ Regards Pavel
Re: [HACKERS] proposal: session server side variables
2017-02-01 6:42 GMT+01:00 Pavel Stehule : > > > 2017-02-01 6:05 GMT+01:00 Michael Paquier : > >> On Wed, Jan 11, 2017 at 10:42 PM, Craig Ringer >> wrote: >> > There is no code yet. Code review and testing is where things get >> firmer. >> > >> > My personal stance right now is that I'd like to see catalog-decared >> typed >> > variables. I would prefer them to be transactional and would at least >> oppose >> > anything that didn't allow future room for that capability. I'd prefer >> that >> > non-transactional vars be clearly declared as such. >> > >> > In the end though... I'm not the one implementing it. I can have some >> > influence through the code review process. But it's whoever steps up >> with a >> > proposed implementation that has the biggest say. The rest of us can >> say yes >> > or no to some degree... but nobody can make someone else implement >> something >> > they don't want. >> >> The last patch is from the 6th of December and does not apply anymore: >> https://www.postgresql.org/message-id/CAFj8pRA9w_AujBAYdLR0U >> VfXwwoxhmn%2BFbNHnD3_NL%3DJ9x3y8w%40mail.gmail.com >> I don't have a better idea than marking this patch as "returned with >> feedback" for now, as the thread has died 3 weeks ago as well. >> > > There is not a agreement on the form of session variables. > Today I found on net a documentation to DB2 "CREATE VARIABLE" command. I had not any idea, so this statement exists already, although it is old feature - I found a doc from 2007. The DB2 design is very similar to my proposal - secured access, persistent metadata, unshared untransactional data limmited by session. They doesn't use a access functions - the access is with notation schemaname.variablename. I proposed this syntax as next step in implementation. The DB2 authors doesn't propose transactional variables - when user needs XA behave, then global temporary tables should be used. My patch was marked as "returned with feedback". Personally, I had not a idea what can be next step and what is preferred design, if some preferred design exists. I don't know what I have to change on my proposal. I understand, so there are two different concepts - 1. using variables for adhoc writing like T-SQL, MySQL or 2. using variables as global session objects for stored procedures. The @1 area is partially solved by psql session variables or by pgAdmin scripting functionality. @2 is partially solved by GUC but without possibility to set a access rights. I didn't found any implementation of XA variables or persistent variables on the world. Regards Pavel > > Regards > > Pavel > > >> -- >> Michael >> > >
Re: [HACKERS] proposal: session server side variables
2017-02-01 6:05 GMT+01:00 Michael Paquier : > On Wed, Jan 11, 2017 at 10:42 PM, Craig Ringer > wrote: > > There is no code yet. Code review and testing is where things get firmer. > > > > My personal stance right now is that I'd like to see catalog-decared > typed > > variables. I would prefer them to be transactional and would at least > oppose > > anything that didn't allow future room for that capability. I'd prefer > that > > non-transactional vars be clearly declared as such. > > > > In the end though... I'm not the one implementing it. I can have some > > influence through the code review process. But it's whoever steps up > with a > > proposed implementation that has the biggest say. The rest of us can say > yes > > or no to some degree... but nobody can make someone else implement > something > > they don't want. > > The last patch is from the 6th of December and does not apply anymore: > https://www.postgresql.org/message-id/CAFj8pRA9w_AujBAYdLR0UVfXwwoxhmn% > 2BFbNHnD3_NL%3DJ9x3y8w%40mail.gmail.com > I don't have a better idea than marking this patch as "returned with > feedback" for now, as the thread has died 3 weeks ago as well. > There is not a agreement on the form of session variables. Regards Pavel > -- > Michael >
Re: [HACKERS] proposal: session server side variables
On Wed, Jan 11, 2017 at 10:42 PM, Craig Ringer wrote: > There is no code yet. Code review and testing is where things get firmer. > > My personal stance right now is that I'd like to see catalog-decared typed > variables. I would prefer them to be transactional and would at least oppose > anything that didn't allow future room for that capability. I'd prefer that > non-transactional vars be clearly declared as such. > > In the end though... I'm not the one implementing it. I can have some > influence through the code review process. But it's whoever steps up with a > proposed implementation that has the biggest say. The rest of us can say yes > or no to some degree... but nobody can make someone else implement something > they don't want. The last patch is from the 6th of December and does not apply anymore: https://www.postgresql.org/message-id/CAFj8pRA9w_AujBAYdLR0UVfXwwoxhmn%2BFbNHnD3_NL%3DJ9x3y8w%40mail.gmail.com I don't have a better idea than marking this patch as "returned with feedback" for now, as the thread has died 3 weeks ago as well. -- Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
On 11 Jan. 2017 16:29, "Fabien COELHO" wrote: > I'm lost. This is precisely what I had in mind above with "read-only transaction" which is "warranted not to fail". I do not understand about which point you write "No". I misread. We agree. >> Are you "voting" for or against [Pavel's] proposal? ISTM that you are currently counted as "for". Mixed. We don't really vote anyway. There is no code yet. Code review and testing is where things get firmer. My personal stance right now is that I'd like to see catalog-decared typed variables. I would prefer them to be transactional and would at least oppose anything that didn't allow future room for that capability. I'd prefer that non-transactional vars be clearly declared as such. In the end though... I'm not the one implementing it. I can have some influence through the code review process. But it's whoever steps up with a proposed implementation that has the biggest say. The rest of us can say yes or no to some degree... but nobody can make someone else implement something they don't want.
Re: [HACKERS] proposal: session server side variables
Hello Craig. I'm not so sure about Craig precise opinion, but I cannot talk in his name. I think that I understood that he points out that there exists a situation where the use case is okay despite an untransactional variable: if the containing transaction is warranted not to fail, and probably (provably?) a read-only transaction is enough for that. Okay, sure... No. I'm saying that if you do a series of checks, then set the variable last, it does not matter if the xact somehow aborts after setting the variable. You have already done all your checks and are satisfied that the user has the appropriate access level. I'm lost. This is precisely what I had in mind above with "read-only transaction" which is "warranted not to fail". I do not understand about which point you write "No". I made the assumption that PostgreSQL is about keeping data safe and secure, and that misleading features which do not comply with this goal should be kept out. [...] Sometimes compromises are a thing. Indeed. Sequence is an interesting compromise with a clear use case and a measurable performance impact resulting from this. Note that sequences do have a key transactional property: it is transactionaly warranted that distinct committed transactions, whenever they occur (simultaneously or after a crash), get distinct values (bar cycles, sure). IMHO, security & compromise do not work well together, so should be avoided. I recognize that this is an opinion. As for the particular case, there is no deep problem about session variables being transactional: The available ones already have this property. They are pretty fast (eg 0.186 µs/get + cast to int on my laptop, or 5.3 million retrieval per second). I'm yet to understand how compromising security is worth the added value of the discussed proposal. It is not about performance. The static checkability is moot. I have already argued about all that... Now, I have already said that I actually agree that transactional vars are probably a good default and something we should have if we do this. Yep, I read that. Pavel updated proposal does not do that. Are you "voting" for or against the proposal? ISTM that you are currently counted as "for". But they are not the One True And Only Way. Indeed. The idea that security & compromise do not go well together is an opinion, and people may feel that a security feature can be compromised. We could add a "maybe corrupt the database" feature because it provides better performance to some use case: MySQL has made this initial choice that performance is better than data safety, with great popular success. I do not think that pg should fellow such path, the product is not on the same market. I understood that data safety & security are key properties expected of PostgreSQL and that it should remain a goal of the project. You can call this hyperbolic, or a misunderstanding, but that is the position I am defending on this thread. I'm clearly wrong: some people are okay with a security feature proven not to work in some case, if it works for their particular (read-only) case. Many normal things work only in some cases. COMMIT can be indeterminate if you lose your connection after sending COMMIT and before getting a reply. The commit *is* determinate on the server. Knowing whether it succeeded by a client is indeed indeterminate because the message saying so may be lost, as you point out. So. I would like transactional variables and think you have made a good case for them. I'm not that sure:-) If they prove impractical, [...] ISTM that I have also shown that they are practical, so there is no good reason to compromise. -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
On 11 January 2017 at 06:09, Fabien COELHO wrote: > I'm not so sure about Craig precise opinion, but I cannot talk in his name. > I think that I understood that he points out that there exists a situation > where the use case is okay despite an untransactional variable: if the > containing transaction is warranted not to fail, and probably (provably?) a > read-only transaction is enough for that. Okay, sure... No. I'm saying that if you do a series of checks, then set the variable last, it does not matter if the xact somehow aborts after setting the variable. You have already done all your checks and are satisfied that the user has the appropriate access level. This does not cover all use cases. It's not suitable if the checks involve writes to the database since you can then have a situation where the writes are lost but the variable setting retained. However, it does cover some common and useful ones like looking up external services, possibly expensive queries, etc, and setting a variable to cache "yup, access approved". > I made the assumption that PostgreSQL is about keeping data safe and secure, > and that misleading features which do not comply with this goal should be > kept out. This is hyperbolic. Every system has rules and restrictions. Appropriately documented, I don't see a problem. Should we also remove sequences because they violate transactional boundaries and users get confused by them? You won't mind that you can only use synthetic keys completely serially, right? Sometimes compromises are a thing. Now, I have already said that I actually agree that transactional vars are probably a good default and something we should have if we do this. But they are not the One True And Only Way. > I'm clearly wrong: some people are okay with a security feature proven not > to work in some case, if it works for their particular (read-only) case. Many normal things work only in some cases. COMMIT can be indeterminate if you lose your connection after sending COMMIT and before getting a reply. Did my transaction commit? Um, I dunno. (Yes, I know we have 2PC). That's pretty fundmental... So. I would like transactional variables and think you have made a good case for them. If they prove impractical, I think variables with access controls that are not transactional are also OK though less useful, so long as they are clearly documented. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
Hello Robert, You're just ignoring explanations from other people - Craig in particular - about why it DOES satisfy their use case. I'm not so sure about Craig precise opinion, but I cannot talk in his name. I think that I understood that he points out that there exists a situation where the use case is okay despite an untransactional variable: if the containing transaction is warranted not to fail, and probably (provably?) a read-only transaction is enough for that. Okay, sure... This falls under "the feature works sometime", which I think is not acceptable for a security thing in pg core. And the reason his argument is valid is because he is questioning your premise. [...] Yes. I made the assumption that PostgreSQL is about keeping data safe and secure, and that misleading features which do not comply with this goal should be kept out. This is indeed a subjective opinion, not provable truth. I only assumed that this opinion was implicitely shared, so that providing a counter example with the feature where data is not safe or secure was enough to dismiss the proposal. I'm clearly wrong: some people are okay with a security feature proven not to work in some case, if it works for their particular (read-only) case. I do not like Pavel's feature, this is a subjective opinion. This feature does not provide a correct solution for the use case, this is an objective fact. The presented feature does not have a real use case, this is too bad. If the presented feature had no use case, I don't think there would be 3 or 4 people arguing for it. Those people aren't stupid. I have not said that, nor thought that. I pointed out my arguments, basically I answer "security must always work" to "the feature can work sometimes". Then it cycles. As I can offer limited time for reviewing features, at some point I do not have any more time to argue constructively and convince people, that is life. That is when I tried to conclude my contribution by sending my review. [..] Are you also willing to accept other people's differing conclusions? I do not have to "accept", or not, differing conclusions. The committer decides in the end, because they have the power, I just have words. All I can say is that as a committer I would not commit such a feature. As a basic contributor, I can hope that the best decision is made in the end, and for that I try to express arguments precisely and objectively, that is the point of reviewing a proposal and give advice about how it should be amended if I think it should. I believe that the words "silly" and "academic" were used about certain proposals that you made, [..] it does necessarily imply personal disrespect. Sure. "Silly academic" suits me though, I'm fine with it:-) -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
I do not like Pavel's feature, this is a subjective opinion. This feature does not provide a correct solution for the use case, this is an objective fact. The presented feature does not have a real use case, this is too bad. Oh, also, you might want to tell Oracle and the many people who use package variables that. As it can be used safely with nested transaction, I have no doubt that they do that, and that auditors check that carefully when auditing code:-) [...] Your unwillingness to listen to anyone else isn't doing your argument any favours though. Hmmm. I'm far from perfect and I have a limited supply of patience when logic does not always apply in a long discussion. However I think that my review of Pavel proposal is fair, with a clear separation of objective (proven) facts and subjective but argumented opinions. I do not think that I can contribute anything more by continuing argumenting, so I wish I would not have been dragged back into this thread:-( Despite a lot of effort, Pavel proposal is still about a untransactional (by default) session variables. Too bad. Time out for me. I'm deeply against that, I have said it: I think it would harm PostgreSQL to provide such a misleading security feature. Then I'm done. If a committer wants to add untransactional session variables with permissions, it is their priviledge, and my blessing is not needed anyway. -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
Hello Craig, I have submitted a proof of this fact in the form of a counter example which (1) (pseudo) implements the use-case by logging into an audit table the fact a user accesses the secure level (2) shows that the status of a non transactional session variable used for keeping this status is wrong for the use case in some cases (it says that all is well while appending to the audit table failed). You've been assuming everyone else cares about auditing such access into a table. No, I have not. For the PosgreSQL product, I'm really assuming that a security feature should work in all cases, not just some cases with implicit uncheckable restrictions, especially restrictions related to transactions which is all a database is about. I think that providing a misleading feature is a bad idea. Note that my blessing is not required. If a committer wants to add this then they can do it. But you're fixated on the idea that without that use case satisfied the rest is useless, and that's simply not the case. Transactional vars are only needed if you make _write_ changes to the DB that must be committed atomically with the var change. If you're only doing (maybe expensive) lookups, it doesn't matter. It does not matter if and only if the transaction does not fail, not because the variable is not transactional. Basically, if it is untransactional, then it works only if it behaves exactly like a transaction... Again ... I think you've assumed everyone else is talking about the same security-related case you are. I'm looking forward to see any use case which requires untransactional variables with permissions and works correctly without adding un-database constraints such as "please do not use in transactions that change any data because then it may or may not work". It's kind of like someone coming to you and saying they want to add an engine to their glider, and you explaining that it's totally useless to add an engine unless it can also function as a submarine. Um, that's nice, but not what they asked for. Hmmm... I think that it is really like adding an engine on a glider which does not work if the glider flies under a cloud. You just have to recall that you should not fly under a cloud when the engine is turned on. -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
On Tue, Jan 10, 2017 at 1:31 AM, Fabien COELHO wrote: > I have submitted a proof of this fact in the form of a counter example which > (1) (pseudo) implements the use-case by logging into an audit table the fact > a user accesses the secure level (2) shows that the status of a non > transactional session variable used for keeping this status is wrong for the > use case in some cases (it says that all is well while appending to the > audit table failed). > > I feel entitled to point out to other people that their belief that a > feature as described provides a correct solution to a particular use case is > wrong, if it is factually the case. If they persist in this belief despite > the submitted proof, I can only be sad about it, because if pg provides a > feature for a security-relared use case which does not work correctly it is > just shooting one's foot. You're just ignoring explanations from other people - Craig in particular - about why it DOES satisfy their use case. And the reason his argument is valid is because he is questioning your premise. You are proving "if A, then B" and he's saying, "yes, but not A". That's not a logical fallacy on his part. That's you proving something that is in his view irrelevant to the desirability of the feature. > I do not like Pavel's feature, this is a subjective opinion. This feature > does not provide a correct solution for the use case, this is an objective > fact. The presented feature does not have a real use case, this is too bad. If the presented feature had no use case, I don't think there would be 3 or 4 people arguing for it. Those people aren't stupid. > Finally, I did not "veto" this feature, I reviewed it in depth and concluded > negatively. Sure, that's pretty fair. Are you also willing to accept other people's differing conclusions? > You are a committer and I'm just a "silly academic", you do not > have to listen to anything I say and can take majority votes against proofs > if you want. I believe that the words "silly" and "academic" were used about certain proposals that you made, and you have here pulled them out of the context in which they were written and recast them as general judgements on you rather than statements about certain ideas which you proposed or certain arguments which you made. I think most people on this mailing list, including me, are very careful to avoid "ad hominum" arguments, and I believe that is also the case in the arguments made to you. Everybody's ideas on this mailing list, including mine, come in for criticism from time to time. That doesn't necessarily imply personal disrespect. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
On 10 January 2017 at 14:31, Fabien COELHO wrote: > I do not like Pavel's feature, this is a subjective opinion. This feature > does not provide a correct solution for the use case, this is an objective > fact. The presented feature does not have a real use case, this is too bad. Oh, also, you might want to tell Oracle and the many people who use package variables that. Now, that said, huge numbers of people blindly do all sorts of unsafe things and mostly get away with it. Using MERGE in concurrent OLTP workloads. Racey upserts. Blindly assuming xacts will succeed and not keeping the info around to retry them until confirmation of commit is received. That sort of business. Nonetheless, it's pretty clear they're far from having a "real use case". I'd like to see transactional vars. I think it's worthwhile and you've made a reasonable argument that they're useful, and should probably even be the default. Your unwillingness to listen to anyone else isn't doing your argument any favours though. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
On 10 January 2017 at 14:31, Fabien COELHO wrote: > I have submitted a proof of this fact in the form of a counter example which > (1) (pseudo) implements the use-case by logging into an audit table the fact > a user accesses the secure level (2) shows that the status of a non > transactional session variable used for keeping this status is wrong for the > use case in some cases (it says that all is well while appending to the > audit table failed). You've been assuming everyone else cares about auditing such access into a table. Personally I tend to agree with you that it's useful enough to justify transactional vars. But you're fixated on the idea that without that use case satisfied the rest is useless, and that's simply not the case. Transactional vars are only needed if you make _write_ changes to the DB that must be committed atomically with the var change. If you're only doing (maybe expensive) lookups, it doesn't matter. > I feel entitled to point out to other people that their belief that a > feature as described provides a correct solution to a particular use case is > wrong, if it is factually the case. If they persist in this belief despite > the submitted proof, I can only be sad about it, because if pg provides a > feature for a security-relared use case which does not work correctly it is > just shooting one's foot. Again ... I think you've assumed everyone else is talking about the same security-related case you are. I haven't seen Pavel talking about access audit logging. If he has been, then my mistake and you're quite correct. But my reading is that you've been _assuming_ that. > I do not like Pavel's feature, this is a subjective opinion. This feature > does not provide a correct solution for the use case, this is an objective > fact. For _your_ use case. > The presented feature does not have a real use case, this is too bad. No, it just doesn't match your idea of what the use case is. It does have other uses that are perfectly valid. Look up access rights, set var. It's fine. Now, I think we might as well do it transactionally, but it's not some kind of absolute requirement; if you're not transactional, it just means you can't do reliable audit logging of access into tables. Of course, we can't do that already for anything else. It's kind of like someone coming to you and saying they want to add an engine to their glider, and you explaining that it's totally useless to add an engine unless it can also function as a submarine. Um, that's nice, but not what they asked for. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
2017-01-10 7:31 GMT+01:00 Fabien COELHO : > > Hello Robert, > > Half-persistence (in definition, not in value) is not a key feature needed >>> by the use-case. >>> >> >> Well, you don't get to decide that. >> > > I do not think that your reprimand is deserved about this point: I did not > decide a subjective opinion, I noted an objective fact. > > You've been told by at least three or four people that they don't want >> variables to be transactional, you've been pointed to documentation links >> showing that in other database systems including Oracle variables are not >> transactional, and you still insist that this proposal is senseless unless >> variables are transactional. >> > > Indeed. > > I have submitted a proof of this fact in the form of a counter example > which (1) (pseudo) implements the use-case by logging into an audit table > the fact a user accesses the secure level (2) shows that the status of a > non transactional session variable used for keeping this status is wrong > for the use case in some cases (it says that all is well while appending to > the audit table failed). > > I have also recognized that the use-case could be implemented safely, > although not correctly, if pg provides nested/autonomous transactions like > Oracle, DB2 or MS SQL does, but I think that having such a useful feature > is quite far away... > > You have every right to decide what you think is useful, but you don't >> have a right to decide what other people think is useful. >> > > Hmmm. > > I feel entitled to point out to other people that their belief that a > feature as described provides a correct solution to a particular use case > is wrong, if it is factually the case. If they persist in this belief > despite the submitted proof, I can only be sad about it, because if pg > provides a feature for a security-relared use case which does not work > correctly it is just shooting one's foot. > > I do not like Pavel's feature, this is a subjective opinion. This feature > does not provide a correct solution for the use case, this is an objective > fact. The presented feature does not have a real use case, this is too bad. > I wrote more time, so transactional and temporal support can be optional feature. The people who uses package or module variables in other RDBMS probably doesn't agree with you, so there are not real use case. The transaction support is not main point in my proposal - the main points are: 1. catalog based - created only once time, persistent metadata 2. allows be schema organized - like our PL functions 3. disallow identifier conflict - the name is unique in catalogue 4. allows to use declared secure access After this discussion I append points 5. can be temporal - metadata are cleaned after end of life scope 6. can be transactional where content should be sensitive on possible rollback Regards Pavel > > Finally, I did not "veto" this feature, I reviewed it in depth and > concluded negatively. You are a committer and I'm just a "silly academic", > you do not have to listen to anything I say and can take majority votes > against proofs if you want. > > -- > Fabien. >
Re: [HACKERS] proposal: session server side variables
Hello Robert, Half-persistence (in definition, not in value) is not a key feature needed by the use-case. Well, you don't get to decide that. I do not think that your reprimand is deserved about this point: I did not decide a subjective opinion, I noted an objective fact. You've been told by at least three or four people that they don't want variables to be transactional, you've been pointed to documentation links showing that in other database systems including Oracle variables are not transactional, and you still insist that this proposal is senseless unless variables are transactional. Indeed. I have submitted a proof of this fact in the form of a counter example which (1) (pseudo) implements the use-case by logging into an audit table the fact a user accesses the secure level (2) shows that the status of a non transactional session variable used for keeping this status is wrong for the use case in some cases (it says that all is well while appending to the audit table failed). I have also recognized that the use-case could be implemented safely, although not correctly, if pg provides nested/autonomous transactions like Oracle, DB2 or MS SQL does, but I think that having such a useful feature is quite far away... You have every right to decide what you think is useful, but you don't have a right to decide what other people think is useful. Hmmm. I feel entitled to point out to other people that their belief that a feature as described provides a correct solution to a particular use case is wrong, if it is factually the case. If they persist in this belief despite the submitted proof, I can only be sad about it, because if pg provides a feature for a security-relared use case which does not work correctly it is just shooting one's foot. I do not like Pavel's feature, this is a subjective opinion. This feature does not provide a correct solution for the use case, this is an objective fact. The presented feature does not have a real use case, this is too bad. Finally, I did not "veto" this feature, I reviewed it in depth and concluded negatively. You are a committer and I'm just a "silly academic", you do not have to listen to anything I say and can take majority votes against proofs if you want. -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
On 10 January 2017 at 05:14, Robert Haas wrote: > 2. The user doesn't need to re-declare the variables you want to use > at the beginning of every session. This is also the reason why many > people want global temporary tables. They don't do anything that > can't be done with local temporary tables; they're just more > convenient to use. They'll also help a lot with pg_attribute and pg_class bloat. I don't feel strongly either way about catalog use, but definitely think declare-before-use is crucial. Pretty much every language that implicitly declares variables has landed up adding a way to require them to be declared for a reason. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
On Thu, Jan 5, 2017 at 5:39 AM, Fabien COELHO wrote: > Half-persistence (in definition, not in value) is not a key feature needed > by the use-case. Well, you don't get to decide that. You've been told by at least three or four people that they don't want variables to be transactional, you've been pointed to documentation links showing that in other database systems including Oracle variables are not transactional, and you still insist that this proposal is senseless unless variables are transactional. You have every right to decide what you think is useful, but you don't have a right to decide what other people think is useful. You don't get veto power over what Pavel wants to implement, even if you personally would not choose to implement it that way, and especially not when multiple people are agreeing with Pavel and disagreeing with you. On the substance of this issue, I would note that Pavel's idea of entering variables in pg_class has a number of advantages: 1. The code can control permissions using the same system that we use to control permissions on all other objects, instead of having to create a completely new one. Similarly for dependencies. This wouldn't matter if it were possible to get by with no system for privileges on variables or with a very simple system such as you proposed upthread, but I think that's somewhat unrealistic in the face of security-definer functions and row-level security. Execution in multiple privilege contexts within the same session is a fact of life, and whatever design gets chosen has to somehow cope with that; using the existing infrastructure is one reasonable choice. Trying to do something excessively simple here will result in security bugs. 2. The user doesn't need to re-declare the variables you want to use at the beginning of every session. This is also the reason why many people want global temporary tables. They don't do anything that can't be done with local temporary tables; they're just more convenient to use. 3. If somebody goes to drop a type, they'll have to use CASCADE to get rid of the dependent variable. That might tip them off to conduct an investigation into whether that variables is being used. If the variables are established entirely on a per-session basis, there will be no such warning at DROP time. You'll only find out about the problem when the application starts failing. 4. As Pavel already said, it makes things easier for a static checker. I'm not certain that anyone, including you, has correctly understood Pavel's point here, which is perhaps because Pavel's first language is not English. But I'm pretty sure I understand it, so let me try to restate it more straightforwardly. Suppose a static checker encounters the statement SET VARIABLE flumpf = 1 (assume for purposes of this bullet point that this is the syntax for setting a variable). If variables have to be catalogued, and flumpf is not in the catalog, this is a probably a bug. If flumpf is in the catalog but is of a type that cannot contain the value 1, this is also probably a bug. But if variables do not have to be catalogued, then the static checker will have a hard time inferring anything about the correctness of this statement. It is not impossible; for example, the static checker could have a requirement that the user running it set up the session with all required variables before running the checker. But that is not particularly convenient. IIUC, Pavel's point is that a user who creates a function which uses a certain variable would probably also put code in that same function to create the variable if it is not yet present. And a static checker probably can't see through that. You've argued that there is no problem here, but it seems absolutely unarguable to me that static checkers benefit from having more things statically configured and less stuff figured out at runtime. That's why it's easier to statically check programming languages with strongly typed variables than it is to statically check languages with run-time typing. Now, there are certainly advantages to NOT entering variables in pg_class, too. For example, it makes the operation much more light-weight. If the code uses basically the same variables over and over again, entering them in the catalog doesn't cost anything meaningful. If it's constantly creating new variables, that's going to create catalog bloat if those variables have to be added to the catalogs, and that's going to stink. I don't mean to pass judgement here in favor of Pavel's proposal and against other proposals. But it seems clear to me that Pavel's proposal does have certain fairly compelling advantages and a good deal of community support; and your replies don't seem to be acknowledging any of that. I think they should. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your su
Re: [HACKERS] proposal: session server side variables (fwd)
Hello Bruce, Good. So we seem to agree that GUCS are transactional? Uh, I think it is a missing feature, i.e.: https://wiki.postgresql.org/wiki/Todo#Administration Have custom variables be transaction-safe https://www.postgresql.org/message-id/4b577e9f.8000...@dunslane.net Hmmm, that is a subtle one:-) After more testing, the current status is that the values of existing user-defined parameters is cleanly transactional, as already tested: fabien=# SET x.x = 'before'; fabien=# BEGIN; fabien=# SET x.x = 'inside'; fabien=# ROLLBACK; fabien=# SHOW x.x; -- 'before' This is what I meant by "GUCs are transactional". However, as you point out, the existence of the parameter is not: If it is created within an aborted transaction then it still exists afterwards: fabien=# SHOW z.z; ERROR: unrecognized configuration parameter "z.z" fabien=# BEGIN; fabien=# SET z.z = 'yep'; fabien=# ROLLBACK; fabien=# SHOW z.z; -- no error, empty string shown So GUCs are... half-transactional? :-) From the security-related use case perspective, this half transactionality is enough, but it is not very clean. Does not look like a very big issue to fix, it just seems that nobody bothered in the last 6 years... -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables (fwd)
On Thu, Jan 5, 2017 at 11:45:24AM +0100, Fabien COELHO wrote: > > I must tweak my mail client configuration...> > > >>>Good. So we seem to agree that GUCS are transactional? > > > >I'm surprised, I never knew this. > > I must admit that it was also a (good) surprise for me. > > The documentation says it: > > """ > If SET (or equivalently SET SESSION) is issued within a transaction that is > later aborted, the effects of the SET command disappear when the transaction > is rolled back. Once the surrounding transaction is committed, the effects > will persist until the end of the session, unless overridden by another SET. > """ > > But I have not found anything clear about user-defined parameters. Uh, I think it is a missing feature, i.e.: https://wiki.postgresql.org/wiki/Todo#Administration Have custom variables be transaction-safe https://www.postgresql.org/message-id/4b577e9f.8000...@dunslane.net -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
2017-01-06 7:01 GMT+01:00 Pavel Stehule : > > >> >>> >>> Thank you for your work on this topic. >>> >>> Unfortunately, there is significant disagreement in this topic between >>> us. I see a schema based persistent metadata a catalog based security as >>> fundamental feature. Editing config file is not acceptable in any view. >>> >> >> I generally agree with that. That said, it probably wouldn't be hard to >> "register" GUCs during backend startup, based on what's in the catalog for >> the database you're connecting to. There's certainly already a place in the >> code to do this, since you can set per-database values for GUCs. That said, >> IIRC GUCs are setup in such a way that could could just create a new stack >> upon connection. Actually, I think that'd need to happen anyway, otherwise >> these variables are going to look like GUCs even though they're not. > > > Registration on every setup can be little bit expensive - more practical > is variables initialized on demand - when they are used. > And if you have a catalog entry, then it is not necessary - self management variables in memory is not too complex > > Regards > > Pavel > >> >> -- >> Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX >> Experts in Analytics, Data Architecture and PostgreSQL >> Data in Trouble? Get it in Treble! http://BlueTreble.com >> 855-TREBLE2 (855-873-2532) >> >
Re: [HACKERS] proposal: session server side variables
> >> >> Thank you for your work on this topic. >> >> Unfortunately, there is significant disagreement in this topic between >> us. I see a schema based persistent metadata a catalog based security as >> fundamental feature. Editing config file is not acceptable in any view. >> > > I generally agree with that. That said, it probably wouldn't be hard to > "register" GUCs during backend startup, based on what's in the catalog for > the database you're connecting to. There's certainly already a place in the > code to do this, since you can set per-database values for GUCs. That said, > IIRC GUCs are setup in such a way that could could just create a new stack > upon connection. Actually, I think that'd need to happen anyway, otherwise > these variables are going to look like GUCs even though they're not. Registration on every setup can be little bit expensive - more practical is variables initialized on demand - when they are used. Regards Pavel > > -- > Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX > Experts in Analytics, Data Architecture and PostgreSQL > Data in Trouble? Get it in Treble! http://BlueTreble.com > 855-TREBLE2 (855-873-2532) >
Re: [HACKERS] proposal: session server side variables
On 6 January 2017 at 08:44, Jim Nasby wrote: >>(1) private/public visibility (as Oracle does with package vars). >>this point is enough to implement the presented use case. Agreed. >>(2) typing (casting is a pain) We already have typed GUCs and allow them to be user-defined. See DefineCustomBoolVariable, DefineCustomIntVariable, etc. What we lack is a way to declare and use typed dynamically user-defined GUCs at runtime without a C extension. We also lack user interface to load and store values without going via their text representation; there's no current_setting_int4 etc. So if we allow for now the idea that we'd extend the GUC model to do this (which I'm not at all sure is a good thing) ... it's possible. >>(5) have some "permanent" GUC type declarations (maybe editing the >>config file does that already, by the way?) We have that, but it currently requires a C extension. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
On 1/5/17 4:59 AM, Pavel Stehule wrote: - Personnaly, I'm not convinced that a NEW type of session variable is a good thing as pg already has one, and two is one too many. I would find it more useful to enhance existing dynamic session variables with, by order of importance: (1) private/public visibility (as Oracle does with package vars). this point is enough to implement the presented use case. (2) typing (casting is a pain) (3) improved syntax (set_config & current_setting is a pain) Eventually, unrelated to the use case, but in line with your motivations as I understand them: (4) add an option to make a GUC non transactional, iff there is a clear use case for that (maybe debug?). (5) have some "permanent" GUC type declarations (maybe editing the config file does that already, by the way?) Thank you for your work on this topic. Unfortunately, there is significant disagreement in this topic between us. I see a schema based persistent metadata a catalog based security as fundamental feature. Editing config file is not acceptable in any view. I generally agree with that. That said, it probably wouldn't be hard to "register" GUCs during backend startup, based on what's in the catalog for the database you're connecting to. There's certainly already a place in the code to do this, since you can set per-database values for GUCs. That said, IIRC GUCs are setup in such a way that could could just create a new stack upon connection. Actually, I think that'd need to happen anyway, otherwise these variables are going to look like GUCs even though they're not. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com 855-TREBLE2 (855-873-2532) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
Good. So we seem to agree that GUCS are transactional? I'm surprised, I never knew this. I must admit that it was also a (good) surprise for me. The documentation says it: """ If SET (or equivalently SET SESSION) is issued within a transaction that is later aborted, the effects of the SET command disappear when the transaction is rolled back. Once the surrounding transaction is committed, the effects will persist until the end of the session, unless overridden by another SET. """ But I have not found anything clear about user-defined parameters. -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
2017-01-05 11:39 GMT+01:00 Fabien COELHO : > > Hello Pavel, > > There are more reasons, why I would not to use GUC >> > > 0. it is not designed be secure - there is different security model - >> readonly, superuser, others >> > > Sure, GUCs as is are not enough, but the model can be extended instead of > re-inventing the wheel with a new kind of variable. > > 1. it is dynamic - not persistent - cannot be used as package variables >> simply >> > > Half-persistence (in definition, not in value) is not a key feature needed > by the use-case. > > 2. there is different placing - custom requires prefix - I prefer using our >> schemas, because schemas are used in pg like packages in Oracle >> > > Idem. > > 3. large number of GUC decrease performace of end of transactions, >> subtransactions >> > > That is life. The presented use-case really needs only one variable. > > 4. any RDBMS using untransactional variables - it should be default >> optimized behave >> > > Hmmm. Untransactional variables do **NOT** fit the use case, it just works > "sometimes", which is not acceptabe. > > I've spent too much time on reviewing this proposal. My conclusion is: > > - a clear use case linked to security setups has been presented >which requires some kind of secure (i.e. with access control) session >variables, currently not available in pg which has user-defined GUC >which are dynamic, untyped (TEXT), public, transactional. > > - you have proposed a NEW kind of session variables which is: > >(1) statically typed, declared permanently in the catalog, in the >schema/table namespace > >(2) values are session alive > >(3) untransactional, as you insist on that (your 4. above) > >(4) with permissions > > > My feedback is that: > > - The proposed feature does not fit the presented use case it is intended >for. There is no use case for untransactional secure session variables. >The proposal should be amended so that the variables display by default >some transactional properties because it is required for correctly >implementing the use case. > > - Personnaly, I'm not convinced that a NEW type of session variable is >a good thing as pg already has one, and two is one too many. I would >find it more useful to enhance existing dynamic session variables with, >by order of importance: > >(1) private/public visibility (as Oracle does with package vars). >this point is enough to implement the presented use case. > >(2) typing (casting is a pain) > >(3) improved syntax (set_config & current_setting is a pain) > > Eventually, unrelated to the use case, but in line with your motivations > as I understand them: > >(4) add an option to make a GUC non transactional, iff there is >a clear use case for that (maybe debug?). > >(5) have some "permanent" GUC type declarations (maybe editing the >config file does that already, by the way?) > > Thank you for your work on this topic. Unfortunately, there is significant disagreement in this topic between us. I see a schema based persistent metadata a catalog based security as fundamental feature. Editing config file is not acceptable in any view. Best regards Pavel > -- > Fabien. >
Re: [HACKERS] proposal: session server side variables (fwd)
Good. So we seem to agree that GUCS are transactional? I'm surprised, I never knew this. I must admit that it was also a (good) surprise for me. The documentation says it: """ If SET (or equivalently SET SESSION) is issued within a transaction that is later aborted, the effects of the SET command disappear when the transaction is rolled back. Once the surrounding transaction is committed, the effects will persist until the end of the session, unless overridden by another SET. """ But I have not found anything clear about user-defined parameters. -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
Hello Pavel, There are more reasons, why I would not to use GUC 0. it is not designed be secure - there is different security model - readonly, superuser, others Sure, GUCs as is are not enough, but the model can be extended instead of re-inventing the wheel with a new kind of variable. 1. it is dynamic - not persistent - cannot be used as package variables simply Half-persistence (in definition, not in value) is not a key feature needed by the use-case. 2. there is different placing - custom requires prefix - I prefer using our schemas, because schemas are used in pg like packages in Oracle Idem. 3. large number of GUC decrease performace of end of transactions, subtransactions That is life. The presented use-case really needs only one variable. 4. any RDBMS using untransactional variables - it should be default optimized behave Hmmm. Untransactional variables do **NOT** fit the use case, it just works "sometimes", which is not acceptabe. I've spent too much time on reviewing this proposal. My conclusion is: - a clear use case linked to security setups has been presented which requires some kind of secure (i.e. with access control) session variables, currently not available in pg which has user-defined GUC which are dynamic, untyped (TEXT), public, transactional. - you have proposed a NEW kind of session variables which is: (1) statically typed, declared permanently in the catalog, in the schema/table namespace (2) values are session alive (3) untransactional, as you insist on that (your 4. above) (4) with permissions My feedback is that: - The proposed feature does not fit the presented use case it is intended for. There is no use case for untransactional secure session variables. The proposal should be amended so that the variables display by default some transactional properties because it is required for correctly implementing the use case. - Personnaly, I'm not convinced that a NEW type of session variable is a good thing as pg already has one, and two is one too many. I would find it more useful to enhance existing dynamic session variables with, by order of importance: (1) private/public visibility (as Oracle does with package vars). this point is enough to implement the presented use case. (2) typing (casting is a pain) (3) improved syntax (set_config & current_setting is a pain) Eventually, unrelated to the use case, but in line with your motivations as I understand them: (4) add an option to make a GUC non transactional, iff there is a clear use case for that (maybe debug?). (5) have some "permanent" GUC type declarations (maybe editing the config file does that already, by the way?) -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
2017-01-05 10:59 GMT+01:00 Fabien COELHO : > > Good. So we seem to agree that GUCS are transactional? >>> >> I'm surprised, I never knew this. >> > > I must admit that it was also a (good) surprise for me. > > The documentation says it: > > """ > If SET (or equivalently SET SESSION) is issued within a transaction that > is later aborted, the effects of the SET command disappear when the > transaction is rolled back. Once the surrounding transaction is committed, > the effects will persist until the end of the session, unless overridden by > another SET. > """ > > But I have not found anything clear about user-defined parameters. https://www.postgresql.org/docs/current/static/runtime-config-custom.html Pavel > > > -- > Fabien. >
Re: [HACKERS] proposal: session server side variables
On 5 January 2017 at 08:35, Craig Ringer wrote: > On 5 January 2017 at 01:49, Fabien COELHO wrote: >> >>> ok understand >> >> >> Good. So we seem to agree that GUCS are transactional? > > No. We don't agree. They aren't. Uh. I take that back. craig=> SET x.s = 'x'; SET craig=> BEGIN; BEGIN craig=> SET x.s = 'y'; SET craig=> ROLLBACK; ROLLBACK craig=> SHOW x.s; x.s - x (1 row) I'm surprised, I never knew this. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
On 5 January 2017 at 01:49, Fabien COELHO wrote: > >> ok understand > > > Good. So we seem to agree that GUCS are transactional? No. We don't agree. They aren't. The effects of SET LOCAL are reverted whether you commit or rollback. The effects of SET SESSION are never reverted, whether you commit or roll back. craig=> SET x.s = 'x'; SET craig=> BEGIN; BEGIN craig=> SET LOCAL x.s = 'y'; SET craig=> COMMIT; COMMIT craig=> SHOW x.s; x.s - x (1 row) There are simply different scopes, one of which is the transaction scope. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
On 01/04/2017 04:36 PM, Craig Ringer wrote: > On 5 January 2017 at 08:35, Craig Ringer wrote: >> On 5 January 2017 at 01:49, Fabien COELHO wrote: >>> Good. So we seem to agree that GUCS are transactional? >> >> No. We don't agree. They aren't. > > Uh. I take that back. > > craig=> SET x.s = 'x'; > SET > craig=> BEGIN; > BEGIN > craig=> SET x.s = 'y'; > SET > craig=> ROLLBACK; > ROLLBACK > craig=> SHOW x.s; > x.s > - > x > (1 row) > > > I'm surprised, I never knew this. (I have not been able to keep up with the shear volume on this thread, but this caught my eye...) Yeah -- I found it surprising when I first discovered it too. My opinion is that the design for variables should not behave this way. Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development signature.asc Description: OpenPGP digital signature
Re: [HACKERS] proposal: session server side variables
2017-01-04 19:56 GMT+01:00 Fabien COELHO : > > [...] It is on critical path, so every check increase computer time for >> transaction end. >> > > Hmmm... Everything executed is on the critical path... > > It is a very good thing that GUCs are transactional, and this should not >>> be changed, it is a useful feature! Much more useful than non >>> transactional. >>> >> >> Personally, I never used - although I using often nesting >> > > Your position is contradictory: > > First you put forward a variable-with-permissions for a special use case, > you insist that correctness is key and must be checked with static analysis > tools that audit codes, that dynamic variables are too ugly for the > purpose. Fine, even if I disagree with some details, there is some logic in > that: security, audit, checks... why not. > > Then when one shows that correctness requires that the variable is > transactional, this is not so important anymore based on the fact that some > big companies do not do it like that, and suddenly it is enough that it > probably works sometimes. And when the fact that pg already supports > transactional variables is pointed out, just what the use case needs... > then you suggest to remove the property. > The GUC are designed for different purpose - I don't know why somebody did there transaction support - I understand and I remember the nesting support. look to code - the GUC related code has more about 10K lines, probably 5K lines is important for this purpose. There are more reasons, why I would not to use GUC 0. it is not designed be secure - there is different security model - readonly, superuser, others 1. it is dynamic - not persistent - cannot be used as package variables simply 2. there is different placing - custom requires prefix - I prefer using our schemas, because schemas are used in pg like packages in Oracle 3. large number of GUC decrease performace of end of transactions, subtransactions 4. any RDBMS using untransactional variables - it should be default optimized behave Regards Pavel > > What can I say? You've lost me, really. > > -- > Fabien. >
Re: [HACKERS] proposal: session server side variables
[...] It is on critical path, so every check increase computer time for transaction end. Hmmm... Everything executed is on the critical path... It is a very good thing that GUCs are transactional, and this should not be changed, it is a useful feature! Much more useful than non transactional. Personally, I never used - although I using often nesting Your position is contradictory: First you put forward a variable-with-permissions for a special use case, you insist that correctness is key and must be checked with static analysis tools that audit codes, that dynamic variables are too ugly for the purpose. Fine, even if I disagree with some details, there is some logic in that: security, audit, checks... why not. Then when one shows that correctness requires that the variable is transactional, this is not so important anymore based on the fact that some big companies do not do it like that, and suddenly it is enough that it probably works sometimes. And when the fact that pg already supports transactional variables is pointed out, just what the use case needs... then you suggest to remove the property. What can I say? You've lost me, really. -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
2017-01-04 18:49 GMT+01:00 Fabien COELHO : > > ok understand >> > > Good. So we seem to agree that GUCS are transactional? > > The logic depends on transactions and on nesting level (nesting doesn't >> depends on transactions only) >> > > Yep, it probably also happens with LOCAL which hides the previous value > and restores the initial one when exiting. > > void AtEOXact_GUC(bool isCommit, int nestLevel) >> >> Probably we should to use CallXactCallbacks instead - then is not a >> performance impact when there are not transactional variables. >> > > I do not understand your point. > It is on critical path, so every check increase computer time for transaction end. Regards Pavel > > It is a very good thing that GUCs are transactional, and this should not > be changed, it is a useful feature! Much more useful than non transactional. > Personally, I never used - although I using often nesting regards Pavel > > Moreover I think that transactional is expensive when writing things to > disk, but in memory the overhead is reduced, and if you need it then you > need it. > > -- > Fabien. >
Re: [HACKERS] proposal: session server side variables
ok understand Good. So we seem to agree that GUCS are transactional? The logic depends on transactions and on nesting level (nesting doesn't depends on transactions only) Yep, it probably also happens with LOCAL which hides the previous value and restores the initial one when exiting. void AtEOXact_GUC(bool isCommit, int nestLevel) Probably we should to use CallXactCallbacks instead - then is not a performance impact when there are not transactional variables. I do not understand your point. It is a very good thing that GUCs are transactional, and this should not be changed, it is a useful feature! Much more useful than non transactional. Moreover I think that transactional is expensive when writing things to disk, but in memory the overhead is reduced, and if you need it then you need it. -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
2017-01-04 17:58 GMT+01:00 Fabien COELHO : > > See attached scripts for instance. >>> >> >> Your test shows so SET SESSION has not transactional behaviour - the >> transactions fails, but the value is not reverted to NULL. >> > > There are *two* function calls, the first fails and the second succeeds. > Here is the trace with a some comments: > > [...] > > ## SET SESSION x.x = 'null'; > SET > -- previous has set x.x = 'null' > > ## SELECT setupSecurityContext(3); > -- first setup... function call > NOTICE: SET secured = FALSE > NOTICE: SET secured = TRUE > -- there is a SET to 'ok' just after this print > -- at the end the transaction fails: > ERROR: insert or update on table "log" violates foreign key constraint > "log_sid_fkey" > DETAIL: Key (sid)=(3) is not present in table "stuff". > -- no result is displayed from the SELECT > > ## SHOW x.x; > nul > -- the value is the initial value, it has been reverted > > ## SELECT setupSecurityContext(2); > -- second setup... function call > NOTICE: SET secured = FALSE > NOTICE: SET secured = TRUE > -- trues is displayed, the function succeeded > t > > ## SHOW x.x; > ok > -- the new value is shown ok understand The logic depends on transactions and on nesting level (nesting doesn't depends on transactions only) /* * Do GUC processing at transaction or subtransaction commit or abort, or * when exiting a function that has proconfig settings, or when undoing a * transient assignment to some GUC variables. (The name is thus a bit of * a misnomer; perhaps it should be ExitGUCNestLevel or some such.) * During abort, we discard all GUC settings that were applied at nesting * levels >= nestLevel. nestLevel == 1 corresponds to the main transaction. */ void AtEOXact_GUC(bool isCommit, int nestLevel) Probably we should to use CallXactCallbacks instead - then is not a performance impact when there are not transactional variables. Regards Pavel > > > -- > Fabien. >
Re: [HACKERS] proposal: session server side variables
2017-01-04 17:30 GMT+01:00 Fabien COELHO : > > > Now we can this feature emulate with dblink, and there are patches in >> commitfest based on background workers, and emulation will be cheaper. >> > > I had not noticed that "background session" proposal. That's definitely an > interesting feature to have for some use cases. Dblink implies a new > connection I think, pretty expensive. I wish that the proposal would be > language independent, like DB2 simple AUTONOMOUS declaration on a function. > It seems quite heavily linked to PL/pgSQL right now. Maybe year ago here was a discussion about autonomous transaction design - Robert proposed transaction scope - some like 'BEGIN AUTONOMOUS", I proposed function level. The syntax is not pretty important - this functionality is interesting - mainly for loging to tables - but there are risks - it again border transactional| untransactional - autonomous transactions are "untransactional" from outer transaction perspective - so some unwanted artefacts or risks are possible there - and application design should to respect it. Regards Pavel > > > -- > Fabien. >
Re: [HACKERS] proposal: session server side variables
See attached scripts for instance. Your test shows so SET SESSION has not transactional behaviour - the transactions fails, but the value is not reverted to NULL. There are *two* function calls, the first fails and the second succeeds. Here is the trace with a some comments: [...] ## SET SESSION x.x = 'null'; SET -- previous has set x.x = 'null' ## SELECT setupSecurityContext(3); -- first setup... function call NOTICE: SET secured = FALSE NOTICE: SET secured = TRUE -- there is a SET to 'ok' just after this print -- at the end the transaction fails: ERROR: insert or update on table "log" violates foreign key constraint "log_sid_fkey" DETAIL: Key (sid)=(3) is not present in table "stuff". -- no result is displayed from the SELECT ## SHOW x.x; nul -- the value is the initial value, it has been reverted ## SELECT setupSecurityContext(2); -- second setup... function call NOTICE: SET secured = FALSE NOTICE: SET secured = TRUE -- trues is displayed, the function succeeded t ## SHOW x.x; ok -- the new value is shown -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
> >> Um, what? No, not at all. >> >> GUCs are scoped, but not transactional, [...] >> > > The documentation is very scarse, so I have tested it. > > All tests I have done with commit & rollback on session variables (SET > SESSION) have shown a clean transactional behavior, with the value reverted > on ROLLBACK, whether intentional or automatic, or the new value set on > COMMIT. See attached scripts for instance. > Your test shows so SET SESSION has not transactional behaviour - the transactions fails, but the value is not reverted to NULL. It is good example of antipattern for this routine type :) Pavel > > > Fabien.
Re: [HACKERS] proposal: session server side variables
Now we can this feature emulate with dblink, and there are patches in commitfest based on background workers, and emulation will be cheaper. I had not noticed that "background session" proposal. That's definitely an interesting feature to have for some use cases. Dblink implies a new connection I think, pretty expensive. I wish that the proposal would be language independent, like DB2 simple AUTONOMOUS declaration on a function. It seems quite heavily linked to PL/pgSQL right now. -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
Hello, The security-related use-case you have presented stores the status of the verification in a variable. If the variable is untransactional, then it has been shown that the variable status > may say ok while the verification has really really failed. That's only a concern if the setting xact performs writes. Sure. However I do not see the point of proposing a feature which works only sometimes, on the condition that the security setup does NOT involve storing data in the database (!), otherwise it may be insecure in some cases, sorry mates. That does not look too serious, esp if the use-case concern is all about security. If it's a read-only lookup, all it has to do is set the variable last. Yep, I guess it would probably work for read-only transactions. I agree that transactional variables whose value assignments come into effect on commit would be useful. Like we have for NOTIFY. I do not agree that they are _necessary_ such that a feature is not useful without them. Nor do I agree that they are necessary for security related use. The feature would be clearly misleading without transactional support, because people would use it with false expectation that it works securely, which is not the case. Morover, there is no special cost in implementing transactional on session variables, has it is already done by pg. It can probably be reused. Um, what? No, not at all. GUCs are scoped, but not transactional, [...] The documentation is very scarse, so I have tested it. All tests I have done with commit & rollback on session variables (SET SESSION) have shown a clean transactional behavior, with the value reverted on ROLLBACK, whether intentional or automatic, or the new value set on COMMIT. See attached scripts for instance. LOCAL variables are a different thing, they just disappear at the end of the session, it is more a scoping thing. We'd _definitely_ need to be able to declare such variables, so we could specify their ON COMMIT behaviour (which GUCs don't have) Hmmm. We do not have to declare any ON COMMIT behaviour of TABLES, they are just transactional. and define their scope (like we do for GUCs). I'm fine with defining scopes. An alternative is to implement sub (nested) transactions, like Oracle and MS SQL Server... but that would be quite some work. What? We have those already, see SAVEPOINT and ROLLBACK TO SAVEPOINT. No, that is not what I meant. Unless you mean autonomous transactions, which are not really nested, Yes, that is why I wrote "nested" above. they're closer to having the outer xact suspend while another xact works, then resuming the outer xact. Yep. The point is that you can test the success of the nested transaction before setting the status. -- Fabien. deferred.sql Description: application/sql -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
2017-01-04 14:33 GMT+01:00 Fabien COELHO : > > An alternative is to implement sub (nested) transactions, like Oracle and >> MS SQL Server... but that would be quite some work. >> > > As a complement, a search showed that IBM DB2, cited as a reference by > Pavel, has AUTONOMOUS transactions, which looks pretty much the same thing > as nested transactions. The documentation presents an interesting use > security-related use case: > I had on my mind autonomous transactions. > > https://www.ibm.com/developerworks/data/library/techarticle/ > dm-0907autonomoustransactions/ > > The idea is that an application must record an attempt to access a data > even if the attempt fails and is rolled-back. > Now we can this feature emulate with dblink, and there are patches in commitfest based on background workers, and emulation will be cheaper. Regards Pavel > > This feature used carefully within an appropriate pattern would allow to > ensure that if the setup transaction fails then the session status is > FALSE. One possible inconsistency which may arise with sub xacts is that > the status may stay FALSE while the setup has succeeded, however this on > the safe side wrt to the security use case. > > -- > Fabien. >
Re: [HACKERS] proposal: session server side variables
On 4 Jan. 2017 19:03, "Fabien COELHO" wrote: >>> I respect your opinion and don't agree with it. >> >> >> Yeah. I'm pretty overwhelmingly unconvinced too. > > I'm lost. > > The security-related use-case you have presented stores the status of the > verification in a variable. If the variable is untransactional, then it has > been shown that the variable status > may say ok while the verification has > really really failed. That's only a concern if the setting xact performs writes. If it's a read-only lookup, all it has to do is set the variable last. I agree that transactional variables whose value assignments come into effect on commit would be useful. Like we have for NOTIFY. I do not agree that they are _necessary_ such that a feature is not useful without them. Nor do I agree that they are necessary for security related use. > Morover, there is no special cost in implementing transactional on session > variables, has it is already done by pg. It can probably be reused. Um, what? No, not at all. GUCs are scoped, but not transactional, in the sense that a SET LOCAL _overrides_ the outer SET for the lifetime of the xact or until overwritten by some later SET LOCAL. On xact end, whether rollback or commit, we just unwind the whole scope by popping a stack. Additional handling is present for subxact and special scopes like functions with SET. Transactional assignments would instead need some kind of (sub)transaction stack that flattens onto the outer layer on commit or is popped and discarded on rollback. Not overwhelmingly expensive or hard, but not something we have already. We'd _definitely_ need to be able to declare such variables, so we could specify their ON COMMIT behaviour (which GUCs don't have) and define their scope (like we do for GUCs). > An alternative is to implement sub (nested) transactions, like Oracle and MS > SQL Server... but that would be quite some work. What? We have those already, see SAVEPOINT and ROLLBACK TO SAVEPOINT. Unless you mean autonomous transactions, which are not really nested, they're closer to having the outer xact suspend while another xact works, then resuming the outer xact. -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
An alternative is to implement sub (nested) transactions, like Oracle and MS SQL Server... but that would be quite some work. As a complement, a search showed that IBM DB2, cited as a reference by Pavel, has AUTONOMOUS transactions, which looks pretty much the same thing as nested transactions. The documentation presents an interesting use security-related use case: https://www.ibm.com/developerworks/data/library/techarticle/dm-0907autonomoustransactions/ The idea is that an application must record an attempt to access a data even if the attempt fails and is rolled-back. This feature used carefully within an appropriate pattern would allow to ensure that if the setup transaction fails then the session status is FALSE. One possible inconsistency which may arise with sub xacts is that the status may stay FALSE while the setup has succeeded, however this on the safe side wrt to the security use case. -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
I respect your opinion and don't agree with it. Yeah. I'm pretty overwhelmingly unconvinced too. I'm lost. The security-related use-case you have presented stores the status of the verification in a variable. If the variable is untransactional, then it has been shown that the variable status may say ok while the verification has really really failed. This means that subsequent operations would be executed believing wrongly that the security was ok. Not good. Morover, there is no special cost in implementing transactional on session variables, has it is already done by pg. It can probably be reused. An alternative is to implement sub (nested) transactions, like Oracle and MS SQL Server... but that would be quite some work. So basically I do not see any point in not doing transactional variables. -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
Hello, I'm not sure I understand your point. If Oracle provides unsafe package variables that can fool auditors, it is not a sufficient reason for Pg to provide the same doubtful feature. And if they have sub-transactions then their feature may not necessarily be unsafe, at least if the coding is careful, but this point does not apply to pg. unsafe is wrong word - are you first man, what I know who are expecting transactions from variables - the variables are coming from procedural world - there are not transactions. We have established that the correctness of the security context use case presented by Craig requires transactional variables. This is not my fault. If you present a new feature to implement this use case, then it must match the case requirements. your mental model about variables is pretty artificial - it is strange so Oracle, MSSQL, DB2 30 years didn't find so variables should be transactional. As already said, Pg GUCs are transactional, so Pg is out of its mind? Maybe it is not the case in Oracle when programmed with PL/SQL, then fine. As I said, your pattern can be correct iff a sub-transaction is used. If Oracle has sub-stransaction then untransactional variables can be used for the use case by setting them outside the security verification transaction. So what is maybe fine in Oracle is not fine with Pg without subtransactions. I agree, so there can be some advantages - but I disagree so transactional is major and required feature. Hmmm. I strongly oppose adding a feature which does not implement correctly the use case it is designed for. There are possible artefacts on border transactional and untransactional world - so developer should to use patterns that reduces negative impacts of these artefacts. I do not think that probabilistic security is a good sales pitch. Moreover there is no particular issue with implenting the needed feature, all the mecanism are already available in Pg, so it looks masochistic to refuse to implement a feature which is already available and happen to be necessary to the use case correctness. -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
On 4 January 2017 at 17:31, Pavel Stehule wrote: >> I have also updated and simplified the "simple session variable" >> description, because now I'm convinced that they must be transactional, and >> that a distinct declaration statement is a pain. > > I respect your opinion and don't agree with it. Yeah. I'm pretty overwhelmingly unconvinced too. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
2017-01-04 9:56 GMT+01:00 Fabien COELHO : > > With respect, I don't share your opinion - it is not enough for usage like >> package variables - there usually should not to use any dependency on >> transactions. >> > > I'm not sure I understand your point. If Oracle provides unsafe package > variables that can fool auditors, it is not a sufficient reason for Pg to > provide the same doubtful feature. And if they have sub-transactions then > their feature may not necessarily be unsafe, at least if the coding is > careful, but this point does not apply to pg. unsafe is wrong word - are you first man, what I know who are expecting transactions from variables - the variables are coming from procedural world - there are not transactions. your mental model about variables is pretty artificial - it is strange so Oracle, MSSQL, DB2 30 years didn't find so variables should be transactional. I agree, so there can be some advantages - but I disagree so transactional is major and required feature. There are possible artefacts on border transactional and untransactional world - so developer should to use patterns that reduces negative impacts of these artefacts. > > > More it is dynamic - it should be hard inconsistency to implement CREATE or >> DECLARE statement for GUC. So it is out my proposal (and my goal). >> > > I have added a few questions/remarks about your updated proposal in the > wiki. Feel free to update/answer/discuss these. > > I have also updated and simplified the "simple session variable" > description, because now I'm convinced that they must be transactional, and > that a distinct declaration statement is a pain. I respect your opinion and don't agree with it. Regards Pavel > > > -- > Fabien. >
Re: [HACKERS] proposal: session server side variables
With respect, I don't share your opinion - it is not enough for usage like package variables - there usually should not to use any dependency on transactions. I'm not sure I understand your point. If Oracle provides unsafe package variables that can fool auditors, it is not a sufficient reason for Pg to provide the same doubtful feature. And if they have sub-transactions then their feature may not necessarily be unsafe, at least if the coding is careful, but this point does not apply to pg. More it is dynamic - it should be hard inconsistency to implement CREATE or DECLARE statement for GUC. So it is out my proposal (and my goal). I have added a few questions/remarks about your updated proposal in the wiki. Feel free to update/answer/discuss these. I have also updated and simplified the "simple session variable" description, because now I'm convinced that they must be transactional, and that a distinct declaration statement is a pain. -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
2017-01-03 20:56 GMT+01:00 Fabien COELHO :
>
> Hello,
>
> Probably there is not big difference between RESET and UNDO in complexity
>> of implementation. You have to do partial implementation of MVCC. No
>> simple
>> code.
>>
>
> I think so; yes; indeed.
>
> Also note that user-defined GUCs already implements the transactional
>>> property, so probably the mecanism is already available and can be
>>> reused.
>>>
>>
>> GUC are stack based - the value doesn't depends if transaction was
>> successful or not.
>>
>
> Hmmm... this looks transactional to me:
>
> SELECT set_config('x.x', 'before', FALSE); -- 'before'
> BEGIN;
> SELECT set_config('x.x', 'within', FALSE); -- 'within'
> ROLLBACK;
> SELECT current_setting('x.x'); -- 'before'
> BEGIN;
> SELECT set_config('x.x', 'inside', FALSE); -- 'inside'
> COMMIT;
> SELECT current_setting('x.x'); -- 'inside'
>
> I would say the stack is needed for SAVEPOINT:
>
> SELECT set_config('x.x', 'before', FALSE); -- 'before'
> BEGIN;
> SELECT set_config('x.x', 'within', FALSE); -- 'within'
> SAVEPOINT within;
> SELECT set_config('x.x', 'inside', FALSE); -- 'inside'
> SELECT current_setting('x.x'); -- 'inside'
> ROLLBACK TO SAVEPOINT within;
> SELECT current_setting('x.x'); -- 'within'
> SELECT set_config('x.x', 'further', FALSE); -- 'further'
> ROLLBACK;
> SELECT current_setting('x.x'); -- 'before'
>
> So basically the use case needs GUCs with some access control. Or just
> role-private GUCs and some access function tricks would do as well for the
> use case. At least it is probably much easier to add privacy to gucs than
> to (re)implement permissions and MVCC on some session variables. And it
> would be nice if GUCs could be typed as well...
With respect, I don't share your opinion - it is not enough for usage like
package variables - there usually should not to use any dependency on
transactions.
More it is dynamic - it should be hard inconsistency to implement CREATE or
DECLARE statement for GUC. So it is out my proposal (and my goal).
Regards
Pavel
>
>
> --
> Fabien.
>
Re: [HACKERS] proposal: session server side variables
Hello,
Probably there is not big difference between RESET and UNDO in complexity
of implementation. You have to do partial implementation of MVCC. No simple
code.
I think so; yes; indeed.
Also note that user-defined GUCs already implements the transactional
property, so probably the mecanism is already available and can be reused.
GUC are stack based - the value doesn't depends if transaction was
successful or not.
Hmmm... this looks transactional to me:
SELECT set_config('x.x', 'before', FALSE); -- 'before'
BEGIN;
SELECT set_config('x.x', 'within', FALSE); -- 'within'
ROLLBACK;
SELECT current_setting('x.x'); -- 'before'
BEGIN;
SELECT set_config('x.x', 'inside', FALSE); -- 'inside'
COMMIT;
SELECT current_setting('x.x'); -- 'inside'
I would say the stack is needed for SAVEPOINT:
SELECT set_config('x.x', 'before', FALSE); -- 'before'
BEGIN;
SELECT set_config('x.x', 'within', FALSE); -- 'within'
SAVEPOINT within;
SELECT set_config('x.x', 'inside', FALSE); -- 'inside'
SELECT current_setting('x.x'); -- 'inside'
ROLLBACK TO SAVEPOINT within;
SELECT current_setting('x.x'); -- 'within'
SELECT set_config('x.x', 'further', FALSE); -- 'further'
ROLLBACK;
SELECT current_setting('x.x'); -- 'before'
So basically the use case needs GUCs with some access control. Or just
role-private GUCs and some access function tricks would do as well for the
use case. At least it is probably much easier to add privacy to gucs than
to (re)implement permissions and MVCC on some session variables. And it
would be nice if GUCs could be typed as well...
--
Fabien.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
2017-01-03 18:52 GMT+01:00 Fabien COELHO : > > ** PLEASE ** >>> COULD YOU REMOVE THE PARTS OF EMAILS YOU ARE NOT RESPONDING TO WHEN >>> REPLYING IN THE THREAD? >>> ** THANKS ** >> >> > Hmmm. It seems that you can't. You should, really. I am sorry - The gmail client mask me these parts. I'll clean it more > > > If you use patterns that I wrote - the security context will be valid always. >>> >>> No: This pattern assumes that operations started in the "TRY" zone >>> cannot fail later on... This assumption is false because of possible >>> deferred triggers for instance. See attached example: >>> >> >> ok .. it is pretty artificial, but ok. >> > > Good. We seem to agree that some kind of transactional support is needed > for the use case, which is pretty logical. > > In this case the reset to NULL on ROLLBACK should be enough. >> > > Probably. > > So I expect that you are going to update your proposal somehow to provide > some transactional properties. > > Note that if you have some mecanism for doing a NULL on rollback, then why > not just keep and reset the previous value if needed? This just means that > you have a transactional variable, which is fine from my point of view. As > I already wrote, session variable are memory only, so transactional does > not involve costs such as WAL. > There is not cost such as WAL - in any update, you have to check if this is first update in transaction, and if it is, then you have to create new memory context and create new callback that will be evaluated on rollback. Probably there is not big difference between RESET and UNDO in complexity of implementation. You have to do partial implementation of MVCC. No simple code. > > Also note that user-defined GUCs already implements the transactional > property, so probably the mecanism is already available and can be reused. GUC are stack based - the value doesn't depends if transaction was successful or not. > -- > Fabien. >
Re: [HACKERS] proposal: session server side variables
On 1/3/17 10:33 AM, Fabien COELHO wrote: ** PLEASE ** COULD YOU REMOVE THE PARTS OF EMAILS YOU ARE NOT RESPONDING TO WHEN REPLYING IN THE THREAD? ** THANKS ** +1. Frankly, I've been skipping most of your (Pavel) replies in this thread because of this. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com 855-TREBLE2 (855-873-2532) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
** PLEASE ** COULD YOU REMOVE THE PARTS OF EMAILS YOU ARE NOT RESPONDING TO WHEN REPLYING IN THE THREAD? ** THANKS ** Hmmm. It seems that you can't. You should, really. If you use patterns that I wrote - the security context will be valid always. No: This pattern assumes that operations started in the "TRY" zone cannot fail later on... This assumption is false because of possible deferred triggers for instance. See attached example: ok .. it is pretty artificial, but ok. Good. We seem to agree that some kind of transactional support is needed for the use case, which is pretty logical. In this case the reset to NULL on ROLLBACK should be enough. Probably. So I expect that you are going to update your proposal somehow to provide some transactional properties. Note that if you have some mecanism for doing a NULL on rollback, then why not just keep and reset the previous value if needed? This just means that you have a transactional variable, which is fine from my point of view. As I already wrote, session variable are memory only, so transactional does not involve costs such as WAL. Also note that user-defined GUCs already implements the transactional property, so probably the mecanism is already available and can be reused. -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
2017-01-03 17:33 GMT+01:00 Fabien COELHO : > > ** PLEASE ** > > COULD YOU REMOVE THE PARTS OF EMAILS YOU ARE NOT RESPONDING TO WHEN > REPLYING IN THE THREAD? > > ** THANKS ** > > [...] Then B believes that A succeeded, which is not the case. >>> >> >> No, just your design is unhappy >> > > > > SELECT A(..) >> SET SESSION VARIABLE status_ok = false; >> -- do all, if fails there, >> -- then follow line fails too, or never be executed >> SET SESSION VARIABLE status_ok = true; >> > > My point is that there is no commit in this code, the commit is performed > *AFTER* the last set session, and it mail fail then. > > or >> >> SET SESSION VARIABLE status_ok = true >> TRY >> do something >> CATCH >>ROLLBACK >>SET SESSION VARIABLE status_ok = false >> >> Both I can do in current PL >> > > The fact that "do something" worked does not preclude the overall > transaction to work and to revert "do something" and let status_ok as true. > > The key issue is that the final status (commit or rollback) of the >>> containing transaction cannot be known from within the function, so the >>> session variables cannot reflect this status. >>> >>> So somehow the status_ok variable must be (1) rolledback to previous >>> value >>> or (2) removed (3) set to FALSE or (4) set to NULL. It cannot be TRUE if >>> A >>> containing transactions has failed for the security as I understand it. >>> >>> you don't need do rollback variable if you write well A >> > > My point is that A may still fail *after* setting the variable, because it > is in a transaction. > > If you use patterns that I wrote - the security context will be valid >> always. >> > > No: This pattern assumes that operations started in the "TRY" zone cannot > fail later on... This assumption is false because of possible deferred > triggers for instance. See attached example: > ok .. it is pretty artificial, but ok. In this case the reset to NULL on ROLLBACK should be enough. > > NOTICE: SET secured = FALSE > NOTICE: SET secured = TRUE > ERROR: insert or update on table "log" violates foreign key constraint > "log_sid_fkey" > DETAIL: Key (sid)=(3) is not present in table "stuff". > > The error occurs after secured has been set to TRUE. It is possible only if you are use deferred constraints. It is hard to imagine this scenario in functions like A. Probably you would not to risk on rollback log information. So you will use there elog or some form of autonomous transaction. > > -- > Fabien.
Re: [HACKERS] proposal: session server side variables
** PLEASE ** COULD YOU REMOVE THE PARTS OF EMAILS YOU ARE NOT RESPONDING TO WHEN REPLYING IN THE THREAD? ** THANKS ** [...] Then B believes that A succeeded, which is not the case. No, just your design is unhappy SELECT A(..) SET SESSION VARIABLE status_ok = false; -- do all, if fails there, -- then follow line fails too, or never be executed SET SESSION VARIABLE status_ok = true; My point is that there is no commit in this code, the commit is performed *AFTER* the last set session, and it mail fail then. or SET SESSION VARIABLE status_ok = true TRY do something CATCH ROLLBACK SET SESSION VARIABLE status_ok = false Both I can do in current PL The fact that "do something" worked does not preclude the overall transaction to work and to revert "do something" and let status_ok as true. The key issue is that the final status (commit or rollback) of the containing transaction cannot be known from within the function, so the session variables cannot reflect this status. So somehow the status_ok variable must be (1) rolledback to previous value or (2) removed (3) set to FALSE or (4) set to NULL. It cannot be TRUE if A containing transactions has failed for the security as I understand it. you don't need do rollback variable if you write well A My point is that A may still fail *after* setting the variable, because it is in a transaction. If you use patterns that I wrote - the security context will be valid always. No: This pattern assumes that operations started in the "TRY" zone cannot fail later on... This assumption is false because of possible deferred triggers for instance. See attached example: NOTICE: SET secured = FALSE NOTICE: SET secured = TRUE ERROR: insert or update on table "log" violates foreign key constraint "log_sid_fkey" DETAIL: Key (sid)=(3) is not present in table "stuff". The error occurs after secured has been set to TRUE. -- Fabien. deferred.sql Description: application/sql -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
2017-01-03 15:40 GMT+01:00 Fabien COELHO : > > Hello again, > > *** PLEASE, could you remove the parts of emails you are not responding to > when replying in the thread? THANKS. *** > > [...] Did I understand? >>> >> > I guess that the answer is "no":-) > > When you are running under only one transaction, then you don't need to >> solve reset variables on rollback, because you cannot do anything when >> system fails. Only when you are handling a exception, then system continue, >> and you can or you cannot to set the variable. >> > > Sorry, I do not understand these sentences. > > The usual scenario of using secure content is not related to transactions >> - it is related to session. There are two kind of functions >> >> A. slow and expensive that creates secure content/context >> B. other that use secure content/context >> >> When you are running A, then some secure context is initialised or it is >> invalided. When A fails, then B doesn't work. >> > > Yes, I understand that it is the expected property: B must not work if A > has failed... I'm trying to understand what properties are required on the > session variables wrt to how A ran to achieve this. > > When A is successful, then context is valid to another call of A. Next >> call of A set context or invalidate context. The transactions play nothing >> in this game. >> > > Anything in PostgreSQL is always under a transaction... My concern is for > the following: > > -- in a session, there is a transaction > BEGIN; > SELECT A(...); > -- in A: > -- -> check this and that ... > -- -> insert in log ... > -- -> update something else ... > -- -> all seems fine... > -- SET SESSION VARIABLE status_ok = TRUE; > -- -> could do something else... > -- return from A > ROLLBACK; > -- the commit fails, because some differed trigger somewhere is > unhappy > -- or the user changed its mind... > > Now A has failed, but this could not be known from within the function, > and the status_ok is wrong. If the session proceeds with: > > SELECT B(); > > Then B believes that A succeeded, which is not the case. No, just your design is unhappy SELECT A(..) SET SESSION VARIABLE status_ok = false; -- do all, if fails there, then follow line fails too, or never be executed SET SESSION VARIABLE status_ok = true; or SET SESSION VARIABLE status_ok = true TRY do something CATCH ROLLBACK SET SESSION VARIABLE status_ok = false Both I can do in current PL > > The key issue is that the final status (commit or rollback) of the > containing transaction cannot be known from within the function, so the > session variables cannot reflect this status. > > So somehow the status_ok variable must be (1) rolledback to previous value > or (2) removed (3) set to FALSE or (4) set to NULL. It cannot be TRUE if A > containing transactions has failed for the security as I understand it. > you don't need do rollback variable if you write well A > > Maybe it could work with subtransactions: A calls A', A' succeeds (return, > COMMIT is ok), *then* set user_status = ok. The session variables reflects > that A' succeeded, and if A fails later it is ok because the security is > based on the success of A', not the one of A. However, I'm not sure how > subtransactions can be stated simply and within a session in pg. > > The content of variable (used in PL) is defined by scope - not by >> successful or unsuccessful transactions. The security content will be >> valid >> although user have to do rollback. >> > > I do not understand how the "security context" can be valid of there has > been a rollback which has cancelled all operations: Some log may not have > been written for instance, which would be a key assumption for establishing > the validity of the security context. If you use patterns that I wrote - the security context will be valid always > > > -- > Fabien. >
Re: [HACKERS] proposal: session server side variables
Hello again, *** PLEASE, could you remove the parts of emails you are not responding to when replying in the thread? THANKS. *** [...] Did I understand? I guess that the answer is "no":-) When you are running under only one transaction, then you don't need to solve reset variables on rollback, because you cannot do anything when system fails. Only when you are handling a exception, then system continue, and you can or you cannot to set the variable. Sorry, I do not understand these sentences. The usual scenario of using secure content is not related to transactions - it is related to session. There are two kind of functions A. slow and expensive that creates secure content/context B. other that use secure content/context When you are running A, then some secure context is initialised or it is invalided. When A fails, then B doesn't work. Yes, I understand that it is the expected property: B must not work if A has failed... I'm trying to understand what properties are required on the session variables wrt to how A ran to achieve this. When A is successful, then context is valid to another call of A. Next call of A set context or invalidate context. The transactions play nothing in this game. Anything in PostgreSQL is always under a transaction... My concern is for the following: -- in a session, there is a transaction BEGIN; SELECT A(...); -- in A: -- -> check this and that ... -- -> insert in log ... -- -> update something else ... -- -> all seems fine... -- SET SESSION VARIABLE status_ok = TRUE; -- -> could do something else... -- return from A ROLLBACK; -- the commit fails, because some differed trigger somewhere is unhappy -- or the user changed its mind... Now A has failed, but this could not be known from within the function, and the status_ok is wrong. If the session proceeds with: SELECT B(); Then B believes that A succeeded, which is not the case. The key issue is that the final status (commit or rollback) of the containing transaction cannot be known from within the function, so the session variables cannot reflect this status. So somehow the status_ok variable must be (1) rolledback to previous value or (2) removed (3) set to FALSE or (4) set to NULL. It cannot be TRUE if A containing transactions has failed for the security as I understand it. Maybe it could work with subtransactions: A calls A', A' succeeds (return, COMMIT is ok), *then* set user_status = ok. The session variables reflects that A' succeeded, and if A fails later it is ok because the security is based on the success of A', not the one of A. However, I'm not sure how subtransactions can be stated simply and within a session in pg. The content of variable (used in PL) is defined by scope - not by successful or unsuccessful transactions. The security content will be valid although user have to do rollback. I do not understand how the "security context" can be valid of there has been a rollback which has cancelled all operations: Some log may not have been written for instance, which would be a key assumption for establishing the validity of the security context. -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
2017-01-03 13:03 GMT+01:00 Fabien COELHO : > > Hello Pavel, > > PLEASE, could you remove the parts of emails you are not responding to > when replying in the thread? THANKS. > > The current status is that both proposals are useless because the use case > needs "some" transactional property for security. But probably some > improvements are possible. > Is there use case, when you would to play with transactions and variables and RESET is not enough? >>> >>> I do not know. If you explain more clearly what is meant by a "RESET" on >>> a >>> variable when the transaction fails, then maybe I can have an opinion. >>> Currently I'm just guessing in the dark the precise intended semantics. >>> >> >> reset can means "set to default" >> > > "can"? The question is what does it mean in your proposal, not what it may > mean. So I understand that it means "variable always reset to its default > value at the end of the transaction". yes > > > Now when I though about it - this scenario is not interesting for PL - >> probably can be interesting for some interactive work. In PL you can handle >> transactions - so you know if was or was not any exceptions. And if you >> didn't handle the exception, then you are in "need rollback state", so you >> cannot to anything - look on variable value too. In PL is usually important >> transaction start - difficult question if it can means subtransaction start >> too. >> > > What I understand from this use case variation is that the secure variable > is expected to be set & used only *within* a single transaction, although > across multiple functions typically called from some server-side PL-script, > so that its value outside of the transaction does not matter wrt to > security concerns. Did I understand? When you are running under only one transaction, then you don't need to solve reset variables on rollback, because you cannot do anything when system fails. Only when you are handling a exception, then system continue, and you can or you cannot to set the variable. The usual scenario of using secure content is not related to transactions - it is related to session. There are two kind of functions A. slow and expensive that creates secure content/context B. other that use secure content/context When you are running A, then some secure context is initialised or it is invalided. When A fails, then B doesn't work. When A is successful, then context is valid to another call of A. Next call of A set context or invalidate context. The transactions play nothing in this game. The content of variable (used in PL) is defined by scope - not by successful or unsuccessful transactions. The security content will be valid although user have to do rollback. > > For this use-case, ISTM that the scope of the variable is necessarily the > transaction, not the session, i.e. like using "set_config(..., TRUE)". > > -- > Fabien. >
Re: [HACKERS] proposal: session server side variables
Hello Pavel, PLEASE, could you remove the parts of emails you are not responding to when replying in the thread? THANKS. The current status is that both proposals are useless because the use case needs "some" transactional property for security. But probably some improvements are possible. Is there use case, when you would to play with transactions and variables and RESET is not enough? I do not know. If you explain more clearly what is meant by a "RESET" on a variable when the transaction fails, then maybe I can have an opinion. Currently I'm just guessing in the dark the precise intended semantics. reset can means "set to default" "can"? The question is what does it mean in your proposal, not what it may mean. So I understand that it means "variable always reset to its default value at the end of the transaction". Now when I though about it - this scenario is not interesting for PL - probably can be interesting for some interactive work. In PL you can handle transactions - so you know if was or was not any exceptions. And if you didn't handle the exception, then you are in "need rollback state", so you cannot to anything - look on variable value too. In PL is usually important transaction start - difficult question if it can means subtransaction start too. What I understand from this use case variation is that the secure variable is expected to be set & used only *within* a single transaction, although across multiple functions typically called from some server-side PL-script, so that its value outside of the transaction does not matter wrt to security concerns. Did I understand? For this use-case, ISTM that the scope of the variable is necessarily the transaction, not the session, i.e. like using "set_config(..., TRUE)". -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
2017-01-02 16:55 GMT+01:00 Fabien COELHO : > > Hello, > > In my proposal was support for transaction scope - ON COMMIT RESET clause should be ok >>> >>> Could you update the wiki, both the proposal and the use-case >>> implementation, to reflect this point? >>> >>> Moreover, is there any actual use-case for non-transactional secure >>> half-persistent session variables? AFAICS the "secure" part implies both >>> permissions and transactional for the presented security-related use >>> case. >>> If there is no use case for these combined features, then ISTM that you >>> should update to proposal so that the variables are always transactional, >>> which is both simpler, more consistent, and I think more acceptable. >>> >> >> If you are transaction sensitive, then you have to be sensitive to >> subtransactions - then the work is much more complex. >> > > Maybe, probably, I do not really know. For now, I'm trying to determine > how the proposals fits Craig's use case. > > The current status is that both proposals are useless because the use case > needs "some" transactional property for security. But probably some > improvements are possible. > > Is there use case, when you would to play with transactions and variables >> and RESET is not enough? >> > > I do not know. If you explain more clearly what is meant by a "RESET" on a > variable when the transaction fails, then maybe I can have an opinion. > Currently I'm just guessing in the dark the precise intended semantics. reset can means "set to default" Now when I though about it - this scenario is not interesting for PL - probably can be interesting for some interactive work. In PL you can handle transactions - so you know if was or was not any exceptions. And if you didn't handle the exception, then you are in "need rollback state", so you cannot to anything - look on variable value too. In PL is usually important transaction start - difficult question if it can means subtransaction start too. Regards Pavel > > > -- > Fabien. >
Re: [HACKERS] proposal: session server side variables
Hello, In my proposal was support for transaction scope - ON COMMIT RESET clause should be ok Could you update the wiki, both the proposal and the use-case implementation, to reflect this point? Moreover, is there any actual use-case for non-transactional secure half-persistent session variables? AFAICS the "secure" part implies both permissions and transactional for the presented security-related use case. If there is no use case for these combined features, then ISTM that you should update to proposal so that the variables are always transactional, which is both simpler, more consistent, and I think more acceptable. If you are transaction sensitive, then you have to be sensitive to subtransactions - then the work is much more complex. Maybe, probably, I do not really know. For now, I'm trying to determine how the proposals fits Craig's use case. The current status is that both proposals are useless because the use case needs "some" transactional property for security. But probably some improvements are possible. Is there use case, when you would to play with transactions and variables and RESET is not enough? I do not know. If you explain more clearly what is meant by a "RESET" on a variable when the transaction fails, then maybe I can have an opinion. Currently I'm just guessing in the dark the precise intended semantics. -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
Attention! rollback is significantly expensive than RESET. I'm quite unclear about the difference... Transactional for an unshared only-in-memory session object is probably easy to implement, no WAL is needed... So I do not see the difference you have to store previous value This does not fully answer my question. Maybe RESET would put NULL instead of the previous value in a rollback? If so, I must admit that I do not see any fundamental issue with holding temporarily the initial value of an in-memory session variables so as to be able to rool it back if required... There are no any product where variables are transactional - we should not to create wheel. Well, AFAICS PostgreSQL GUCs are transactional. that is exception .. That is just logic: if you make an argument based on "it does not exist", then the argument is void if someone produces a counter example. show me some transactiinal variables from msql, oracle, db2 I do not really know these three particular products. All I can say is that from a semantical point of view the contents of any one-row temporary relation is somehow a transactional session variable. However I do not know whether the 3 cited products have temporary tables, this is just a guess. -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
2017-01-02 11:48 GMT+01:00 Fabien COELHO : > > Hello Pavel, > > In my proposal was support for transaction scope - ON COMMIT RESET clause >> should be ok >> > > Could you update the wiki, both the proposal and the use-case > implementation, to reflect this point? > > Moreover, is there any actual use-case for non-transactional secure > half-persistent session variables? AFAICS the "secure" part implies both > permissions and transactional for the presented security-related use case. > If there is no use case for these combined features, then ISTM that you > should update to proposal so that the variables are always transactional, > which is both simpler, more consistent, and I think more acceptable. > If you are transaction sensitive, then you have to be sensitive to subtransactions - then the work is much more complex. Is there use case, when you would to play with transactions and variables and RESET is not enough? > > Also, you used a TEMPORARY session variable in one implementation, but > this is not described in the proposal, I think it is worth mentioning it > there as well. I will fix it. Regards Pavel > > > -- > Fabien. >
Re: [HACKERS] proposal: session server side variables
Yep, the variable value must be rolled back, I think. Attention! rollback is significantly expensive than RESET. I'm quite unclear about the difference... Transactional for an unshared only-in-memory session object is probably easy to implement, no WAL is needed... So I do not see the difference. There are no any product where variables are transactional - we should not to create wheel. Well, AFAICS PostgreSQL GUCs are transactional. -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
Hello Pavel, In my proposal was support for transaction scope - ON COMMIT RESET clause should be ok Could you update the wiki, both the proposal and the use-case implementation, to reflect this point? Moreover, is there any actual use-case for non-transactional secure half-persistent session variables? AFAICS the "secure" part implies both permissions and transactional for the presented security-related use case. If there is no use case for these combined features, then ISTM that you should update to proposal so that the variables are always transactional, which is both simpler, more consistent, and I think more acceptable. Also, you used a TEMPORARY session variable in one implementation, but this is not described in the proposal, I think it is worth mentioning it there as well. -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
2017-01-02 10:39 GMT+01:00 Fabien COELHO : > > Hello Craig, > > What if setup_user() succeeds as a function but the transaction it belongs >>> to fails for some reason (eg deferred constraints, other operation related >>> to setting user up but outside of this function fails, there is replication >>> issue... whatever, a transaction may fail by definition)? >>> >>> ISTM that the security models requires that USER_IS_AUDITOR is reverted, >>> so although it is definitely a session variable, it must be transactional >>> (MVCC) nevertheless. >>> >> >> No strong opinion here. >> >> IMO the simplest answer should be the main focus here: if it's session >> level, it's session level. Not kinda-sesion-level kinda-transaction-level. >> > > There is no contradiction between session level & transactions: a session > executes transactions, fine. TEMP tables are MVCC *and* session level. > > I can see occasional uses for what you describe though. >> > > My question is not strictly about use, it is about a key security point > related to the presented use case, which is about security. The whole > discussion of the thread being about somehow-secured session variables. > > ISTM that if the transaction setting the value fails and the secure > variable says that all is well thus allows other operations to proceed > believing that the credentials have been veted while in reality they have > not, that's no good. > > So my understanding of this use case is that the involved session variable > which hold the state must be transactional. Other use cases may have > different requirements and security implications. > > If we landed up with an xact scope option like we have for SET LOCAL GUCs, >> > > ISTM that it is a little different. The GUC local option makes the > variable value always disappear after the xacts, whether it succeeds or > not. The semantics needed here is that the value must disappear if the xact > fails but not if it succeeds, which is the current behavior of GUCs with > is_local=FALSE. > > the option to mark it ON COMMIT RESET or ON COMMIT SET would be useful I >> guess. I'm not sure if it's worth the complexity. >> > > My question right now is rather to determine what are the precise and hard > requirements of the use case. > > I guess defaulting to rolling back variable effects on xact rollback would >> be ok too. Just kind of limiting. >> > > Yep, the variable value must be rolled back, I think. attention! rollback is significantly expensive than RESET. There are no any product where variables are transactional - we should not to create wheel. Regards Pavel > > > -- > Fabien. >
Re: [HACKERS] proposal: session server side variables
Hello Craig, What if setup_user() succeeds as a function but the transaction it belongs to fails for some reason (eg deferred constraints, other operation related to setting user up but outside of this function fails, there is replication issue... whatever, a transaction may fail by definition)? ISTM that the security models requires that USER_IS_AUDITOR is reverted, so although it is definitely a session variable, it must be transactional (MVCC) nevertheless. No strong opinion here. IMO the simplest answer should be the main focus here: if it's session level, it's session level. Not kinda-sesion-level kinda-transaction-level. There is no contradiction between session level & transactions: a session executes transactions, fine. TEMP tables are MVCC *and* session level. I can see occasional uses for what you describe though. My question is not strictly about use, it is about a key security point related to the presented use case, which is about security. The whole discussion of the thread being about somehow-secured session variables. ISTM that if the transaction setting the value fails and the secure variable says that all is well thus allows other operations to proceed believing that the credentials have been veted while in reality they have not, that's no good. So my understanding of this use case is that the involved session variable which hold the state must be transactional. Other use cases may have different requirements and security implications. If we landed up with an xact scope option like we have for SET LOCAL GUCs, ISTM that it is a little different. The GUC local option makes the variable value always disappear after the xacts, whether it succeeds or not. The semantics needed here is that the value must disappear if the xact fails but not if it succeeds, which is the current behavior of GUCs with is_local=FALSE. the option to mark it ON COMMIT RESET or ON COMMIT SET would be useful I guess. I'm not sure if it's worth the complexity. My question right now is rather to determine what are the precise and hard requirements of the use case. I guess defaulting to rolling back variable effects on xact rollback would be ok too. Just kind of limiting. Yep, the variable value must be rolled back, I think. -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
2017-01-02 3:06 GMT+01:00 Craig Ringer : > > > On 1 Jan. 2017 20:03, "Fabien COELHO" wrote: > > > > What if setup_user() succeeds as a function but the transaction it belongs > to fails for some reason (eg deferred constraints, other operation related > to setting user up but outside of this function fails, there is replication > issue... whatever, a transaction may fail by definition)? > > ISTM that the security models requires that USER_IS_AUDITOR is reverted, > so although it is definitely a session variable, it must be transactional > (MVCC) nevertheless. > > > No strong opinion here. > > IMO the simplest answer should be the main focus here: if it's session > level, it's session level. Not kinda-sesion-level kinda-transaction-level. > > I can see occasional uses for what you describe though. If we landed up > with an xact scope option like we have for SET LOCAL GUCs, the option to > mark it ON COMMIT RESET or ON COMMIT SET would be useful I guess. I'm not > sure if it's worth the complexity. > In my proposal was support for transaction scope - ON COMMIT RESET clause should be ok Regards Pavel > > I guess defaulting to rolling back variable effects on xact rollback would > be ok too. Just kind of limiting. >
Re: [HACKERS] proposal: session server side variables
On 1 Jan. 2017 20:03, "Fabien COELHO" wrote: What if setup_user() succeeds as a function but the transaction it belongs to fails for some reason (eg deferred constraints, other operation related to setting user up but outside of this function fails, there is replication issue... whatever, a transaction may fail by definition)? ISTM that the security models requires that USER_IS_AUDITOR is reverted, so although it is definitely a session variable, it must be transactional (MVCC) nevertheless. No strong opinion here. IMO the simplest answer should be the main focus here: if it's session level, it's session level. Not kinda-sesion-level kinda-transaction-level. I can see occasional uses for what you describe though. If we landed up with an xact scope option like we have for SET LOCAL GUCs, the option to mark it ON COMMIT RESET or ON COMMIT SET would be useful I guess. I'm not sure if it's worth the complexity. I guess defaulting to rolling back variable effects on xact rollback would be ok too. Just kind of limiting.
Re: [HACKERS] proposal: session server side variables
2017-01-01 11:28 GMT+01:00 Fabien COELHO :
>
> Hello Pavel, and Happy new year!
>
> (1) Having some kind of variable, especially in interactive mode, allows to
>>>
manipulate previous results and reuse them later, without having to
> resort to repeated sub-queries or to retype non trivial values.
>
> Client side psql :-variables are untyped and unescaped, thus not very
> convenient for this purpose.
>
You can currently (ab)use user defined GUCs for this.
>>>
>>> How? It seems that I have missed the syntax to assign the result of a
>>> query to a user-defined guc, and to reuse it simply in a query.
>>>
>>
> postgres=# select set_config('myvar.text', (select
>> current_timestamp::text), false);
>>
>
> Thanks for the pointer! The documentation is rather scarse...
>
> They are indeed session or transaction-alive. They seem to be
> user-private, which is good. However they are text only, casts are needed
> in practice as shown by your example, and I find the syntax quite
> unfriendly for interactive use. I'm not sure about performance.
>
With some simple getter/setter functions you can get better comfort.
For not text variables you needs one cast more - probably only "date"
"timestamp" can be noticeable slower.
Regards
Pavel
>
> I have added a subsection about them in the wiki.
>
> --
> Fabien.
>
Re: [HACKERS] proposal: session server side variables
Hello Craig, and happy new year,
Someone asked me off-list what use cases such a thing would have,
since it seems not to be spelled out very clearly in this discussion.
I think we're all assuming knowledge here.
So.
* Session starts
* app does SELECT setup_user('user-auth-key-data', 'some-other-blob')
** setup_user is SECURITY DEFINER to 'appadmin'
** 'appadmin' owns a variable IS_AUDITOR. Other roles have only read
access to it.
** setup_user(...) does whatever expensive/slow work it has to do
** setup_user sets USER_IS_AUDITOR var
* Later RLS policies simply reference USER_IS_AUDITOR var. They don't
need to know the 'user-auth-key-data', or do whatever expensive
processing that it does.
* Other later triggers, etc, also reference USER_IS_AUDITOR
* User cannot make themselves an auditor by SETting USER_IS_AUDITOR
That's the general idea.
After giving it some thoughts, I have a question about this use case wrt
to transactions:
What if setup_user() succeeds as a function but the transaction it belongs
to fails for some reason (eg deferred constraints, other operation related
to setting user up but outside of this function fails, there is
replication issue... whatever, a transaction may fail by definition)?
ISTM that the security models requires that USER_IS_AUDITOR is reverted,
so although it is definitely a session variable, it must be transactional
(MVCC) nevertheless.
--
Fabien.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
Hello Pavel, and Happy new year!
(1) Having some kind of variable, especially in interactive mode, allows to
manipulate previous results and reuse them later, without having to
resort to repeated sub-queries or to retype non trivial values.
Client side psql :-variables are untyped and unescaped, thus not very
convenient for this purpose.
You can currently (ab)use user defined GUCs for this.
How? It seems that I have missed the syntax to assign the result of a
query to a user-defined guc, and to reuse it simply in a query.
postgres=# select set_config('myvar.text', (select
current_timestamp::text), false);
Thanks for the pointer! The documentation is rather scarse...
They are indeed session or transaction-alive. They seem to be
user-private, which is good. However they are text only, casts are needed
in practice as shown by your example, and I find the syntax quite
unfriendly for interactive use. I'm not sure about performance.
I have added a subsection about them in the wiki.
--
Fabien.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
2016-12-31 18:46 GMT+01:00 Fabien COELHO : > >DROP VARIABLE super_secret; >>>CREATE VARIABLE super_secret ...; >>> >> >> But you don't do it in functions - these variables are persistent - you >> don't create it or drop inside functions. The content is secure, so you >> don't need to hide this variable against other. >> > > ISTM that you are still missing my point. > > I understood that you want a static analysis tool to re-assure you about > how your session variables are manipulated. I do not see how such a tool > can give any assurance without checking that the variable meta-data are not > changed by some malicious code inserted in a function. if you afraid this, then just use grep to verify functions that have this code. It is same like tables - you can generate it dynamicly, but is risks - similar to use dynamic SQL. Sure, there is a exceptions - but there are rules for PL - don't use dynamic SQL if it is not deadly necessary, use SQL security, not own, ... > > > >>> I'm not sure that I understand these sentences. >>> >> >> >> so I don't prefer any design that increase a area where plpgsql_check >> should not work. >> > > My assumption is that plpgsql_check can be improved. For instance, I > assume that if "secure session variables" are added, then it will be > enhanced to do some checking about these and take them into account. If > "simple session variables" are added, I assume that it would also be > updated accordingly. in simple session variables there are not any safe point - any authoritative point. Sure I can do some - I can introduce some hints, etc - but it is workaround - nothing more - it like C development without header files. > > > I wrote my notes there. >>> >>> Great! I restructured a little bit and tried to improve the English. I >>> also added questions when some statement that I think are too optimistic, >>> or are unclear to me. >>> >> >> we have just different perspectives >> > > I'm trying to have sentences that are both clear and true. If I think that > a sentence is imprecise because it is missing a key hypothesis, then I try > to improve it, whether it is mine or someone else. > > > -- > Fabien. >
Re: [HACKERS] proposal: session server side variables
>
> If you do not have expectations, then all is fine.
>
> (1) Having some kind of variable, especially in interactive mode, allows to
>>> manipulate previous results and reuse them later, without having to
>>> resort
>>> to repeated sub-queries or to retype non trivial values.
>>>
>>> Client side psql :-variables are untyped and unescaped, thus not very
>>> convenient for this purpose.
>>>
>>
>> You can currently (ab)use user defined GUCs for this.
>>
>
> How? It seems that I have missed the syntax to assign the result of a
> query to a user-defined guc, and to reuse it simply in a query.
>
>
postgres=# select set_config('myvar.text', (select
current_timestamp::text), false);
+---+
| set_config |
+---+
| 2016-12-31 18:56:42.894246+01 |
+---+
(1 row)
Time: 0,448 ms
postgres=# select current_setting('myvar.text');
+---+
|current_setting|
+---+
| 2016-12-31 18:56:42.894246+01 |
+---+
(1 row)
--
> Fabien.
>
Re: [HACKERS] proposal: session server side variables
DROP VARIABLE super_secret; CREATE VARIABLE super_secret ...; But you don't do it in functions - these variables are persistent - you don't create it or drop inside functions. The content is secure, so you don't need to hide this variable against other. ISTM that you are still missing my point. I understood that you want a static analysis tool to re-assure you about how your session variables are manipulated. I do not see how such a tool can give any assurance without checking that the variable meta-data are not changed by some malicious code inserted in a function. I'm not sure that I understand these sentences. so I don't prefer any design that increase a area where plpgsql_check should not work. My assumption is that plpgsql_check can be improved. For instance, I assume that if "secure session variables" are added, then it will be enhanced to do some checking about these and take them into account. If "simple session variables" are added, I assume that it would also be updated accordingly. I wrote my notes there. Great! I restructured a little bit and tried to improve the English. I also added questions when some statement that I think are too optimistic, or are unclear to me. we have just different perspectives I'm trying to have sentences that are both clear and true. If I think that a sentence is imprecise because it is missing a key hypothesis, then I try to improve it, whether it is mine or someone else. -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
Hello Craig, As for "slow", I have just tested overheads with pgbench, comparing a direct arithmetic operation (as a proxy to a fast session variable consultation) to constant returning plpgsql functions with security definer and security invoker, on a direct socket connection, with prepared statements: select 1 + 0: 0.020 ms select one_sd() : 0.024 ms select one_si() : 0.024 ms That's one call per executor run. Not really an effective test. I really did 10 calls per transaction. For one call it was 24 ms vs 28 ms. However I'm not sure of the respective overheads of the protocol, planer and executor, though. Consider cases like row security where you're testing 1 rows. Another test: calling 1,000,000 times one_sd() or one_si() in a plpgsql loops seems to cost about 1.1 seconds on my laptop. I'd say that the function call is about 2/3 of that time, the rest is on the loop and exit test. SELECT NOW(); DO LANGUAGE plpgsql $$ DECLARE count INT DEFAULT 0; BEGIN LOOP count := count + ONE_SD() ; EXIT WHEN count = 100; END LOOP; END; $$; SELECT NOW(); Based on these evidences, I continue to think that there is no significant performance issue with calling simple security definer functions. Hopefully the planner will inline the test if it's a function declared stable, but it may not. Indeed they are, so the planner should factor out the test when possible. * On what basis do you _oppose_ persistently defining variables in the catalogs as their own entities? In understand that you are speaking of "persistent session variables". For me a database is about persistence (metadata & data) with safety (transactions) and security (permissions)... and maybe performance:-) Pavel's proposal creates a new object with 2 (secure metadata-persistence) out of 4 properties... I'm not a ease with introducting a new half-database concept in a database. I strongly disagree. If you want "all-database" properties ... use tables. Sure. I am not sure about what are you disagreeing with, as I'm just describing Pavel's proposal... We generally add new features when that's not sufficient to achieve something. Most notably SEQUENCEs, which deliberately violate transaction isolation and atomicity in order to deliver a compelling benefit not otherwise achieveable. Yes, sure. On the other hand there are dynamic session variables (mysql, mssql, oracle have some variants) which are useful on their own without pretending to be database objects (no CREATE/ALTER/DROP, GRANT/REVOKE). We have precent here for sequences. Yes, they do confuse users, but they're also VERY useful, and the properties of variables would be clearer IMO. Yep. But my point is that before adding a new strange object type I would prefer that there is no other solution. I'm not especially attached to doing them as database objects; I'm just as happy with something declared at session start by some function that then intends to set and use the variable. But I don't think your argument against a DDL-like approach holds water. I have expectations about objects hold by a database, and these new object fails them. If you do not have expectations, then all is fine. (1) Having some kind of variable, especially in interactive mode, allows to manipulate previous results and reuse them later, without having to resort to repeated sub-queries or to retype non trivial values. Client side psql :-variables are untyped and unescaped, thus not very convenient for this purpose. You can currently (ab)use user defined GUCs for this. How? It seems that I have missed the syntax to assign the result of a query to a user-defined guc, and to reuse it simply in a query. -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
2016-12-31 17:51 GMT+01:00 Fabien COELHO : > > unexpectedly, that would be missed by a PL/pgSQL analysis tool... There is >>> no miracle. >>> >> >> No - metadata, in my design, are persistent - like tables - so you don't >> calculate so any functions can drop a variables. The deployment of secure >> variables is same like table deployment. No dynamic there. >> > > You are missing my point: Static analysis is about proving properties. If > you need metadata to be persistent, then you should check that it is the > case, i.e. the static analysis must check that there is no metadata changes > anywhere. For instance, an analysis tool should reject a function which > contains: > >GRANT UPDATE ON VARIABLE super_secret_variable TO PUBLIC; > It doesn't need to reject this functions - but this information is not visible in any other functions. But this tasks you are do in deployment scripts - not in functions. > > Or does: > >DROP VARIABLE super_secret; >CREATE VARIABLE super_secret ...; > But you don't do it in functions - these variables are persistent - you don't create it or drop inside functions. The content is secure, so you don't need to hide this variable against other. > > If a static analysis tool is specific to one language, then it can only > checks that all is well in functions in those languages, but as there may > be functions written in other languages as well then the check is somehow > partial. This is not a bad thing, it just illustrate that you cannot check > everything. That is quality ensurance. > > [...] Indeed, probably there exists some class of typos that may not be >>> found by some static analysis implementations on PL/pgSQL functions which >>> uses basic session variables. >>> >> >> yes, and I would not to append any new case that cannot be covered by >> plpgsql check. Dynamic SQL and our temporal tables are enough issues >> already. >> > > I'm not sure that I understand these sentences. so I don't prefer any design that increase a area where plpgsql_check should not work. > > > I wrote my notes there. >> > > Great! I restructured a little bit and tried to improve the English. I > also added questions when some statement that I think are too optimistic, > or are unclear to me. we have just different perspectives Regards Pavel > > > -- > Fabien. >
Re: [HACKERS] proposal: session server side variables
unexpectedly, that would be missed by a PL/pgSQL analysis tool... There is no miracle. No - metadata, in my design, are persistent - like tables - so you don't calculate so any functions can drop a variables. The deployment of secure variables is same like table deployment. No dynamic there. You are missing my point: Static analysis is about proving properties. If you need metadata to be persistent, then you should check that it is the case, i.e. the static analysis must check that there is no metadata changes anywhere. For instance, an analysis tool should reject a function which contains: GRANT UPDATE ON VARIABLE super_secret_variable TO PUBLIC; Or does: DROP VARIABLE super_secret; CREATE VARIABLE super_secret ...; If a static analysis tool is specific to one language, then it can only checks that all is well in functions in those languages, but as there may be functions written in other languages as well then the check is somehow partial. This is not a bad thing, it just illustrate that you cannot check everything. That is quality ensurance. [...] Indeed, probably there exists some class of typos that may not be found by some static analysis implementations on PL/pgSQL functions which uses basic session variables. yes, and I would not to append any new case that cannot be covered by plpgsql check. Dynamic SQL and our temporal tables are enough issues already. I'm not sure that I understand these sentences. I wrote my notes there. Great! I restructured a little bit and tried to improve the English. I also added questions when some statement that I think are too optimistic, or are unclear to me. -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
2016-12-31 1:16 GMT+01:00 Craig Ringer : > On 30 December 2016 at 21:00, Fabien COELHO wrote: > > > As for "slow", I have just tested overheads with pgbench, comparing a > direct > > arithmetic operation (as a proxy to a fast session variable > consultation) to > > constant returning plpgsql functions with security definer and security > > invoker, on a direct socket connection, with prepared statements: > > > > select 1 + 0: 0.020 ms > > select one_sd() : 0.024 ms > > select one_si() : 0.024 ms > > That's one call per executor run. Not really an effective test. > > Consider cases like row security where you're testing 1 rows. > Hopefully the planner will inline the test if it's a function declared > stable, but it may not. > > > > However the one-row property is just hoped for, and on principle a > database > > is about declaring constraints that are enforced afterwards. > > > > I see two clean solutions to this use case: declaring tables as one row, > or > > having scalar objects. > > > I agree that's a common issue. > > The unique partial index on 1 hack in postgres works, though it's ugly. > > Adding a whole new different storage concept seems like massive > overkill for this problem, which is minor and already easily solved. > Someone could make 1-row tables prettier with a new constraint type > instead maybe, if it's really considered that ugly. Personally I'd > just document the unique expression index hack. > > CREATE UNIQUE INDEX onerow ON mytable((1)); > > >> * On what basis do you _oppose_ persistently defining variables in the > >> catalogs as their own entities? > > > > In understand that you are speaking of "persistent session variables". > > > > For me a database is about persistence (metadata & data) with safety > > (transactions) and security (permissions)... and maybe performance:-) > > > > Pavel's proposal creates a new object with 2 (secure > metadata-persistence) > > out of 4 properties... I'm not a ease with introducting a new > half-database > > concept in a database. > > I strongly disagree. If you want "all-database" properties ... use tables. > > We generally add new features when that's not sufficient to achieve > something. Most notably SEQUENCEs, which deliberately violate > transaction isolation and atomicity in order to deliver a compelling > benefit not otherwise achieveable. > > Similarly for advisory locking. > > > On the other hand there are dynamic session variables (mysql, mssql, > oracle > > have some variants) which are useful on their own without pretending to > be > > database objects (no CREATE/ALTER/DROP, GRANT/REVOKE). > > We have precent here for sequences. Yes, they do confuse users, but > they're also VERY useful, and the properties of variables would be > clearer IMO. > > I'm not especially attached to doing them as database objects; I'm > just as happy with something declared at session start by some > function that then intends to set and use the variable. But I don't > think your argument against a DDL-like approach holds water. > > >> (My own objection is that "temporary variables" would make our existing > >> catalog bloat issues for temp objects even worse). > > > > > > I do agree that inefficient temporary variables are worthless, but ISTM > that > > Pavel's proposal is not exactly about temporary variables, it is about > > temporary-valued permanent-variables. So there is no temporary (on the > fly) > > variable as such, and if it is extended for this purpose then indeed the > > catalog costs look expensive. > > I meant that we'd certainly want CREATE TEMPORARY VARIABLE for ones > that go away at end of session, if we were going to have > catalog-object-like variables. Which would result in catalog bloat. > Because our catalog is MVCC, then bloating is unremovable - but if we implement global temporary tables, then metadata of temporary objects can be stored there - the main catalogue can be stable. But the question? When you would to use local temporary variables? When you cannot to use global variables? Probably in adhoc scripts, in interactive work, ... It is minimal impact on catalogue. The performance problems can be in PL usage, or intensive application usage - and there can be used global variables. Analogy with our temporary tables - if we can use global temporary tables in critical PL, then local temporary tables can be nice feature perfect for interactive work, and nobody have to fix a catalogue bloat. Design of possibility to do local temporary variable is minimal work. I don't afraid about performance when developers can use global variables as option Regards Pavel > > (1) Having some kind of variable, especially in interactive mode, allows > to > > manipulate previous results and reuse them later, without having to > resort > > to repeated sub-queries or to retype non trivial values. > > > > Client side psql :-variables are untyped and unescaped, thus not very > > convenient for this purpose. > > You can currently
Re: [HACKERS] proposal: session server side variables
On 30 December 2016 at 21:00, Fabien COELHO wrote: > As for "slow", I have just tested overheads with pgbench, comparing a direct > arithmetic operation (as a proxy to a fast session variable consultation) to > constant returning plpgsql functions with security definer and security > invoker, on a direct socket connection, with prepared statements: > > select 1 + 0: 0.020 ms > select one_sd() : 0.024 ms > select one_si() : 0.024 ms That's one call per executor run. Not really an effective test. Consider cases like row security where you're testing 1 rows. Hopefully the planner will inline the test if it's a function declared stable, but it may not. > However the one-row property is just hoped for, and on principle a database > is about declaring constraints that are enforced afterwards. > > I see two clean solutions to this use case: declaring tables as one row, or > having scalar objects. I agree that's a common issue. The unique partial index on 1 hack in postgres works, though it's ugly. Adding a whole new different storage concept seems like massive overkill for this problem, which is minor and already easily solved. Someone could make 1-row tables prettier with a new constraint type instead maybe, if it's really considered that ugly. Personally I'd just document the unique expression index hack. CREATE UNIQUE INDEX onerow ON mytable((1)); >> * On what basis do you _oppose_ persistently defining variables in the >> catalogs as their own entities? > > In understand that you are speaking of "persistent session variables". > > For me a database is about persistence (metadata & data) with safety > (transactions) and security (permissions)... and maybe performance:-) > > Pavel's proposal creates a new object with 2 (secure metadata-persistence) > out of 4 properties... I'm not a ease with introducting a new half-database > concept in a database. I strongly disagree. If you want "all-database" properties ... use tables. We generally add new features when that's not sufficient to achieve something. Most notably SEQUENCEs, which deliberately violate transaction isolation and atomicity in order to deliver a compelling benefit not otherwise achieveable. Similarly for advisory locking. > On the other hand there are dynamic session variables (mysql, mssql, oracle > have some variants) which are useful on their own without pretending to be > database objects (no CREATE/ALTER/DROP, GRANT/REVOKE). We have precent here for sequences. Yes, they do confuse users, but they're also VERY useful, and the properties of variables would be clearer IMO. I'm not especially attached to doing them as database objects; I'm just as happy with something declared at session start by some function that then intends to set and use the variable. But I don't think your argument against a DDL-like approach holds water. >> (My own objection is that "temporary variables" would make our existing >> catalog bloat issues for temp objects even worse). > > > I do agree that inefficient temporary variables are worthless, but ISTM that > Pavel's proposal is not exactly about temporary variables, it is about > temporary-valued permanent-variables. So there is no temporary (on the fly) > variable as such, and if it is extended for this purpose then indeed the > catalog costs look expensive. I meant that we'd certainly want CREATE TEMPORARY VARIABLE for ones that go away at end of session, if we were going to have catalog-object-like variables. Which would result in catalog bloat. > (1) Having some kind of variable, especially in interactive mode, allows to > manipulate previous results and reuse them later, without having to resort > to repeated sub-queries or to retype non trivial values. > > Client side psql :-variables are untyped and unescaped, thus not very > convenient for this purpose. You can currently (ab)use user defined GUCs for this. Ugly, but effective, and honestly something we could bless into general use if we decided to. It's not that bad. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
2016-12-30 18:39 GMT+01:00 Fabien COELHO : > > DECLARE @var EXTERNAL >>> >>> I do not know what you mean by 'EXTERNAL'. >>> >> >> it means "used as shared in session" >> > > Shared by whom? There is no such thing in the proposal I have made on the > wiki or in mails. In the proposal variables are private to the role which > creates them. shared by functions in session. > > > It is possible to find "some" typos, depending on the code: you can check >>> that a variable is both assigned and used somewhere, otherwise it is very >>> probably a typo. Perl does that *statically*, before executing a script. >>> >> >> "Before execution" .. I am speaking "without execution". >> > > Before execution is also without execution. You can run "perl -c" to get > the warning. > > theoretically, if you check all possible functions, you can find some >> issues - but you have to check all function on server. >> > > Yes, sure. It seems to be the same with your proposal: if a hidden > function drops and recreates a session variable with a different type, or > changes its permission, then some static checks are void as well, that is > life. Also, a SQL function may access and modify the variables > unexpectedly, that would be missed by a PL/pgSQL analysis tool... There is > no miracle. > > No - metadata, in my design, are persistent - like tables - so you don't calculate so any functions can drop a variables. The deployment of secure variables is same like table deployment. No dynamic there. > Basically, there may be issues even if static analysis tools says that all > is well. > > Elsewhere you cannot to find typo in DECLARE statement. >> > > Indeed, probably there exists some class of typos that may not be found by > some static analysis implementations on PL/pgSQL functions which uses basic > session variables. > yes, and I would not to append any new case that cannot be covered by plpgsql check. Dynamic SQL and our temporal tables are enough issues already. > > By the way, are you planing to contribute to the wiki? > > https://wiki.postgresql.org/wiki/Variable_Design I wrote my notes there. > > > -- > Fabien. >
Re: [HACKERS] proposal: session server side variables
DECLARE @var EXTERNAL I do not know what you mean by 'EXTERNAL'. it means "used as shared in session" Shared by whom? There is no such thing in the proposal I have made on the wiki or in mails. In the proposal variables are private to the role which creates them. It is possible to find "some" typos, depending on the code: you can check that a variable is both assigned and used somewhere, otherwise it is very probably a typo. Perl does that *statically*, before executing a script. "Before execution" .. I am speaking "without execution". Before execution is also without execution. You can run "perl -c" to get the warning. theoretically, if you check all possible functions, you can find some issues - but you have to check all function on server. Yes, sure. It seems to be the same with your proposal: if a hidden function drops and recreates a session variable with a different type, or changes its permission, then some static checks are void as well, that is life. Also, a SQL function may access and modify the variables unexpectedly, that would be missed by a PL/pgSQL analysis tool... There is no miracle. Basically, there may be issues even if static analysis tools says that all is well. Elsewhere you cannot to find typo in DECLARE statement. Indeed, probably there exists some class of typos that may not be found by some static analysis implementations on PL/pgSQL functions which uses basic session variables. By the way, are you planing to contribute to the wiki? https://wiki.postgresql.org/wiki/Variable_Design -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
2016-12-30 15:34 GMT+01:00 Fabien COELHO : > > Hello again, > > So any dynamic created object and related operations are not checkable by >>> plpgsql_check (or any tool). >>> >>> NO. Your first sentence does not imply this very general statement. >>> >> >> If you have not unique definition, you cannot to it. There is not >> possibility different between typo and decision. We are talking about >> static analyze - so code should not be executed - and you don't know what >> function will be started first. >> > > Yes, I assure you that I really know how static analysis works... All the > properties I described below may be proved without executing the code, that > was my point... > > > Some things that I think can be statically proved within a session, that >>> would cover some security concerns: >>> >>> (1) For statically named private dynamic variables declared/used at >>> different points it can be checked without relying on the function order >>> that all declarations are consistent, i.e. the same type, same default >>> value if any. >>> >> >> what is "static" private dynamic variable ? You are using new terminology. >> > > They are my session variable, I just spelled out some properties to be > precise about what I am stating, otherwise it is a mess. The name of the > variable is "static" (statically-named), i.e. it is known directly in the > code. However the variable creation and value are "dynamic". > > Static variables like Clang static variables are not usable - you would to >> share content between different functions. >> > > Sure. I mean static as in "static analysis", i.e. by looking at the code > without executing it, as you put it. > > (2) (3) (4) [...] >>> >> You are speaking about runtime check. >> > > Not really, I was speaking about properties statically provable, which I > understood was your concern. Now the properties proved may imply a runtime > assumption, for instance that the code has executed without error up to > some point in the program, which is basically impossible to prove > statically. > > BEGIN >> RAISE NOTICE '%', @var; >> END; >> >> Without "execution", you cannot to say if usage of @var is correct or not >> > > It depends about your definition of "correct". > > For this very instance it would not matter: if the variable was not > created beforehand, then an error is raised because it does not exist, if > it was created before hand, then an error is raised because that is what > the code is doing... So an error is always raised if the variable is not > right. > > ok, we can use a DECLARE var >> >> DECLARE @var EXTERNAL >> > > I do not know what you mean by 'EXTERNAL'. it means "used as shared in session" > > > BEGIN >> RAISE NOTICE '%', @var; >> END; >> >> ok, I can do static check - but >> > > 1. anytime I have to repeat DECLARE statement >> > > Yes, twice in the complete use case: one for the function which checks the > credentials, one for the getter function. > > 2. there is not possible to find typo in DECLARE statement >> > > It is possible to find "some" typos, depending on the code: you can check > that a variable is both assigned and used somewhere, otherwise it is very > probably a typo. Perl does that *statically*, before executing a script. "Before execution" .. I am speaking "without execution". theoretically, if you check all possible functions, you can find some issues - but you have to check all function on server. Elsewhere you cannot to find typo in DECLARE statement. Regards Pavel > > > -- > Fabien. >
Re: [HACKERS] proposal: session server side variables
Hello again, So any dynamic created object and related operations are not checkable by plpgsql_check (or any tool). NO. Your first sentence does not imply this very general statement. If you have not unique definition, you cannot to it. There is not possibility different between typo and decision. We are talking about static analyze - so code should not be executed - and you don't know what function will be started first. Yes, I assure you that I really know how static analysis works... All the properties I described below may be proved without executing the code, that was my point... Some things that I think can be statically proved within a session, that would cover some security concerns: (1) For statically named private dynamic variables declared/used at different points it can be checked without relying on the function order that all declarations are consistent, i.e. the same type, same default value if any. what is "static" private dynamic variable ? You are using new terminology. They are my session variable, I just spelled out some properties to be precise about what I am stating, otherwise it is a mess. The name of the variable is "static" (statically-named), i.e. it is known directly in the code. However the variable creation and value are "dynamic". Static variables like Clang static variables are not usable - you would to share content between different functions. Sure. I mean static as in "static analysis", i.e. by looking at the code without executing it, as you put it. (2) (3) (4) [...] You are speaking about runtime check. Not really, I was speaking about properties statically provable, which I understood was your concern. Now the properties proved may imply a runtime assumption, for instance that the code has executed without error up to some point in the program, which is basically impossible to prove statically. BEGIN RAISE NOTICE '%', @var; END; Without "execution", you cannot to say if usage of @var is correct or not It depends about your definition of "correct". For this very instance it would not matter: if the variable was not created beforehand, then an error is raised because it does not exist, if it was created before hand, then an error is raised because that is what the code is doing... So an error is always raised if the variable is not right. ok, we can use a DECLARE var DECLARE @var EXTERNAL I do not know what you mean by 'EXTERNAL'. BEGIN RAISE NOTICE '%', @var; END; ok, I can do static check - but 1. anytime I have to repeat DECLARE statement Yes, twice in the complete use case: one for the function which checks the credentials, one for the getter function. 2. there is not possible to find typo in DECLARE statement It is possible to find "some" typos, depending on the code: you can check that a variable is both assigned and used somewhere, otherwise it is very probably a typo. Perl does that *statically*, before executing a script. -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
2016-12-30 12:01 GMT+01:00 Pavel Stehule : > > > 2016-12-30 10:29 GMT+01:00 Craig Ringer : > >> On 30 December 2016 at 16:46, Fabien COELHO wrote: >> > >> >> Pavel's personal requirements include that it be well suited for >> >> static analysis of plpgsql using his plpgsql_check tool. So he wants >> >> persistent definitions. >> > >> > >> > I've been in static analysis for the last 25 years, and the logic of >> this >> > statement fails me. >> >> I have no opinion here, as I've not seen plpgsql_check nor do I >> understand the issues Pavel perceives with having dynamic definitions >> of variables. >> >> All I'm saying is that you two are talking around in circles by >> repeating different requirements to each other, and it's not going to >> get anywhere unless you both change your approach. It sounds like >> you're already trying to do that. >> >> > I do not think that a feature should be designed around the current >> > limitations of a particular external tool, esp. if said tool can be >> improved >> > at a reasonable cost. >> >> Not arguing there. >> >> I was initially inclined to favour Pavel's proposal because it fits a >> RLS use case I was somewhat interested in. But so would dynamic >> variables resolved at runtime so long as they were fast. >> >> Personally I don't much care what the result is, so long as it can >> satisfy some kind of reasonable security isolation, such that role A >> can set it, B can read it but not set it, and role C can do neither. >> Preferably without resorting to creating SECURITY DEFINER accessors, >> since they're messy and slow. Support for data typing would also be >> nice too. >> >> If it doesn't deliver security controls then IMO there's not much >> advantage over (ab)using GUCs with current_setting(...). >> >> Exploring the other areas discussed: >> >> Personally I think MVCC, persistent variables are a totally >> unnecessary idea that solves a problem we don't have. But maybe I >> don't understand your use cases. I expect anything like that would >> land up using a pg_catalog relation as a k/v-like store with different >> columns for different types or something of the like, which is really >> something the user can do well enough for themselves. I don't see the >> point at all. >> >> Non-MVCC persistent variables would probably be prohibitively >> expensive to make crash-safe, and seem even more pointless. >> >> Now, I can see shared variables whose state is visible across backends >> but is neither MVCC nor persistent being a fun toy, albeit not one I >> find likely to be particularly useful personally. But we can probably >> already do that in extensions, we've got most if not all of the needed >> infrastructure. Because we're a shared-nothing-by-default system, such >> variables will probably need shared memory segments that need to be >> allocated and, if new vars are added or their values grow too much, >> re-allocated. Plus locks to control access. All of which we can >> already do. Most of the uses I can think of for such things are met >> reasonably well by advisory locking already, and I expect most of the >> rest would be met by autonomous commit, so it feels a bit like a >> feature looking for a use-case. >> >> So lets take a step back or eight and ask "why?" >> >> >> Pavel: >> >> * Why is it so necessary for plpgsql variables to be implemented as >> persistent entities that are in the catalogs in order for you to >> achieve the static checking you want to? Is this due to limitations of >> your approach in plpgsql_check, or more fundamental issues? Explain. >> > > There are few reasons: > > 1. plpgsql_check cannot to know a order of calls of functions. So any > dynamic created object and related operations are not checkable by > plpgsql_check (or any tool). If you create variable in one function, then > this information is not available in other function. > > 2. You can write some hints - like Fabien proposal - it is not vulnerable > against typo. It is much more safer to have one "created" variable, then > more "declared" variables and believe so all declarations are valid. The > created variable is only on one place - you can do simple check if > reference to variable is valid or not. With query to system catalogue, you > can get the list of all variables - you can see very simply if some > variables are redundant, obsolete, wrong. > > 3. The catalogue objects allow to create well granularity of access > rights. without it, you have to introduce only "PRIVATE" variables - and > then you have to GRANT rights via security definer functions. There is not > simple reply to question who can work with this variable, who has access, > ... you have to check a rights to getter functions. When variables are > catalogue objects, then this reply is simple. Catalogue objects allows to > have "declared" security. Without catalogue objects we have to construct > the security. For me, a declared security is stronger. > My concept is similar to "global" varia
Re: [HACKERS] proposal: session server side variables
2016-12-30 14:45 GMT+01:00 Fabien COELHO : > > Please Pavel, could you avoid citing a whole long mail just for commenting > one point? > > * Why is it so necessary for plpgsql variables to be implemented as >>> persistent entities that are in the catalogs in order for you to >>> achieve the static checking you want to? Is this due to limitations of >>> your approach in plpgsql_check, or more fundamental issues? Explain. >>> >> >> There are few reasons: >> >> 1. plpgsql_check cannot to know a order of calls of functions. >> > > Indeed. > > So any dynamic created object and related operations are not checkable by >> plpgsql_check (or any tool). >> > > NO. Your first sentence does not imply this very general statement. > If you have not unique definition, you cannot to it. There is not possibility different between typo and decision. We are talking about static analyze - so code should not be executed - and you don't know what function will be started first. > > Some things that I think can be statically proved within a session, that > would cover some security concerns: > > (1) For statically named private dynamic variables declared/used at > different points it can be checked without relying on the function order > that all declarations are consistent, i.e. the same type, same default > value if any. > what is "static" private dynamic variable ? You are using new terminology. Static variables like Clang static variables are not usable - you would to share content between different functions. > > (2) Then the value of the variable is either the default value (eg NULL) > or the assigned value at points of assignement, which must be a valid value > for the type, otherwise the assignement would have failed. > > (3) If there is only one assignment in the code, then you know that the > variable can only have been assigned a non default value from this point. > Probably nice to have in a security context, but it requires you to be > sure that you have access to all the code... > > (4) For a session boolean, then for code "IF @var IS NOT NULL AND NOT @var > THEN RAISE 'cannot access'; END", in a sequence, then one can prove that > for any pl code after this point in the sequence @var has been previously > assigned to true, otherwise the exception would have been raised. > You are speaking about runtime check.There is not a problem to define some rules for runtime check. What is not possible in your design 1. BEGIN RAISE NOTICE '%', @var; END; Without "execution", you cannot to say if usage of @var is correct or not ok, we can use a DECLARE var DECLARE @var EXTERNAL BEGIN RAISE NOTICE '%', @var; END; ok, I can do static check - but 1. anytime I have to repeat DECLARE statement 2. there is not possible to find typo in DECLARE statement Regards Pavel > > > AFAICS, for "static" session variables the only difference is that the > declaration consistency (1) is slightly more built-in, although you still > have to check that no function DROP/re-CREATE the session variable with a > different type, which is quite close to checking (1) for a dynamic session > variable. > > Other properties (2), (3), (4) are exactly the same. > > 2. [...] 3. >> > > Please could you put your pros & cons in the wiki as well? > > Or don't you want to use it? > > -- > Fabien. >
Re: [HACKERS] proposal: session server side variables
Please Pavel, could you avoid citing a whole long mail just for commenting one point? * Why is it so necessary for plpgsql variables to be implemented as persistent entities that are in the catalogs in order for you to achieve the static checking you want to? Is this due to limitations of your approach in plpgsql_check, or more fundamental issues? Explain. There are few reasons: 1. plpgsql_check cannot to know a order of calls of functions. Indeed. So any dynamic created object and related operations are not checkable by plpgsql_check (or any tool). NO. Your first sentence does not imply this very general statement. Some things that I think can be statically proved within a session, that would cover some security concerns: (1) For statically named private dynamic variables declared/used at different points it can be checked without relying on the function order that all declarations are consistent, i.e. the same type, same default value if any. (2) Then the value of the variable is either the default value (eg NULL) or the assigned value at points of assignement, which must be a valid value for the type, otherwise the assignement would have failed. (3) If there is only one assignment in the code, then you know that the variable can only have been assigned a non default value from this point. Probably nice to have in a security context, but it requires you to be sure that you have access to all the code... (4) For a session boolean, then for code "IF @var IS NOT NULL AND NOT @var THEN RAISE 'cannot access'; END", in a sequence, then one can prove that for any pl code after this point in the sequence @var has been previously assigned to true, otherwise the exception would have been raised. AFAICS, for "static" session variables the only difference is that the declaration consistency (1) is slightly more built-in, although you still have to check that no function DROP/re-CREATE the session variable with a different type, which is quite close to checking (1) for a dynamic session variable. Other properties (2), (3), (4) are exactly the same. 2. [...] 3. Please could you put your pros & cons in the wiki as well? Or don't you want to use it? -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
2016-12-30 11:03 GMT+01:00 Craig Ringer : > On 30 December 2016 at 17:29, Craig Ringer wrote: > > > So lets take a step back or eight and ask "why?" > > Oh, and speaking of, I see Pavel's approach as looking for a > PostgreSQL-adapted way to do something like Oracle's PL/SQL package > variables. Right Pavel? > It was main motivation - the question was - how to share (in one session) secure some information between function calls. The PostgreSQL is specific in multi language support - but purpose is same. > > If so, their properties are, as far as I as a non-Oracle-person can tell: > > * Can be package-private or public. If public, can be both got and set > by anyone. If private, can be got and set directly only by code in > package. (Our equivalent is "by the owner"). As far as I can tell > outside access to package-private variables still uses the variable > get/set syntax, but is automatically proxied via getter/setter methods > defined in the package, if defined, otherwise inaccessible. > > * Value not visible across sessions. Ever. > > * Can have an initialiser / DEFAULT value. > > * Non-persistent, value lost at session end. > > A typical example, where package variables are init'd from a table: > > http://www.dba-oracle.com/plsql/t_plsql_global_data.htm > > which relies on package initializers, something we don't have (but can > work around easily enough with a little verbosity). > > This shows both public vars and package-private ones. > > See also https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/ > constantvar_declaration.htm > > > I believe these package variable properties are the properties Pavel > seeks to model/emulate. Declared statically, value persistent only > within the same session, non-transactional, can be private. > > Certainly there's nothing here that requires us to allow GRANTs. > Simple ownership tests would supply us with similar functionality to > what Oracle users have, allowing for our lack of packages and > inability to hide the _existence_ of an object, only its contents. > The packages has own scope - so any access from packages is allowed. I cannot do it in Postgres without explicitly written setter/getter functions. So GRANTS reduces a requirement to write security definer envelop functions. Sure - owner doesn't need it. If your application is one user, or if you are owner, then you don't need to use GRANT. > > > My views: > > I am personally overwhelmingly opposed to variables that automagically > create themselves when dereferenced, a-la Perl. Write $serialised > (english spelling) not $serialized (US spelling) and you get a silent > null. Fun! Hell. No. This is why failure to "use strict" in Perl is a > near-criminal offense. > > I'd also strongly prefer to require vars to be declared before first > use. Again, like "use strict", and consistent with how Pg behaves > elsewhere. Otherwise we need some kind of magic syntax to say "this is > a variable", plus vars that get created on first assignment suck > almost as badly as ones that're null on undefined deference. Spend > half an hour debugging and figure out that you typo'd an assignment. > Again, "use strict". > > I fail to see any real utility to cross-session vars, persistent or > otherwise, at this point. Use a normal or unlogged relation. > > I don't see the point of untyped variables with no ownership or rights > controls. (ab)use a GUC. Note that you can achieve both xact-scoped > and session-scoped that way, with xact-scoped vars assigned using SET > LOCAL being unwound on xact end. > > Unless we also propose to add ON CONNECT triggers, I think some kind > of persistency of declaration is useful but not critical. We'll land > up with apps sending preambles of declarations on session start > otherwise. But the most compelling use cases are for things where > there'll be a procedure invoked by the user or app on connect anyway, > so it can declare stuff there. I'm utterly unconvinced that it's > necessary to have them in the catalogs to achieve static checking. > > -- > Craig Ringer http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services >
Re: [HACKERS] proposal: session server side variables
Hello Craig, A long mail with many questions, that I tried to answered clearly, the result is too long... [...] I have no opinion here, as I've not seen plpgsql_check nor do I understand the issues Pavel perceives with having dynamic definitions of variables. I understand that Pavel assumes that a static analysis tool cannot take into account a dynamic variable, hence is reserve. All I'm saying is that you two are talking around in circles by repeating different requirements to each other, and it's not going to get anywhere unless you both change your approach. It sounds like you're already trying to do that. Yep, that is why I have created the wiki page, so at least a argument should not be repeated cyclically, it should be written once. [...] I was initially inclined to favour Pavel's proposal because it fits a RLS use case I was somewhat interested in. But so would dynamic variables resolved at runtime so long as they were fast. Fitting the need of use cases is the key point, obviously. [...] Preferably without resorting to creating SECURITY DEFINER accessors, since they're messy and slow. I'm not sure what you mean about "messy", but if you can objectivate this it can be an argument. Please feel free to explain it on the wiki. As for "slow", I have just tested overheads with pgbench, comparing a direct arithmetic operation (as a proxy to a fast session variable consultation) to constant returning plpgsql functions with security definer and security invoker, on a direct socket connection, with prepared statements: select 1 + 0: 0.020 ms select one_sd() : 0.024 ms select one_si() : 0.024 ms I do not think that there is a significant "slowness" issue with using function calls. Exploring the other areas discussed: Personally I think MVCC, persistent variables are a totally unnecessary [...] But maybe I don't understand your use cases. I've done a survey about the schema of projects based on databases, mysql or pgsql. A significant number of them use a common pattern based on a one-row table, essentially to hold some scalar information about the application version and facilitate upgrades. However the one-row property is just hoped for, and on principle a database is about declaring constraints that are enforced afterwards. I see two clean solutions to this use case: declaring tables as one row, or having scalar objects. Now, I can see shared variables whose state is visible across backends but is neither MVCC nor persistent being a fun toy, albeit not one I find likely to be particularly useful personally. Yep, I'm skeptical as well. I would like to see a convincing use case. Pavel: * Why is it so necessary for plpgsql variables to be implemented as persistent entities that are in the catalogs in order for you to achieve the static checking you want to? Is this due to limitations of your approach in plpgsql_check, or more fundamental issues? Explain. Note about this question not addressed to me: currently "plpgsql_check" cannot analyze any session variables as no such concept exists, whether with or without persistent declarations. Fabien: * What use do you have for persistent-data variables? Please set out some use cases where they solve problems that are currently hard to to solve or greatly improve on the existing solutions. It is about the often seen one-row pattern, that I think should be enforced either with some singleton table declaration, or scalar objects. * On what basis do you _oppose_ persistently defining variables in the catalogs as their own entities? In understand that you are speaking of "persistent session variables". For me a database is about persistence (metadata & data) with safety (transactions) and security (permissions)... and maybe performance:-) Pavel's proposal creates a new object with 2 (secure metadata-persistence) out of 4 properties... I'm not a ease with introducting a new half-database concept in a database. I could accept it for a convincing use case that absolutely require that for deep reasons. On the other hand there are dynamic session variables (mysql, mssql, oracle have some variants) which are useful on their own without pretending to be database objects (no CREATE/ALTER/DROP, GRANT/REVOKE). If I can make these to handle the special case and avoid a new special half-database concept, I would prefer it. The key point about all that is to discuss, understand and evaluate the involved use cases. (My own objection is that "temporary variables" would make our existing catalog bloat issues for temp objects even worse). I do agree that inefficient temporary variables are worthless, but ISTM that Pavel's proposal is not exactly about temporary variables, it is about temporary-valued permanent-variables. So there is no temporary (on the fly) variable as such, and if it is extended for this purpose then indeed the catalog costs look expensive. * Do y
Re: [HACKERS] proposal: session server side variables
2016-12-30 10:29 GMT+01:00 Craig Ringer : > On 30 December 2016 at 16:46, Fabien COELHO wrote: > > > >> Pavel's personal requirements include that it be well suited for > >> static analysis of plpgsql using his plpgsql_check tool. So he wants > >> persistent definitions. > > > > > > I've been in static analysis for the last 25 years, and the logic of this > > statement fails me. > > I have no opinion here, as I've not seen plpgsql_check nor do I > understand the issues Pavel perceives with having dynamic definitions > of variables. > > All I'm saying is that you two are talking around in circles by > repeating different requirements to each other, and it's not going to > get anywhere unless you both change your approach. It sounds like > you're already trying to do that. > > > I do not think that a feature should be designed around the current > > limitations of a particular external tool, esp. if said tool can be > improved > > at a reasonable cost. > > Not arguing there. > > I was initially inclined to favour Pavel's proposal because it fits a > RLS use case I was somewhat interested in. But so would dynamic > variables resolved at runtime so long as they were fast. > > Personally I don't much care what the result is, so long as it can > satisfy some kind of reasonable security isolation, such that role A > can set it, B can read it but not set it, and role C can do neither. > Preferably without resorting to creating SECURITY DEFINER accessors, > since they're messy and slow. Support for data typing would also be > nice too. > > If it doesn't deliver security controls then IMO there's not much > advantage over (ab)using GUCs with current_setting(...). > > Exploring the other areas discussed: > > Personally I think MVCC, persistent variables are a totally > unnecessary idea that solves a problem we don't have. But maybe I > don't understand your use cases. I expect anything like that would > land up using a pg_catalog relation as a k/v-like store with different > columns for different types or something of the like, which is really > something the user can do well enough for themselves. I don't see the > point at all. > > Non-MVCC persistent variables would probably be prohibitively > expensive to make crash-safe, and seem even more pointless. > > Now, I can see shared variables whose state is visible across backends > but is neither MVCC nor persistent being a fun toy, albeit not one I > find likely to be particularly useful personally. But we can probably > already do that in extensions, we've got most if not all of the needed > infrastructure. Because we're a shared-nothing-by-default system, such > variables will probably need shared memory segments that need to be > allocated and, if new vars are added or their values grow too much, > re-allocated. Plus locks to control access. All of which we can > already do. Most of the uses I can think of for such things are met > reasonably well by advisory locking already, and I expect most of the > rest would be met by autonomous commit, so it feels a bit like a > feature looking for a use-case. > > So lets take a step back or eight and ask "why?" > > > Pavel: > > * Why is it so necessary for plpgsql variables to be implemented as > persistent entities that are in the catalogs in order for you to > achieve the static checking you want to? Is this due to limitations of > your approach in plpgsql_check, or more fundamental issues? Explain. > There are few reasons: 1. plpgsql_check cannot to know a order of calls of functions. So any dynamic created object and related operations are not checkable by plpgsql_check (or any tool). If you create variable in one function, then this information is not available in other function. 2. You can write some hints - like Fabien proposal - it is not vulnerable against typo. It is much more safer to have one "created" variable, then more "declared" variables and believe so all declarations are valid. The created variable is only on one place - you can do simple check if reference to variable is valid or not. With query to system catalogue, you can get the list of all variables - you can see very simply if some variables are redundant, obsolete, wrong. 3. The catalogue objects allow to create well granularity of access rights. without it, you have to introduce only "PRIVATE" variables - and then you have to GRANT rights via security definer functions. There is not simple reply to question who can work with this variable, who has access, ... you have to check a rights to getter functions. When variables are catalogue objects, then this reply is simple. Catalogue objects allows to have "declared" security. Without catalogue objects we have to construct the security. For me, a declared security is stronger. Regards Pavel > > Fabien: > > * What use do you have for persistent-data variables? Please set out > some use cases where they solve problems that are currently hard to to > solve or greatly improve on the existi
