Re: [GENERAL] Packages for Ubuntu Wily (15.10)

[Antony Gelberg]

Hi Steve, Adrian and list, sorry for my delay in replying.  As I suggested
in my OP, there aren't currently 9.3 (or 9.4) packages for Wily in PGDG,
that's why I was asking if whoever's responsible for these packages could
flip the switch to build them. It would be ever so helpful.

See
http://apt.postgresql.org/pub/repos/apt/dists/wily-pgdg/9.3/binary-amd64/
and
http://apt.postgresql.org/pub/repos/apt/dists/wily-pgdg/9.4/binary-amd64/ -
the Packages file is empty, whereas
http://apt.postgresql.org/pub/repos/apt/dists/wily-pgdg/9.5/binary-amd64/
Packages file contains the relevant data.

On 7 December 2015 at 19:43, Steve Crawford 
wrote:

> You should be able to add the pgdg repository to your system and then
> install through apt as normal. Scroll down to the "PostgreSQL APT
> repository" section on this page:
> http://www.postgresql.org/download/linux/ubuntu/
>
> Cheers,
> Steve
>
> On Mon, Dec 7, 2015 at 9:27 AM, Antony Gelberg 
> wrote:
>
>> Hi all,
>>
>> We want to run 9.3 on the above distro, which comes with 9.4 as standard
>> (in the distribution). However, we note that there are only 9.5 packages in
>> the postgresql 15.10 repository. Can somebody flip the switch to build
>> these? We really aren't ready to upgrade to 9.4 at the present time.
>>
>> Hope somebody can help. :)
>>
>> Antony
>>
>> --
>> http://www.linkedin.com/in/antgel
>> http://about.me/antonygelberg
>>
>
>


-- 
http://www.linkedin.com/in/antgel
http://about.me/antonygelberg

Re: [GENERAL] json indexing and data types

[Kaare Rasmussen]

Hi Oleg

This is known problem, that's why we stop developing jsquery and are 
working on sql-level query language for jsonb, then you'll use all 
power and extendability of SQL.  The idea is to use power of 
subselects and unnest to unroll jsonb to sql level.

There is presentation at pgconf.eu  on this
https://wiki.postgresql.org/images/4/4e/Createam.pdf, see slide #27


This is very interesting. Thanks for the update. And to all who answered 
this topic, sorry for awoling. I just got busy, but thanks for all the 
replies, I got something to think about.



But I'm afraid it'll come to 9.6.


I'll hope it comes in 9.6. I'll definitely look forward to that.

/kaare

Re: [GENERAL] Does PostgreSQL support to write glusterfs by Libgfapi

[Scott Mead]

On Wed, Dec 16, 2015 at 1:26 AM, zh1029  wrote:

> Hi,
>   It seems low performance PostgreSQL(9.3.6) while writing data to
> glusterFS
> distributed file system. libgfapi is provide since GlusterFS version 3.4 to
> avoid kernel visits/data copy which can improve its performance. But I
> didn't find out any instruction from the PostgreSQL web page. Do you know
> if
> PostgreSQL later release supports using libgfapi to optimized write data to
> ClusterFS file system.
>
> The real question is, why are you using GLusterFS?  It's important to be
careful since PostgreSQL provides no mechanism to allow for shared-disk
clustering (active/active).  If you are planning on using active/passive,
you must plan carefully so as not to create a split-brain scenario.


--
Scott Mead
Sr. Architect
*OpenSCG*
PostgreSQL, Java & Linux Experts
http://openscg.com 



>
>
> --
> View this message in context:
> http://postgresql.nabble.com/Does-PostgreSQL-support-to-write-glusterfs-by-Libgfapi-tp5877793.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] Packages for Ubuntu Wily (15.10)

[Adrian Klaver]

On 12/16/2015 04:42 AM, Antony Gelberg wrote:

Hi Steve, Adrian and list, sorry for my delay in replying.  As I
suggested in my OP, there aren't currently 9.3 (or 9.4) packages for
Wily in PGDG, that's why I was asking if whoever's responsible for these
packages could flip the switch to build them. It would be ever so helpful.


Take a look at:
http://apt.postgresql.org/pub/repos/apt/dists/wily-pgdg-testing/



See
http://apt.postgresql.org/pub/repos/apt/dists/wily-pgdg/9.3/binary-amd64/ and
http://apt.postgresql.org/pub/repos/apt/dists/wily-pgdg/9.4/binary-amd64/ -
the Packages file is empty, whereas
http://apt.postgresql.org/pub/repos/apt/dists/wily-pgdg/9.5/binary-amd64/ 
Packages
file contains the relevant data.

On 7 December 2015 at 19:43, Steve Crawford
mailto:scrawf...@pinpointresearch.com>>
wrote:

You should be able to add the pgdg repository to your system and
then install through apt as normal. Scroll down to the "PostgreSQL
APT repository" section on this page:
http://www.postgresql.org/download/linux/ubuntu/

Cheers,
Steve

On Mon, Dec 7, 2015 at 9:27 AM, Antony Gelberg
mailto:antony.gelb...@gmail.com>> wrote:

Hi all,

We want to run 9.3 on the above distro, which comes with 9.4 as
standard (in the distribution). However, we note that there are
only 9.5 packages in the postgresql 15.10 repository. Can
somebody flip the switch to build these? We really aren't ready
to upgrade to 9.4 at the present time.

Hope somebody can help. :)

Antony

--
http://www.linkedin.com/in/antgel
http://about.me/antonygelberg





--
http://www.linkedin.com/in/antgel
http://about.me/antonygelberg



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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Packages for Ubuntu Wily (15.10)

[Antony Gelberg]

Thanks, this looks like it will do the job. Now we just have to hack the
Puppet Postgres module in order to handle the -testing source. :)

On 16 December 2015 at 16:25, Adrian Klaver 
wrote:

> On 12/16/2015 04:42 AM, Antony Gelberg wrote:
>
>> Hi Steve, Adrian and list, sorry for my delay in replying.  As I
>> suggested in my OP, there aren't currently 9.3 (or 9.4) packages for
>> Wily in PGDG, that's why I was asking if whoever's responsible for these
>> packages could flip the switch to build them. It would be ever so helpful.
>>
>
> Take a look at:
> http://apt.postgresql.org/pub/repos/apt/dists/wily-pgdg-testing/
>
>
>> See
>> http://apt.postgresql.org/pub/repos/apt/dists/wily-pgdg/9.3/binary-amd64/
>> and
>> http://apt.postgresql.org/pub/repos/apt/dists/wily-pgdg/9.4/binary-amd64/
>> -
>> the Packages file is empty, whereas
>> http://apt.postgresql.org/pub/repos/apt/dists/wily-pgdg/9.5/binary-amd64/
>> Packages
>> file contains the relevant data.
>>
>> On 7 December 2015 at 19:43, Steve Crawford
>> mailto:scrawf...@pinpointresearch.com>>
>> wrote:
>>
>> You should be able to add the pgdg repository to your system and
>> then install through apt as normal. Scroll down to the "PostgreSQL
>> APT repository" section on this page:
>> http://www.postgresql.org/download/linux/ubuntu/
>>
>> Cheers,
>> Steve
>>
>> On Mon, Dec 7, 2015 at 9:27 AM, Antony Gelberg
>> mailto:antony.gelb...@gmail.com>> wrote:
>>
>> Hi all,
>>
>> We want to run 9.3 on the above distro, which comes with 9.4 as
>> standard (in the distribution). However, we note that there are
>> only 9.5 packages in the postgresql 15.10 repository. Can
>> somebody flip the switch to build these? We really aren't ready
>> to upgrade to 9.4 at the present time.
>>
>> Hope somebody can help. :)
>>
>> Antony
>>
>> --
>> http://www.linkedin.com/in/antgel
>> http://about.me/antonygelberg
>>
>>
>>
>>
>>
>> --
>> http://www.linkedin.com/in/antgel
>> http://about.me/antonygelberg
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>



-- 
http://www.linkedin.com/in/antgel
http://about.me/antonygelberg

Re: [GENERAL] Does PostgreSQL support to write glusterfs by Libgfapi

[Alan Hodgson]

On Tuesday, December 15, 2015 11:26:40 PM zh1029 wrote:
> Hi,
>   It seems low performance PostgreSQL(9.3.6) while writing data to glusterFS
> distributed file system. libgfapi is provide since GlusterFS version 3.4 to
> avoid kernel visits/data copy which can improve its performance. But I
> didn't find out any instruction from the PostgreSQL web page. Do you know
> if PostgreSQL later release supports using libgfapi to optimized write data
> to ClusterFS file system.
> 

Putting a database on Gluster is a horrible idea in any case. But no, 
PostgreSQL does not have Gluster-specific support.

I'm actually trying to think of a use case where it would make sense; I 
suppose a large data warehouse could theoretically see some sequential read 
improvements from a scale-out cluster file system. But you could only have one 
client node accessing it.

signature.asc
Description: This is a digitally signed message part.

[GENERAL] grant connect on database

[spur230]

I am using Postgres 9.4.  I created a login role and gave  select to all
tables to a schema as follows:


create role fix;
create role dcv login password 'mypassword' in role fix;
grant select on all tables on schema xzy to dcv';

I  was able to   connect to the database without giving 'grant connect on
database db1 to dcv'

My question is when do I have to explicitly  give connect grant? How did
user dcv  connect to database without the  connect  privilege?

Thanks



--
View this message in context: 
http://postgresql.nabble.com/grant-connect-on-database-tp5877872.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] grant connect on database

[Jerry Sievers]

spur230  writes:

> I am using Postgres 9.4.  I created a login role and gave  select to all
> tables to a schema as follows:
>
>
> create role fix;
> create role dcv login password 'mypassword' in role fix;
> grant select on all tables on schema xzy to dcv';
>
> I  was able to   connect to the database without giving 'grant connect on
> database db1 to dcv'
>
> My question is when do I have to explicitly  give connect grant? How did
> user dcv  connect to database without the  connect  privilege?

NEver, unless you were to revoke it from public..

Connect is granted to public by default.



> Thanks
>
>
>
> --
> View this message in context: 
> http://postgresql.nabble.com/grant-connect-on-database-tp5877872.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consult...@comcast.net
p: 312.241.7800


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] grant connect on database

[Adrian Klaver]

On 12/16/2015 11:58 AM, Jerry Sievers wrote:

spur230  writes:


I am using Postgres 9.4.  I created a login role and gave  select to all
tables to a schema as follows:


create role fix;
create role dcv login password 'mypassword' in role fix;
grant select on all tables on schema xzy to dcv';

I  was able to   connect to the database without giving 'grant connect on
database db1 to dcv'

My question is when do I have to explicitly  give connect grant? How did
user dcv  connect to database without the  connect  privilege?


NEver, unless you were to revoke it from public..

Connect is granted to public by default.


For what is GRANTed by default see:

http://www.postgresql.org/docs/9.4/interactive/sql-grant.html

Highlights:

"The key word PUBLIC indicates that the privileges are to be granted to 
all roles, including those that might be created later. PUBLIC can be 
thought of as an implicitly defined group that always includes all 
roles. Any particular role will have the sum of privileges granted 
directly to it, privileges granted to any role it is presently a member 
of, and privileges granted to PUBLIC."





"PostgreSQL grants default privileges on some types of objects to 
PUBLIC. No privileges are granted to PUBLIC by default on tables, 
columns, schemas or tablespaces. For other types, the default privileges 
granted to PUBLIC are as follows: CONNECT and CREATE TEMP TABLE for 
databases; EXECUTE privilege for functions; and USAGE privilege for 
languages. The object owner can, of course, REVOKE both default and 
expressly granted privileges. (For maximum security, issue the REVOKE in 
the same transaction that creates the object; then there is no window in 
which another user can use the object.) Also, these initial default 
privilege settings can be changed using the ALTER DEFAULT PRIVILEGES 
command."







Thanks



--
View this message in context: 
http://postgresql.nabble.com/grant-connect-on-database-tp5877872.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.





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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] "Immediate shutdown if postmaster.pid is removed" not mentioned in Release Notes

[Victor Yegorov]

I noticed, that 9.5 release notes (beta2) do not mention
commit 7e2a18a9161fee7e67642863f72b51d77d3e996.


I think this one should be added.


-- 
Victor Y. Yegorov

Re: [GENERAL] "Immediate shutdown if postmaster.pid is removed" not mentioned in Release Notes

[Tatsuo Ishii]

> I noticed, that 9.5 release notes (beta2) do not mention
> commit 7e2a18a9161fee7e67642863f72b51d77d3e996.
> 
> 
> I think this one should be added.

+1.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] "Immediate shutdown if postmaster.pid is removed" not mentioned in Release Notes

[Tom Lane]

Victor Yegorov  writes:
> I noticed, that 9.5 release notes (beta2) do not mention
> commit 7e2a18a9161fee7e67642863f72b51d77d3e996.

We don't normally document back-patched patches in as-yet-unreleased
branches, since the point of release notes is to tell you what's new
compared to the previous release, and this item isn't (or won't be).

Also, I doubt that this is of any great concern to the average user.
It's not a scenario that would come up in anything I would call a
supported use-case.  If we're to reverse the aforementioned policy,
there are probably quite a few patches that would now need to be
documented as "new in 9.5" and are more significant than this.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] "Immediate shutdown if postmaster.pid is removed" not mentioned in Release Notes

[David G. Johnston]

On Wed, Dec 16, 2015 at 3:42 PM, Tom Lane  wrote:

> Victor Yegorov  writes:
> > I noticed, that 9.5 release notes (beta2) do not mention
> > commit 7e2a18a9161fee7e67642863f72b51d77d3e996.
>
> We don't normally document back-patched patches in as-yet-unreleased
> branches, since the point of release notes is to tell you what's new
> compared to the previous release, and this item isn't (or won't be).
>
> Also, I doubt that this is of any great concern to the average user.
> It's not a scenario that would come up in anything I would call a
> supported use-case.  If we're to reverse the aforementioned policy,
> there are probably quite a few patches that would now need to be
> documented as "new in 9.5" and are more significant than this.
>

​IOW, if you are familiar with how a previous version works you should keep
up with its release notes to catch a bug-fix behavior change like this.
Those who will end up learning on the upcoming release will simply learn
that this is how things work.

David J.
​

[GENERAL] Check old and new tuple in row-level policy?

[Karl Czajkowski]

I've been trying to learn more about the row-security policies but
coming up short in my searches.  Was there any consideration to
allowing access to both old and new row tuples in a POLICY ... CHECK
expression?  This idiom I've seen in the lower level rule and trigger
systems seems like it would allow for much more powerful row-security
policies.

The simple illustrations of row-security policy always seem to
consider an 'owner' field in the tuple compared to current_user.  If
you could consult both old and new values, you could generalize to
storing application ACLs in rows and using those ACLs to decide row
access while also ensuring that ACLs cannot be changed in ways
inconsistent with the privilege level of the current user.

For example, if the current user is in the old ACL value, allow them
to modify the ACL otherwise require that the new ACL value be equal to
the old ACL value.  This would allow a user to be given write access
to some columns while restricting others, but on a row-by-row basis.

Right now, as I understand it, you can only compare the old values to
session state in the WITH condition and new values to the session
state in the CHECK condition, but never consider old and new values
simultaneously.  This excludes a wide and useful gray area between
no trust and full trust to amend row content.


Karl



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] "Immediate shutdown if postmaster.pid is removed" not mentioned in Release Notes

[Tatsuo Ishii]

> We don't normally document back-patched patches in as-yet-unreleased
> branches, since the point of release notes is to tell you what's new
> compared to the previous release, and this item isn't (or won't be).

That makes sense.

> Also, I doubt that this is of any great concern to the average user.
> It's not a scenario that would come up in anything I would call a
> supported use-case.  If we're to reverse the aforementioned policy,
> there are probably quite a few patches that would now need to be
> documented as "new in 9.5" and are more significant than this.

It's possible that novice admins accidentally remove postmaster.pid (I
saw this kind of incidents a few times while supporting customers if
my memory serves). Anyway I am happy as long as it's clearly
documented in the release notes of the next versions.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Check old and new tuple in row-level policy?

[Stephen Frost]

Karl,

* Karl Czajkowski (kar...@isi.edu) wrote:
> I've been trying to learn more about the row-security policies but
> coming up short in my searches.  Was there any consideration to
> allowing access to both old and new row tuples in a POLICY ... CHECK
> expression?  This idiom I've seen in the lower level rule and trigger
> systems seems like it would allow for much more powerful row-security
> policies.

It was discussed, albeit rather late in the cycle (this past fall, as I
recall...), and is certainly something we can consider implementing in a
future release.

> The simple illustrations of row-security policy always seem to
> consider an 'owner' field in the tuple compared to current_user.  If
> you could consult both old and new values, you could generalize to
> storing application ACLs in rows and using those ACLs to decide row
> access while also ensuring that ACLs cannot be changed in ways
> inconsistent with the privilege level of the current user.

There is still a need to refer back to some kind of state that is
external to the table under consideration to determine what the session
level access is, no?  Even if the ACLs are in a table somewhere, how do
you know who the current user is?

> For example, if the current user is in the old ACL value, allow them
> to modify the ACL otherwise require that the new ACL value be equal to
> the old ACL value.  This would allow a user to be given write access
> to some columns while restricting others, but on a row-by-row basis.

It's possible to disallow access to that column using column-level
privileges.  Further, how is that column populated?  I would generally
expect it to be populated by consulting some session-level variable (a
custom one, or CURRENT_USER or similar).  If that already exists, then
it can certainly be used in RLS policies.

> Right now, as I understand it, you can only compare the old values to
> session state in the WITH condition and new values to the session
> state in the CHECK condition, but never consider old and new values
> simultaneously.  This excludes a wide and useful gray area between
> no trust and full trust to amend row content.

I'm certainly not against adding that capability, but I do think more
detail around this use-case which you feel it'd be useful for would be
great.  I don't quite see how saying "if the old and new value stay the
same, then you can modify anything" makes sense- you have to consult
some external source to determine if you're the owner of that row,
right?  Otherwise, anyone could change any row, provided that keep that
column the same, and that hardly seems like what you'd want.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] dblink_connect fails

[James Sewell]

No it is not.

Just in case I tried setting it to 'postgres', logged in without -U
(doesn't work without PGUSER set) and tried the operation again.

Same result.

Cheers,


James Sewell,
PostgreSQL Team Lead / Solutions Architect
__


Level 2, 50 Queen St, Melbourne VIC 3000

*P *(+61) 3 8370 8000  *W* www.lisasoft.com  *F *(+61) 3 8370 8099


On Wed, Dec 16, 2015 at 3:26 PM, Joe Conway  wrote:

> On 12/15/2015 06:24 PM, James Sewell wrote:
> > I have a Windows PostgreSQL server where dblink_connect fails to pick up
> > the current user as follows:
>
> > ffm=# SELECT dblink_connect('master', 'dbname=ffm');
> > ERROR:  could not establish connection
> > DETAIL:  FATAL:  role "PRDSWIDEGRID01$" does not exist
>
> > Has anyone seen this before? It seems very odd to me, I have another
> > identical machine (except for being on 9.4.0) which this works on.
>
>
> Do you happen to have a PGUSER variable defined in your environment
> (that is, in the environment as seen by the OS user the postmaster runs
> under)?
>
> See: http://www.postgresql.org/docs/9.4/static/libpq-envars.html
>
> Joe
>
> --
> Crunchy Data - http://crunchydata.com
> PostgreSQL Support for Secure Enterprises
> Consulting, Training, & Open Source Development
>
>

-- 


--
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

Re: [GENERAL] dblink_connect fails

[Adrian Klaver]

On 12/16/2015 04:53 PM, James Sewell wrote:

No it is not.

Just in case I tried setting it to 'postgres', logged in without -U
(doesn't work without PGUSER set) and tried the operation again.


Do you mean this:

DETAIL:  FATAL:  role "PRDSWIDEGRID01$" does not exist

or that you could not connect for another reason?

If for the original reason, does the role PRDSWIDEGRID01$ actually exist 
somewhere?





Same result.

Cheers,


James Sewell,
PostgreSQL Team Lead / Solutions Architect
__

Level 2, 50 Queen St, Melbourne VIC 3000

*P *(+61) 3 8370 8000***W* www.lisasoft.com *F *(+61) 3 8370 8099

On Wed, Dec 16, 2015 at 3:26 PM, Joe Conway mailto:m...@joeconway.com>> wrote:

On 12/15/2015 06:24 PM, James Sewell wrote:
> I have a Windows PostgreSQL server where dblink_connect fails to pick up
> the current user as follows:

> ffm=# SELECT dblink_connect('master', 'dbname=ffm');
> ERROR:  could not establish connection
> DETAIL:  FATAL:  role "PRDSWIDEGRID01$" does not exist

> Has anyone seen this before? It seems very odd to me, I have another
> identical machine (except for being on 9.4.0) which this works on.


Do you happen to have a PGUSER variable defined in your environment
(that is, in the environment as seen by the OS user the postmaster runs
under)?

See: http://www.postgresql.org/docs/9.4/static/libpq-envars.html

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development




The contents of this email are confidential and may be subject to legal
or professional privilege and copyright. No representation is made that
this email is free of viruses or other defects. If you have received
this communication in error, you may not copy or distribute any part of
it or otherwise disclose its contents to anyone. Please advise the
sender of your incorrect receipt of this correspondence.




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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] dblink_connect fails

[Joe Conway]

On 12/16/2015 04:53 PM, James Sewell wrote:
> No it is not.
> 
> Just in case I tried setting it to 'postgres', logged in without -U
> (doesn't work without PGUSER set) and tried the operation again.

> > DETAIL:  FATAL:  role "PRDSWIDEGRID01$" does not exist

That "PRDSWIDEGRID01$" is coming from somewhere in your environment.
Looks like it is supposed to be a variable of some sort which is
supposed to resolve to an actual postgres user but for some reason doesn't.

What about FDWs?


-- 
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development



signature.asc
Description: OpenPGP digital signature

Re: [GENERAL] dblink_connect fails

[Melvin Davidson]

Perhaps the attached will help. It's a sample function that I wrote and
tested a few years ago on PG v8.3
It worked then, so it should be a good model for you.

On Wed, Dec 16, 2015 at 8:00 PM, Adrian Klaver 
wrote:

> On 12/16/2015 04:53 PM, James Sewell wrote:
>
>> No it is not.
>>
>> Just in case I tried setting it to 'postgres', logged in without -U
>> (doesn't work without PGUSER set) and tried the operation again.
>>
>
> Do you mean this:
>
> DETAIL:  FATAL:  role "PRDSWIDEGRID01$" does not exist
>
> or that you could not connect for another reason?
>
> If for the original reason, does the role PRDSWIDEGRID01$ actually exist
> somewhere?
>
>
>
>> Same result.
>>
>> Cheers,
>>
>>
>> James Sewell,
>> PostgreSQL Team Lead / Solutions Architect
>> __
>>
>> Level 2, 50 Queen St, Melbourne VIC 3000
>>
>> *P *(+61) 3 8370 8000***W* www.lisasoft.com *F *(+61) 3 8370 8099
>>
>> On Wed, Dec 16, 2015 at 3:26 PM, Joe Conway > > wrote:
>>
>> On 12/15/2015 06:24 PM, James Sewell wrote:
>> > I have a Windows PostgreSQL server where dblink_connect fails to
>> pick up
>> > the current user as follows:
>>
>> > ffm=# SELECT dblink_connect('master', 'dbname=ffm');
>> > ERROR:  could not establish connection
>> > DETAIL:  FATAL:  role "PRDSWIDEGRID01$" does not exist
>>
>> > Has anyone seen this before? It seems very odd to me, I have another
>> > identical machine (except for being on 9.4.0) which this works on.
>>
>>
>> Do you happen to have a PGUSER variable defined in your environment
>> (that is, in the environment as seen by the OS user the postmaster
>> runs
>> under)?
>>
>> See: http://www.postgresql.org/docs/9.4/static/libpq-envars.html
>>
>> Joe
>>
>> --
>> Crunchy Data - http://crunchydata.com
>> PostgreSQL Support for Secure Enterprises
>> Consulting, Training, & Open Source Development
>>
>>
>>
>> 
>> The contents of this email are confidential and may be subject to legal
>> or professional privilege and copyright. No representation is made that
>> this email is free of viruses or other defects. If you have received
>> this communication in error, you may not copy or distribute any part of
>> it or otherwise disclose its contents to anyone. Please advise the
>> sender of your incorrect receipt of this correspondence.
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.
-- Function: test_archive(text, text, text, text, date)

-- DROP FUNCTION test_archive(text, text, text, text, date);

CREATE OR REPLACE FUNCTION test_archive(text, text, text, text, date)
  RETURNS bigint AS
$BODY$
-- CREATED BY: Melvin Davidson
-- DATE: 2008-08-11
-- 
-- Execute this function from archive db
-- General flow
-- 1. Open connection to production db 
-- 2. Insert into archive db as select from with criteria
-- 3. Delete from production db with same criteria
-- 4. Close connection.
-- 5. Return the number of TXMaster (Total) records archived.
--
-- ERROR HANDLING
-- If dblink connection fails, -1 is returned
-- If archive date is >= Current Date, -10 is returned
-- If Total number of transactions inserted does not = TXMaster inserts, 
-- rollback is done and -100 returned
-- If Total number of remote transactions deleted does not = remote TXMaster 
deletes, 
-- rollback is done and -200 returned

DECLARE
-- Parameter(s)
p_host_ip   ALIAS FOR $1;
p_dbnameALIAS FOR $2;
p_user  ALIAS FOR $3;
p_passwdALIAS FOR $4;
p_date  ALIAS FOR $5;

-- Variable(s)
v_ctr   BIGINT DEFAULT 0;
v_txmaster_ctr  BIGINT DEFAULT 0;
v_txassetcycle_ctr  BIGINT DEFAULT 0;
v_txassetdamaged_ctrBIGINT DEFAULT 0;
v_txassetfilledemptied_ctr  BIGINT DEFAULT 0;
v_txassetfound_ctr  BIGINT DEFAULT 0;
v_txassetlost_ctr   BIGINT DEFAULT 0;
v_txassetmoved_ctr  BIGINT DEFAULT 0;
v_txassetOwnerChanged_ctr   BIGINT DEFAULT 0;
v_txassetprodasscheck_ctr   BIGINT DEFAULT 0;
v_txassetrepaired_ctr   BIGINT DEFAULT 0;
v_txassettagassigned_ctrBIGINT DEFAULT 0;
v_txbillingaction_ctr   BIGINT DEFAULT 0;
v_txexception_ctr   BIGINT DEFAULT 0;
v_txorderdetailfilled_ctr   BIGINT DEFAULT 0;
v_txassettagremoved_ctr BIGINT DEFAULT 0;

[GENERAL] Fwd: dblink_connect fails

[James Sewell]

Oops left off the list.

-- Forwarded message --
From: *James Sewell* 
Date: Thursday, 17 December 2015
Subject: dblink_connect fails
To: Joe Conway 


On Thu, Dec 17, 2015 at 12:11 PM, Joe Conway > wrote:

> On 12/16/2015 04:53 PM, James Sewell wrote:
> > No it is not.
> >
> > Just in case I tried setting it to 'postgres', logged in without -U
> > (doesn't work without PGUSER set) and tried the operation again.
>
> > > DETAIL:  FATAL:  role "PRDSWIDEGRID01$" does not exist
>
> That "PRDSWIDEGRID01$" is coming from somewhere in your environment.
> Looks like it is supposed to be a variable of some sort which is
> supposed to resolve to an actual postgres user but for some reason doesn't.
>
> What about FDWs?
>

This is a new database with some tables and dblink loaded.

The  PRDSWIDEGRID01 is actually the hostname - but I just can't see how
it's getting injected.

My understanding was that psql -U should override? And also that any user
variable just sets the user PostgreSQL variable - which is postgres.

Strange stuff. I've just tried from cygwin, from Windows psql and from
PGAdmin - all the same result.

>
>
> --
> Crunchy Data - http://crunchydata.com
> PostgreSQL Support for Secure Enterprises
> Consulting, Training, & Open Source Development
>
>



-- 

James Sewell,
PostgreSQL Team Lead / Solutions Architect
__


Level 2, 50 Queen St, Melbourne VIC 3000

*P *(+61) 3 8370 8000  *W* www.lisasoft.com  *F *(+61) 3 8370 8099

-- 


--
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

Re: [GENERAL] Check old and new tuple in row-level policy?

[Karl Czajkowski]

On Dec 16, Stephen Frost modulated:

> There is still a need to refer back to some kind of state that is
> external to the table under consideration to determine what the session
> level access is, no?  Even if the ACLs are in a table somewhere, how do
> you know who the current user is?
> 

Yes, I was assuming session variables of some sort.

Right now, I am looking to use the row-security policies for a web
application. We would just have the application server authenticate as
a service role and set session variables holding the web client's
authentication context.  The WITH and CHECK conditions would compare
row content against these session variables to enforce web client
authorization decisions underneath our application queries.

The row content includes ownership and ACL-like content as well as
other application content subject to special access rules expressed in
terms of the ownership and ACLs. If we authenticated users to the
database, we would want to consult current_user and something like
current_roles (an array of all roles granted to the
current_user). Instead, we'll be using analogous session context
asserted to us by the web service.

We've done previous systems where we compile all the application
policy checks into the SQL queries generated by the application, but I
think it would be more appropriate to split these out and have
generalized enforcement at the database level. It feels like a close
but not perfect fit already.  It's fraught with perile to handle all
the data visibility rules while generating any application-level data
filtering expressions, joins, etc.!

I'm trying to boil out some simple illustrations. Discussing an entire
cohesive system is difficult and probably counter-productive...

The technical idea is to have policies that consider the relationship
between old data, new data, and session context to only allow
particular state transitions for row UPDATE. For INSERT, SELECT, and
DELETE, I think the current policy model is already sufficient.

Just a few possible use cases to illustrate mixed tests of old and new
row values:

  1. Interlocks between record states and supplemental access
 rights. A community might not allow records to be marked readable
 until they have been giving a passing QA grade. A subsequent
 consumer might revise the to a failing grade, but not revoke the
 current access rights due to transparency rules.

  2. State-transition rules for specific values. Enforce that regular
 users can only move a workflow state in along normal edges,
 while an admin user may be able to intervene and make abnormal
 transitions.  Or, allow users to fill in "missing" data such as
 replacing NULL or other defaults with better values, but only
 administrators can erase data back to NULL states.

  3. Classification systems or other quasi-monotonic permissions
 models where a user may advance the access class of a record in
 one direction, but only special administrators can reverse the
 direction.

 A. A publishing system might make it easy to draft data in
smaller, private groups but once published it is hard to
retract things from the public record.

 B. Confidentiality systems might do the opposite, allowing things
to be flagged as sensitive and locked down more easily than
relaxing access restrictions.

 C. Community-based delegation systems might make it easy to
"share" records with additional consumers by adding to an ACL
but only the more privileged owner of the row can remove
entries from the ACL to "unshare".

  4. Custody or provenance records.  Certain unusual state-transitions
 of data values may only be allowed if an explantory record is
 appended to a small log array stored in the row.

I think that there is significant overlap between authorization, state
transition models, and data integrity constraints once you start
considering collaborative applications with mutable records.

The next big leap beyond considering NEW and OLD values during
condition checks would be to use scalar subqueries to examine the row
within the context of other existing rows in the same or different
tables.  I have not looked to see if this is possible in the current
policy system, but I imagine we would try hard to avoid doing this due
to performance implications, even if it is allowed...


Karl



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Fwd: dblink_connect fails

[Joe Conway]

On 12/16/2015 06:10 PM, James Sewell wrote:
> Oops left off the list.

Me too -- response repeated below...

> -- Forwarded message --
> From: *James Sewell*  >
> Date: Thursday, 17 December 2015
> Subject: dblink_connect fails
> To: Joe Conway mailto:m...@joeconway.com>>
> 
> 
> On Thu, Dec 17, 2015 at 12:11 PM, Joe Conway  > wrote:
> 
> On 12/16/2015 04:53 PM, James Sewell wrote:
> > No it is not.
> >
> > Just in case I tried setting it to 'postgres', logged in without -U
> > (doesn't work without PGUSER set) and tried the operation again.
> 
> > > DETAIL:  FATAL:  role "PRDSWIDEGRID01$" does not exist

> This is a new database with some tables and dblink loaded.
> 
> The  PRDSWIDEGRID01 is actually the hostname - but I just can't see how
> it's getting injected.
> 
> My understanding was that psql -U should override? And also that any
> user variable just sets the user PostgreSQL variable - which is postgres.

Hmmm, well the way you start up psql should be irrelevant here.

What goes on with dblink_connect() is more-or-less completely controlled
by libpq's PQconnectdb(). When you do not provide a user explicitly in
your libpq connect string it defaults to the user that the current
process is running under unless the PGUSER environment variable has been
defined. See:

http://www.postgresql.org/docs/9.4/interactive/libpq-envars.html

"The following environment variables can be used to select default
 connection parameter values, which will be used by PQconnectdb,
 PQsetdbLogin and PQsetdb if no value is directly specified by the
 calling code.

 PGUSER behaves the same as the user connection parameter."

-and-

http://www.postgresql.org/docs/9.4/interactive/libpq-connect.html#LIBPQ-PARAMKEYWORDS

"user

PostgreSQL user name to connect as. Defaults to be the same as
the operating system name of the user running the application."

So in your case, does your postgres server run as an OS user called
PRDSWIDEGRID01$ for some reason?

Joe



-- 
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development



signature.asc
Description: OpenPGP digital signature

Re: [GENERAL] Check old and new tuple in row-level policy?

[Stephen Frost]

* Karl Czajkowski (kar...@isi.edu) wrote:
> I think that there is significant overlap between authorization, state
> transition models, and data integrity constraints once you start
> considering collaborative applications with mutable records.

Even with OLD/NEW being available to UPDATE, many of the state
transistion checks which you describe may well be better suited to
triggers rather than policies..  I can imagine such complicated
transistions quickly moving beyond SQL expressions and into procedural
logic.  I don't believe using policies for UPDATE which simply end up
calling a function to do a bunch of complicated considerations to be
particularly better than an UPDATE trigger which does the same.

> The next big leap beyond considering NEW and OLD values during
> condition checks would be to use scalar subqueries to examine the row
> within the context of other existing rows in the same or different
> tables.  I have not looked to see if this is possible in the current
> policy system, but I imagine we would try hard to avoid doing this due
> to performance implications, even if it is allowed...

Policies are certainly able to have references to other tables through
subqueries.  You are correct that there are performance considerations,
but those are essentially the same considerations you would have if the
application was to perform the same joins and queries as part of the
query, or if you were to include those in a view.

Thanks!

Stephen


signature.asc
Description: Digital signature

[GENERAL] Index contains unexpected zero page at block

[Victor Blomqvist]

>From time to time I get this and similar errors in my Postgres log file:

< 2015-12-17 07:45:05.976 CST >ERROR:  index
"user_pictures_picture_dhash_idx" contains unexpected zero page at block
123780
< 2015-12-17 07:45:05.976 CST >HINT:  Please REINDEX it.
< 2015-12-17 07:45:05.976 CST >CONTEXT:  PL/pgSQL function
select_pictures_by_dhash(bigint,integer,integer) line 3 at RETURN QUERY
< 2015-12-17 07:45:05.976 CST >STATEMENT:  SELECT * FROM
select_pictures_by_dhash(855924535268850,100,0)

I have tried to recreate the index (create new index, drop the old one),
but it doesnt help, the error comes back anyway. Also, its not there all
the time, when I tried to rerun the statement from the logfile above a
couple of hours later it worked fine without any error.

The server is a read slave, set up with streaming replication. We run
PostgreSQL 9.3.5.

Is this anything I should be worried about, and if so, what can I do to fix
it? Will it be fixed with a newer version of Postgres?

Thanks!
Victor

Re: [GENERAL] Index contains unexpected zero page at block

[Tom Lane]

Victor Blomqvist  writes:
>> From time to time I get this and similar errors in my Postgres log file:
> < 2015-12-17 07:45:05.976 CST >ERROR:  index
> "user_pictures_picture_dhash_idx" contains unexpected zero page at block
> 123780

Hm, can't tell for sure from the error message text, but the index name
suggests that this is a hash index?

> The server is a read slave, set up with streaming replication. We run
> PostgreSQL 9.3.5.

Hash indexes are not WAL-logged, which means their contents do not
propagate to slave servers, which basically means you cannot use them
in replication setups.

> Will it be fixed with a newer version of Postgres?

Adding WAL-logging to hash indexes has been on the to-do list for a long
time; but it's never gotten done, in part because there has never been
any clear evidence that hash indexes are better than btree indexes for
any real-world purpose.  I'm curious why you chose this index type in
the first place.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Index contains unexpected zero page at block

[Victor Blomqvist]

Sorry, I should have included the index definition, its a normal btree
index on a bigint column:

CREATE INDEX user_pictures_picture_dhash_idx
  ON user_pictures
  USING btree
  (picture_dhash);

And the table itself:
CREATE TABLE user_pictures (picture_dhash bigint)
(and ~10 other columns not relevant for this I think)

/Victor


On Thu, Dec 17, 2015 at 12:22 PM, Tom Lane  wrote:

> Victor Blomqvist  writes:
> >> From time to time I get this and similar errors in my Postgres log file:
> > < 2015-12-17 07:45:05.976 CST >ERROR:  index
> > "user_pictures_picture_dhash_idx" contains unexpected zero page at block
> > 123780
>
> Hm, can't tell for sure from the error message text, but the index name
> suggests that this is a hash index?
>
> > The server is a read slave, set up with streaming replication. We run
> > PostgreSQL 9.3.5.
>
> Hash indexes are not WAL-logged, which means their contents do not
> propagate to slave servers, which basically means you cannot use them
> in replication setups.
>
> > Will it be fixed with a newer version of Postgres?
>
> Adding WAL-logging to hash indexes has been on the to-do list for a long
> time; but it's never gotten done, in part because there has never been
> any clear evidence that hash indexes are better than btree indexes for
> any real-world purpose.  I'm curious why you chose this index type in
> the first place.
>
> regards, tom lane
>

Re: [GENERAL] Fwd: dblink_connect fails

[Adrian Klaver]

On 12/16/2015 06:10 PM, James Sewell wrote:

Oops left off the list.

-- Forwarded message --
From: *James Sewell* mailto:james.sew...@lisasoft.com>>
Date: Thursday, 17 December 2015
Subject: dblink_connect fails
To: Joe Conway mailto:m...@joeconway.com>>


On Thu, Dec 17, 2015 at 12:11 PM, Joe Conway > wrote:

On 12/16/2015 04:53 PM, James Sewell wrote:
> No it is not.
>
> Just in case I tried setting it to 'postgres', logged in without -U
> (doesn't work without PGUSER set) and tried the operation again.

> > DETAIL:  FATAL:  role "PRDSWIDEGRID01$" does not exist

That "PRDSWIDEGRID01$" is coming from somewhere in your environment.
Looks like it is supposed to be a variable of some sort which is
supposed to resolve to an actual postgres user but for some reason
doesn't.

What about FDWs?


This is a new database with some tables and dblink loaded.

The  PRDSWIDEGRID01 is actually the hostname - but I just can't see how
it's getting injected.

My understanding was that psql -U should override? And also that any
user variable just sets the user PostgreSQL variable - which is postgres.

Strange stuff. I've just tried from cygwin, from Windows psql and from
PGAdmin - all the same result.


That is all from the client point of view. As Joe Conway has mentioned 
you need to be looking from the server point of view. In other words 
what is the environment for the Postgres server you are running 
dblink_connect in?






--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development





--

James Sewell,
PostgreSQL Team Lead / Solutions Architect
__

Level 2, 50 Queen St, Melbourne VIC 3000

*P *(+61) 3 8370 8000***W* www.lisasoft.com *F *(+61) 3 8370 8099



The contents of this email are confidential and may be subject to legal
or professional privilege and copyright. No representation is made that
this email is free of viruses or other defects. If you have received
this communication in error, you may not copy or distribute any part of
it or otherwise disclose its contents to anyone. Please advise the
sender of your incorrect receipt of this correspondence.




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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general