Re: [HACKERS] alter user/role CURRENT_USER

2014-10-29 Thread Kyotaro HORIGUCHI
Hello, thank you all for many comments. At the first, I removed changes for role-vs-user consistency and remove all added role named other than current_user. The followings are one-by-one answer for the comments so far, please let me know if I missed anything. - The necessity of the new function

Re: [HACKERS] group locking: incomplete patch, just for discussion

2014-10-29 Thread Simon Riggs
On 28 October 2014 23:24, Robert Haas wrote: >> You asked for my help, but I'd like to see some concrete steps towards >> an interim feature so I can see some benefit in a clear direction. >> >> Can we please have the first step we discussed? Parallel CREATE INDEX? >> (Note the please) > > What I

Re: [HACKERS] WIP: Access method extendability

2014-10-29 Thread Simon Riggs
On 28 October 2014 23:25, Andres Freund wrote: > On 2014-10-28 20:17:57 +, Simon Riggs wrote: >> On 28 October 2014 17:47, Andres Freund wrote: >> > On 2014-10-28 17:45:36 +, Simon Riggs wrote: >> >> I'd like to avoid all of the pain by making persistent AMs that are >> >> recoverable aft

Re: [HACKERS] WIP: multivariate statistics / proof of concept

2014-10-29 Thread David Rowley
On Mon, Oct 13, 2014 at 11:00 AM, Tomas Vondra wrote: > Hi, > > attached is a WIP patch implementing multivariate statistics. The code > certainly is not "ready" - parts of it look as if written by a rogue > chimp who got bored of attempts to type the complete works of William > Shakespeare, and

Re: [HACKERS] Allow peer/ident to fall back to md5?

2014-10-29 Thread Andres Freund
On 2014-10-29 10:52:38 +0800, Craig Ringer wrote: >peer >peer with_md5_fallback >peer md5_fallback=on >peer_or_md5 If, we should make it generic. Like 'peer, md5'. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development

Re: [HACKERS] Allow peer/ident to fall back to md5?

2014-10-29 Thread Andres Freund
On 2014-10-29 02:39:49 -0400, Noah Misch wrote: > local all all peer continue I like this one. But then I perhaps edited too many pam configuration files. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training

Re: [HACKERS] Allow peer/ident to fall back to md5?

2014-10-29 Thread Craig Ringer
On 10/29/2014 05:46 PM, Andres Freund wrote: > I like this one. But then I perhaps edited too many pam configuration > files. It seems good to me too. I haven't looked at how viable it is in implementation terms. I think we could only properly support 'continue' on peer/ident in the v3 protocol.

[HACKERS] Failback to old master

2014-10-29 Thread Maeldron T.
Hello, I swear I have read a couple of old threads. Yet I am not sure if it safe to failback to the old master in case of async replication without base backup. Considering: I have the latest 9.3 server A: master B: slave B is actively connected to A I shut down A manually with -m fast (it's the

Re: [HACKERS] Directory/File Access Permissions for COPY and Generic File Access Functions

2014-10-29 Thread Stephen Frost
* Peter Eisentraut (pete...@gmx.net) wrote: > On 10/27/14 7:36 PM, Stephen Frost wrote: > > MySQL: > > http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_file > > > > (note they provide a way to limit access also, via secure_file_priv) > > They have a single privilege to allow t

Re: [HACKERS] Directory/File Access Permissions for COPY and Generic File Access Functions

2014-10-29 Thread Stephen Frost
Robert, * Robert Haas (robertmh...@gmail.com) wrote: > On Tue, Oct 28, 2014 at 3:19 PM, Stephen Frost wrote: > > I agree that this makes it feel awkward. Peter had an interesting > > suggestion to make the dir aliases available as actual aliases for the > > commands which they would be relevant

Re: [HACKERS] WIP: multivariate statistics / proof of concept

2014-10-29 Thread Tomas Vondra
Dne 29 Říjen 2014, 10:41, David Rowley napsal(a): > > I've not really gotten around to looking at the patch yet, but I'm also > wondering if it would be simple include allowing functional statistics > too. > The pg_mv_statistic name seems to indicate multi columns, but how about > stats on date(dat

Re: [HACKERS] printing table in asciidoc with psql

2014-10-29 Thread Szymon Guz
On 17 October 2014 09:01, Pavel Stehule wrote: > Hi Szymon > > I found a small bug - it doesn't escape "|" well > > postgres=# select * from mytab ; > a | numeric_b | c > --+---+ > Ahoj |10 | 2014-10-17 > Hello|20 | 2014-10-18 > H

Re: [HACKERS] WIP: multivariate statistics / proof of concept

2014-10-29 Thread Petr Jelinek
On 29/10/14 10:41, David Rowley wrote: On Mon, Oct 13, 2014 at 11:00 AM, Tomas Vondra http://www.postgresql.org/message-id/CAApHDvp2vH=7O-gp-zAf7aWy+A-WHWVg7h3Vc6=5pf9uf34...@mail.gmail.com . Without giving it too much thought, perhaps any expression that can be indexed should be allowed to have

Re: [HACKERS] WIP: multivariate statistics / proof of concept

2014-10-29 Thread Tomas Vondra
Dne 29 Říjen 2014, 12:31, Petr Jelinek napsal(a): > On 29/10/14 10:41, David Rowley wrote: >> On Mon, Oct 13, 2014 at 11:00 AM, Tomas Vondra > >> The last point is really just "unfinished implementation" - the >> syntax I >> propose is this: >> >> ALTER TABLE ... ADD STATISTICS (opt

Re: [HACKERS] PostgreSQL Service Name Enhancement - Wildcard support for LDAP/DNS lookup

2014-10-29 Thread Albe Laurenz
I have suggested a similar feature before and met with little enthusiasm: http://www.postgresql.org/message-id/d960cb61b694cf459dcfb4b0128514c2f34...@exadv11.host.magwien.gv.at I still think it would be a nice feature and would make pg_service.conf more useful than it is now. Yours, Laurenz Albe

Re: [HACKERS] Allow peer/ident to fall back to md5?

2014-10-29 Thread Stephen Frost
* Andres Freund (and...@2ndquadrant.com) wrote: > On 2014-10-29 02:39:49 -0400, Noah Misch wrote: > > local all all peer continue > > I like this one. But then I perhaps edited too many pam configuration > files. I don't particularly like it, for much the same reason... I'd be fine with "fallbac

Re: [HACKERS] group locking: incomplete patch, just for discussion

2014-10-29 Thread Amit Kapila
On Wed, Oct 29, 2014 at 2:18 PM, Simon Riggs wrote: > > My proposal is we do a parallel index build scan... just as we > discussed earlier, so you would be following the direction set by Dev > Meeting, not just a proposal of mine. > > As I mentioned previously when you started discussing shared me

Re: [HACKERS] WITH CHECK and Column-Level Privileges

2014-10-29 Thread Stephen Frost
Robert, all, * Robert Haas (robertmh...@gmail.com) wrote: > On Mon, Sep 29, 2014 at 10:26 AM, Stephen Frost wrote: > > In the end, it sounds like we all agree that the right approach is to > > simply remove this detail and avoid the issue entirely. > > Well, I think that's an acceptable approach

Re: [HACKERS] group locking: incomplete patch, just for discussion

2014-10-29 Thread Robert Haas
On Wed, Oct 29, 2014 at 4:48 AM, Simon Riggs wrote: > If you do wish to pursue || Seq Scan, then a working prototype would > help. It allows us to see that there is an open source solution we are > working to solve the problems for. People can benchmark it, understand > the benefits and issues it

Re: [HACKERS] TAP test breakage on MacOS X

2014-10-29 Thread Robert Haas
On Tue, Oct 28, 2014 at 9:01 PM, Peter Eisentraut wrote: > Well, they caught the fact that pg_basebackup can't back up tablespaces > with names longer than 99 characters, for example. > > But it's wrong to expect the primary value of tests to be to detect > previously unknown bugs. Yes, that has

Re: [HACKERS] WITH CHECK and Column-Level Privileges

2014-10-29 Thread Robert Haas
On Wed, Oct 29, 2014 at 8:16 AM, Stephen Frost wrote: > suggestions. If the user does not have table-level SELECT rights, > they'll see for the "Failing row contains" case, they'll get: > > Failing row contains (col1, col2, col3) = (1, 2, 3). > > Or, if they have no access to any columns: > > Fai

Re: [HACKERS] Directory/File Access Permissions for COPY and Generic File Access Functions

2014-10-29 Thread Robert Haas
On Wed, Oct 29, 2014 at 6:50 AM, Stephen Frost wrote: > This could work though. We could add an array to pg_authid which is a > complex type that combines the permission allowed with the directory > somehow. Feels like it might get a bit clumsy though. Sure, I'm just throwing things out to see

Re: [HACKERS] Directory/File Access Permissions for COPY and Generic File Access Functions

2014-10-29 Thread Stephen Frost
* Robert Haas (robertmh...@gmail.com) wrote: > On Wed, Oct 29, 2014 at 6:50 AM, Stephen Frost wrote: > > This could work though. We could add an array to pg_authid which is a > > complex type that combines the permission allowed with the directory > > somehow. Feels like it might get a bit clums

Re: [HACKERS] WITH CHECK and Column-Level Privileges

2014-10-29 Thread Stephen Frost
* Robert Haas (robertmh...@gmail.com) wrote: > On Wed, Oct 29, 2014 at 8:16 AM, Stephen Frost wrote: > > suggestions. If the user does not have table-level SELECT rights, > > they'll see for the "Failing row contains" case, they'll get: > > > > Failing row contains (col1, col2, col3) = (1, 2, 3).

Re: [HACKERS] pg_dump/pg_restore seem broken on hamerkop

2014-10-29 Thread Andrew Dunstan
On 10/29/2014 12:26 AM, Tom Lane wrote: I wrote: Alvaro Herrera writes: [Some more code and git-log reading later] I see that the %z is a very recent addition: it only got there as of commit ad5d46a449, of September 5th ... and now I also see that hamerkop's last green run before the failure

[HACKERS] Validating CHECK constraints with SPI

2014-10-29 Thread Dan Robinson
Hi all, If I'm reading correctly in src/backend/commands/tablecmds.c, it looks like PostgreSQL does a full table scan in validateCheckConstraint and in the constraint validation portion of ATRewriteTable. Since the table is locked to updates while the constraint is validating, this means you have

Re: [HACKERS] group locking: incomplete patch, just for discussion

2014-10-29 Thread Simon Riggs
On 29 October 2014 12:08, Amit Kapila wrote: > On Wed, Oct 29, 2014 at 2:18 PM, Simon Riggs wrote: >> >> My proposal is we do a parallel index build scan... just as we >> discussed earlier, so you would be following the direction set by Dev >> Meeting, not just a proposal of mine. >> >> As I ment

Re: [HACKERS] Trailing comma support in SELECT statements

2014-10-29 Thread Kevin Grittner
Tom Lane wrote: > Jim Nasby writes: >> On 10/28/14, 4:25 PM, David E. Wheeler wrote: >>> This one, however, is more a judgment of people and their >>> practices rather than the feature itself. Color me unimpressed. >> >> +1. >> >> Having users sweat of comma placement in this day and age is >> pr

Re: [HACKERS] Directory/File Access Permissions for COPY and Generic File Access Functions

2014-10-29 Thread Alvaro Herrera
Robert Haas wrote: > To articular my own concerns perhaps a bit better, there are two major > things I don't like about the whole DIRALIAS proposal. Number one, > you're creating this SQL object whose name is not actually used for > anything other than manipulating the alias you created. The use

Re: [HACKERS] Validating CHECK constraints with SPI

2014-10-29 Thread Alvaro Herrera
Dan Robinson wrote: > Hi all, > > If I'm reading correctly in src/backend/commands/tablecmds.c, it looks like > PostgreSQL does a full table scan in validateCheckConstraint and in the > constraint validation portion of ATRewriteTable. > > Since the table is locked to updates while the constraint

Re: [HACKERS] group locking: incomplete patch, just for discussion

2014-10-29 Thread Simon Riggs
On 29 October 2014 12:28, Robert Haas wrote: > I care much more about getting the general infrastructure in place to > make parallel programming feasible in PostgreSQL than I do about > getting one particular case working. And more than feasible: I want > it to be relatively straightforward. Th

Re: [HACKERS] Validating CHECK constraints with SPI

2014-10-29 Thread Tom Lane
Dan Robinson writes: > Since the table is locked to updates while the constraint is validating, > this means you have to jump through hoops if you want to add a CHECK > constraint to a large table in a production setting. This validation could > be considerably faster if we enabled it to use relev

Re: [HACKERS] Failback to old master

2014-10-29 Thread Robert Haas
On Wed, Oct 29, 2014 at 6:21 AM, Maeldron T. wrote: > I swear I have read a couple of old threads. Yet I am not sure if it safe to > failback to the old master in case of async replication without base backup. > > Considering: > I have the latest 9.3 server > A: master > B: slave > B is actively c

Re: [HACKERS] WIP: Access method extendability

2014-10-29 Thread Robert Haas
On Tue, Oct 28, 2014 at 7:25 PM, Andres Freund wrote: > To me this is a pretty independent issue. I quite agree. As Stephen was at pains to remind me last night on another thread, we cannot force people to write the patches we think they should write. They get to pursue what they think the prio

Re: [HACKERS] pg_basebackup fails with long tablespace paths

2014-10-29 Thread Robert Haas
On Tue, Oct 28, 2014 at 8:29 PM, Peter Eisentraut wrote: > On 10/20/14 2:59 PM, Tom Lane wrote: >> My Salesforce colleague Thomas Fanghaenel observed that the TAP tests >> for pg_basebackup fail when run in a sufficiently deeply-nested directory >> tree. > > As for the test, we can do something li

Re: [HACKERS] Directory/File Access Permissions for COPY and Generic File Access Functions

2014-10-29 Thread Tom Lane
Stephen Frost writes: > Agreed- additional input from others would be great. I think this entire concept is a bad idea that will be a never-ending source of security holes. There are too many things that a user with filesystem access can do to get superuser-equivalent status. Here is one trivia

Re: [HACKERS] Directory/File Access Permissions for COPY and Generic File Access Functions

2014-10-29 Thread Andres Freund
On 2014-10-29 10:47:58 -0400, Tom Lane wrote: > Here is one trivial example: you want to let user joe import COPY > data quickly, so you give him read access in directory foo, which he > has write access on from his own account. Surely that's right in the > middle of use cases you had in mind, or

Re: [HACKERS] Directory/File Access Permissions for COPY and Generic File Access Functions

2014-10-29 Thread Robert Haas
On Wed, Oct 29, 2014 at 10:52 AM, Andres Freund wrote: >> The larger point though is that this is just one of innumerable attack >> routes for anyone with the ability to make the server do filesystem reads >> or writes of his choosing. If you think that's something you can safely >> give to peopl

Re: [HACKERS] Directory/File Access Permissions for COPY and Generic File Access Functions

2014-10-29 Thread Stephen Frost
Tom, * Tom Lane (t...@sss.pgh.pa.us) wrote: > Stephen Frost writes: > > Agreed- additional input from others would be great. > > I think this entire concept is a bad idea that will be a never-ending > source of security holes. There are too many things that a user with > filesystem access can d

Re: [HACKERS] Directory/File Access Permissions for COPY and Generic File Access Functions

2014-10-29 Thread Stephen Frost
* Robert Haas (robertmh...@gmail.com) wrote: > On Wed, Oct 29, 2014 at 10:52 AM, Andres Freund > wrote: > >> The larger point though is that this is just one of innumerable attack > >> routes for anyone with the ability to make the server do filesystem reads > >> or writes of his choosing. If yo

Re: [HACKERS] Directory/File Access Permissions for COPY and Generic File Access Functions

2014-10-29 Thread Alvaro Herrera
Robert Haas wrote: > On Wed, Oct 29, 2014 at 10:52 AM, Andres Freund > wrote: > >> The larger point though is that this is just one of innumerable attack > >> routes for anyone with the ability to make the server do filesystem reads > >> or writes of his choosing. If you think that's something y

Re: [HACKERS] Directory/File Access Permissions for COPY and Generic File Access Functions

2014-10-29 Thread Stephen Frost
* Alvaro Herrera (alvhe...@2ndquadrant.com) wrote: > Robert Haas wrote: > > On Wed, Oct 29, 2014 at 10:52 AM, Andres Freund > > wrote: > > >> The larger point though is that this is just one of innumerable attack > > >> routes for anyone with the ability to make the server do filesystem reads > >

Re: [HACKERS] Directory/File Access Permissions for COPY and Generic File Access Functions

2014-10-29 Thread Tom Lane
Stephen Frost writes: > * Tom Lane (t...@sss.pgh.pa.us) wrote: >> The larger point though is that this is just one of innumerable attack >> routes for anyone with the ability to make the server do filesystem reads >> or writes of his choosing. If you think that's something you can safely >> give

Re: [HACKERS] group locking: incomplete patch, just for discussion

2014-10-29 Thread Robert Haas
On Wed, Oct 29, 2014 at 10:21 AM, Simon Riggs wrote: > There is a real danger that your "ta-dah" moment sometime in the > future contains flaws which need to be addressed, but we now have > piles of questionable infrastructure lieing around. If you have > similar doubts about anything I'm doing, p

Re: [HACKERS] Directory/File Access Permissions for COPY and Generic File Access Functions

2014-10-29 Thread Robert Haas
On Wed, Oct 29, 2014 at 11:34 AM, Stephen Frost wrote: > The specifics actually depend on (on Linux, at least) the value of > /proc/sys/fs/protected_hardlink, which has existed in upstream since 3.6 > (not sure about the RHEL kernels, though I expect they've incorporated > it also at some point al

Re: [HACKERS] Trailing comma support in SELECT statements

2014-10-29 Thread Robert Haas
On Tue, Oct 28, 2014 at 7:59 PM, David Johnston wrote: > I'd be much more inclined to favor this if the user is provided a capability > to have warnings emitted whenever extraneous commas are present - either via > some form of strict mode or linting configuration. My experience with this kind of

Re: [HACKERS] Directory/File Access Permissions for COPY and Generic File Access Functions

2014-10-29 Thread Andres Freund
On 2014-10-29 11:52:43 -0400, Robert Haas wrote: > On Wed, Oct 29, 2014 at 11:34 AM, Stephen Frost wrote: > > The specifics actually depend on (on Linux, at least) the value of > > /proc/sys/fs/protected_hardlink, which has existed in upstream since 3.6 > > (not sure about the RHEL kernels, though

Re: [HACKERS] Directory/File Access Permissions for COPY and Generic File Access Functions

2014-10-29 Thread Robert Haas
On Wed, Oct 29, 2014 at 12:00 PM, Andres Freund wrote: > It's possible to do this securely by doing a fstat() and checking the > link count. Good point. >> And it >> still doesn't protect against the case where you hardlink to a file >> and then the permissions on that file are later changed. >

Re: [HACKERS] Directory/File Access Permissions for COPY and Generic File Access Functions

2014-10-29 Thread Andres Freund
On 2014-10-29 12:03:54 -0400, Robert Haas wrote: > >> And it > >> still doesn't protect against the case where you hardlink to a file > >> and then the permissions on that file are later changed. > > > > Imo that's simply not a problem that we need to solve - it's much more > > general and independ

Re: [HACKERS] Directory/File Access Permissions for COPY and Generic File Access Functions

2014-10-29 Thread Tom Lane
Stephen Frost writes: > * Robert Haas (robertmh...@gmail.com) wrote: >> I think the question is "just how innumerable are those attack >> routes"? So, we can prevent a symlink from being used via O_NOFOLLOW. >> But what about hard links? > You can't hard link to files you don't own. That restri

Re: [HACKERS] Directory/File Access Permissions for COPY and Generic File Access Functions

2014-10-29 Thread Stephen Frost
* Andres Freund (and...@2ndquadrant.com) wrote: > On 2014-10-29 12:03:54 -0400, Robert Haas wrote: > > I don't see how you can draw an arbitrary line there. We either > > guarantee that the logged-in user can't usurp the server's > > permissions, or we don't. Making it happen only sometimes in ca

Re: [HACKERS] Directory/File Access Permissions for COPY and Generic File Access Functions

2014-10-29 Thread Andres Freund
On 2014-10-29 12:09:00 -0400, Tom Lane wrote: > Stephen Frost writes: > > * Robert Haas (robertmh...@gmail.com) wrote: > >> I think the question is "just how innumerable are those attack > >> routes"? So, we can prevent a symlink from being used via O_NOFOLLOW. > >> But what about hard links? >

Re: [HACKERS] Directory/File Access Permissions for COPY and Generic File Access Functions

2014-10-29 Thread Tom Lane
Stephen Frost writes: > * Alvaro Herrera (alvhe...@2ndquadrant.com) wrote: >> Users cannot create a hard link to a file they can't already access. > The specifics actually depend on (on Linux, at least) the value of > /proc/sys/fs/protected_hardlink, which has existed in upstream since 3.6 > (not

Re: [HACKERS] Directory/File Access Permissions for COPY and Generic File Access Functions

2014-10-29 Thread Stephen Frost
* Tom Lane (t...@sss.pgh.pa.us) wrote: > Stephen Frost writes: > > * Alvaro Herrera (alvhe...@2ndquadrant.com) wrote: > >> Users cannot create a hard link to a file they can't already access. > > > The specifics actually depend on (on Linux, at least) the value of > > /proc/sys/fs/protected_hardl

Re: [HACKERS] Allow peer/ident to fall back to md5?

2014-10-29 Thread Josh Berkus
On 10/29/2014 02:52 AM, Craig Ringer wrote: > On 10/29/2014 05:46 PM, Andres Freund wrote: >> I like this one. But then I perhaps edited too many pam configuration >> files. > > It seems good to me too. I haven't looked at how viable it is in > implementation terms. > > I think we could only prop

Re: [HACKERS] Directory/File Access Permissions for COPY and Generic File Access Functions

2014-10-29 Thread Tom Lane
Stephen Frost writes: > * Tom Lane (t...@sss.pgh.pa.us) wrote: >> No such file in RHEL 6.6 :-(. > Ouch. Although- have you tested when happens there? Pretty much exactly the same thing I just saw on OSX, ie, nothing. [tgl@sss1 zzz]$ touch foo [tgl@sss1 zzz]$ ls -l total 0 -rw-rw-r--. 1 tgl tgl

Re: [HACKERS] group locking: incomplete patch, just for discussion

2014-10-29 Thread Simon Riggs
On 29 October 2014 15:43, Robert Haas wrote: > On Wed, Oct 29, 2014 at 10:21 AM, Simon Riggs wrote: >> There is a real danger that your "ta-dah" moment sometime in the >> future contains flaws which need to be addressed, but we now have >> piles of questionable infrastructure lieing around. If yo

Re: [HACKERS] Directory/File Access Permissions for COPY and Generic File Access Functions

2014-10-29 Thread Stephen Frost
* Tom Lane (t...@sss.pgh.pa.us) wrote: > This points up the fact that platform-specific security holes are likely > to be a huge part of the problem. I won't even speculate about our odds > of building something that's secure on Windows. Andres' suggestion to only provide it on platforms which su

Re: [HACKERS] Directory/File Access Permissions for COPY and Generic File Access Functions

2014-10-29 Thread Adam Brightwell
Robert, > To articular my own concerns perhaps a bit better, there are two major > things I don't like about the whole DIRALIAS proposal. Number one, > you're creating this SQL object whose name is not actually used for > anything other than manipulating the alias you created. The users are > s

Re: [HACKERS] Directory/File Access Permissions for COPY and Generic File Access Functions

2014-10-29 Thread Adam Brightwell
Alvaro, I think it would make more sense if the file-accessing command specified > the DIRALIAS (or DIRECTORY, whatever we end up calling this) and a > pathname relative to the base one. Something like > > postgres=# CREATE DIRECTORY logdir ALIAS FOR '/pgsql/data/pg_log'; Following this, what d

Re: [HACKERS] Allow peer/ident to fall back to md5?

2014-10-29 Thread Jim Nasby
On 10/29/14, 11:23 AM, Josh Berkus wrote: I don't see a problem with having a "continue" directive, and documenting that it only works with peer and ident. Maybe someday (protocol bump) we can have a way to make other methods continue, and then nobody will need to change their files to support t

Re: [HACKERS] Deferring some AtStart* allocations?

2014-10-29 Thread Robert Haas
On Tue, Oct 28, 2014 at 10:16 AM, Andres Freund wrote: > On 2014-10-24 11:25:23 -0400, Robert Haas wrote: >> On Fri, Oct 24, 2014 at 10:10 AM, Andres Freund >> wrote: >> > What I was thinking was that you'd append the messages to the layer one >> > level deeper than the parent. Then we'd missed

Re: [HACKERS] Directory/File Access Permissions for COPY and Generic File Access Functions

2014-10-29 Thread Stephen Frost
Adam, * Adam Brightwell (adam.brightw...@crunchydatasolutions.com) wrote: > Pardon my ignorance, but can you help me understand the advantage of not > having absolute path names in the COPY command? If you're writing ETL processes and/or PL/PgSQL code which embeds the COPY command and you migrate

Re: [HACKERS] Failback to old master

2014-10-29 Thread Maeldron T.
Thank you, Robert. I thought that removing the recovery.conf file makes the slave master only after the slave was restarted. (Unlike creating the a trigger_file). Isn't this true? I also thought that if there was a crash on the original master and it applied WAL entries on itself that are not pre

Re: [HACKERS] Directory/File Access Permissions for COPY and Generic File Access Functions

2014-10-29 Thread Robert Haas
On Wed, Oct 29, 2014 at 12:36 PM, Adam Brightwell wrote: > Robert, > >> To articular my own concerns perhaps a bit better, there are two major >> things I don't like about the whole DIRALIAS proposal. Number one, >> you're creating this SQL object whose name is not actually used for >> anything o

Re: [HACKERS] Failback to old master

2014-10-29 Thread Robert Haas
On Wed, Oct 29, 2014 at 12:43 PM, Maeldron T. wrote: > Thank you, Robert. > > I thought that removing the recovery.conf file makes the slave master only > after the slave was restarted. (Unlike creating the a trigger_file). Isn't > this true? Yes, but after the restart, the slave will also rewind

Re: [HACKERS] foreign data wrapper option manipulation during Create foreign table time?

2014-10-29 Thread Robert Haas
On Tue, Oct 28, 2014 at 5:26 PM, Demai Ni wrote: > I am looking for a couple pointers here about fdw, and how to change the > option values during CREATE table time. > > I am using postgres-xc-1.2.1 right now. For example, it contains file_fdw, > whose create-table-stmt looks like: > CREATE FOREIG

Re: [HACKERS] Directory/File Access Permissions for COPY and Generic File Access Functions

2014-10-29 Thread Jeremy Harris
On 29/10/14 16:11, Andres Freund wrote: > I do think checking the link count to > be 1 is safe though. You will fail against certain styles of online-backup. -- Cheers, Jeremy -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http:/

Re: [HACKERS] Proposal: Log inability to lock pages during vacuum

2014-10-29 Thread Jim Nasby
On 10/21/14, 6:05 PM, Tom Lane wrote: Jim Nasby writes: - What happens if we run out of space to remember skipped blocks? You forget some, and are no worse off than today. (This might be an event worthy of logging, if the array is large enough that we don't expect it to happen often ...) M

Re: [HACKERS] lag_until_you_get_something() OVER () window function

2014-10-29 Thread Kirk Roybal
This is cleaner and better. Thanks for the link, I hope to see it in a commitfest some time soon. /Kirk On 2014-10-28 16:34, Vladimir Sitnikov wrote: > There is already a patch for that (ignore/respect nulls in lead/lag): > https://commitfest.postgresql.org/action/patch_view?id=1096 [1]

Re: [HACKERS] foreign data wrapper option manipulation during Create foreign table time?

2014-10-29 Thread Ronan Dunklau
Le mercredi 29 octobre 2014 12:49:12 Robert Haas a écrit : > On Tue, Oct 28, 2014 at 5:26 PM, Demai Ni wrote: > > I am looking for a couple pointers here about fdw, and how to change the > > option values during CREATE table time. > > > > I am using postgres-xc-1.2.1 right now. For example, it co

Re: [HACKERS] lag_until_you_get_something() OVER () window function

2014-10-29 Thread Kirk Roybal
This is a pretty elegant way of getting there. It also does a better job of respecting the window frame. I'll use this until this https://commitfest.postgresql.org/action/patch_view?id=1096 [1] shows up. Thanks On 2014-10-28 17:35, Merlin Moncure wrote: > On Tue, Oct 28, 2014 at 12:40

Re: [HACKERS] Directory/File Access Permissions for COPY and Generic File Access Functions

2014-10-29 Thread Andres Freund
On 2014-10-29 16:38:44 +, Jeremy Harris wrote: > On 29/10/14 16:11, Andres Freund wrote: > > I do think checking the link count to > > be 1 is safe though. > > You will fail against certain styles of online-backup. Meh. I don't think that's really a problem for the usecases for COPY FROM. G

Re: [HACKERS] Materialized views don't show up in information_schema

2014-10-29 Thread Robert Haas
On Mon, Oct 27, 2014 at 11:45 AM, Stephen Frost wrote: >> But I think it's the wrong thing anyway, because it presumes that, >> when Kevin chose to make materialized views a different relkind and a >> different object type, rather than just a property of an object, he >> made the wrong call, and I

Re: [HACKERS] Autovacuum fails to keep visibility map up-to-date in mostly-insert-only-tables

2014-10-29 Thread Robert Haas
On Mon, Oct 27, 2014 at 5:51 PM, Alvaro Herrera wrote: > Jeff Janes wrote: >> It is only a page read if you have to read the page. It would seem optimal >> to have bgwriter adventitiously set hint bits and vm bits, because that is >> the last point at which the page can be changed without risking

Re: [HACKERS] Directory/File Access Permissions for COPY and Generic File Access Functions

2014-10-29 Thread Stephen Frost
* Jeremy Harris (j...@wizmail.org) wrote: > On 29/10/14 16:11, Andres Freund wrote: > > I do think checking the link count to > > be 1 is safe though. > > You will fail against certain styles of online-backup. Fail-safe though, no? For my part, I'm not particularly bothered by that; we'd have t

Re: [HACKERS] Directory/File Access Permissions for COPY and Generic File Access Functions

2014-10-29 Thread Tom Lane
Stephen Frost writes: > * Tom Lane (t...@sss.pgh.pa.us) wrote: >> This points up the fact that platform-specific security holes are likely >> to be a huge part of the problem. I won't even speculate about our odds >> of building something that's secure on Windows. > Andres' suggestion to only pr

Re: [HACKERS] Materialized views don't show up in information_schema

2014-10-29 Thread Stephen Frost
* Robert Haas (robertmh...@gmail.com) wrote: > On Mon, Oct 27, 2014 at 11:45 AM, Stephen Frost wrote: > > I don't think Kevin was wrong to use a different relkind, but I don't > > buy into the argument that a different relkind means it's not a view. > > As for the other comments, I agree that a ma

Re: [HACKERS] proposal: CREATE DATABASE vs. (partial) CHECKPOINT

2014-10-29 Thread Robert Haas
On Mon, Oct 27, 2014 at 8:01 PM, Tomas Vondra wrote: > (3) write-heavy workloads / large template database > > Current approach wins, for two reasons: (a) for large databases the > WAL-logging overhead may generate much more I/O than a checkpoint, > and (b) it may generate so many WAL

Re: [HACKERS] Materialized views don't show up in information_schema

2014-10-29 Thread Robert Haas
On Wed, Oct 29, 2014 at 1:26 PM, Stephen Frost wrote: > I agree with this, certainly, but these are not considerations that the > SQL spec takes into account. I've always found it odd of the spec to > avoid these considerations and concerns, but it is the spec and it's > viewpoint that we're disc

Re: [HACKERS] Add CREATE support to event triggers

2014-10-29 Thread Robert Haas
On Tue, Oct 28, 2014 at 6:00 AM, Andres Freund wrote: >> Uhm. Obviously we didn't have jsonb when I started this and we do have >> them now, so I could perhaps see about updating the patch to do things >> this way; but I'm not totally sold on that idea, as my ObjTree stuff is >> a lot easier to m

Re: [HACKERS] Materialized views don't show up in information_schema

2014-10-29 Thread Stephen Frost
* Robert Haas (robertmh...@gmail.com) wrote: > On Wed, Oct 29, 2014 at 1:26 PM, Stephen Frost wrote: > > I agree with this, certainly, but these are not considerations that the > > SQL spec takes into account. I've always found it odd of the spec to > > avoid these considerations and concerns, bu

Re: [HACKERS] Directory/File Access Permissions for COPY and Generic File Access Functions

2014-10-29 Thread Tom Lane
Andres Freund writes: > On 2014-10-29 16:38:44 +, Jeremy Harris wrote: >> On 29/10/14 16:11, Andres Freund wrote: >>> I do think checking the link count to >>> be 1 is safe though. >> You will fail against certain styles of online-backup. > Meh. I don't think that's really a problem for the

Re: [HACKERS] Directory/File Access Permissions for COPY and Generic File Access Functions

2014-10-29 Thread Kevin Grittner
Tom Lane wrote: > So at this point we've decided that we must forbid access to symlinked or > hardlinked files, which is a significant usability penalty; we've also > chosen to blow off most older platforms entirely; and we've only spent > about five minutes actually looking for security issues,

Re: [HACKERS] Lockless StrategyGetBuffer() clock sweep

2014-10-29 Thread Robert Haas
On Mon, Oct 27, 2014 at 9:32 AM, Andres Freund wrote: > I've previously posted a patch at > http://archives.postgresql.org/message-id/20141010160020.GG6670%40alap3.anarazel.de > that reduces contention in StrategyGetBuffer() by making the clock sweep > lockless. Robert asked me to post it to a ne

Re: [HACKERS] Materialized views don't show up in information_schema

2014-10-29 Thread Robert Haas
On Wed, Oct 29, 2014 at 1:57 PM, Stephen Frost wrote: >> No. Materialized views don't have column defaults, and marking them >> security_barrier does nothing. > > I'm a bit confused by this- views have column defaults? Yep. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterpris

Re: [HACKERS] Directory/File Access Permissions for COPY and Generic File Access Functions

2014-10-29 Thread Tom Lane
I wrote: > ... and we've only spent > about five minutes actually looking for security issues, with no good > reason to assume there are no more. Oh, here's another one: what I read in RHEL6's open(2) man page is O_NOFOLLOW If pathname is a symbolic link, then the open fails.

Re: [HACKERS] WIP: Access method extendability

2014-10-29 Thread Simon Riggs
On 29 October 2014 09:27, Simon Riggs wrote: > The current system does not allow for the possibility of a corruption > bug. If one occurs, the only thing an AM can do is PANIC. It has no > mechanism to isolate the problem and deal with it, which affects the > whole server. > > So the issue is one

Re: [HACKERS] Directory/File Access Permissions for COPY and Generic File Access Functions

2014-10-29 Thread Stephen Frost
Kevin, * Kevin Grittner (kgri...@ymail.com) wrote: > Tom Lane wrote: > > So at this point we've decided that we must forbid access to symlinked or > > hardlinked files, which is a significant usability penalty; we've also > > chosen to blow off most older platforms entirely; and we've only spent

Re: [HACKERS] proposal: CREATE DATABASE vs. (partial) CHECKPOINT

2014-10-29 Thread Tomas Vondra
On 29.10.2014 18:31, Robert Haas wrote: > On Mon, Oct 27, 2014 at 8:01 PM, Tomas Vondra wrote: >> (3) write-heavy workloads / large template database >> >> Current approach wins, for two reasons: (a) for large databases the >> WAL-logging overhead may generate much more I/O than a checkpoi

Re: [HACKERS] pg_background (and more parallelism infrastructure patches)

2014-10-29 Thread Robert Haas
On Sat, Oct 25, 2014 at 7:01 AM, Alvaro Herrera wrote: > I do think that dsm_keep_mapping is a strange name for what it does. OK, so let me see if I can summarize the votes so far on this (highly important?) naming issue: - Andres doesn't like "unkeep". He suggests dsm_manage_mapping(), dsm_en

Re: [HACKERS] pg_background (and more parallelism infrastructure patches)

2014-10-29 Thread Andres Freund
On 2014-10-29 15:00:36 -0400, Robert Haas wrote: > 1. Does anyone strongly object to that course of action? I don't. > 2. Does anyone wish to argue for or against back-patching the name > changes to 9.4? +1. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadran

Re: [HACKERS] lag_until_you_get_something() OVER () window function

2014-10-29 Thread Merlin Moncure
On Wed, Oct 29, 2014 at 12:04 PM, Kirk Roybal wrote: > This [custom aggregate gapfill] is a pretty elegant way of getting there. > > It also does a better job of respecting the window frame. > > I'll use this until this > https://commitfest.postgresql.org/action/patch_view?id=1096 shows up. Yes.

Re: [HACKERS] Lockless StrategyGetBuffer() clock sweep

2014-10-29 Thread Andres Freund
On 2014-10-29 14:18:33 -0400, Robert Haas wrote: > On Mon, Oct 27, 2014 at 9:32 AM, Andres Freund wrote: > > I've previously posted a patch at > > http://archives.postgresql.org/message-id/20141010160020.GG6670%40alap3.anarazel.de > > that reduces contention in StrategyGetBuffer() by making the cl

Re: [HACKERS] pg_receivexlog --status-interval add fsync feedback

2014-10-29 Thread Robert Haas
On Wed, Oct 22, 2014 at 9:26 AM, Heikki Linnakangas wrote: > We seem to be going in circles. You suggested having two options, > --feedback, and --fsync, which is almost exactly what Furuya posted > originally. I objected to that, because I think that user interface is too > complicated. Instead,

Re: [HACKERS] Add shutdown_at_recovery_target option to recovery.conf

2014-10-29 Thread Asif Naeem
Hi Petr, I have spent sometime to review the patch, overall patch looks good, it applies fine and make check run without issue. If recovery target is specified and shutdown_at_recovery_target is set to true, it shutdown the server at specified recovery point. I do have few points to share i.e. 1.

Re: [HACKERS] pg_background (and more parallelism infrastructure patches)

2014-10-29 Thread Andres Freund
On 2014-10-22 19:03:28 -0400, Robert Haas wrote: > On Wed, Oct 8, 2014 at 6:32 PM, Andres Freund wrote: > > I got to ask: Why is it helpful that we have this in contrib? I have a > > good share of blame to bear for that, but I think we need to stop > > dilluting contrib evermore with test programs

Re: [HACKERS] Directory/File Access Permissions for COPY and Generic File Access Functions

2014-10-29 Thread Stephen Frost
* Tom Lane (t...@sss.pgh.pa.us) wrote: > So heaven help you if you grant user joe access in directory > /home/joe/copydata, or any other directory whose parent is writable by > him. He can just remove the directory and replace it with a symlink to > whatever directory contains files he'd like the

Re: [HACKERS] pg_background (and more parallelism infrastructure patches)

2014-10-29 Thread Petr Jelinek
On 29/10/14 20:00, Robert Haas wrote: After reviewing all of those possibilities with the sort of laser-like focus the situation demands, I'm inclined to endorse Alvaro's proposal to rename the existing dsm_keep_mapping() function to dsm_pin_mapping() and the existing dsm_keep_segment() function

Re: [HACKERS] Directory/File Access Permissions for COPY and Generic File Access Functions

2014-10-29 Thread Tom Lane
Stephen Frost writes: > * Kevin Grittner (kgri...@ymail.com) wrote: >> What's interesting and disappointing here is that not one of these >> suggested vulnerabilities seems like a possibility on a database >> server managed in what I would consider a sane and secure manner[1]. > For my part- I ag

  1   2   >