Re: [HACKERS] proposal: session server side variables

2017-02-06 Thread Pavel Stehule
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

2017-02-06 Thread 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?


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-06 Thread Pavel Stehule
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 Thread Pavel Stehule
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

2017-02-03 Thread Pavel Stehule
>
>
>
>
>>
>> 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

2017-02-03 Thread 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.


--
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-02 Thread Pavel Stehule
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

2017-02-02 Thread 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.


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

2017-02-02 Thread Michael Paquier
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

2017-02-02 Thread Pavel Stehule
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-02 Thread Pavel Stehule
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-01-31 Thread 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_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

2017-01-31 Thread 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.
-- 
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

2017-01-11 Thread Craig Ringer
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

2017-01-11 Thread Fabien COELHO


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

2017-01-10 Thread Craig Ringer
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

2017-01-10 Thread Fabien COELHO


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

2017-01-10 Thread Fabien COELHO



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

2017-01-10 Thread Fabien COELHO


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

2017-01-10 Thread Robert Haas
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

2017-01-10 Thread Craig Ringer
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

2017-01-10 Thread Craig Ringer
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 Thread Pavel Stehule
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

2017-01-09 Thread 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.


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

2017-01-09 Thread Craig Ringer
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

2017-01-09 Thread Robert Haas
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 

Re: [HACKERS] proposal: session server side variables (fwd)

2017-01-08 Thread Fabien COELHO


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)

2017-01-07 Thread Bruce Momjian
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-05 Thread Pavel Stehule
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

2017-01-05 Thread 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.

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

2017-01-05 Thread Craig Ringer
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

2017-01-05 Thread Jim Nasby

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

2017-01-05 Thread 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.

--
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 Thread Pavel Stehule
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)

2017-01-05 Thread 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.

--
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 Thread 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?)

--
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 Thread Pavel Stehule
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

2017-01-04 Thread Craig Ringer
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

2017-01-04 Thread Craig Ringer
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

2017-01-04 Thread Joe Conway
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 Thread Pavel Stehule
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

2017-01-04 Thread 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.


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 Thread Pavel Stehule
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

2017-01-04 Thread 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 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 Thread Pavel Stehule
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 Thread Pavel Stehule
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

2017-01-04 Thread 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

--
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 Thread Pavel Stehule
>
>> 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

2017-01-04 Thread 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.


--
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 Thread Fabien COELHO


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 Thread Pavel Stehule
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

2017-01-04 Thread Craig Ringer
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

2017-01-04 Thread 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:


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

2017-01-04 Thread Fabien COELHO



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

2017-01-04 Thread Fabien COELHO


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

2017-01-04 Thread Craig Ringer
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 Thread Pavel Stehule
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

2017-01-04 Thread 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.



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 Thread Pavel Stehule
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

2017-01-03 Thread 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...


--
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 Thread Pavel Stehule
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

2017-01-03 Thread Jim Nasby

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

2017-01-03 Thread 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.

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 Thread Pavel Stehule
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

2017-01-03 Thread 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:


 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 Thread Pavel Stehule
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

2017-01-03 Thread 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.

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 Thread Pavel Stehule
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

2017-01-03 Thread 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".


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 Thread Pavel Stehule
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

2017-01-02 Thread 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.


--
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 Thread Fabien COELHO



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 Thread Pavel Stehule
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

2017-01-02 Thread Fabien COELHO



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

2017-01-02 Thread 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.


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 Thread Pavel Stehule
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

2017-01-02 Thread 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.

--
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-01 Thread Pavel Stehule
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

2017-01-01 Thread 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.

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 Thread Pavel Stehule
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

2017-01-01 Thread Fabien COELHO


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

2017-01-01 Thread 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.


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 Thread Pavel Stehule
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

2016-12-31 Thread Pavel Stehule
>
> 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

2016-12-31 Thread 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.




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

2016-12-31 Thread Fabien COELHO


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 Thread Pavel Stehule
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

2016-12-31 Thread 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;

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-30 Thread Pavel Stehule
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
> > 

Re: [HACKERS] proposal: session server side variables

2016-12-30 Thread 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.

> (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 Thread Pavel Stehule
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

2016-12-30 Thread 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.



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 Thread Pavel Stehule
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

2016-12-30 Thread 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'.


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 Thread Pavel Stehule
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 

Re: [HACKERS] proposal: session server side variables

2016-12-30 Thread Pavel Stehule
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

2016-12-30 Thread 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.

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 Thread Pavel Stehule
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

2016-12-30 Thread Fabien COELHO


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 

Re: [HACKERS] proposal: session server side variables

2016-12-30 Thread 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.

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 

  1   2   >