Re: Relocatable Binaries (RPMs) : custom installation path for PostgreSQL

[Adrian Klaver]

On 12/31/18 8:19 AM, chiru r wrote:

Hi All,

I wanted to install the PostgreSQL-11 Software with Custom 
installation/binary paths.


Currently I am using EDB one click installer software with option file 
like below to achieve, However EDB stopped and no longer provides one 
click installer for Linux.


*Example:*

PostgreSQL install software file : *postgresql-9.6.9-1-linux-x64.run*

*cat Optionsfile.txt*

mode=unattended

datadir=/u02/pgdata01/9.6/data

serverport=5432

prefix=/u01/postgres/9.6

I have installed CommunityPostgreSQL RPMs and are going into 
"/usr/pgsql-11/" by default.



#-> pwd

/usr/pgsql-11

[root@user:/usr/pgsql-11]#

#-> ls -l

total 16

drwxr-xr-x 2 root root 4096 Dec 21 13:49 bin

drwxr-xr-x 3 root root 4096 Dec 21 13:49 doc

drwxr-xr-x 3 root root 4096 Dec 21 13:49 lib

drwxr-xr-x 8 root root 4096 Dec 21 13:49 share


Please let us know how to get the PostgreSQL-11 installed in above 
custom paths using RPMs? .


I would try contacting the packagers via:

https://yum.postgresql.org/contact.php




Thanks,

Chiranjeevi




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Thoughts on row-level security for webapps?

[Adrian Klaver]

On 12/31/18 8:57 AM, Siegfried Bilstein wrote:

Hi all,

I'm evaluating using a tool called Postgraphile that generates a 
GraphSQL server from a postgres setup. The recommended way of handling 
security is to implement RLS within postgres and simply have the 
webserver take a cookie or similar and define which user is querying data.


I've normally built webapps like this: pull out user id from a session 
cookie -> the API endpoint verifies the user and whether or not it has 
access to the given data -> app code mutates the data.


With Postgraphile the request specifies the mutation and the server 
processes the request and relies on Postgres to determine if the user 
has correct access rights.


So there is still user information being passed in, correct?



It seems like I would need to create a ROLE for every single member that 
signs up for my website which I'm a little concerned about. Is this a 
common usage pattern for SQL security? Any gotchas relying on RLS?


The ROLE will be determined by the user name used to make the 
connection. Is it possible to change the connection login depending on 
the website user privileges?





--
Siggy Bilstein
CTO ofAyuda Care 
Book some time  with me!



--
Adrian Klaver
adrian.kla...@aklaver.com

Query help

[Chuck Martin]

Sorry if this is too basic a question for this list, but I don't fully get
how to use aggregates (sum()) and group-by together. I'm trying to get a
list of transactions where the total for a given account exceeds a given
number. I'm not sure an example is needed, but if so, consider this
simplified data:

accountid.   name
1  bill
2. james
3  sarah
4  carl

transaction
id. amount.  accountid. name
1.  50.  1   bill
2.  25.  2   james
3   35   4   carl
4.  75.  1   bill
5   25.  1   bill
6   50   3   sarah

results wanted-all transactions where account total >= 50

id. amount.  accountid.name
1.  50.  1   bill
3.  75.  1   bill
4   25.  1   bill
5   50   3   sarah

I've tried to understand how to use GROUP BY and HAVING, but the penny
won't drop. I keep getting errors saying that all columns in the SELECT
have to also be in the GROUP BY, but nothing I've done seems to produce the
correct results. I think because the GROUP BY contains multiple columns, so
each row is treated as a group. It also is difficult to parse out since in
the real world, many more tables and columns are involved.

Chuck Martin
Avondale Software

RE: Query help

[Scot Kreienkamp]

Any columns that aren’t involved in a summary operation (think math type or 
some other type of summary operation) have to be in the group by statement.

From what you show below, I would try something like this (untested):

Select accountid,name,sum(amount) from table where sum(amount) >’50’ group by 
accountid,name sort by accountid,name;

You can’t show the transaction ID unless you have duplicate transaction ID’s 
that you wanted to group by.  If you did try to show it you’d get the entire 
table.  Or you could use a more advanced query to gather the multiple 
transaction ID’s into a single record for the query results which would let the 
sum and group by work.


Scot Kreienkamp |Senior Systems Engineer | La-Z-Boy Corporate
One La-Z-Boy Drive| Monroe, Michigan 48162 | Office: 734-384-6403 | | Mobile: 
7349151444 | Email: scot.kreienk...@la-z-boy.com
From: Chuck Martin [mailto:clmar...@theombudsman.com]
Sent: Tuesday, January 01, 2019 2:06 PM
To: pgsql-general 
Subject: Query help

Sorry if this is too basic a question for this list, but I don't fully get how 
to use aggregates (sum()) and group-by together. I'm trying to get a list of 
transactions where the total for a given account exceeds a given number. I'm 
not sure an example is needed, but if so, consider this simplified data:

accountid.   name
1  bill
2. james
3  sarah
4  carl

transaction
id. amount.  accountid. name
1.  50.  1   bill
2.  25.  2   james
3   35   4   carl
4.  75.  1   bill
5   25.  1   bill
6   50   3   sarah

results wanted-all transactions where account total >= 50

id. amount.  accountid.name
1.  50.  1   bill
3.  75.  1   bill
4   25.  1   bill
5   50   3   sarah

I've tried to understand how to use GROUP BY and HAVING, but the penny won't 
drop. I keep getting errors saying that all columns in the SELECT have to also 
be in the GROUP BY, but nothing I've done seems to produce the correct results. 
I think because the GROUP BY contains multiple columns, so each row is treated 
as a group. It also is difficult to parse out since in the real world, many 
more tables and columns are involved.

Chuck Martin
Avondale Software

This message is intended only for the individual or entity to which it is 
addressed.  It may contain privileged, confidential information which is exempt 
from disclosure under applicable laws.  If you are not the intended recipient, 
you are strictly prohibited from disseminating or distributing this information 
(other than to the intended recipient) or copying this information.  If you 
have received this communication in error, please notify us immediately by 
e-mail or by telephone at the above number. Thank you.

Re: Query help

[John W Higgins]

On Tue, Jan 1, 2019 at 11:06 AM Chuck Martin 
wrote:

> Sorry if this is too basic a question for this list, but I don't fully get
> how to use aggregates (sum()) and group-by together. I'm trying to get a
> list of transactions where the total for a given account exceeds a given
> number. I'm not sure an example is needed, but if so, consider this
> simplified data:
>
> accountid.   name
> 1  bill
> 2. james
> 3  sarah
> 4  carl
>
> transaction
> id. amount.  accountid. name
> 1.  50.  1   bill
> 2.  25.  2   james
> 3   35   4   carl
> 4.  75.  1   bill
> 5   25.  1   bill
> 6   50   3   sarah
>
> results wanted-all transactions where account total >= 50
>
> id. amount.  accountid.name
> 1.  50.  1   bill
> 3.  75.  1   bill
> 4   25.  1   bill
> 5   50   3   sarah
>
>
You have 2 concepts here - identify the accounts with a total over 50 and
then show the transactions for those accounts. I prefer CTEs here because
they allow for better understanding (to me) of the steps involved. A
subquery would work here as well.

with accounts_over_total as (
select accountid from transactions where sum(amount) >= 50 group by
accountid)
select transactions.* from transactions join accounts_over_total on
transactions.accountid = accounts.accountid

John


> I've tried to understand how to use GROUP BY and HAVING, but the penny
> won't drop. I keep getting errors saying that all columns in the SELECT
> have to also be in the GROUP BY, but nothing I've done seems to produce the
> correct results. I think because the GROUP BY contains multiple columns, so
> each row is treated as a group. It also is difficult to parse out since in
> the real world, many more tables and columns are involved.
>
> Chuck Martin
> Avondale Software
>

Query help

[David G. Johnston]

On Tuesday, January 1, 2019, Chuck Martin  wrote:

> results wanted-all transactions where account total >= 50
>
> id. amount.  accountid.name
> 1.  50.  1   bill
> 3.  75.  1   bill
> 4   25.  1   bill
> 5   50   3   sarah
>

This result does not require group by, just the where clause you’ve noted
above.

David J.

Re: Query help

[David G. Johnston]

On Tuesday, January 1, 2019, David G. Johnston 
wrote:

> On Tuesday, January 1, 2019, Chuck Martin 
> wrote:
>
>> results wanted-all transactions where account total >= 50
>>
>> id. amount.  accountid.name
>> 1.  50.  1   bill
>> 3.  75.  1   bill
>> 4   25.  1   bill
>> 5   50   3   sarah
>>
>
> This result does not require group by, just the where clause you’ve noted
> above.
>
>
Never mind...missed how the 25 got included

David J.

Re: Query help

[Adrian Klaver]

On 1/1/19 11:05 AM, Chuck Martin wrote:
Sorry if this is too basic a question for this list, but I don't fully 
get how to use aggregates (sum()) and group-by together. I'm trying to 
get a list of transactions where the total for a given account exceeds a 
given number. I'm not sure an example is needed, but if so, consider 
this simplified data:


accountid.   name
1                  bill
2.                 james
3                  sarah
4                  carl

transaction
id.         amount.      accountid.     name
1.          50.              1                   bill
2.          25.              2                   james
3           35               4                   carl
4.          75.              1                   bill
5           25.              1                   bill
6           50               3                   sarah

results wanted-all transactions where account total >= 50

id.         amount.      accountid.    name
1.          50.              1                   bill
3.          75.              1                   bill
4           25.              1                   bill
5           50               3                   sarah

I've tried to understand how to use GROUP BY and HAVING, but the penny 
won't drop. I keep getting errors saying that all columns in the SELECT 
have to also be in the GROUP BY, but nothing I've done seems to produce 
the correct results. I think because the GROUP BY contains multiple 
columns, so each row is treated as a group. It also is difficult to 
parse out since in the real world, many more tables and columns are 
involved.


Window Functions?:

https://www.postgresql.org/docs/11/tutorial-window.html


Or do something like(untested):

select transactionid, amount, accountid, name from transaction join 
(select accountid, sum(amount) from transaction group by(accountid)) as 
account_sum on transaction.transactionid = account_sum.accountid and 
account_sum.sum >= 50




Chuck Martin
Avondale Software



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Query help

[Adrian Klaver]

On 1/1/19 11:26 AM, John W Higgins wrote:
On Tue, Jan 1, 2019 at 11:06 AM Chuck Martin > wrote:


Sorry if this is too basic a question for this list, but I don't
fully get how to use aggregates (sum()) and group-by together. I'm
trying to get a list of transactions where the total for a given
account exceeds a given number. I'm not sure an example is needed,
but if so, consider this simplified data:

accountid.   name
1                  bill
2.                 james
3                  sarah
4                  carl

transaction
id.         amount.      accountid.     name
1.          50.              1                   bill
2.          25.              2                   james
3           35               4                   carl
4.          75.              1                   bill
5           25.              1                   bill
6           50               3                   sarah

results wanted-all transactions where account total >= 50

id.         amount.      accountid.    name
1.          50.              1                   bill
3.          75.              1                   bill
4           25.              1                   bill
5           50               3                   sarah


You have 2 concepts here - identify the accounts with a total over 50 
and then show the transactions for those accounts. I prefer CTEs here 
because they allow for better understanding (to me) of the steps 
involved. A subquery would work here as well.


with accounts_over_total as (
select accountid from transactions where sum(amount) >= 50 group by 
accountid)


Unfortunately there is a hitch in the above:(

select p_item_no from projection where sum(qty) > 100 group 
by(p_item_no); 

ERROR:  aggregate functions are not allowed in WHERE 



LINE 1: select p_item_no, sum(qty) from projection where sum(qty) >

select transactions.* from transactions join accounts_over_total on 
transactions.accountid = accounts.accountid


John

I've tried to understand how to use GROUP BY and HAVING, but the
penny won't drop. I keep getting errors saying that all columns in
the SELECT have to also be in the GROUP BY, but nothing I've done
seems to produce the correct results. I think because the GROUP BY
contains multiple columns, so each row is treated as a group. It
also is difficult to parse out since in the real world, many more
tables and columns are involved.

Chuck Martin
Avondale Software




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Query help

[David G. Johnston]

On Tuesday, January 1, 2019, Adrian Klaver 
wrote:

> On 1/1/19 11:26 AM, John W Higgins wrote:
>>
>> with accounts_over_total as (
>> select accountid from transactions where sum(amount) >= 50 group by
>> accountid)
>>
>
> Unfortunately there is a hitch in the above:(
>
> select p_item_no from projection where sum(qty) > 100 group by(p_item_no);
> ERROR:  aggregate functions are not allowed in WHERE
>

Which is where the HAVING clause comes in.  It filters out groups based on
an expression containing an aggregate function.

David J.

Re: Relocatable Binaries (RPMs) : custom installation path for PostgreSQL

[Brent Wood]

Have you considered symlinking the default paths to where you want it to reside?
That approach would allow updates & upgrades to work as they expect, without 
changing default paths or config files.
Much as described 
here:https://help.cloud66.com/rails/how-to-guides/databases/shells/change-psql-directory.html



Cheers,
  Brent Wood

 
On 12/31/18 8:19 AM, chiru r wrote:
> Hi All,
> 
> I wanted to install the PostgreSQL-11 Software with Custom 
> installation/binary paths.
> 
> Currently I am using EDB one click installer software with option file 
> like below to achieve, However EDB stopped and no longer provides one 
> click installer for Linux.
> 
> *Example:*
> 
> PostgreSQL install software file : *postgresql-9.6.9-1-linux-x64.run*
> 
> *cat Optionsfile.txt*
> mode=unattended
> datadir=/u02/pgdata01/9.6/data
> serverport=5432
> prefix=/u01/postgres/9.6
> I have installed CommunityPostgreSQL RPMs and are going into 
> "/usr/pgsql-11/" by default.
> 

> #-> pwd
> /usr/pgsql-11
> 
> [root@user:/usr/pgsql-11]#
> 
> #-> ls -l
> total 16
> 
> drwxr-xr-x 2 root root 4096 Dec 21 13:49 bin
> drwxr-xr-x 3 root root 4096 Dec 21 13:49 doc
> drwxr-xr-x 3 root root 4096 Dec 21 13:49 lib
> drwxr-xr-x 8 root root 4096 Dec 21 13:49 share
> 
> Please let us know how to get the PostgreSQL-11 installed in above 
> custom paths using RPMs? .
> 
> 
> Thanks,
> 
> Chiranjeevi> 

   

Re: Query help

[Adrian Klaver]

On 1/1/19 12:05 PM, David G. Johnston wrote:
On Tuesday, January 1, 2019, Adrian Klaver > wrote:


On 1/1/19 11:26 AM, John W Higgins wrote:

with accounts_over_total as (
select accountid from transactions where sum(amount) >= 50 group
by accountid)


Unfortunately there is a hitch in the above:(

select p_item_no from projection where sum(qty) > 100 group
by(p_item_no);
ERROR:  aggregate functions are not allowed in WHERE


Which is where the HAVING clause comes in.  It filters out groups based 
on an expression containing an aggregate function.


Hmm, guess I should spend more time reading the SELECT docs:)



David J.



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Relocatable Binaries (RPMs) : custom installation path for PostgreSQL

[chiru r]

Yes, at this moment we are not considering symlinks.

We would like to have the options in RPMs itself.

On Tue, Jan 1, 2019 at 3:53 PM Brent Wood  wrote:

> Have you considered symlinking the default paths to where you want it to
> reside?
>
> That approach would allow updates & upgrades to work as they expect,
> without changing default paths or config files.
>
> Much as described here:
>
> https://help.cloud66.com/rails/how-to-guides/databases/shells/change-psql-directory.html
>
>
>
> Cheers,
>
>   Brent Wood
>
> --
>
> On 12/31/18 8:19 AM, chiru r wrote:
> > Hi All,
> >
> > I wanted to install the PostgreSQL-11 Software with Custom
> > installation/binary paths.
> >
> > Currently I am using EDB one click installer software with option file
> > like below to achieve, However EDB stopped and no longer provides one
> > click installer for Linux.
> >
> > *Example:*
> >
> > PostgreSQL install software file : *postgresql-9.6.9-1-linux-x64.run*
> >
> > *cat Optionsfile.txt*
> > mode=unattended
> > datadir=/u02/pgdata01/9.6/data
> > serverport=5432
> > prefix=/u01/postgres/9.6
> > I have installed CommunityPostgreSQL RPMs and are going into
> > "/usr/pgsql-11/" by default.
> >
>
> > #-> pwd
> > /usr/pgsql-11
> >
> > [root@user:/usr/pgsql-11]#
> >
> > #-> ls -l
> > total 16
> >
> > drwxr-xr-x 2 root root 4096 Dec 21 13:49 bin
> > drwxr-xr-x 3 root root 4096 Dec 21 13:49 doc
> > drwxr-xr-x 3 root root 4096 Dec 21 13:49 lib
> > drwxr-xr-x 8 root root 4096 Dec 21 13:49 share
> >
> > Please let us know how to get the PostgreSQL-11 installed in above
> > custom paths using RPMs? .
> >
> >
> > Thanks,
> >
> > Chiranjeevi
> >
>
>

Re: Relocatable Binaries (RPMs) : custom installation path for PostgreSQL

[Rob Sargent]

Do Options and One-Click seem oxymoronic to anyone else?

> On Jan 1, 2019, at 5:18 PM, chiru r  wrote:
> 
> Yes, at this moment we are not considering symlinks.
> 
> We would like to have the options in RPMs itself.
> 
>> On Tue, Jan 1, 2019 at 3:53 PM Brent Wood  wrote:
>> Have you considered symlinking the default paths to where you want it to 
>> reside?
>> 
>> That approach would allow updates & upgrades to work as they expect, without 
>> changing default paths or config files.
>> 
>> Much as described here:
>> https://help.cloud66.com/rails/how-to-guides/databases/shells/change-psql-directory.html
>> 
>> 
>> 
>> Cheers,
>> 
>>   Brent Wood
>> 
>> 
>> On 12/31/18 8:19 AM, chiru r wrote:
>> > Hi All,
>> > 
>> > I wanted to install the PostgreSQL-11 Software with Custom 
>> > installation/binary paths.
>> > 
>> > Currently I am using EDB one click installer software with option file 
>> > like below to achieve, However EDB stopped and no longer provides one 
>> > click installer for Linux.
>> > 
>> > *Example:*
>> > 
>> > PostgreSQL install software file : *postgresql-9.6.9-1-linux-x64.run*
>> > 
>> > *cat Optionsfile.txt*
>> > mode=unattended
>> > datadir=/u02/pgdata01/9.6/data
>> > serverport=5432
>> > prefix=/u01/postgres/9.6
>> > I have installed CommunityPostgreSQL RPMs and are going into 
>> > "/usr/pgsql-11/" by default.
>> > 
>> 
>> > #-> pwd
>> > /usr/pgsql-11
>> > 
>> > [root@user:/usr/pgsql-11]#
>> > 
>> > #-> ls -l
>> > total 16
>> > 
>> > drwxr-xr-x 2 root root 4096 Dec 21 13:49 bin
>> > drwxr-xr-x 3 root root 4096 Dec 21 13:49 doc
>> > drwxr-xr-x 3 root root 4096 Dec 21 13:49 lib
>> > drwxr-xr-x 8 root root 4096 Dec 21 13:49 share
>> > 
>> > Please let us know how to get the PostgreSQL-11 installed in above 
>> > custom paths using RPMs? .
>> > 
>> > 
>> > Thanks,
>> > 
>> > Chiranjeevi
>> > 
>>