Re: [HACKERS] list of credits for release notes

[Dave Page]

> On 27 Sep 2017, at 19:47, Peter Eisentraut  
> wrote:
> 
> At the PGCon Developer Meeting it was agreed[0] to add a list of credits
> to the release notes, including everyone who was mentioned in a commit
> message.  I have now completed that list.

Wow, it’s bigger than I expected. Thanks for compiling it.

> 
> Attached is the proposed documentation commit as well as the raw list.

At the very least my name is missing (I contributed the monitoring roles patch 
and pg_ls_log/waldir. I have no idea if others are.


> 
> Thoughts on the heading?  I have considered "Credits",
> "Acknowledgements", "Thanks", but the first seemed better than the other
> ones

I prefer Acknowledgments.

> 
> This was a manual process, so mistakes could have been made.  I have
> gently edited variant spellings and obvious typos.
> 
> 
> For the following mentions I could not identify a name:
> 
> mthrockmor...@hme.com
> Tels
> Zertrin 
> zam...@gmail.com
> bug #14654 reported by James C.
> Jov in bug #14749
> yxq 
> 
> I respect that some people don't want their name on record, but then
> they don't go into the release credits either, I think.
> 
> The considered commits have been
> 
> git log REL9_6_STABLE..REL_10_STABLE
> 
> currently up to 9ebc7781444fd15d56ed16e5312a954483e85cd9.
> 
> I have also cross-checked the list against all PG10 commit fests, the
> committers list, and the contributors list on the web site.  (That
> doesn't mean I added all those, but checked for obvious omissions
> against those.)
> 
> The list is sorted using COLLATE "en-x-icu".
> 
> 
> Any thoughts?
> 
> 
> [0]:
> https://wiki.postgresql.org/wiki/PgCon_2017_Developer_Meeting#Release_notes_scope.2C_and_giving_credit
> 
> -- 
> Peter Eisentraut  http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
> <0001-Add-list-of-credits-to-release-notes.patch>
> 
> 
> -- 
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers


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

Re: [HACKERS] pl/perl extension fails on Windows

[Dave Page]

On Thu, Aug 17, 2017 at 2:58 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:

> Dave Page <dp...@postgresql.org> writes:
> > It's ActiveState Perl 5.8.8. Printing $Config{ccflags} doesn't seem to do
> > anything, but perl -V output is below:
>
> That's weird ... you get nothing from
>
> perl -MConfig -e 'print $Config{ccflags}'
>

Didn't realise I needed the -MConfig bit (told you my perl-fu was weak :-)
):

C:\Perl\bin>perl -MConfig -e "print $Config{ccflags}"
-nologo -GF -W3 -MD -Zi -DNDEBUG -O1 -DWIN32 -D_CONSOLE -DNO_STRICT
-DHAVE_DES_FCRYPT -DNO_HASH_SEED -DUSE_SITECUSTOMIZE
-DPERL_IMPLICIT_CONTEXT -DPERL_IMPLICIT_SYS -DUSE_PERLIO
-DPERL_MSVCRT_READFIX

-- 
Dave Page
PostgreSQL Core Team
http://www.postgresql.org/

Re: [HACKERS] pl/perl extension fails on Windows

[Dave Page]

trings
27719 Document the functions htmlify() and anchorify() in Pod::Html
27619 Bug in Term::ReadKey being triggered by a bug in
Term::ReadLine
27549 Move DynaLoader.o into libperl.so
27528 win32_pclose() error exit doesn't unlock mutex
27527 win32_async_check() can loop indefinitely
27515 ignore directories when searching @INC
27359 Fix -d:Foo=bar syntax
27210 Fix quote typo in c2ph
27203 Allow compiling swigged C++ code
27200 Make stat() on Windows handle trailing slashes correctly
27133 Initialise lastparen in the regexp structure
27061 L and Pod::Html
27034 Avoid "Prototype mismatch" warnings with autouse
26970 Make Passive mode the default for Net::FTP
26921 Avoid getprotobyname/number calls in IO::Socket::INET
26897,26903 Make common IPPROTO_* constants always available
26670 Make '-s' on the shebang line parse -foo=bar switches
26637 Make Borland and MinGW happy with change 26379
26536 INSTALLSCRIPT versus INSTALLDIRS
26379 Fix alarm() for Windows 2003
26087 Storable 0.1 compatibility
25861 IO::File performace issue
25084 long groups entry could cause memory exhaustion
24699 ICMP_UNREACHABLE handling in Net::Ping
  Built under MSWin32
  Compiled at Jan 23 2007 15:57:46
  @INC:
c:/perl/site/lib
c:/perl/lib
.

C:\Program Files\Microsoft Visual Studio 8\VC>

-- 
Dave Page
PostgreSQL Core Team
http://www.postgresql.org/

Re: [HACKERS] pl/perl extension fails on Windows

[Dave Page]

On Wed, Jul 12, 2017 at 4:35 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:

> Sandeep Thakkar <sandeep.thak...@enterprisedb.com> writes:
> > I compiled PG 10 beta1/beta2 with "--with-perl" option on Windows and the
> > extension crashes the database.
> > *src/pl/plperl/Util.c: loadable library and perl binaries are mismatched
> > (got handshake key 0A900080, needed 0AC80080)*
>
> > This is seen with Perl 5.24 but not with 5.20, 5.16. What I found is that
> > the handshake function is added in Perl 5.21.x and probably that is why
> we
> > don't see this issue in earlier versions.
>
> Well, we have various buildfarm machines running perls newer than that,
> eg, crake, with 5.24.1.  So I'd say there is something busted about your
> perl installation.  Perhaps leftover bits of an older version somewhere?
>

Well crake is a Fedora box - and we have no problems on Linux, only on
Windows.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: [HACKERS] Patch - Tcl 8.6 version support for PostgreSQL

[Dave Page]

On Thu, May 4, 2017 at 3:54 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:

> Alvaro Herrera <alvhe...@2ndquadrant.com> writes:
> > Something like the (untested) attached perhaps?
>
> Looks plausible, I'm not in a position to test though.


Sandeep/Paresh - can you test please?

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: [HACKERS] Patch - Tcl 8.6 version support for PostgreSQL

[Dave Page]

On Mon, Apr 24, 2017 at 9:26 PM, Andres Freund <and...@anarazel.de> wrote:

> On 2017-04-24 16:18:30 -0400, Robert Haas wrote:
> > On Sat, Apr 22, 2017 at 1:58 PM, Sandeep Thakkar <
> > sandeep.thak...@enterprisedb.com> wrote:
> >
> > > Tcl8.6 is already supported in PostgreSQL.
> > >
> >
> > What commit added support for it?
>
> I don't think the main build mechanism requires explicit support of new
> versions. configure just checks for some prerequisites.
>

Right - and they were adjusted here: https://git.postgresql.
org/gitweb/?p=postgresql.git;a=commit;h=eaba54c20c5ab2cb6aaffa57fd
4990dfe2c7


-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: [HACKERS] Monitoring roles patch

[Dave Page]

On Thu, Mar 30, 2017 at 2:24 PM, Simon Riggs <si...@2ndquadrant.com> wrote:
> On 30 March 2017 at 18:29, Simon Riggs <si...@2ndquadrant.com> wrote:
>
>> Moving to commit this over the next hour. Last chance...
>
> Done. Great work Dave, thanks everybody.

Thanks Simon.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] Monitoring roles patch

[Dave Page]

On Wed, Mar 29, 2017 at 2:51 PM, Stephen Frost <sfr...@snowman.net> wrote:
>
> Dave's currently hacking on a new patch based on our discussion, so I'd
> suggest waiting another hour or so anyway until he's done.
>
> Might be a bit longer as he's trying to do it in a hallway at
> PGConf.US...

Thanks Stephen.

Here's an updated patch, and description of the changes. Simon,
Stephen and Robert have looked at the description and are all happy
with it \o/. Thank you to them for taking the time out of the
conference to go through it with me.

Here's what it does:

1) Creates the following default roles:

  - pg_monitor - Top-level role that is GRANTed all of the following
roles by default. Also GRANTed access to some additional functions.
  - pg_read_all_settings - A role that can read all GUCs.
  - pg_read_all_stats - A role that can read un-redacted pg_stat_*
views via the functions supporting them, as well as
pg_database_size/pg_tablespace_size.
  - pg_stat_scan_tables - A role that can execute monitoring functions
that may lock tables.

2) pg_database_size and pg_tablespace_size have hard-coded permission
checks updated to allow execution by pg_read_all_stats.

3) GUC read permission checks for superuser have been replaced with
checks for membership in pg_read_all_settings.

4) pg_buffercache functions have GRANTed execute permissions to pg_monitor.

5) pg_freespacemap functions have GRANTed execute permissions to
pg_stat_scan_tables.

6) pg_stat_statements has its hard-coded permission check updated to
allow execution by pg_read_all_stats, and the same role is GRANTed
permission to execute pg_stat_statements_reset().

7) pg_visibility functions have GRANTed executed permissions to
pg_stat_scan_tables.

8) pgrowlocks has it's hard-coded permission check updated to allow
execution by pg_stat_scan_tables,

9) pgstattuple functions have GRANTed executed permissions to
pg_stat_scan_tables.

10) pg_stat_get_wal_receiver has its hard-coded permission check
updated to allow execution by pg_read_all_stats

11) pg_ls_logdir and pg_ls_waldir have execute permissions GRANTed to pg_monitor

12) Un-redacted use of the functions underpinning the pg_stat_* views
is available to pg_read_all_stats.


-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
diff --git a/contrib/pg_buffercache/Makefile b/contrib/pg_buffercache/Makefile
index 497dbeb229..18f7a87452 100644
--- a/contrib/pg_buffercache/Makefile
+++ b/contrib/pg_buffercache/Makefile
@@ -4,8 +4,9 @@ MODULE_big = pg_buffercache
 OBJS = pg_buffercache_pages.o $(WIN32RES)
 
 EXTENSION = pg_buffercache
-DATA = pg_buffercache--1.2.sql pg_buffercache--1.1--1.2.sql \
-   pg_buffercache--1.0--1.1.sql pg_buffercache--unpackaged--1.0.sql
+DATA = pg_buffercache--1.2.sql pg_buffercache--1.2--1.3.sql \
+   pg_buffercache--1.1--1.2.sql pg_buffercache--1.0--1.1.sql \
+   pg_buffercache--unpackaged--1.0.sql
 PGFILEDESC = "pg_buffercache - monitoring of shared buffer cache in real-time"
 
 ifdef USE_PGXS
diff --git a/contrib/pg_buffercache/pg_buffercache--1.2--1.3.sql 
b/contrib/pg_buffercache/pg_buffercache--1.2--1.3.sql
new file mode 100644
index 00..b37ef0112e
--- /dev/null
+++ b/contrib/pg_buffercache/pg_buffercache--1.2--1.3.sql
@@ -0,0 +1,7 @@
+/* contrib/pg_buffercache/pg_buffercache--1.2--1.3.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION pg_buffercache UPDATE TO '1.3'" to load this file. 
\quit
+
+GRANT EXECUTE ON FUNCTION pg_buffercache_pages() TO pg_monitor;
+GRANT SELECT ON pg_buffercache TO pg_monitor;
diff --git a/contrib/pg_buffercache/pg_buffercache.control 
b/contrib/pg_buffercache/pg_buffercache.control
index a4d664f3fa..8c060ae9ab 100644
--- a/contrib/pg_buffercache/pg_buffercache.control
+++ b/contrib/pg_buffercache/pg_buffercache.control
@@ -1,5 +1,5 @@
 # pg_buffercache extension
 comment = 'examine the shared buffer cache'
-default_version = '1.2'
+default_version = '1.3'
 module_pathname = '$libdir/pg_buffercache'
 relocatable = true
diff --git a/contrib/pg_freespacemap/Makefile b/contrib/pg_freespacemap/Makefile
index 7bc0e9555d..0a2f000ec6 100644
--- a/contrib/pg_freespacemap/Makefile
+++ b/contrib/pg_freespacemap/Makefile
@@ -4,8 +4,8 @@ MODULE_big = pg_freespacemap
 OBJS = pg_freespacemap.o $(WIN32RES)
 
 EXTENSION = pg_freespacemap
-DATA = pg_freespacemap--1.1.sql pg_freespacemap--1.0--1.1.sql \
-   pg_freespacemap--unpackaged--1.0.sql
+DATA = pg_freespacemap--1.1.sql pg_freespacemap--1.1--1.2.sql \
+   pg_freespacemap--1.0--1.1.sql pg_freespacemap--unpackaged--1.0.sql
 PGFILEDESC = "pg_freespacemap - monitoring of free space map"
 
 ifdef USE_PGXS
diff --git a/contrib/pg_freespacemap/pg_freespacemap--1.1--1.2.sql 
b/contrib/pg_freespacemap/pg_freespacemap--1.1--1.2.sql
new file mode 100644
index 00..

Re: [HACKERS] Schedule and Release Management Team for PG10

[Dave Page]

On Wed, Mar 29, 2017 at 3:10 PM, Andres Freund <and...@anarazel.de> wrote:
> On 2017-03-29 16:04:50 -0300, Alvaro Herrera wrote:
>> Tom Lane wrote:
>>
>> > My own thought is that there's room for at least a few days' slop in
>> > the end date of the final commitfest, depending on what patches remain
>> > open and what the prospects are for getting them done.  (In the past
>> > we've sometimes let the final fest stretch on indefinitely, which is
>> > clearly the Wrong Thing; but that doesn't mean that the Right Thing is
>> > to say that it ends at 2017-04-01 00:00 UTC no matter what.)  The RMT
>> > should look at things in another day or two and make a judgment call
>> > about that.
>>
>> I was rather surprised to see the March commitfest declared to exactly
>> one month and feature freeze immediately thereafter.  Last time around
>> we left 2 weeks between CF end and feature freeze; the previous one I
>> think we had the final CF last two months.  Not stretch on indefinitely,
>> but we know the final CF for a cycle takes more effort than previous
>> ones, so it seems reasonable to give more time.  We have a large number
>> of patches still waiting for review.
>
> +1

+1


-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] Monitoring roles patch

[Dave Page]

Hi

On Tue, Mar 28, 2017 at 2:22 PM, Stephen Frost <sfr...@snowman.net> wrote:
> Dave,
>
> * Dave Page (dp...@pgadmin.org) wrote:
>> OK, so before I start hacking again, here's a proposal based on my
>> understanding of folks comments, and so open questions. If I can get
>> agreement and answers, I'll be able to break out vi again without
>> (hopefully) too many more revisions:
>>
>> pg_read_all_stats: Will have C-coded access to pg_stats views and
>> pg_*_size that are currently hard-coded to superuser
>
> Not quite sure what you mean here by 'C-coded access to pg_stats
> *views*', but I'm guessing that isn't exactly what you meant since I'm
> sure we aren't going to change how view permissions are done in this
> patch.  I take it you mean access to the functions under the views?  If
> so, I believe this is correct.

Right, sorry I wasn't clear.

>> pg_read_all_settings: Will have C-coded access to read GUCs that are
>> currently hard-coded to the superuser
>
> Right.
>
>> pg_monitor: Will include pg_read_all_stats and pg_read_all_settings,
>> and all explicitly GRANTable rights, e.g. in contrib modules.
>
> Right.
>
>> Patch to be rebased on Simon's updated version.
>
> Right.
>
>> Questions:
>>
>> - pg_stat_statements has a hard-coded superuser check. Shall I remove
>> that, and include REVOKE ALL FROM ALL and then GRANT to pg_monitor?
>
> pg_stat_statements shouldn't have ever had that superuser check and it
> shouldn't have ever used '==' for the user check, it should have been
> using 'has_privs_of_role()' from the start, which means that the
> superuser check isn't necessary.
>
> I don't think we should remove that check, nor should we REVOKE EXECUTE
> from public for the function.  We *should* add a hard-coded role check
> to allow another role which isn't a member of the role whose query it is
> to view the query.  That shouldn't be pg_monitor, of course (as
> discussed).  I don't think pg_read_all_stats or pg_read_all_settings
> really covers this case either- this is more like pg_read_all_queries
> and should also be used for pg_stat_activity.

OK, so essentially what I did, except s/pg_read_all_stats/pg_read_all_queries ?

> That would then be granted to pg_monitor.
>
>> - pgrowlocks has hard-coded access to superuser and users with SELECT
>> on the table being examined. How should this be handled?
>
> I don't see any hard-coded superuser check?  There is a
> pg_class_aclcheck() for SELECT rights on the table.  I like the idea of
> having another way to grant access to run this function on a table
> besides giving SELECT rights on the entire table to the user.  This
> would fall under the mandate of the role described in your next bullet,
> in my view.
>
>> - Stephen suggested a separate role for functions that can lock
>> tables. Is this still desired, or shall we just grant access to
>> pg_monitor (I think the latter is fine)?
>
> Right, I was thinking something like pg_stat_all_tables or
> pg_stat_scan_tables or similar.  We would add that (perhaps also with a
> SELECT check like pgrowlocks has) for the other functions like
> pgstattuple and pg_freespacemap and pg_visibility.

So pgstattuple, pg_sfreespacemap, pg_visibility and pgrowlocks to be
allowed access from members of pg_stat_scan_tables, which in turn is
granted to pg_monitor?

Thanks!

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] Monitoring roles patch

[Dave Page]

On Tue, Mar 28, 2017 at 1:52 PM, Mark Dilger <hornschnor...@gmail.com> wrote:
>
>> On Mar 28, 2017, at 9:55 AM, Robert Haas <robertmh...@gmail.com> wrote:
>>
>> On Tue, Mar 28, 2017 at 12:47 PM, Dave Page <dp...@pgadmin.org> wrote:
>>>> I don't see any precedent in the code for having a hardcoded role, other 
>>>> than
>>>> superuser, and allowing privileges based on a hardcoded test for membership
>>>> in that role.  I'm struggling to think of all the security implications of 
>>>> that.
>>>
>>> This would be the first.
>>
>> Isn't pg_signal_backend an existing precedent?
>
> Sorry, I meant to say that there is no precedent for allowing access to data 
> based
> on a hardcoded test for membership in a role other than superuser.

This doesn't allow access to data, except through monitoring of
queries that are executed (e.g. full access to pg_stat_activity) -
which you can avoid by not using the role if that's your choice.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] Monitoring roles patch

[Dave Page]

OK, so before I start hacking again, here's a proposal based on my
understanding of folks comments, and so open questions. If I can get
agreement and answers, I'll be able to break out vi again without
(hopefully) too many more revisions:

pg_read_all_stats: Will have C-coded access to pg_stats views and
pg_*_size that are currently hard-coded to superuser

pg_read_all_settings: Will have C-coded access to read GUCs that are
currently hard-coded to the superuser

pg_monitor: Will include pg_read_all_stats and pg_read_all_settings,
and all explicitly GRANTable rights, e.g. in contrib modules.

Patch to be rebased on Simon's updated version.

Questions:

- pg_stat_statements has a hard-coded superuser check. Shall I remove
that, and include REVOKE ALL FROM ALL and then GRANT to pg_monitor?

- pgrowlocks has hard-coded access to superuser and users with SELECT
on the table being examined. How should this be handled?

- Stephen suggested a separate role for functions that can lock
tables. Is this still desired, or shall we just grant access to
pg_monitor (I think the latter is fine)?

- Based on Peter's concerns, is pg_read_all_stats the right name?
Maybe pg_read_monitoring_stats?

Thanks!

On Tue, Mar 28, 2017 at 1:37 PM, Stephen Frost <sfr...@snowman.net> wrote:
> Greetings,
>
> * Robert Haas (robertmh...@gmail.com) wrote:
>> On Tue, Mar 28, 2017 at 12:47 PM, Dave Page <dp...@pgadmin.org> wrote:
>> >> I don't see any precedent in the code for having a hardcoded role, other 
>> >> than
>> >> superuser, and allowing privileges based on a hardcoded test for 
>> >> membership
>> >> in that role.  I'm struggling to think of all the security implications 
>> >> of that.
>> >
>> > This would be the first.
>>
>> Isn't pg_signal_backend an existing precedent?
>
> Yes, it is.
>
> Thanks!
>
> Stephen



-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] Monitoring roles patch

[Dave Page]

On Tue, Mar 28, 2017 at 12:55 PM, Robert Haas <robertmh...@gmail.com> wrote:
> On Tue, Mar 28, 2017 at 12:47 PM, Dave Page <dp...@pgadmin.org> wrote:
>>> I don't see any precedent in the code for having a hardcoded role, other 
>>> than
>>> superuser, and allowing privileges based on a hardcoded test for membership
>>> in that role.  I'm struggling to think of all the security implications of 
>>> that.
>>
>> This would be the first.
>
> Isn't pg_signal_backend an existing precedent?

Good point. Clearly time for some caffeine.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] Monitoring roles patch

[Dave Page]

On Tue, Mar 28, 2017 at 12:04 PM, Mark Dilger <hornschnor...@gmail.com> wrote:
>
>> On Mar 28, 2017, at 8:34 AM, Dave Page <dp...@pgadmin.org> wrote:
>>
>> On Tue, Mar 28, 2017 at 11:31 AM, Peter Eisentraut
>> <peter.eisentr...@2ndquadrant.com> wrote:
>>> This patch touches the pg_buffercache and pg_freespacemap extensions,
>>> but there appear to be some files missing.
>>
>> Are you looking at an old version? There was one where I forgot to add
>> some files, but that was fixed within an hour or so in a new version.
>>
>> Right now I'm waiting for discussion to conclude before updating the
>> patch again.
>
> There does not seem to be a new patch since Robert made his "modest proposal",
> so I guess I just have to ask questions about how this would work.

There hasn't been one yet.

> I don't see any precedent in the code for having a hardcoded role, other than
> superuser, and allowing privileges based on a hardcoded test for membership
> in that role.  I'm struggling to think of all the security implications of 
> that.

This would be the first.

> If I have even one table in my database which is security sensitive, such that
> I cannot allow users to see the size of the table, nor whether the table has
> unvacuumed rows (owing to the fact that would give away that it has been
> changed since the last vacuum time), then I can't use pg_real_all_stats for
> anything, right?  And I would need to exercise some due diligence to make
> certain it does not get granted to anybody?

Right.

> What happens if I execute:
>
> REVOKE ALL ON TABLE mysecuretable FROM pg_read_all_stats?
>
> Does it work?

Yes, for the documented use of GRANT/REVOKE on a table.

> Does it silently fail?  Does it raise an exception?

No and no.

> Does
> pg_read_all_stats still have access to stats for mysecuretable?

Yes, because the ACL on the table controls reading/writing the data in
the table. It doesn't have any bearing on any kind of table metadata.
A user who has no privileges on a table can already look at (for
example) pg_stat_all_tables and see the sort of info you're talking
about. This patch would just allow members of a specific role get the
on-disk size as well, *if* you choose to use it.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] Monitoring roles patch

[Dave Page]

On Tue, Mar 28, 2017 at 11:39 AM, Peter Eisentraut
<peter.eisentr...@2ndquadrant.com> wrote:
> On 3/28/17 11:34, Dave Page wrote:
>> On Tue, Mar 28, 2017 at 11:31 AM, Peter Eisentraut
>> <peter.eisentr...@2ndquadrant.com> wrote:
>>> This patch touches the pg_buffercache and pg_freespacemap extensions,
>>> but there appear to be some files missing.
>>
>> Are you looking at an old version? There was one where I forgot to add
>> some files, but that was fixed within an hour or so in a new version.
>
> What is the name and date or your latest patch?

pg_monitor_v4.diff, 23rd March.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] Monitoring roles patch

[Dave Page]

On Tue, Mar 28, 2017 at 11:31 AM, Peter Eisentraut
<peter.eisentr...@2ndquadrant.com> wrote:
> This patch touches the pg_buffercache and pg_freespacemap extensions,
> but there appear to be some files missing.

Are you looking at an old version? There was one where I forgot to add
some files, but that was fixed within an hour or so in a new version.

Right now I'm waiting for discussion to conclude before updating the
patch again.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] Monitoring roles patch

[Dave Page]

On Mon, Mar 27, 2017 at 3:51 AM, Simon Riggs <si...@2ndquadrant.com> wrote:
> On 25 March 2017 at 16:30, Dave Page <dp...@pgadmin.org> wrote:
>
>> I believe this and other reasons we've described are exactly why other DBMS' 
>> do what we're proposing.
>
> It would help review if you could show some links and give a
> commentary on what you think others do, what they get right and what
> they get wrong, so we can be sure we are providing something people
> actually want and/or expect. POLA needed. I don't want to be reading
> various blogs about what those numpties on the Postgres project did in
> v10. Thanks

Most other DBMSs seem to provide either capabilities (or privileges,
whatever they may be called by the vendor) that can be assigned to
roles, or pre-defined roles with capabilities, or some combination of
the two.

SQL Server provides a number of server and database level roles that
are pre-configured for specific tasks, with set of capabilities. See
https://msdn.microsoft.com/en-us/library/ms189612.aspx for example.

DB2 appears to provide capabilities that can be assigned to roles. See
https://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.sec.doc/doc/c0050531.html

Oracle has something of a mix or roles and capabilities, eg. the DBA
role and SYSOPER privileges, e.g.
https://docs.oracle.com/cd/B28359_01/server.111/b28310/dba005.htm#ADMIN11040

What is being proposed here is a similar system, but focussing on
pre-defined roles. These make it easy to grant privileges for specific
purposes en-masse, without requiring the user to use them, i.e.
they're free to ignore them if they wish. As they are roles, they also
have the freedom to extend or restrict them in cases where privileges
are acquired through GRANT.

I believe this offers both the greatest flexibility and the most
straightforward and easy to use interface for the end user - the
ability to customise is maximised, whilst the default roles will be
both safe to use and should work out of the box for the majority of
monitoring scenarios.

The most important thing is that we'll be able to stop users having to
grant superuser privileges to their monitoring roles.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] Monitoring roles patch

[Dave Page]

Hi

> On 25 Mar 2017, at 15:55, Stephen Frost <sfr...@snowman.net> wrote:
> 
> Robert,
> 
> * Robert Haas (robertmh...@gmail.com) wrote:
>>> On Fri, Mar 24, 2017 at 12:46 PM, Dave Page <dp...@pgadmin.org> wrote:
>>>> On Fri, Mar 24, 2017 at 4:24 PM, Robert Haas <robertmh...@gmail.com> wrote:
>>>> That's possible, but it really depends on the tool, not on core
>>>> PostgreSQL.  The tool should be the one providing the script, because
>>>> the tool is what knows its own permissions requirements.  Users who
>>>> care about security won't want to grant every privilege given by a
>>>> pg_monitor role to a tool that only needs a subset of those
>>>> privileges.
>>> 
>>> The upshot of this would be that every time there's a database server
>>> upgrade that changes the permissions required somehow, the user has to
>>> login to every server they have and run a script. It is no longer a
>>> one-time thing, which makes it vastly more painful to deal with
>>> upgrades.
>> 
>> So, I might be all wet here, but I would have expected that changes on
>> the TOOL side would be vastly more frequent.  I mean, we do not change
>> what a certain builtin permission does very often.  If we add
>> pg_read_all_settings, what is the likelihood that the remit of that
>> role is ever going to change?  I would judge that was is vastly more
>> likely is that a new version of some tool would start needing that
>> privilege (or some other) where it didn't before.  If that happens,
>> and the script is on the tool side, then you just add a new line to
>> the script.  If the script is built into initdb, then you've got to
>> wait for the next major release before you can update the definition
>> of pg_monitor - and maybe argue with other tool authors with different
>> requirements.
> 
> The expectation here is that the pg_monitor role will include all of the
> reasonable privileges in a given release that a monitor tool should be
> using.

Exactly.

> 
> While it's likely that monitoring tools will not all be able to work
> with every function they would then have access to on day 1, I'm sure
> the goal for all of them will be to reach the point where they are using
> all of the functions and tables they then have access to.

Right - and we have discussed here, in at least one developer meeting, and with 
the tools team at EDB what should be included, so I'm confident we have a solid 
starting point that would be generally useful.

> 
> Moving forward, the privileges being added in future versions of PG (the
> ones you point out as happening at initdb-time) would be only those
> which are associated with new features in those later versions of PG.
> Those new features aren't going to be back-patched and therefore it
> wouldn't be possible for the tool to provide a script to GRANT access to
> those new features which would work on older versions of PG, meaning
> that the script you suggest would necessairly be PG-version specific.

Indeed.

Another issue with having the admin run a script is that any new databases 
created from template0 or the default template1, may all need the script to be 
run at that point. Admin tools that can automatically and immediately start 
monitoring new databases may need manual admin/dbowner intervention which could 
be extremely painful in large environments.

I believe this and other reasons we've described are exactly why other DBMS' do 
what we're proposing.

> 
> In other words, I don't believe this is a valid concern.
> 
>> So I'm fine with this:
>> 
>> -if (tblspcOid != MyDatabaseTableSpace)
>> +if (tblspcOid != MyDatabaseTableSpace &&
>> +!is_member_of_role(GetUserId(), DEFAULT_ROLE_READ_ALL_STATS))
>> 
>> But I don't like this:
>> 
>> +GRANT EXECUTE ON FUNCTION pgstattuple(text) TO pg_read_all_stats;
> 
> I believe we understood that to be the case.  I don't object to the
> clarification, of course.
> 
>> My position is that execute permission on a function is already
>> grantable, so granting it by default to a built-in role is just
>> removing flexibility which would otherwise be available to the user.
> 
> To remove flexibility would require that we remove the ability to
> independently GRANT that right.  We are not doing that.  Nor are we
> taking anything away from the user by added a new default role- we
> already claimed the pg_ namespace for roles in 9.6.

Right - and if a user doesn't like it, they can easily just not use the default 
role and create their own alternative instead.

>From a usability perspective, I cannot see any downsides to the default roles 
>as proposed. They take nothing away from the users that don't want/need them, 
>and add significant convenience for those that do.

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

Re: [HACKERS] Monitoring roles patch

[Dave Page]

On Fri, Mar 24, 2017 at 4:24 PM, Robert Haas <robertmh...@gmail.com> wrote:
>
>> If we make the users run all the statements individually then they'll
>> also have to get an updated script for the next version of PG too
>> because we will have added things that the tools will want access to.
>
> That's possible, but it really depends on the tool, not on core
> PostgreSQL.  The tool should be the one providing the script, because
> the tool is what knows its own permissions requirements.  Users who
> care about security won't want to grant every privilege given by a
> pg_monitor role to a tool that only needs a subset of those
> privileges.

The upshot of this would be that every time there's a database server
upgrade that changes the permissions required somehow, the user has to
login to every server they have and run a script. It is no longer a
one-time thing, which makes it vastly more painful to deal with
upgrades.

>> With the approach that Dave and I are advocating, we can avoid all of
>> that.  Contrib modules can bake-in GRANTs to the appropriate roles,
>> upgrades can be handled smoothly even when we add new capabilities which
>> are appropriate, users have a simple and straight-forward way to set up
>> good monitoring, and tool authors will know what permissions are
>> available and can finally have a better answer than "well, just make the
>> monior user superuser if you want to avoid all these complexities."
>
> They can have that anyway.  They just have to run a script provided by
> the tool rather than one provided by the core project as a
> one-size-fits-all solution for every tool.

Do you object to having individual default roles specifically for
cases where there are superuser-only checks at the moment that prevent
GRANT? e.g. pg_show_all_settings for SHOW, pg_show_all_stats for
pg_tablespace_size and friends, pg_stat_statements for, well,
pg_stat_statements and so on? It would be an inferior solution in my
opinion, given that it would demonstrably cause users more work, but
at least we could do something.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

[HACKERS] Re: [COMMITTERS] pgsql: Fix and simplify check for whether we're running as Windows serv

[Dave Page]

On Friday, March 24, 2017, Heikki Linnakangas <hlinn...@iki.fi> wrote:

> On 03/22/2017 07:44 PM, Robert Haas wrote:
>
>> On Wed, Mar 22, 2017 at 10:13 AM, Alvaro Herrera
>> <alvhe...@2ndquadrant.com> wrote:
>>
>>> Heikki Linnakangas wrote:
>>>
>>>> I did some archeology, and found CheckTokenMembership() in MinGW's
>>>> w32api
>>>> packages version 3.14
>>>> (https://sourceforge.net/projects/mingw/files/MinGW/Base/
>>>> w32api/w32api-3.14/,
>>>> in include/winbase.h). According to the timestamps on that download
>>>> page,
>>>> that was released in 2009. That was the oldest version I could find, so
>>>> it
>>>> might go even further back.
>>>>
>>>> Dave, do you know exactly what version of MinGW narwhal is running? And
>>>> how
>>>> difficult is it to upgrade to something slightly more modern? Ease of
>>>> upgrade is another good data point on how far we need to support old
>>>> versions.
>>>>
>>>
>>> Given that this was backpatched and that it broke narwhal in all
>>> branches, I think the solution needs to make narwhal work again without
>>> requiring it to upgrade; so we should acquire CheckTokenMembership via
>>> dynloading just like we do the other functions.  If we want to require a
>>> newer mingw version in pg10, that's acceptable, but it should be a
>>> separate patch.
>>>
>>
>> +1 for not moving the minimum system requirements in the back-branches.
>>
>
> Ok. I reverted this patch in the back-branches, and applied the much less
> invasive "V2" patch [1] instead. HEAD is unchanged, so narwhal still fails
> there.
>
> Dave: the consensus is that we no longer support the old version of MinGW
> that narwhal is using, for PostgreSQL v 10. Can you modify the
> configuration of narwhal to not try building 'master' anymore, or upgrade
> the toolchain, please?
>

I've disabled it. Thanks.


-- 
Dave Page
PostgreSQL Core Team
http://www.postgresql.org/

Re: [HACKERS] Monitoring roles patch

[Dave Page]

7a..d1da580c9c 100644
>> --- a/src/backend/utils/misc/guc.c
>> +++ b/src/backend/utils/misc/guc.c
>> @@ -34,6 +34,7 @@
>>  #include "access/xact.h"
>>  #include "access/xlog_internal.h"
>>  #include "catalog/namespace.h"
>> +#include "catalog/pg_authid.h"
>>  #include "commands/async.h"
>>  #include "commands/prepare.h"
>>  #include "commands/user.h"
>> @@ -6677,10 +6678,12 @@ GetConfigOption(const char *name, bool missing_ok, 
>> bool restrict_superuser)
>>   }
>>   if (restrict_superuser &&
>>   (record->flags & GUC_SUPERUSER_ONLY) &&
>> - !superuser())
>> + !superuser() &&
>> + !is_member_of_role(GetUserId(), 
>> DEFAULT_ROLE_READ_ALL_SETTINGS))
>
> Seems like having a variable to indicate if the caller should be able to
> read all these settings or not might be nice to have, so we have one
> place that figures out the privileges question.

Those checks are not all identical, and they're in different functions
so a variable wouldn't work (I assume you're not suggesting I add a
global :-p ). I could push part of each check out into a separate
function, but it doesn't seem like it would really add to the
readability to me.

> A few comments where that variable is set wouldn't be bad either.
>
> Also, I'm thinking the pg_monitor documentation really needs to be
> expanded and made very clear.  I'll think a bit more on just how to try
> and phrase that, but the description included certainly seemed
> inadequate.

I've added some additional text below the table.

> That's it for a quick review.  If we can get these changes done and
> there aren't any more questions or concerns, I'm happy to continue
> working to move this forward.  I definitely see the usefulness of these
> changes and it'd be great to have an answer to the oft-asked question of
> how to do proper monitoring with a non-superuser role.

Thanks - updated patch attached.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
diff --git a/contrib/pg_buffercache/Makefile b/contrib/pg_buffercache/Makefile
index 497dbeb229..18f7a87452 100644
--- a/contrib/pg_buffercache/Makefile
+++ b/contrib/pg_buffercache/Makefile
@@ -4,8 +4,9 @@ MODULE_big = pg_buffercache
 OBJS = pg_buffercache_pages.o $(WIN32RES)
 
 EXTENSION = pg_buffercache
-DATA = pg_buffercache--1.2.sql pg_buffercache--1.1--1.2.sql \
-   pg_buffercache--1.0--1.1.sql pg_buffercache--unpackaged--1.0.sql
+DATA = pg_buffercache--1.2.sql pg_buffercache--1.2--1.3.sql \
+   pg_buffercache--1.1--1.2.sql pg_buffercache--1.0--1.1.sql \
+   pg_buffercache--unpackaged--1.0.sql
 PGFILEDESC = "pg_buffercache - monitoring of shared buffer cache in real-time"
 
 ifdef USE_PGXS
diff --git a/contrib/pg_buffercache/pg_buffercache.control 
b/contrib/pg_buffercache/pg_buffercache.control
index a4d664f3fa..8c060ae9ab 100644
--- a/contrib/pg_buffercache/pg_buffercache.control
+++ b/contrib/pg_buffercache/pg_buffercache.control
@@ -1,5 +1,5 @@
 # pg_buffercache extension
 comment = 'examine the shared buffer cache'
-default_version = '1.2'
+default_version = '1.3'
 module_pathname = '$libdir/pg_buffercache'
 relocatable = true
diff --git a/contrib/pg_freespacemap/Makefile b/contrib/pg_freespacemap/Makefile
index 7bc0e9555d..0a2f000ec6 100644
--- a/contrib/pg_freespacemap/Makefile
+++ b/contrib/pg_freespacemap/Makefile
@@ -4,8 +4,8 @@ MODULE_big = pg_freespacemap
 OBJS = pg_freespacemap.o $(WIN32RES)
 
 EXTENSION = pg_freespacemap
-DATA = pg_freespacemap--1.1.sql pg_freespacemap--1.0--1.1.sql \
-   pg_freespacemap--unpackaged--1.0.sql
+DATA = pg_freespacemap--1.1.sql pg_freespacemap--1.1--1.2.sql \
+   pg_freespacemap--1.0--1.1.sql pg_freespacemap--unpackaged--1.0.sql
 PGFILEDESC = "pg_freespacemap - monitoring of free space map"
 
 ifdef USE_PGXS
diff --git a/contrib/pg_freespacemap/pg_freespacemap.control 
b/contrib/pg_freespacemap/pg_freespacemap.control
index 764db30d18..ac8fc5050a 100644
--- a/contrib/pg_freespacemap/pg_freespacemap.control
+++ b/contrib/pg_freespacemap/pg_freespacemap.control
@@ -1,5 +1,5 @@
 # pg_freespacemap extension
 comment = 'examine the free space map (FSM)'
-default_version = '1.1'
+default_version = '1.2'
 module_pathname = '$libdir/pg_freespacemap'
 relocatable = true
diff --git a/contrib/pg_stat_statements/Makefile 
b/contrib/pg_stat_statements/Makefile
index 298951a5f5..39b368b70e 100644
--- a/contrib/pg_stat_statements/Makefile
+++ b/contrib/pg_stat_statements/Makefile
@@ -4,9 +4,10 @@ MODULE_big = pg_stat_statements
 OBJS = pg_stat_statements.o $(WIN32RES)
 
 EXTENSION = pg_stat_statements
-DATA = pg_stat_statements--1.4.sql pg_sta

Re: [HACKERS] Monitoring roles patch

[Dave Page]

On Wed, Mar 22, 2017 at 3:46 PM, Dave Page <dp...@pgadmin.org> wrote:
> On Wed, Mar 22, 2017 at 1:15 PM, Stephen Frost <sfr...@snowman.net> wrote:
>>
>> I did specifically ask for explicit roles to be made to enable such
>> capability and that the pg_monitor role be GRANT'd those roles instead
>> of hacking the pg_monitor OID into those checks, but it seems like
>> that's not been done yet.
>
> Yeah, sorry - I missed that for pg_stat_activity. I'll update the patch.

Updated patch attached. This changes pg_read_all_gucs to
pg_read_all_settings, and adds pg_read_all_stats which it grants to
pg_monitor. pg_read_all_stats has full access to the pg_stat_ views
(as pg_monitor did previously), and is used in the various contrib
modules in place of pg_monitor.

Thanks.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
diff --git a/contrib/pg_buffercache/Makefile b/contrib/pg_buffercache/Makefile
index 497dbeb229..18f7a87452 100644
--- a/contrib/pg_buffercache/Makefile
+++ b/contrib/pg_buffercache/Makefile
@@ -4,8 +4,9 @@ MODULE_big = pg_buffercache
 OBJS = pg_buffercache_pages.o $(WIN32RES)
 
 EXTENSION = pg_buffercache
-DATA = pg_buffercache--1.2.sql pg_buffercache--1.1--1.2.sql \
-   pg_buffercache--1.0--1.1.sql pg_buffercache--unpackaged--1.0.sql
+DATA = pg_buffercache--1.2.sql pg_buffercache--1.2--1.3.sql \
+   pg_buffercache--1.1--1.2.sql pg_buffercache--1.0--1.1.sql \
+   pg_buffercache--unpackaged--1.0.sql
 PGFILEDESC = "pg_buffercache - monitoring of shared buffer cache in real-time"
 
 ifdef USE_PGXS
diff --git a/contrib/pg_buffercache/pg_buffercache.control 
b/contrib/pg_buffercache/pg_buffercache.control
index a4d664f3fa..8c060ae9ab 100644
--- a/contrib/pg_buffercache/pg_buffercache.control
+++ b/contrib/pg_buffercache/pg_buffercache.control
@@ -1,5 +1,5 @@
 # pg_buffercache extension
 comment = 'examine the shared buffer cache'
-default_version = '1.2'
+default_version = '1.3'
 module_pathname = '$libdir/pg_buffercache'
 relocatable = true
diff --git a/contrib/pg_freespacemap/Makefile b/contrib/pg_freespacemap/Makefile
index 7bc0e9555d..0a2f000ec6 100644
--- a/contrib/pg_freespacemap/Makefile
+++ b/contrib/pg_freespacemap/Makefile
@@ -4,8 +4,8 @@ MODULE_big = pg_freespacemap
 OBJS = pg_freespacemap.o $(WIN32RES)
 
 EXTENSION = pg_freespacemap
-DATA = pg_freespacemap--1.1.sql pg_freespacemap--1.0--1.1.sql \
-   pg_freespacemap--unpackaged--1.0.sql
+DATA = pg_freespacemap--1.1.sql pg_freespacemap--1.1--1.2.sql \
+   pg_freespacemap--1.0--1.1.sql pg_freespacemap--unpackaged--1.0.sql
 PGFILEDESC = "pg_freespacemap - monitoring of free space map"
 
 ifdef USE_PGXS
diff --git a/contrib/pg_freespacemap/pg_freespacemap.control 
b/contrib/pg_freespacemap/pg_freespacemap.control
index 764db30d18..ac8fc5050a 100644
--- a/contrib/pg_freespacemap/pg_freespacemap.control
+++ b/contrib/pg_freespacemap/pg_freespacemap.control
@@ -1,5 +1,5 @@
 # pg_freespacemap extension
 comment = 'examine the free space map (FSM)'
-default_version = '1.1'
+default_version = '1.2'
 module_pathname = '$libdir/pg_freespacemap'
 relocatable = true
diff --git a/contrib/pg_stat_statements/Makefile 
b/contrib/pg_stat_statements/Makefile
index 298951a5f5..39b368b70e 100644
--- a/contrib/pg_stat_statements/Makefile
+++ b/contrib/pg_stat_statements/Makefile
@@ -4,9 +4,10 @@ MODULE_big = pg_stat_statements
 OBJS = pg_stat_statements.o $(WIN32RES)
 
 EXTENSION = pg_stat_statements
-DATA = pg_stat_statements--1.4.sql pg_stat_statements--1.3--1.4.sql \
-   pg_stat_statements--1.2--1.3.sql pg_stat_statements--1.1--1.2.sql \
-   pg_stat_statements--1.0--1.1.sql pg_stat_statements--unpackaged--1.0.sql
+DATA = pg_stat_statements--1.4.sql pg_stat_statements--1.4--1.5.sql \
+   pg_stat_statements--1.3--1.4.sql pg_stat_statements--1.2--1.3.sql \
+   pg_stat_statements--1.1--1.2.sql pg_stat_statements--1.0--1.1.sql \
+   pg_stat_statements--unpackaged--1.0.sql
 PGFILEDESC = "pg_stat_statements - execution statistics of SQL statements"
 
 LDFLAGS_SL += $(filter -lm, $(LIBS))
diff --git a/contrib/pg_stat_statements/pg_stat_statements.c 
b/contrib/pg_stat_statements/pg_stat_statements.c
index 221ac98d4a..cec94d5896 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.c
+++ b/contrib/pg_stat_statements/pg_stat_statements.c
@@ -62,6 +62,7 @@
 #include 
 
 #include "access/hash.h"
+#include "catalog/pg_authid.h"
 #include "executor/instrument.h"
 #include "funcapi.h"
 #include "mb/pg_wchar.h"
@@ -1386,7 +1387,7 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
MemoryContext per_query_ctx;
MemoryContext oldcontext;
Oid userid = GetUserId();
-   boolis_superuser = superuser();
+   boolis_superuser = false;
 

Re: [HACKERS] Monitoring roles patch

[Dave Page]

On Wed, Mar 22, 2017 at 1:15 PM, Stephen Frost <sfr...@snowman.net> wrote:
>
> I did specifically ask for explicit roles to be made to enable such
> capability and that the pg_monitor role be GRANT'd those roles instead
> of hacking the pg_monitor OID into those checks, but it seems like
> that's not been done yet.

Yeah, sorry - I missed that for pg_stat_activity. I'll update the patch.


-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] Monitoring roles patch

[Dave Page]

On Wed, Mar 22, 2017 at 12:55 PM, Peter Eisentraut
<peter.eisentr...@2ndquadrant.com> wrote:
> On 3/22/17 07:48, Dave Page wrote:
>> With the patch, complex monitoring systems can easily be setup with
>> something like:
>>
>> CREATE ROLE monitoring_user LOGIN;
>> GRANT pg_monitor TO monitoring_role;
>
> That assumes that we have thought of all the ways in which people might
> want to monitor things.

Right - it was discussed here, and at other meetings. We may not have
everything but either users can GRANT anything we need that we missed
later, or we can add them in a future release.

> If we do it via GRANTs instead, then users can easily extend it.

They can do that anyway.

> If we instead change the hardcoded superuser checks to hardcoded
> some-other-role checks, then the whole system instantly becomes unusable
> the moment someone wants to monitor something we haven't thought of.

I haven't replaced the checks, I've made them superuser || pg_monitor.
Nothing is going to break if we haven't thought of something.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] Monitoring roles patch

[Dave Page]

On Wed, Mar 22, 2017 at 11:32 AM, Robert Haas <robertmh...@gmail.com> wrote:
> On Fri, Feb 24, 2017 at 5:14 AM, Dave Page <dp...@pgadmin.org> wrote:
>> - Adds a default role called pg_monitor
>> - Gives members of the pg_monitor role full access to:
>> pg_ls_logdir() and pg_ls_waldir()
>> pg_stat_* views and functions
>> pg_tablespace_size() and pg_database_size()
>> Contrib modules:
>> pg_buffercache,
>> pg_freespacemap,
>> pgrowlocks,
>> pg_stat_statements,
>> pgstattuple and
>> pg_visibility (but NOT pg_truncate_visibility_map() )
>> - Adds a default role called pg_read_all_gucs
>> - Allows members of pg_read_all_gucs to, well, read all GUCs
>> - Grants pg_read_all_gucs to pg_monitor
>
> I like the pg_read_all_gucs role, which I agree with Peter should be
> called pg_read_all_settings.

No objection to that change.

> I'd be inclined to skip the rest of
> this.  If an individual user wants to grant that bundle of privileges
> to a role, they can do it with or without pg_monitor.

GRANT cannot be used in all cases, as some of the functions changed
have hard-coded superuser checks. In those cases, I've added
pg_monitor membership to the permission checks in the C code.

The reason for having the role is to minimise the amount of work
required by the user to setup a role for the purposes of monitoring
the server. This is a practice which is seen in other DBMSs for
usability.

With the patch, complex monitoring systems can easily be setup with
something like:

CREATE ROLE monitoring_user LOGIN;
GRANT pg_monitor TO monitoring_role;

Without, the users setting up their monitoring system will have to run
a much more complex set of GRANTs, almost certainly requiring
scripting.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] Monitoring roles patch

[Dave Page]

Hi

On Thu, Mar 16, 2017 at 7:04 PM, Denish Patel <denish.j.pa...@gmail.com> wrote:
> Hi Dave,
>
> The patch failed applied...
>
> patch -p1 < /home/vagrant/pg_monitor.diff
> patching file contrib/pg_buffercache/Makefile
> patching file contrib/pg_buffercache/pg_buffercache--1.2--1.3.sql
> patching file contrib/pg_buffercache/pg_buffercache.control
> patching file contrib/pg_freespacemap/Makefile
> patching file contrib/pg_freespacemap/pg_freespacemap--1.1--1.2.sql
> patching file contrib/pg_freespacemap/pg_freespacemap.control
> patching file contrib/pg_stat_statements/Makefile
> patching file contrib/pg_stat_statements/pg_stat_statements--1.4--1.5.sql
> patching file contrib/pg_stat_statements/pg_stat_statements.c
> patching file contrib/pg_stat_statements/pg_stat_statements.control
> patching file contrib/pg_visibility/Makefile
> Hunk #1 succeeded at 4 with fuzz 1.
> patching file contrib/pg_visibility/pg_visibility--1.1--1.2.sql
> patching file contrib/pg_visibility/pg_visibility.control
> patching file contrib/pgrowlocks/pgrowlocks.c
> patching file contrib/pgstattuple/pgstattuple--1.4--1.5.sql
> patching file doc/src/sgml/catalogs.sgml
> Hunk #1 succeeded at 10027 (offset 11 lines).
> patching file doc/src/sgml/func.sgml
> Hunk #1 succeeded at 19364 (offset 311 lines).
> Hunk #2 succeeded at 19648 (offset 311 lines).
> patching file doc/src/sgml/pgbuffercache.sgml
> patching file doc/src/sgml/pgfreespacemap.sgml
> patching file doc/src/sgml/pgrowlocks.sgml
> patching file doc/src/sgml/pgstatstatements.sgml
> patching file doc/src/sgml/pgstattuple.sgml
> patching file doc/src/sgml/pgvisibility.sgml
> patching file doc/src/sgml/user-manag.sgml
> patching file src/backend/catalog/system_views.sql
> Hunk #1 FAILED at 1099.
> 1 out of 1 hunk FAILED -- saving rejects to file 
> src/backend/catalog/system_views.sql.rej
> patching file src/backend/replication/walreceiver.c
> patching file src/backend/utils/adt/dbsize.c
> Hunk #1 succeeded at 17 (offset -1 lines).
> Hunk #2 succeeded at 89 (offset -1 lines).
> Hunk #3 succeeded at 179 (offset -1 lines).
> patching file src/backend/utils/adt/pgstatfuncs.c
> patching file src/backend/utils/misc/guc.c
> Hunk #2 succeeded at 6678 (offset 10 lines).
> Hunk #3 succeeded at 6728 (offset 10 lines).
> Hunk #4 succeeded at 8021 (offset 10 lines).
> Hunk #5 succeeded at 8053 (offset 10 lines).
> patching file src/include/catalog/pg_authid.h
>
> Reject file contents...
>
> cat src/backend/catalog/system_views.sql.rej
> --- src/backend/catalog/system_views.sql
> +++ src/backend/catalog/system_views.sql
> @@ -1099,3 +1099,7 @@
>
>  REVOKE EXECUTE ON FUNCTION pg_ls_logdir() FROM public;
>  REVOKE EXECUTE ON FUNCTION pg_ls_waldir() FROM public;
> +GRANT EXECUTE ON FUNCTION pg_ls_logdir() TO pg_monitor;
> +GRANT EXECUTE ON FUNCTION pg_ls_waldir() TO pg_monitor;
> +
> +GRANT pg_read_all_gucs TO pg_monitor;
>
> The new status of this patch is: Waiting on Author

Odd - I get very different rejects than you. Perhaps you didn't apply
my pg_ls_logdir() patch first? In any case, that was committed last
night.

Here's a rebased patch.

Thanks!

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
diff --git a/contrib/pg_buffercache/Makefile b/contrib/pg_buffercache/Makefile
index 497dbeb229..18f7a87452 100644
--- a/contrib/pg_buffercache/Makefile
+++ b/contrib/pg_buffercache/Makefile
@@ -4,8 +4,9 @@ MODULE_big = pg_buffercache
 OBJS = pg_buffercache_pages.o $(WIN32RES)
 
 EXTENSION = pg_buffercache
-DATA = pg_buffercache--1.2.sql pg_buffercache--1.1--1.2.sql \
-   pg_buffercache--1.0--1.1.sql pg_buffercache--unpackaged--1.0.sql
+DATA = pg_buffercache--1.2.sql pg_buffercache--1.2--1.3.sql \
+   pg_buffercache--1.1--1.2.sql pg_buffercache--1.0--1.1.sql \
+   pg_buffercache--unpackaged--1.0.sql
 PGFILEDESC = "pg_buffercache - monitoring of shared buffer cache in real-time"
 
 ifdef USE_PGXS
diff --git a/contrib/pg_buffercache/pg_buffercache--1.2--1.3.sql 
b/contrib/pg_buffercache/pg_buffercache--1.2--1.3.sql
new file mode 100644
index 00..b37ef0112e
--- /dev/null
+++ b/contrib/pg_buffercache/pg_buffercache--1.2--1.3.sql
@@ -0,0 +1,7 @@
+/* contrib/pg_buffercache/pg_buffercache--1.2--1.3.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION pg_buffercache UPDATE TO '1.3'" to load this file. 
\quit
+
+GRANT EXECUTE ON FUNCTION pg_buffercache_pages() TO pg_monitor;
+GRANT SELECT ON pg_buffercache TO pg_monitor;
diff --git a/contrib/pg_buffercache/pg_buffercache.control 
b/contrib/pg_buffercache/pg_buffercache.control
index a4d664f3fa..8c060ae9ab 100644
--- a/contrib/pg_buffercache/pg_buffercache.co

Re: [HACKERS] pg_ls_waldir() & pg_ls_logdir()

[Dave Page]

On Thu, Mar 16, 2017 at 7:05 PM, Robert Haas <robertmh...@gmail.com> wrote:
> On Thu, Mar 16, 2017 at 6:09 AM, Dave Page <dp...@pgadmin.org> wrote:
>> Hmm, good point. Google seems to be saying there isn't one. Patch
>> updated as you suggest (and I've added back in a function declaration
>> that got lost in the rebasing of the last version).
>
> OK, I took another look at this:
>
> - The documentation wasn't consistent with itself about the order in
> which the three columns were mentioned.  I changed it to say name,
> size, modification time both places and made the code also return the
> columns in that order.  And I renamed the columns to name, size, and
> modification, the last of which was chosen to match pg_stat_file().
>
> - I added an error check for the stat() call.
>
> - I moved the code to genfile.c where pg_ls_dir() already is; it seems
> to fit within the charter of that file.
>
> - I changed it to build a heap tuple directly instead of converting to
> text and then back to datums.  Seems less error-prone that way, and
> more consistent with what's done elsewhere in genfile.c.
>
> - I made it use a static-allocated buffer instead of a palloc'd one,
> just so it doesn't leak into the surrounding context.
>
> - I removed the function prototype and instead declared the helper
> function static.  If there's an intent to expose that function to
> extensions, the prototype should be in a header, not the .c file.
>
> - I adjusted the language in the documentation to be a bit more
> similar to what we've done elsewhere.
>
> With those changes, committed.

Thanks!

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] pg_ls_waldir() & pg_ls_logdir()

[Dave Page]

On Thu, Mar 16, 2017 at 9:54 AM, Thomas Munro
<thomas.mu...@enterprisedb.com> wrote:
> On Thu, Mar 16, 2017 at 10:40 PM, Dave Page <dp...@pgadmin.org> wrote:
>>> +const int n = snprintf(NULL, 0, "%lld", attrib.st_size);
>
> I wonder what the portable printf directive is for off_t.  Maybe
> better to use INT64_FORMAT and cast to int64?

Hmm, good point. Google seems to be saying there isn't one. Patch
updated as you suggest (and I've added back in a function declaration
that got lost in the rebasing of the last version).

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index a521912317..e15ad77dec 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -19646,7 +19646,8 @@ postgres=# SELECT * FROM 
pg_walfile_name_offset(pg_stop_backup());
 database cluster directory and the log_directory can be
 accessed.  Use a relative path for files in the cluster directory,
 and a path matching the log_directory configuration setting
-for log files.  Use of these functions is restricted to superusers.
+for log files.  Use of these functions is restricted to superusers
+except where stated otherwise.

 

@@ -19669,6 +19670,26 @@ postgres=# SELECT * FROM 
pg_walfile_name_offset(pg_stop_backup());
   
   

+pg_ls_logdir()
+   
+   setof record
+   
+List the name, last modification time and size of files in the log 
directory.
+Execute permission may be granted to non-superuser roles.
+   
+  
+  
+   
+pg_ls_waldir()
+   
+   setof record
+   
+List the name, last modification time and size of files in the WAL 
directory.
+Execute permission may be granted to non-superuser roles.
+   
+  
+  
+   
 pg_read_file(filename text 
[, offset bigint, length bigint 
[, missing_ok boolean] ])

text
@@ -19699,7 +19720,7 @@ postgres=# SELECT * FROM 
pg_walfile_name_offset(pg_stop_backup());

 

-All of these functions take an optional missing_ok parameter,
+Some of these functions take an optional missing_ok 
parameter,
 which specifies the behavior when the file or directory does not exist.
 If true, the function returns NULL (except
 pg_ls_dir, which returns an empty result set). If
@@ -19720,6 +19741,26 @@ postgres=# SELECT * FROM 
pg_walfile_name_offset(pg_stop_backup());

 

+pg_ls_logdir
+   
+   
+pg_ls_logdir returns the last modified time (mtime), size
+and names of all the file in the log directory. By default only superusers
+can use this function, however additional roles may be granted execute
+permission if required.
+   
+
+   
+pg_ls_waldir
+   
+   
+pg_ls_waldir returns the last modified time (mtime), size
+and names of all the file in the write ahead log (WAL) directory. By
+default only superusers can use this function, however additional roles
+may be granted execute permission if required.
+   
+
+   
 pg_read_file


diff --git a/src/backend/catalog/system_views.sql 
b/src/backend/catalog/system_views.sql
index 0bce20914e..b6552da4b0 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1102,3 +1102,6 @@ REVOKE EXECUTE ON FUNCTION pg_stat_reset() FROM public;
 REVOKE EXECUTE ON FUNCTION pg_stat_reset_shared(text) FROM public;
 REVOKE EXECUTE ON FUNCTION pg_stat_reset_single_table_counters(oid) FROM 
public;
 REVOKE EXECUTE ON FUNCTION pg_stat_reset_single_function_counters(oid) FROM 
public;
+
+REVOKE EXECUTE ON FUNCTION pg_ls_logdir() FROM public;
+REVOKE EXECUTE ON FUNCTION pg_ls_waldir() FROM public;
diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c
index 1ec7f32470..ad75d18a2f 100644
--- a/src/backend/utils/adt/misc.c
+++ b/src/backend/utils/adt/misc.c
@@ -15,12 +15,14 @@
 #include "postgres.h"
 
 #include 
+#include 
 #include 
 #include 
 #include 
 #include 
 
 #include "access/sysattr.h"
+#include "access/xlog_internal.h"
 #include "catalog/pg_authid.h"
 #include "catalog/catalog.h"
 #include "catalog/pg_tablespace.h"
@@ -44,6 +46,8 @@
 #include "utils/builtins.h"
 #include "utils/timestamp.h"
 
+/* Generic function to return a directory listing of files */
+Datum pg_ls_dir_files(FunctionCallInfo fcinfo, char *dir);
 
 /*
  * Common subroutine for num_nulls() and num_nonnulls().
@@ -982,3 +986,111 @@ pg_current_logfile_1arg(PG_FUNCTION_ARGS)
 {
return pg_current_logfile(fcinfo);
 }
+
+
+typedef struct
+{
+   char*location;
+   DIR *dirdesc;
+} directory_fctx;
+
+/* Generic function to return a directory listing of files */
+Datum
+pg_ls_dir_files(Funct

Re: [HACKERS] pg_ls_waldir() & pg_ls_logdir()

[Dave Page]

Hi

On Wed, Mar 15, 2017 at 5:27 PM, Robert Haas <robertmh...@gmail.com> wrote:
> On Mon, Feb 20, 2017 at 6:21 AM, Dave Page <dp...@pgadmin.org> wrote:
>> Patch includes the code and doc updates.
>
> Review:
>
> +strftime(mtime, 25, "%Y-%m-%d %H:%M:%S %Z",
> localtime(&(attrib.st_ctime)));
> +const int n = snprintf(NULL, 0, "%lld", attrib.st_size);
> +char size[n+1];
> +snprintf(size, n+1, "%lld", attrib.st_size);
>
> We don't allow variable declarations in mid-block.  You've been
> programming in C++ for too long!

Err, yeah. Ooops. Fixed.

> The documentation should be updated to say that access to
> pg_ls_logdir() and pg_ls_waldir() can be granted via the permissions
> system (see the paragraph above the table you updated).
>
> The four existing functions in the documentation table each have a
> corresponding paragraph below the table, but the two new functions you
> added don't.

Added.

> +1 for the concept.

Thanks, updated patch attached.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index a521912317..e15ad77dec 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -19646,7 +19646,8 @@ postgres=# SELECT * FROM 
pg_walfile_name_offset(pg_stop_backup());
 database cluster directory and the log_directory can be
 accessed.  Use a relative path for files in the cluster directory,
 and a path matching the log_directory configuration setting
-for log files.  Use of these functions is restricted to superusers.
+for log files.  Use of these functions is restricted to superusers
+except where stated otherwise.

 

@@ -19669,6 +19670,26 @@ postgres=# SELECT * FROM 
pg_walfile_name_offset(pg_stop_backup());
   
   

+pg_ls_logdir()
+   
+   setof record
+   
+List the name, last modification time and size of files in the log 
directory.
+Execute permission may be granted to non-superuser roles.
+   
+  
+  
+   
+pg_ls_waldir()
+   
+   setof record
+   
+List the name, last modification time and size of files in the WAL 
directory.
+Execute permission may be granted to non-superuser roles.
+   
+  
+  
+   
 pg_read_file(filename text 
[, offset bigint, length bigint 
[, missing_ok boolean] ])

text
@@ -19699,7 +19720,7 @@ postgres=# SELECT * FROM 
pg_walfile_name_offset(pg_stop_backup());

 

-All of these functions take an optional missing_ok parameter,
+Some of these functions take an optional missing_ok 
parameter,
 which specifies the behavior when the file or directory does not exist.
 If true, the function returns NULL (except
 pg_ls_dir, which returns an empty result set). If
@@ -19720,6 +19741,26 @@ postgres=# SELECT * FROM 
pg_walfile_name_offset(pg_stop_backup());

 

+pg_ls_logdir
+   
+   
+pg_ls_logdir returns the last modified time (mtime), size
+and names of all the file in the log directory. By default only superusers
+can use this function, however additional roles may be granted execute
+permission if required.
+   
+
+   
+pg_ls_waldir
+   
+   
+pg_ls_waldir returns the last modified time (mtime), size
+and names of all the file in the write ahead log (WAL) directory. By
+default only superusers can use this function, however additional roles
+may be granted execute permission if required.
+   
+
+   
 pg_read_file


diff --git a/src/backend/catalog/system_views.sql 
b/src/backend/catalog/system_views.sql
index 0bce20914e..b6552da4b0 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1102,3 +1102,6 @@ REVOKE EXECUTE ON FUNCTION pg_stat_reset() FROM public;
 REVOKE EXECUTE ON FUNCTION pg_stat_reset_shared(text) FROM public;
 REVOKE EXECUTE ON FUNCTION pg_stat_reset_single_table_counters(oid) FROM 
public;
 REVOKE EXECUTE ON FUNCTION pg_stat_reset_single_function_counters(oid) FROM 
public;
+
+REVOKE EXECUTE ON FUNCTION pg_ls_logdir() FROM public;
+REVOKE EXECUTE ON FUNCTION pg_ls_waldir() FROM public;
diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c
index 1ec7f32470..2fadad860b 100644
--- a/src/backend/utils/adt/misc.c
+++ b/src/backend/utils/adt/misc.c
@@ -15,12 +15,14 @@
 #include "postgres.h"
 
 #include 
+#include 
 #include 
 #include 
 #include 
 #include 
 
 #include "access/sysattr.h"
+#include "access/xlog_internal.h"
 #include "catalog/pg_authid.h"
 #include "catalog/catalog.h"
 #include "catalog/pg_tablespace.h"
@@ -982,3 +984,111 @@ pg_current_logfile_1arg(PG_FUNCTION_

[HACKERS] Monitoring roles patch

[Dave Page]

Per the discussion at
https://www.postgresql.org/message-id/CA%2BOCxoyYxO%2BJmzv2Micj4uAaQdAi6nq0w25BPQgLLxsrvTmREw%40mail.gmail.com,
attached is a patch that implements the following:

- Adds a default role called pg_monitor
- Gives members of the pg_monitor role full access to:
pg_ls_logdir() and pg_ls_waldir()
pg_stat_* views and functions
pg_tablespace_size() and pg_database_size()
Contrib modules:
pg_buffercache,
pg_freespacemap,
pgrowlocks,
pg_stat_statements,
pgstattuple and
pg_visibility (but NOT pg_truncate_visibility_map() )
- Adds a default role called pg_read_all_gucs
- Allows members of pg_read_all_gucs to, well, read all GUCs
- Grants pg_read_all_gucs to pg_monitor

Note that updates to contrib modules followed the strategy recently
used in changes to pgstattuple following discussion here, in which the
installation SQL script is left at the prior version, and an update
script is added and default version number bumped to match that of the
upgrade script.

Patch includes doc updates, and is dependent on my pg_ls_logdir() and
pg_ls_waldir() patch
(https://www.postgresql.org/message-id/CA+OCxow-X=D2fWdKy+HP+vQ1LtrgbsYQ=cshzzbqyft5joy...@mail.gmail.com).

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
diff --git a/contrib/pg_buffercache/Makefile b/contrib/pg_buffercache/Makefile
index 497dbeb229..18f7a87452 100644
--- a/contrib/pg_buffercache/Makefile
+++ b/contrib/pg_buffercache/Makefile
@@ -4,8 +4,9 @@ MODULE_big = pg_buffercache
 OBJS = pg_buffercache_pages.o $(WIN32RES)
 
 EXTENSION = pg_buffercache
-DATA = pg_buffercache--1.2.sql pg_buffercache--1.1--1.2.sql \
-   pg_buffercache--1.0--1.1.sql pg_buffercache--unpackaged--1.0.sql
+DATA = pg_buffercache--1.2.sql pg_buffercache--1.2--1.3.sql \
+   pg_buffercache--1.1--1.2.sql pg_buffercache--1.0--1.1.sql \
+   pg_buffercache--unpackaged--1.0.sql
 PGFILEDESC = "pg_buffercache - monitoring of shared buffer cache in real-time"
 
 ifdef USE_PGXS
diff --git a/contrib/pg_buffercache/pg_buffercache--1.2--1.3.sql 
b/contrib/pg_buffercache/pg_buffercache--1.2--1.3.sql
new file mode 100644
index 00..b37ef0112e
--- /dev/null
+++ b/contrib/pg_buffercache/pg_buffercache--1.2--1.3.sql
@@ -0,0 +1,7 @@
+/* contrib/pg_buffercache/pg_buffercache--1.2--1.3.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION pg_buffercache UPDATE TO '1.3'" to load this file. 
\quit
+
+GRANT EXECUTE ON FUNCTION pg_buffercache_pages() TO pg_monitor;
+GRANT SELECT ON pg_buffercache TO pg_monitor;
diff --git a/contrib/pg_buffercache/pg_buffercache.control 
b/contrib/pg_buffercache/pg_buffercache.control
index a4d664f3fa..8c060ae9ab 100644
--- a/contrib/pg_buffercache/pg_buffercache.control
+++ b/contrib/pg_buffercache/pg_buffercache.control
@@ -1,5 +1,5 @@
 # pg_buffercache extension
 comment = 'examine the shared buffer cache'
-default_version = '1.2'
+default_version = '1.3'
 module_pathname = '$libdir/pg_buffercache'
 relocatable = true
diff --git a/contrib/pg_freespacemap/Makefile b/contrib/pg_freespacemap/Makefile
index 7bc0e9555d..0a2f000ec6 100644
--- a/contrib/pg_freespacemap/Makefile
+++ b/contrib/pg_freespacemap/Makefile
@@ -4,8 +4,8 @@ MODULE_big = pg_freespacemap
 OBJS = pg_freespacemap.o $(WIN32RES)
 
 EXTENSION = pg_freespacemap
-DATA = pg_freespacemap--1.1.sql pg_freespacemap--1.0--1.1.sql \
-   pg_freespacemap--unpackaged--1.0.sql
+DATA = pg_freespacemap--1.1.sql pg_freespacemap--1.1--1.2.sql \
+   pg_freespacemap--1.0--1.1.sql pg_freespacemap--unpackaged--1.0.sql
 PGFILEDESC = "pg_freespacemap - monitoring of free space map"
 
 ifdef USE_PGXS
diff --git a/contrib/pg_freespacemap/pg_freespacemap--1.1--1.2.sql 
b/contrib/pg_freespacemap/pg_freespacemap--1.1--1.2.sql
new file mode 100644
index 00..490bb3bf46
--- /dev/null
+++ b/contrib/pg_freespacemap/pg_freespacemap--1.1--1.2.sql
@@ -0,0 +1,7 @@
+/* contrib/pg_freespacemap/pg_freespacemap--1.1--1.2.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION pg_freespacemap UPDATE TO '1.2'" to load this file. 
\quit
+
+GRANT EXECUTE ON FUNCTION  pg_freespace(regclass, bigint) TO pg_monitor;
+GRANT EXECUTE ON FUNCTION  pg_freespace(regclass) TO pg_monitor;
diff --git a/contrib/pg_freespacemap/pg_freespacemap.control 
b/contrib/pg_freespacemap/pg_freespacemap.control
index 764db30d18..ac8fc5050a 100644
--- a/contrib/pg_freespacemap/pg_freespacemap.control
+++ b/contrib/pg_freespacemap/pg_freespacemap.control
@@ -1,5 +1,5 @@
 # pg_freespacemap extension
 comment = 'examine the free space map (FSM)'
-default_version = '1.1'
+default_version = '1.2'
 module_pathname = '$libdir/pg_freespacemap'
 relocatable = true
diff --git a/contrib/pg_stat_statements/Makefile 
b/contrib/pg_s

Re: [HACKERS] pg_monitor role

[Dave Page]

e in that class for the record). That said, if we ever do add
something like that then there's nothing stopping us from explicitly
documenting that it's excluded from pg_monitor for that reason, and if
desired the user can grant on it as needed.

Using a scheme like that would also mean that the user is more likely
to need to manually update the role their monitoring system uses
following an upgrade.

>   - Do not create our own pg_monitor but instead provide
> documentation/scripts for users to create their own "monitor" roles.

The whole point here is to minimise the requirements on the user, and
have a good set of default roles.

> It seems at least unlikely that we'll never have another pg_stat_X view
> that we want to give pg_monitor access to, so I don't really see "Fix
> what pg_monitor can read forever based on what's in PG10" as being a
> solution.

No, but similarly I don't see any reason why we cannot add new views
by default, and exclude by exception when there's a compelling reason.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] pg_monitor role

[Dave Page]

Hi

On Tue, Feb 21, 2017 at 5:40 PM, Masahiko Sawada <sawada.m...@gmail.com> wrote:
> On Mon, Feb 20, 2017 at 8:48 PM, Dave Page <dp...@pgadmin.org> wrote:
>> Further to the patch I just submitted
>> (https://www.postgresql.org/message-id/CA%2BOCxow-X%3DD2fWdKy%2BHP%2BvQ1LtrgbsYQ%3DCshzZBqyFT5jOYrFw%40mail.gmail.com)
>> I'd like to propose the addition of a default role, pg_monitor.
>>
>> The intent is to make it easy for users to setup a role for fully
>> monitoring their servers, without requiring superuser level privileges
>> which is a problem for many users working within strict security
>> policies.
>>
>> At present, functions or system config info that divulge any
>> installation path related info typically require superuser privileges.
>> This makes monitoring for unexpected changes in configuration or
>> filesystem level monitoring (e.g. checking for large numbers of WAL
>> files or log file info) impossible for non-privileged roles.
>>
>> A similar example is the restriction on the pg_stat_activity.query
>> column, which prevents non-superusers seeing any query strings other
>> than their own.
>>
>> Using ACLs is a problem for a number of reasons:
>>
>> - Users often don't like their database schemas to be modified
>> (cluttered with GRANTs).
>> - ACL modifications would potentially have to be made in every
>> database in a cluster.
>> - Using a pre-defined role minimises the setup that different tools
>> would have to require.
>> - Not all functionality has an ACL (e.g. SHOW)
>>
>> Other DBMSs solve this problem in a similar way.
>>
>> Initially I would propose that permission be granted to the role to:
>>
>> - Execute pg_ls_logdir() and pg_ls_waldir()
>> - Read pg_stat_activity, including the query column for all queries.
>> - Allow "SELECT pg_tablespace_size('pg_global')"
>> - Read all GUCs
>>
>
> Thank you for working on this.

You're welcome.

> What about granting to the role to read other statistic views such as
> pg_stat_replication and pg_stat_wal_receiver? Since these informations
> can only be seen by superuser the for example monitoring and
> clustering tool seems to have the same concern.

Yes, good point.

> And what about the diagnostic tools such as pageinspect and pgstattuple?

I think external/contrib modules should not be included. To install
them you need admin privileges anyway, so you can easily grant
whatever usage privileges you want at that time.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

[HACKERS] pg_monitor role

[Dave Page]

Further to the patch I just submitted
(https://www.postgresql.org/message-id/CA%2BOCxow-X%3DD2fWdKy%2BHP%2BvQ1LtrgbsYQ%3DCshzZBqyFT5jOYrFw%40mail.gmail.com)
I'd like to propose the addition of a default role, pg_monitor.

The intent is to make it easy for users to setup a role for fully
monitoring their servers, without requiring superuser level privileges
which is a problem for many users working within strict security
policies.

At present, functions or system config info that divulge any
installation path related info typically require superuser privileges.
This makes monitoring for unexpected changes in configuration or
filesystem level monitoring (e.g. checking for large numbers of WAL
files or log file info) impossible for non-privileged roles.

A similar example is the restriction on the pg_stat_activity.query
column, which prevents non-superusers seeing any query strings other
than their own.

Using ACLs is a problem for a number of reasons:

- Users often don't like their database schemas to be modified
(cluttered with GRANTs).
- ACL modifications would potentially have to be made in every
database in a cluster.
- Using a pre-defined role minimises the setup that different tools
would have to require.
- Not all functionality has an ACL (e.g. SHOW)

Other DBMSs solve this problem in a similar way.

Initially I would propose that permission be granted to the role to:

- Execute pg_ls_logdir() and pg_ls_waldir()
- Read pg_stat_activity, including the query column for all queries.
- Allow "SELECT pg_tablespace_size('pg_global')"
- Read all GUCs

In the future I would also like to see us add additional roles for
system administration functions, for example, a backup operator role
that would have the appropriate rights to make and restore backups.

Comments?

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

[HACKERS] pg_ls_waldir() & pg_ls_logdir()

[Dave Page]

Hi

Following various conversations on list and in person, including the
developer meeting in Brussels earlier this month, here is a patch that
implements pg_ls_logdir() and pg_ls_waldir() functions.

The ultimate aim of this (and followup work I'll be doing) is to
provide functionality to enable monitoring of PostgreSQL without
requiring a user with superuser permissions as many of us have users
for whom security policies prevent this or make it very difficult.

In order to achieve that, there are various pieces of functionality
such as pg_ls_dir() that need to have superuser checks removed to
allow permissions to be granted to a monitoring role. There were
objections in previous discussions to doing this with such generic
functions, hence this patch which adds two narrowly focussed functions
to allow tools to monitor the contents of the log and WAL directories.
Neither function has a hard-coded superuser check, but have ACLs that
prevent public execution by default.

Patch includes the code and doc updates.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index d7738b18b7..ecd17a3528 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -19360,6 +19360,24 @@ postgres=# SELECT * FROM 
pg_walfile_name_offset(pg_stop_backup());
   
   

+pg_ls_logdir()
+   
+   setof record
+   
+List the name, last modification time and size of files in the log 
directory.
+   
+  
+  
+   
+pg_ls_waldir()
+   
+   setof record
+   
+List the name, last modification time and size of files in the WAL 
directory.
+   
+  
+  
+   
 pg_read_file(filename text 
[, offset bigint, length bigint 
[, missing_ok boolean] ])

text
@@ -19390,7 +19408,7 @@ postgres=# SELECT * FROM 
pg_walfile_name_offset(pg_stop_backup());

 

-All of these functions take an optional missing_ok parameter,
+Some of these functions take an optional missing_ok 
parameter,
 which specifies the behavior when the file or directory does not exist.
 If true, the function returns NULL (except
 pg_ls_dir, which returns an empty result set). If
diff --git a/src/backend/catalog/system_views.sql 
b/src/backend/catalog/system_views.sql
index 38be9cf1a0..4b67102439 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1096,3 +1096,6 @@ REVOKE EXECUTE ON FUNCTION pg_stat_reset() FROM public;
 REVOKE EXECUTE ON FUNCTION pg_stat_reset_shared(text) FROM public;
 REVOKE EXECUTE ON FUNCTION pg_stat_reset_single_table_counters(oid) FROM 
public;
 REVOKE EXECUTE ON FUNCTION pg_stat_reset_single_function_counters(oid) FROM 
public;
+
+REVOKE EXECUTE ON FUNCTION pg_ls_logdir() FROM public;
+REVOKE EXECUTE ON FUNCTION pg_ls_waldir() FROM public;
diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c
index 66d09bcb0c..a8cdf3bcbf 100644
--- a/src/backend/utils/adt/misc.c
+++ b/src/backend/utils/adt/misc.c
@@ -15,12 +15,14 @@
 #include "postgres.h"
 
 #include 
+#include 
 #include 
 #include 
 #include 
 #include 
 
 #include "access/sysattr.h"
+#include "access/xlog_internal.h"
 #include "catalog/pg_authid.h"
 #include "catalog/catalog.h"
 #include "catalog/pg_tablespace.h"
@@ -46,6 +48,8 @@
 
 #define atooid(x)  ((Oid) strtoul((x), NULL, 10))
 
+/* Generic function to return a directory listing of files */
+Datum pg_ls_dir_files(FunctionCallInfo fcinfo, char *dir);
 
 /*
  * Common subroutine for num_nulls() and num_nonnulls().
@@ -892,3 +896,109 @@ parse_ident(PG_FUNCTION_ARGS)
 
PG_RETURN_DATUM(makeArrayResult(astate, CurrentMemoryContext));
 }
+
+
+typedef struct
+{
+   char*location;
+   DIR *dirdesc;
+} directory_fctx;
+
+/* Generic function to return a directory listing of files */
+Datum
+pg_ls_dir_files(FunctionCallInfo fcinfo, char *dir)
+{
+   FuncCallContext *funcctx;
+   struct dirent *de;
+   directory_fctx *fctx;
+
+   if (SRF_IS_FIRSTCALL())
+   {
+   MemoryContext oldcontext;
+   TupleDesc   tupdesc;
+
+   funcctx = SRF_FIRSTCALL_INIT();
+   oldcontext = 
MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+   fctx = palloc(sizeof(directory_fctx));
+
+   tupdesc = CreateTemplateTupleDesc(3, false);
+   TupleDescInitEntry(tupdesc, (AttrNumber) 1, "mtime",
+  TIMESTAMPTZOID, -1, 0);
+   TupleDescInitEntry(tupdesc, (AttrNumber) 2, "size",
+  INT8OID, -1, 0);
+   TupleDescInitEntry(tupdesc, (AttrNumber) 3, "file",
+  

[HACKERS] PostgreSQL Code of Conduct Draft

[Dave Page]

The revised draft of the proposed Code of Conduct for the PostgreSQL
community is at https://wiki.postgresql.org/wiki/Code_of_Conduct.

This updated draft incorporates comments and suggestions from the
community received at PgCon Ottawa and subsequent discussion.

We will not be monitoring the mailing lists for comments or suggested
changes. If you have comments, please email them to
coc-comme...@postgresql.org no later than 2017-03-05, 11:59PM GMT for
the committee to review.

Regards, Dave.

-- 
Dave Page
PostgreSQL Core Team
http://www.postgresql.org/


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

Re: [HACKERS] pg_ls_dir & friends still have a hard-coded superuser check

[Dave Page]

> On 27 Jan 2017, at 17:39, Stephen Frost <sfr...@snowman.net> wrote:
> 
> Greetings,
> 
> * Simon Riggs (si...@2ndquadrant.com) wrote:
>>> On 27 January 2017 at 14:09, Dave Page <dp...@pgadmin.org> wrote:
>>>> On Fri, Jan 27, 2017 at 1:18 PM, Simon Riggs <si...@2ndquadrant.com> wrote:
>>>> 
>>>> If the monitoring tool requires superuser then that is a problem, so
>>>> it would be helpful if it didn't do that, please. Not much use having
>>>> a cool tool if it don't work with the server.
>>> 
>>> Sure, that's what I want - to provide the management and monitoring
>>> capabilities without requiring superuser. Limiting the capability of
>>> the tools is not an option when you talk to users - however for some
>>> of them, having to use full superuser accounts is a problem as well
>>> (especially for those who are used to other DBMSs that do offer more
>>> fine-grained permissions).
> 
> Right, I'm all about providing fine-grained permissions and granting
> those out to monitoring users, but we need to have an understanding of
> what the monitoring really needs (and doesn't need...) to be able to
> ensure that the fine-grained permission system which is built matches
> those needs and allows the admin to grant out exactly the rights needed.
> 
>>>> The management and monitoring tool could be more specific about what
>>>> it actually needs, rather than simply requesting generic read and
>>>> write against the filesystem. Then we can put those specific things
>>>> into the server and we can all be happy. Again, a detailed list would
>>>> help here.
>>> 
>>> Agreed - I do need to do that, and it's on my (extremely long) list.
>>> I'm just chiming in on this thread as requested!
> 
> That would certainly be really nice to have.  I have some ideas, and
> I've been meaning to try and work towards them, but knowing what other
> monitoring systems do would be great.
> 
>> So I think it would be useful to have two modes in tools, one where
>> they know they have superuser and one where they know we don't have
>> it. At least we'll know we can't do certain things rather than just
>> have them fail.
> 
> Having such a flag in monitoring tools where it makes sense sounds
> reasonable to me, though there isn't really anything different for the
> backend to do to support this (I don't think..?).

No, that's exactly what we don't want, because then users cannot do anything 
that we can currently grant them permissions for - it's the all-or-nothing 
approach.

What we currently do is allow users to try every thing, then let the backend 
complain if it wants, and relay the access denied message to the user.

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

Re: [HACKERS] pg_ls_dir & friends still have a hard-coded superuser check

[Dave Page]

On Fri, Jan 27, 2017 at 1:18 PM, Simon Riggs <si...@2ndquadrant.com> wrote:
> On 27 January 2017 at 12:56, Dave Page <dp...@pgadmin.org> wrote:
>
>> Probably the most common complaint I get from users
>> regarding the management & monitoring tools I work on is that they
>> have to use superuser accounts to get the full benefits, unlike other
>> DBMSs where you can create a role with just the required privileges
>> (or indeed, other DBMSs that ship with such roles pre-defined for
>> convenience).
>
> This is still just the Adminpack argument. This has been going on for
> about a decade? Longer.

Not entirely. Some simple examples:

- Controlling recovery. This is fixed in 9.6+, but prior to that,
granting execute privs on pg_xlog_replay_pause() and/or
pg_xlog_replay_resume() would be accepted but wouldn't work.

- Access to certain GUCs. For example, it could be argued that "SHOW
log_directory" is quite reasonable for a monitoring tool to run, for
the purposes of auditing/alerting admins to any changes made by a
rogue superuser.

- ALTER SYSTEM - clearly there is a use case for allow certain users
to configure the database server, but not necessarily have the full
rights of a superuser that would allow them access to all the data
(yeah, I know that separation is far more complex than that alone, but
I hope you get the point).

> If the monitoring tool requires superuser then that is a problem, so
> it would be helpful if it didn't do that, please. Not much use having
> a cool tool if it don't work with the server.

Sure, that's what I want - to provide the management and monitoring
capabilities without requiring superuser. Limiting the capability of
the tools is not an option when you talk to users - however for some
of them, having to use full superuser accounts is a problem as well
(especially for those who are used to other DBMSs that do offer more
fine-grained permissions).

> The management and monitoring tool could be more specific about what
> it actually needs, rather than simply requesting generic read and
> write against the filesystem. Then we can put those specific things
> into the server and we can all be happy. Again, a detailed list would
> help here.

Agreed - I do need to do that, and it's on my (extremely long) list.
I'm just chiming in on this thread as requested!

> Does the latest version of pgadmin provide access to log files? I
> can't see much that really needs Adminpack anymore, though I've not
> done a thorough analysis at all.

Not yet.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] pg_ls_dir & friends still have a hard-coded superuser check

[Dave Page]

On Fri, Jan 27, 2017 at 1:02 PM, Simon Riggs <si...@2ndquadrant.com> wrote:
> On 26 January 2017 at 22:36, Stephen Frost <sfr...@snowman.net> wrote:
>
>>> Currently, I count three votes in favor of this approach and one
>>> opposed.  If anyone else wants to weigh in, please do.  It would be
>>> helpful if anyone weighing in can be clear about whether (a) they are
>>> in favor of the patch as proposed, or (b) they are not in favor of the
>>> patch as proposed but could support a narrower patch that removed the
>>> checks only from functions with no known escalate-to-superuser risks,
>>> or (c) they oppose all change.  It would also be helpful if the
>>> reasons why each person takes the position that they do could be
>>> mentioned.
>>
>> I agree that it'd be nice if others would weigh in on this.
>
> I oppose the patch as currently presented.
>
> In general, I support the viewpoint that we reduce the number of
> superuser checks. I also recognize that its unlikely that this can be
> reduced to zero without a clear way forwards.
>
> What I suggest we do is this
>
> 1. Take the discussion onto the appropriate private forum, which isn't
> here, IMHO.
>
> 2. Try to agree policy first that matches what other security folk
> will allow. Not much point releasing PostgreSQL and then having other
> people block parts of it so it matches their view of security. We
> should seek to resolve that inherent conflict.
>
> 3. Make a list of all functions that would cause security problems.
> One by one, precisely. If we did remove all superuser checks we would
> need this list documented to advise people of the risks, so it must
> exist before any commit can be made, assuming we believe in
> documentation. Notice that I am after risk documentation, not just "By
> default, use of this function is restricted to superusers" because
> that just leads to people exposing themselves unknowingly when they
> follow the next part which seems like official advice, yet is
> potentially unsafe: "access can be given to other users via GRANT".
>
> 4. Later, work towards a patch. We have some weeks to get this right.
>
> I'm willing to spend time on workshopping this in Brussels, with any 
> attendees.

I already added it to the agenda items.


-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] pg_ls_dir & friends still have a hard-coded superuser check

[Dave Page]

On Thu, Jan 26, 2017 at 10:36 PM, Stephen Frost <sfr...@snowman.net> wrote:
>
> Perhaps unsuprisingly, but you've still not convinced me, so I don't
> agree with this change.
>
>> Currently, I count three votes in favor of this approach and one
>> opposed.  If anyone else wants to weigh in, please do.  It would be
>> helpful if anyone weighing in can be clear about whether (a) they are
>> in favor of the patch as proposed, or (b) they are not in favor of the
>> patch as proposed but could support a narrower patch that removed the
>> checks only from functions with no known escalate-to-superuser risks,
>> or (c) they oppose all change.  It would also be helpful if the
>> reasons why each person takes the position that they do could be
>> mentioned.
>
> I agree that it'd be nice if others would weigh in on this.

As a general point I'm entirely in favour of removing any superuser
checks and replacing them either with standard GRANT ACL config, or
where appropriate, some other type of permission that we can grant to
roles as needed. Probably the most common complaint I get from users
regarding the management & monitoring tools I work on is that they
have to use superuser accounts to get the full benefits, unlike other
DBMSs where you can create a role with just the required privileges
(or indeed, other DBMSs that ship with such roles pre-defined for
convenience).

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] Mail thread references in commits

[Dave Page]

On Fri, Nov 18, 2016 at 1:38 AM, Alvaro Herrera
<alvhe...@2ndquadrant.com> wrote:
> Tom Lane wrote:
>> Andrew Dunstan <and...@dunslane.net> writes:
>> > I love seeing references to email threads in commit messages. It would
>> > make them a lot friendlier though if a full http URL were included
>> > instead of just a Message-ID,
>>
>> I've intentionally not done that, because it does not seem very
>> future-proof.  The message ids will hopefully be unique indefinitely far
>> into the future, but the location of our archives could move.
>
> It won't.  We have far too many in the archives to risk breaking them.
> In fact, we (Magnus) went great lengths to implement a system so that
> old-style PHP links (of which we have a bunch in very old archives,
> including in commit messages) continue to work to this day.  We're far
> too invested in the system now, because of how successful it has proved
> to be.

Right - we go out of our way to ensure we don't break URLs. That's why
anoncvs.postgresql.org is still actively maintained for example.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] ICU integration

[Dave Page]

On Fri, Sep 9, 2016 at 2:27 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:
> Dave Page <dp...@pgadmin.org> writes:
>> We needed a specific version that was newer than that shipped with
>> RHEL 6 (or in EPEL) iirc.
>
> Sure hope that's not true of the currently-proposed patch :-(

Looking back at my old emails, apparently ICU 5.0 and later include
ucol_strcollUTF8() which avoids the need to convert UTF-8 characters
to 16 bit before sorting. RHEL 6 has the older 4.2 version of ICU.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] ICU integration

[Dave Page]

On Fri, Sep 9, 2016 at 9:02 AM, Devrim Gündüz <dev...@gunduz.org> wrote:
>
> Hi,
>
> On Fri, 2016-09-09 at 09:48 +0800, Craig Ringer wrote:
>> Personally I would be pretty reluctant to package libicu when it's
>> already in RH/Fedora. If it were bundled in Pg's source tree and a
>> private copy was built/installed by the build system so it was part of
>> the main postgresql-server package that'd be different.
>
> Agreed. I did not read the whole thread (yet), but if this is something like
> tzdata, I would personally want to use the libuci supplied by OS, like we do
> for tzdata.
>
> (That said, just checked EDB's ICU support. We currently ship our own libicu
> there, as a part of EPAS, but I don't know the reasoning/history behind 
> there.)

We needed a specific version that was newer than that shipped with
RHEL 6 (or in EPEL) iirc.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] The link to download PostgreSQL 9.6 Beta 2 for Windows X64 is broken (The link downloads Beta 1)

[Dave Page]

On Fri, Jul 1, 2016 at 4:07 PM, Jean-Pierre Pelletier
<jppellet...@e-djuster.com> wrote:
> The link to download PostgreSQL 9.6 Beta 2 for Windows X64
> is not working.
> The link does download something, but it's Beta 1.
>
> http://www.enterprisedb.com/products-services-training/pgdownload#windows

There is an issue with the Windows x64 build with the version of VC++
that's used for the installers. I believe there was a fix committed
for this yesterday by Tom.

The button was supposed to be removed until we get an updated build -
apologies for the inconvenience.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] 10.0

[Dave Page]

> On 13 May 2016, at 17:24, Magnus Hagander <mag...@hagander.net> wrote:
> 
>> On Fri, May 13, 2016 at 5:29 PM, Dave Page <dp...@pgadmin.org> wrote:
>> On Fri, May 13, 2016 at 4:23 PM, Thom Brown <t...@linux.com> wrote:
>> >
>> > Well, one potential issues is that there may be projects which have
>> > already coded in 9.6 checks for feature support.
>> 
>> I suspect that won't be an issue (I never heard of it being for 7.5,
>> which was released as 8.0 - but is smattered all over pgAdmin 3 for
>> example) - largely because in such apps we're almost always checking
>> for a version greater than or less than x.y.
>> 
>> I imagine the bigger issue will be apps that have been written
>> assuming the first part of the version number is only a single digit.
> 
> If they are, they are already broken by design. But more to the point, unless 
> you're arguing for *never* changing to 10.0, that's not really something that 
> should decide when we do it, because they will break.
> 
> We have provided multiple ways to check this. For example, we've had 
> PQserverVersion() since forever which returns an integer that you can just 
> compare. We have never claimed that it would be single digit in any of the 
> fields (first, second *or* third). I honestly don't care at all if those 
> applications break.
> 
> (We would, however, have a problem to go above 100 in all fields *except* the 
> first one, since the integer uses a two-digit representation for each)

Oh, I don't care about such code. Just opining that it's a likely issue 
somewhere - and if so, it should be up to users to fix their apps.

Re: [HACKERS] 10.0

[Dave Page]

On Fri, May 13, 2016 at 5:08 PM, Bruce Momjian <br...@momjian.us> wrote:
> On Fri, May 13, 2016 at 12:05:34PM -0400, Stephen Frost wrote:
>> * Dave Page (dp...@pgadmin.org) wrote:
>> > I imagine the bigger issue will be apps that have been written
>> > assuming the first part of the version number is only a single digit.
>>
>> Let's just go with 2016 instead then.
>>
>> At least then users would see how old the version they're running is (I
>> was just recently dealing with a 8.4 user...).
>
> We tried, that, "Postgres95".  ;-)

Awesome: Postgres16 > Postgres95.

That won't be confusing now will it? :-)

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] 10.0

[Dave Page]

On Fri, May 13, 2016 at 4:23 PM, Thom Brown <t...@linux.com> wrote:
>
> Well, one potential issues is that there may be projects which have
> already coded in 9.6 checks for feature support.

I suspect that won't be an issue (I never heard of it being for 7.5,
which was released as 8.0 - but is smattered all over pgAdmin 3 for
example) - largely because in such apps we're almost always checking
for a version greater than or less than x.y.

I imagine the bigger issue will be apps that have been written
assuming the first part of the version number is only a single digit.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] Windows 7, Visual Studio 2010: building PgAdmin3

[Dave Page]

On Sat, Apr 30, 2016 at 8:11 AM, zeray87 <tiggree...@gmail.com> wrote:
> Hello guys,
> This is my first ever post and here goes my apology for being newbie.
>
> I have been able to build PgAdmin3  after several days of hassle on building
> PgAdmin3 using build-wxmsw.bat.
>
> Now i am trying to build PgAdmin3 using Visual Studio 2010. unfortunately,
> the following error appears:
> Error   1   error MSB4175: The task factory "XamlTaskFactory" could not be
> loaded from the assembly "Microsoft.Build.Tasks.v4.0". Unable to create Xaml
> task.  File not found: C:\pgbuild\pgadmin3-jinfroster\pgAdmin3.xml.
> C:\pgbuild\pgadmin3-jinfroster\pgAdmin3.targets 45  6   pgAdmin3

Visual Studio seems to delete that file when doing a clean, for
reasons I never quite figured out. You can just check it out from the
git repo again and it should be OK.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] Windows 7, Visual Studio 2010: building PgAdmin3

[Dave Page]

On Sun, May 1, 2016 at 10:24 AM, Yury Zhuravlev
<u.zhurav...@postgrespro.ru> wrote:
> zeray87 wrote:
>>
>> Hello guys,
>> This is my first ever post and here goes my apology for being newbie.
>>
>> I have been able to build PgAdmin3  after several days of hassle on
>> building
>> PgAdmin3 using build-wxmsw.bat.
>
>
> If I remember it right for PgAdminIII needed mingw now.
> Thanks.

pgAdmin III has never supported Mingw, it's always used VC++.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] Incomplete startup packet errors

[Dave Page]

On Wed, Apr 13, 2016 at 9:02 AM, Magnus Hagander <mag...@hagander.net> wrote:
> It's fairly common to see a lot of "Incomplete startup packet" in the
> logfiles caused by monitoring or healthcheck connections.
>
> I wonder if it would make sense to only log that error if *at least one
> byte* has been received and then it becomes empty. Meaning that if the
> client just connects+disconnects without sending anything, we don't log
> anything. At least at the default log level (we could have a DEBUG level
> that logged "connection closed immediately").
>
> That would get rid of a lot of logspam.
>
> Would that make sense?

Absolutely. It would be very nice to get rid of such noise.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

[HACKERS] 9.6 Release Schedule

[Dave Page]

Per discussion at the Brussels developer meeting and within the
release team, the high level schedule for 9.6 will be:

Beta: May (before PGCon)
Release: September

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] PostgreSQL Auditing

[Dave Page]

On Tue, Feb 2, 2016 at 1:05 AM, Curtis Ruck
<curtis.ruck+pgsql.hack...@gmail.com> wrote:
> or pay
> EnterpriseDB for their 2 year old version that doesn't have all the
> cool/modern jsonb support.

Just for the record, anyone who pays for our "2 year old version that
doesn't have all the cool/modern jsonb support" is also entitled to
use either our 9.4 or 9.5 versions which do have JSONB support. Our
new versions are typically released within a couple of months of
PostgreSQL, and in the case of 9.5, the gap was more like 3 weeks.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] Remove Windows crash dump support?

[Dave Page]

On Thu, Dec 24, 2015 at 2:14 AM, Craig Ringer <cr...@2ndquadrant.com> wrote:
> On 22 December 2015 at 23:48, Alex Ignatov <a.igna...@postgrespro.ru> wrote:
>
>>
>> I think that you can debug crash dump since windbg exists.
>
>
> Nobody in their right mind uses windbg though. Visual Studio is really where
> it's at and the Express versions make it much more practical.
>
> You can't even install Debugging Tools for Windows and Windbg standalone
> anymore.
>
>>
>> Also I think that Postgres on Windows number  of instalations is so tiny
>> because people even today think that it is not so solid as unix version
>> thats why you think that nobody use your code ;).
>
>
> I disagree. Windows Pg users are often at bigger companies and don't talk
> about PostgreSQL as much. Often for fear of reprisals from other database
> vendors they have ongoing relationships with.  At least that's been my
> experience and I'm sure EDB folks will concur.

In my experience PG isn't used much in production on Windows in bigger
companies. It's used a *lot* (and is quite probably the most
frequently downloaded build from EDB or postgresql.org) as an embedded
database in some applications, and for development/test. There are a
huge number of Windows PostgreSQL users out there.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] El Capitan Removes OpenSSL Headers

[Dave Page]

On Tue, Dec 1, 2015 at 9:55 PM, Bruce Momjian <br...@momjian.us> wrote:
> On Tue, Dec  1, 2015 at 06:40:09PM -0300, Alvaro Herrera wrote:
>> Bruce Momjian wrote:
>>
>> > Do we still have licensing issues if we ship Postgres and OpenSSL
>> > together?
>>
>> See
>> https://www.postgresql.org/message-id/20150801151410.GA28344%40awork2.anarazel.de
>
> True, but the current license is unchanged and has the advertising
> clause, which I think we have to honor if we ship OpenSSL:
>
> https://www.openssl.org/source/license.html
>
> I assume Windows has to ship OpenSSL with the installer and has to abide
> by this, for example.  OSX might have to do the same.  It might be good
> to see what we do for Windows packages.

We already do it for all our installers - Windows, OSX and Linux. We
have to, otherwise we wouldn't be able to ensure the same binaries
would run on all the different supported versions.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] El Capitan Removes OpenSSL Headers

[Dave Page]

On Wed, Dec 2, 2015 at 1:06 PM, Bruce Momjian <br...@momjian.us> wrote:
> On Wed, Dec  2, 2015 at 08:53:07AM +0000, Dave Page wrote:
>> On Tue, Dec 1, 2015 at 9:55 PM, Bruce Momjian <br...@momjian.us> wrote:
>> > On Tue, Dec  1, 2015 at 06:40:09PM -0300, Alvaro Herrera wrote:
>> >> Bruce Momjian wrote:
>> >>
>> >> > Do we still have licensing issues if we ship Postgres and OpenSSL
>> >> > together?
>> >>
>> >> See
>> >> https://www.postgresql.org/message-id/20150801151410.GA28344%40awork2.anarazel.de
>> >
>> > True, but the current license is unchanged and has the advertising
>> > clause, which I think we have to honor if we ship OpenSSL:
>> >
>> > https://www.openssl.org/source/license.html
>> >
>> > I assume Windows has to ship OpenSSL with the installer and has to abide
>> > by this, for example.  OSX might have to do the same.  It might be good
>> > to see what we do for Windows packages.
>>
>> We already do it for all our installers - Windows, OSX and Linux. We
>> have to, otherwise we wouldn't be able to ensure the same binaries
>> would run on all the different supported versions.
>
> OK, good.  So the Mac installers would have to do the same thing if they
> also start shipping OpenSSL too.

OSX == Mac.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: Request for dogfood volunteers (was [HACKERS] No Issue Tracker - Say it Ain't So!)

[Dave Page]

> On 2 Oct 2015, at 17:28, Joshua D. Drake  wrote:
> 
> Hello,
> 
> I believe it is pretty obvious we are moving in the direction of having a 
> tracker at this point. The problem is exactly which direction. Stephen has 
> offered some resources for his ideas and now I am offering some resources for 
> mine.
> 
> I am proposing to setup a redmine instance on a VM. I am happy to do a lot of 
> the legwork and should be able to get most of it done before EU. This is what 
> I think I need from my fellows:
> 
> 1. At least two committers
> 2. At least three hackers (preferably that are different from the committers 
> but not required)
> 3. At least two users
> 
> I think we have reached a point where everyone has ideas of what they do and 
> don't want but none of it matters if we don't have a proof of concept to 
> determine what we do and don't know or want.
> 
> Thoughts? Volunteers?

I swore to myself that I'd stay out of this bikeshed, but... we already have a 
Redmine instance.

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

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

[Dave Page]

On Thu, Sep 3, 2015 at 3:56 PM, Robert Haas <robertmh...@gmail.com> wrote:
> On Thu, Sep 3, 2015 at 4:00 AM, Shulgin, Oleksandr
> <oleksandr.shul...@zalando.de> wrote:
>> I believe that having a floating IP for the master is much more practical
>> approach and it doesn't require any patch to libpq or modification of the
>> client connection settings.
>
> I think that's a great approach if all the machines are on the same
> subnet.  If they are in different datacenters, it doesn't work.
>
> The amount of opposition to this feature is remarkable considering
> that it's available in Oracle, SQL Server, MongoDB, Cassandra, and
> MySQL.  See for example:
>
> http://docs.mongodb.org/manual/reference/connection-string/
> https://datastax.github.io/python-driver/getting_started.html
>
> This is a small patch with minimal to no downside implementing a
> feature that is present in most or all of the major competing
> products.  We're really doing ourselves a disservice if we reject it.
> I think it would be far better to progress to talking about what
> design we'd be comfortable with, rather than kidding ourselves that a
> feature that everyone else has and which somebody has taken the time
> to implement (thus, obviously it has value for them) and which has
> been discussed to general approval at PGCon developer meetings and
> which has been endorsed on this thread by three committers is somehow
> something that nobody really needs.  Seriously?

+100

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] The purpose of the core team

[Dave Page]

On Thu, Jun 11, 2015 at 4:26 PM, Robert Haas robertmh...@gmail.com wrote:

 Timing *decisions* are not made by -core, as I've told you in the
 past. They are made by the packagers who do the actual work, based on
 suggestions from -core.

 You have told me that in the past, and I do not accept that it is true.

 The suggestions from -core are always accepted, or as near as makes no
 difference.  So in effect, -core decides.

No, that means we have some very committed people handling the release
process, who are mostly able to put in the effort on the dates
suggested, and on the odd occasion when they can't for some reason, we
(core and packagers) figure out the best date for everyone involved.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] RFC: Remove contrib entirely

[Dave Page]

On Fri, May 29, 2015 at 7:27 AM, Pavel Stehule pavel.steh...@gmail.com wrote:


 2015-05-29 8:20 GMT+02:00 Guillaume Lelarge guilla...@lelarge.info:

 Le 29 mai 2015 8:10 AM, Pavel Stehule pavel.steh...@gmail.com a écrit
 :
 
  Hi
 
  I am not sure if PGXN can substitute contrib - mainly due deployment -
  It doesn't helps with MS Windows. Installing necessary software for
  compilation there is terrible.
 

 I agree it's hard to compile an extension on Windows, but that's already
 what we have. And I'm sure EDB will put all interesting contrib modules in
 their windows installer to help users. They already go way further than any
 Linux packages.

 I afraid so dependency on EDB in this case is wrong - I have respect to EDB
 due  work, but installation other extension from EDB stack is difficult,
 unclean, and nothing what I would to use as new base.

The five or six mouse clicks required to install something like Slony
or PostGIS (or both at once) is difficult and unclean?

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] RFC: Remove contrib entirely

[Dave Page]

On Fri, May 29, 2015 at 8:50 AM, Pavel Stehule pavel.steh...@gmail.com wrote:


 2015-05-29 9:42 GMT+02:00 Michael Paquier michael.paqu...@gmail.com:

 On Fri, May 29, 2015 at 4:11 PM, Pavel Stehule wrote:
  1. VS requires relatively new MS Windows - problem for people with Ms
  Win 7
  and older

 Really, I use Win 2k8 stuff and Win7 quite a lot.


 On Win 7 you have to search and install now unsupported VS EE 2010.

I've been running 2013 on Windows 7 since it came out. Works perfectly
well, and didn't require any unusual installation.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] RFC: Remove contrib entirely

[Dave Page]

On Fri, May 29, 2015 at 9:35 AM, Dave Page dp...@pgadmin.org wrote:
 On Fri, May 29, 2015 at 7:20 AM, Guillaume Lelarge
 guilla...@lelarge.info wrote:
 Le 29 mai 2015 8:10 AM, Pavel Stehule pavel.steh...@gmail.com a écrit :

 Hi

 I am not sure if PGXN can substitute contrib - mainly due deployment - It
 doesn't helps with MS Windows. Installing necessary software for compilation
 there is terrible.


 I agree it's hard to compile an extension on Windows, but that's already
 what we have. And I'm sure EDB will put all interesting contrib modules in
 their windows installer to help users. They already go way further than any
 Linux packages.

 The only extra extension we ship is pl/debugger.

To clarify - that's the only one we ship in the PostgreSQL installer.
There are many more that we ship (both from EDB and other sources),
but users use StackBuilder to choose, download and install them.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] RFC: Remove contrib entirely

[Dave Page]

On Fri, May 29, 2015 at 7:20 AM, Guillaume Lelarge
guilla...@lelarge.info wrote:
 Le 29 mai 2015 8:10 AM, Pavel Stehule pavel.steh...@gmail.com a écrit :

 Hi

 I am not sure if PGXN can substitute contrib - mainly due deployment - It
 doesn't helps with MS Windows. Installing necessary software for compilation
 there is terrible.


 I agree it's hard to compile an extension on Windows, but that's already
 what we have. And I'm sure EDB will put all interesting contrib modules in
 their windows installer to help users. They already go way further than any
 Linux packages.

The only extra extension we ship is pl/debugger.


-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] RFC: Remove contrib entirely

[Dave Page]

On Fri, May 29, 2015 at 9:55 AM, Pavel Stehule pavel.steh...@gmail.com wrote:


 2015-05-29 10:37 GMT+02:00 Dave Page dp...@pgadmin.org:

 On Fri, May 29, 2015 at 7:27 AM, Pavel Stehule pavel.steh...@gmail.com
 wrote:
 
 
  2015-05-29 8:20 GMT+02:00 Guillaume Lelarge guilla...@lelarge.info:
 
  Le 29 mai 2015 8:10 AM, Pavel Stehule pavel.steh...@gmail.com a
  écrit
  :
  
   Hi
  
   I am not sure if PGXN can substitute contrib - mainly due deployment
   -
   It doesn't helps with MS Windows. Installing necessary software for
   compilation there is terrible.
  
 
  I agree it's hard to compile an extension on Windows, but that's
  already
  what we have. And I'm sure EDB will put all interesting contrib modules
  in
  their windows installer to help users. They already go way further than
  any
  Linux packages.
 
  I afraid so dependency on EDB in this case is wrong - I have respect to
  EDB
  due  work, but installation other extension from EDB stack is difficult,
  unclean, and nothing what I would to use as new base.

 The five or six mouse clicks required to install something like Slony
 or PostGIS (or both at once) is difficult and unclean?


 I had a problem with downloading isolated packages with stackbuilder - but
 it was specific, because comp where some packages was installed was not
 access to internet.

Run SB on a machine that is connected and:

- Select remote server on the first step. This will disable any
filtering based on database server version, which is normally applied
if you're installing to the local machine (it will only offer packages
compatible with the database servers you have installed). This is not
required if you have the appropriate DB server installed on the
internet connected machine as well.

- Make a note of the Download Directory on step 3

- Check Skip installation on step 4.

Copy the downloaded installed from the Download Directory to your
server machine, and run them.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] problems on Solaris

[Dave Page]

On Mon, May 25, 2015 at 1:07 AM, Andres Freund and...@anarazel.de wrote:
 On 2015-05-24 19:44:37 -0400, Andrew Dunstan wrote:

 Buildfarm members casteroides and protosciurus have been having some
 problems that seem puzzling. These animals both run on the same machine, but
 with different compilers.

 casteroides runs with the Sun Studio 12 compiler, and has twice in the last
 3 days demonstrated this error:

[5561ce0c.51b7:25] LOG:  starting background worker process test_shm_mq
[5561ce1e.5287:9] PANIC:  stuck spinlock (100cb77f4) detected at 
 atomics.c:30
[5561ce1e.5287:10] STATEMENT:  SELECT test_shm_mq_pipelined(16384, 
 (select string_agg(chr(32+(random()*95)::int), '') from 
 generate_series(1,27)), 200, 3);
[5561ce0c.51b7:26] LOG:  server process (PID 21127) was terminated by 
 signal 6
[5561ce0c.51b7:27] DETAIL:  Failed process was running: SELECT 
 test_shm_mq_pipelined(16384, (select string_agg(chr(32+(random()*95)::int), 
 '') from generate_series(1,27)), 200, 3);
[5561ce0c.51b7:28] LOG:  terminating any other active server processes

 It's not constant - between the two failures was a success.

 That's indeed rather odd. For one the relevant code does nothing but
 lock/unlock a spinlock. For another, there's been no recent change to
 this and casteroides has been running happily for a long time.

 protociurus runs with gcc 3.4.3 and gets this error:

gcc -Wall -Wmissing-prototypes -Wpointer-arith 
 -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute 
 -Wformat-security -fno-strict-aliasing -fwrapv 
 -Wno-unused-command-line-argument -g -I/usr/local/include -m64 -I. 
 -I../../../src/interfaces/libpq -I./../regress -I../../../src/include   -c 
 -o specparse.o specparse.c
In file included from /usr/include/sys/vnode.h:47,
  from /usr/include/sys/stream.h:22,
  from /usr/include/netinet/in.h:66,
  from /usr/include/netdb.h:98,
  from ../../../src/include/port.h:17,
  from ../../../src/include/c.h:1114,
  from ../../../src/include/postgres_fe.h:25,
  from specparse.y:13:
/usr/include/sys/kstat.h:439: error: syntax error before numeric constant
/usr/include/sys/kstat.h:463: error: syntax error before '}' token
/usr/include/sys/kstat.h:464: error: syntax error before '}' token
In file included from /usr/include/sys/stream.h:22,
  from /usr/include/netinet/in.h:66,
  from /usr/include/netdb.h:98,
  from ../../../src/include/port.h:17,
  from ../../../src/include/c.h:1114,
  from ../../../src/include/postgres_fe.h:25,
  from specparse.y:13:
/usr/include/sys/vnode.h:105: error: syntax error before kstat_named_t

 I'd noticed this one as well. This sounds like a installation problem,
 not really ours. Dave, any chance you could look into this, or give
 somebody an account to test what's up?

I'm not going to be able to look at this, at least this week. I can
give someone on the EDB team access - Robert; can one of your guys
take a look?

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] pgAdmin4 Bug fix or my Fault ?

[Dave Page]

Hi

On Thu, May 14, 2015 at 7:44 AM, Seçkin Alan seckina...@gmail.com wrote:
 Bruce,
 Thank you for webpage trick, I send subscrible mail to
 pgadmin-hackers-request.

 Actually I want to developer for pgadmin4.
 I talk with Dave,
 He say, ... You should use git
 diff to create patches and then submit them to
 pgadmin-hackers ...

pgadmin-hack...@postgresql.org != pgsql-hackers@postgresql.org :-)

 So,
 If I want run pgadmin4 in Debian Jessie, need the patch code.

 I send git diff beacause,
 cant run pgadmin4-git-cloned version in jessie.
 There are really bugs?
 or
 is it my fault ?


 Sorry my English is not good,
 I hope you understood my problem.




 On Thu, May 14, 2015 at 2:32 AM, Bruce Momjian br...@momjian.us wrote:

 I think you want PGAdmin support:

 http://www.pgadmin.org/support/list.php

 Also, why isn't the non-subscribe email address listed on that webpage?

 ---

 On Thu, May 14, 2015 at 12:11:15AM +0300, Seçkin Alan wrote:
 Hi,
 I am using Debian Jessie and install pgAdmin4 Required modules.
 after I clone pgAdmin4 from
 http://git.postgresql.org/gitweb/?p=pgadmin4.git;a=summary ,

 First of, ıf I want run setup.py, I must fix bug .
 after I want run pgadmin4.py, I must fix gravatar import line.

 --
   Bruce Momjian  br...@momjian.ushttp://momjian.us
   EnterpriseDB http://enterprisedb.com

   + Everyone has their own god. +



 --
 Seçkin ALAN
 http://sckn.org



-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] proposal: contrib module - generic command scheduler

[Dave Page]

On Tue, May 12, 2015 at 10:25 AM, Pavel Stehule pavel.steh...@gmail.com wrote:
 Generic simple scheduler to contrib
 ===
 Job schedulers are important and sometimes very complex part of any
 software. PostgreSQL miss it. I propose new contrib module, that can be used
 simply for some tasks, and that can be used as base for other more richer
 schedulers. I prefer minimalist design - but strong enough for enhancing
 when it is necessary. Some complex logic can be implemented in PL better
 than in C. Motto: Simply to learn, simply to use, simply to customize.

 Motivation
 --
 Possibility to simplify administration of repeated tasks. Possibility to
 write complex schedulers in PL/pgSQL or other PL.

 Design
 --
 Any scheduled command will be executed in independent worker. The number
 workers for one command can be limited. Total number of workers will be
 limited. Any command will be executed under specified user with known
 timeout in current database. Next step can be implementation global
 scheduler - but we have not a environment for running server side global
 scripts, so I don't think about it in this moment.

 This scheduler does not guarantee number of executions. Without available
 workers the execution will be suspended, after crash the execution can be
 repeated. But it can be solved in upper layer if it is necessary. It is not
 designed as realtime system. Scheduled task will be executed immediately
 when related worker will be free, but the execution window is limited to
 next start.

 This design don't try to solve mechanism for repeating tasks when tasks hash
 a crash. This can be solved better in PL on custom layer when it is
 necessary.

 Scheduled time is stored to type scheduled_time:

 create type scheduled_time as (second int[], minute int[], hour int[], dow
 int[], month int[]);

  (,{1,10,20,30,40,50},,,) .. run every 10 minutes.
  (,{5},,,) .. run once per hour

 The core is table pg_scheduled_commands

 Oid:   1
 name:
 user:  pavel
 stime:(,{5},,,)
 max_workers: 1
 timeout:  10s
 command: SELECT plpgsql_entry(scheduled_time(), scheduled_command_oid())


 set timeout to 0 ~ unlimited, -1 default statement_timeout
 set max_workers to 0 ~ disable tasks

 API
 ---
 pg_create_scheduled_command(name,
  stime,
  command,
  user default current_user,
  max_workers default 1,
  timeout default -1);

 pg_drop_scheduled_command(oid)
 pg_drop_scheduled_command(name);

 pg_update_scheduled_command(oid | name, ...

 Usage:
 --
 pg_create_scheduled_command('delete obsolete data', '(,,{1},,)', $$DELETE
 FROM data WHERE inserted  current_timestamp - interval '1month'$$);
 pg_update_scheduled_command('delete obsolete data', max_workers = 2,
 timeout := '1h');
 pg_drop_scheduled_command('delete obsolete data');

 select * from pg_scheduled_commands;


 Comments, notices?

It's not integrated with the server (though it is integrated with
pgAdmin), but pgAgent provides scheduling services for PostgreSQL
already, offering multi-schedule, multi-step job execution.

http://www.pgadmin.org/docs/1.20/pgagent.html

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] contrib/fuzzystrmatch/dmetaphone.c license

[Dave Page]

On Tue, Feb 24, 2015 at 12:24 PM, Heikki Linnakangas
hlinnakan...@vmware.com wrote:
 contrib/fuzzystrmatch/dmetaphone.c says this:

 /* COPYRIGHT NOTICES ***

 Most of this code is directly from the Text::DoubleMetaphone perl module
 version 0.05 available from http://www.cpan.org.
 It bears this copyright notice:


   Copyright 2000, Maurice Aubrey maur...@hevanet.com.
   All rights reserved.

   This code is based heavily on the C++ implementation by
   Lawrence Philips and incorporates several bug fixes courtesy
   of Kevin Atkinson kev...@users.sourceforge.net.

   This module is free software; you may redistribute it and/or
   modify it under the same terms as Perl itself.


 Is that OK? Perl is dual-licensed under the GPL and the Artistic License,
 so the question is whether the Artistic License is compatible with the
 PostgreSQL license. IANAL, but I couldn't immediately figure out what the
 Artistic License requires, when you pick a piece of code and modify and
 embed it in another project.

My belief (as someone who is not a lawyer, but has spent a fair bit of
time working with them on such issues) is that it is not compatible.
The licence requires derivative works to retain the licence
properties, which have requirements that go well beyond those of our
licence, however, as you point out it's far from clear whether lifting
a piece of code would be subject to those restrictions, or be covered
by clause 8/9 (do we expose a direct interface to this functionality?)
which potentially allow the original licence to be dropped from
derivative works.

It's largely because of such uncertainties that I have been advised in
the past (by those with appropriate letters after their names) to stop
using the Artistic licence. This is why I spent nearly a year working
on changing pgAdmin to the PostgreSQL licence.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] [pgsql-packagers] Palle Girgensohn's ICU patch

[Dave Page]

On Thu, Nov 27, 2014 at 9:09 AM, Jakob Egger ja...@eggerapps.at wrote:

 Am 26.11.2014 um 17:46 schrieb Geoff Montee geoff.mon...@gmail.com:
  This topic reminds me of a thread from a couple months ago:
 
 
 http://www.postgresql.org/message-id/f8268db6-b50f-429f-8289-da8ffa5f2...@tripadvisor.com
 
  It sounds like adding ICU support to core may also allow for adding
  collation versioning to indexes.

 Reading through this thread it becomes clear to me that adding support for
 ICU is more important than I thought, and the only problem is that no one
 has yet volunteered for it :)

 I've started looking through the PostgreSQL source and Palle's patch to
 estimate what needs to be done.

 MINIMUM TODO
 

 * Add support for per-column collations in varstr_comp() in varlena.c.
 Currently the patch creates a single ICU collator for the default collation
 and stores it in a static variable. We would need to change this to create
 collators for each collation and store them in a hash table similar to
 pg_newlocale_from_collation() / lookup_collation_cache()

 * There's a new feature in trunk for faster sorting using SortSupport, so
 we would also need to also patch bttextfastcmp_locale() in varlena.c

 These two changes would allow using ICU for collation. This has two major
 advantages:
 1) Systems with broken strcoll like OS X and FreeBSD can take advantage of
 ICU to offer proper text sorting
 2) You can link with a specific version of ICU to avoid index corruption
 and duplicate keys caused by changing implementations of the glibc strcoll
 function


 NEXT STEPS: Support for more collations
 ===

 ICU offers a lot more collations than the OS. For example, besides de_CH
 it also offers de_CH@collation=phonebook. Adding support for these is a
 bit more involved.

 * initdb would need to be extended to also look for collations offered by
 ICU and add them to the pg_collation catalog.

 * A special case for LC_COLLATE must be added to check_locale() in the
 backend, get_canonical_locale_name() in pg_upgrade, check_locale_name() in
 initdb to support collations provided by ICU

 * pg_perm_setlocale() must get a special case to handle ICU collations

 * the local handling code in pgperl must be modified (when using a ICU
 collation as default collation, we must decide what collation to send to
 perl)

 * convert_string_datum() in selfuncs.c could be patched to use ICU instead
 of strxfrm. However, as far as I understand, this is not absolutely
 required as this is only used by the query planner and would in the worst
 case prevent some optimisation in corner cases

 These changes would probably have an even bigger impact, because then
 people would no longer be limited to the collations supported by the
 locales installed on their OS.

 NEXT STEPS: Collation versioning in indices
 ===

 Since ICU provides reliable versioning of collations, this would allow us
 to finally prevent index corruption caused by changing implementations of
 strcoll. I haven't looked at this in detail, but I assume that this would
 be a small change with potentially big impact.

 Ideally, PostgreSQL would detect when the collation is a different version
 than the one used to create the index, and stop using the index until it is
 rebuilt.


 I'll take a shot at the MINIMUM TODO as outlined above.


We've already included ICU support in our Postgres Plus Advanced Server
product. Before you spend too much time on this, give me a few days to see
if we can get that change contributed back. The people I need to speak to
are OOO for Thanksgiving at the moment though, so it may be a few days.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: [HACKERS] [pgsql-packagers] Palle Girgensohn's ICU patch

[Dave Page]

On Wed, Nov 26, 2014 at 9:48 AM, Jakob Egger ja...@eggerapps.at wrote:

 Bear in mind that this might alter the way indexes are built. From the top 
 of my head, I just can't remember if this is true or not. I'm probably 
 wrong? Magnus? You would have to try.

 That's why I want to include it in the first version of 9.4, when people need 
 to dump  reload their database anyway (I'll make a note not to use 
 pg_upgrade)

You may want to bear in mind that postgres.app is on the main PG
downloads page on the website. If you're patching Postgres to add a
feature like this, it would become a fork and would have to be moved
out of the PostgreSQL Core Distribution section of the download area
as we only include pure distributions there.

-- 
Dave Page
PostgreSQL Core Team
http://www.postgresql.org/


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

Re: [HACKERS] [pgsql-packagers] Palle Girgensohn's ICU patch

[Dave Page]

On Wed, Nov 26, 2014 at 10:13 AM, Jakob Egger ja...@eggerapps.at wrote:
 Am 26.11.2014 um 11:05 schrieb Dave Page dp...@postgresql.org:

 You may want to bear in mind that postgres.app is on the main PG
 downloads page on the website. If you're patching Postgres to add a
 feature like this, it would become a fork and would have to be moved
 out of the PostgreSQL Core Distribution section of the download area
 as we only include pure distributions there.


 I wasn't aware of this. I'll have to bring this up on the Postgres.app
 Github page.

 Personally, I don't think that shipping a database with broken text sorting
 is acceptable; but I can't speak on behalf of the other contributors to
 Postgres.app without consulting them first.

Right - but the correct course of action would be to get the problem
fixed in PostgreSQL itself, not to fork the code which could lead to
other problems for users.

-- 
Dave Page
PostgreSQL Core Team
http://www.postgresql.org/


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

Re: [HACKERS] Getting rid of accept incoming network connections prompts on OS X

[Dave Page]

On Fri, Oct 24, 2014 at 7:18 AM, Peter Eisentraut pete...@gmx.net wrote:
 On 10/21/14 1:16 PM, Tom Lane wrote:
 If you do any Postgres development on OS X, you've probably gotten
 seriously annoyed by the way that, every single time you reinstall the
 postmaster executable, you get a dialog box asking whether you'd like
 to allow it to accept incoming network connections.

 I used to, but somehow I don't see this anymore.  Just to be sure, I
 made sure the firewall is on, checked that postgres is not in the
 exception list, rebooted, built postgresql from scratch, ran make check,
 but no pop-up.

 I'm on Yosemite.  Maybe this was changed.

I've never seen it on any version of OS X (I've worked my way from
Panther to Yosemite). There must be more to it...

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] narwhal and PGDLLIMPORT

[Dave Page]

On Tue, Oct 14, 2014 at 11:38 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 Alvaro Herrera alvhe...@2ndquadrant.com writes:
 It seems we left this in broken state.  Do we need to do more here to
 fix narwhal, or do we want to retire narwhal now?  Something else?  Are
 we waiting on someone in particular to do something specific?

 I think we're hoping that somebody will step up and investigate how
 narwhal's problem might be fixed.  However, the machine's owner (Dave)
 doesn't appear to have the time/interest to do that.  That means that
 our realistic choices are to retire narwhal or revert the linker changes
 that broke it.  Since those linker changes were intended to help expose
 missing-PGDLLIMPORT bugs, I don't much care for the second alternative.

It's a time issue right now I'm afraid (always interested in fixing bugs).

However, if fixing it comes down to upgrading the seriously old
compiler and toolchain on that box (which frankly is so obsolete, I
can't see why anyone would want to use anything like it these days),
then I think the best option is to retire it, and replace it with
Windows 2012R2 and a modern release of MinGW/Msys which is far more
likely to be similar to what someone would want to use at present.

Does anyone really think there's a good reason to keep maintaining
such an obsolete animal?

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] RLS feature has been committed

[Dave Page]

On Tue, Sep 23, 2014 at 4:25 AM, Robert Haas robertmh...@gmail.com wrote:
 On Mon, Sep 22, 2014 at 7:22 PM, Peter Geoghegan p...@heroku.com wrote:
 On Mon, Sep 22, 2014 at 4:02 PM, Andres Freund and...@anarazel.de wrote:
 This patch has been pushed in a clear violation of established policy.

 Fundamental pieces of the patch have changed *after* the commitfest
 started. And there wasn't a recent patch in the commitfest either - the
 entry was moved over from the last round without a new patch.  It didn't
 receive independent review (Robert explicitly said his wasn't a full
 review).  It wasn't marked ready for committer.  The intention to commit
 wasn't announced publically.  There were *clear* and unaddressed
 objections to committing the patch as is, by a committer (Robert)
 nonetheless.

 I have no reason to doubt your version of events here

 Fortunately, you don't have to take anything on faith.  This is a
 public mailing list, and the exact sequence of events is open to
 inspection by anyone who cares to take a few minutes to do so.  You
 can easily verify whether my statement that I asked Stephen twice to
 hold off committing it is correct or not; and you can also verify the
 rest of the history that Andres and I recounted.  This is all there in
 black and white.

Just to be clear here, the *only* issue we should even be discussing
is whether the patch should or should not have been committed in the
face of those objections. As Josh has also noted, the commitfest
process was never meant to constrain what committers do or when they
do it with their own patches or ones they've worked heavily on. They
are there as a backstop to make sure that regardless of what the
committers are doing day to day, patch authors know that their patch
is expected to receive some review within N weeks.


-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] RLS feature has been committed

[Dave Page]

On Tue, Sep 23, 2014 at 2:00 PM, Andres Freund and...@anarazel.de wrote:
 On 2014-09-23 13:23:32 +0100, Dave Page wrote:
 Just to be clear here, the *only* issue we should even be discussing
 is whether the patch should or should not have been committed in the
 face of those objections. As Josh has also noted, the commitfest
 process was never meant to constrain what committers do or when they
 do it with their own patches or ones they've worked heavily on. They
 are there as a backstop to make sure that regardless of what the
 committers are doing day to day, patch authors know that their patch
 is expected to receive some review within N weeks.

 FWIW, while not really at the core of the problem here, I don't think
 this is entirely true anymore.

I'm not aware that we've made any such changes since the process was
originally developed. The fact that developers may constrain their own
review/commit work to certain periods is a personal choice, not policy
or requirement.

 We certainly seem to to expect bigger feature patches to go through the
 commitfest process to some degree. Just look at the discussions about
 *committers* patches being committed or not at each cycles last
 commitfest. Every single time the point in time they've been submitted
 to which CF plays a rather prominent role in the discussion.

They should be tracked on the app certainly, but that doesn't prevent
review/commits being made outside of the commitfest.

 Also look at committers like Robert that *do* feel constrained about
 when to commit or even expect review for submitted patches.

Regardless of what Robert may feel, review should only generally be
*expected* during a commitfest, but it can be done at any time.
Committers are free to commit at any time. The process was never
intended to restrict what committers do or when - in fact when I
introduced the process to -hackers, it was specifically worded to say
that developers are strongly encouraged to take part in the commitfest
reviews, but not forced to, and may continue to work on their patches
as they see fit.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] RLS feature has been committed

[Dave Page]

On Tue, Sep 23, 2014 at 4:19 PM, Robert Haas robertmh...@gmail.com wrote:
 On Tue, Sep 23, 2014 at 11:16 AM, Dave Page dp...@pgadmin.org wrote:
 Regardless of what Robert may feel, review should only generally be
 *expected* during a commitfest, but it can be done at any time.
 Committers are free to commit at any time. The process was never
 intended to restrict what committers do or when - in fact when I
 introduced the process to -hackers, it was specifically worded to say
 that developers are strongly encouraged to take part in the commitfest
 reviews, but not forced to, and may continue to work on their patches
 as they see fit.

 So, just to be clear, you're saying that committers are free to commit
 things even when other community members (who may themselves be
 committers) ask them not to?

At what point did I say anything like that? Do not twist my words.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] What in the world is happening with castoroides and protosciurus?

[Dave Page]

On Sat, Aug 30, 2014 at 11:32 PM, Noah Misch n...@leadboat.com wrote:
 On Tue, Aug 26, 2014 at 10:17:05AM +0100, Dave Page wrote:
 On Tue, Aug 26, 2014 at 1:46 AM, Tom Lane t...@sss.pgh.pa.us wrote:
  For the last month or so, these two buildfarm animals (which I believe are
  the same physical machine) have been erratically failing with errors that
  reflect low-order differences in floating-point calculations.
 
  A recent example is at
 
  http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=protosciurusdt=2014-08-25%2010%3A39%3A52
 
  where the only regression diff is
 
  *** 
  /export/home/dpage/pgbuildfarm/protosciurus/HEAD/pgsql.22860/src/test/regress/expected/hash_index.out
 Mon Aug 25 11:41:00 2014
  --- 
  /export/home/dpage/pgbuildfarm/protosciurus/HEAD/pgsql.22860/src/test/regress/results/hash_index.out
  Mon Aug 25 11:57:26 2014
  ***
  *** 171,179 
SELECT h.seqno AS i8096, h.random AS f1234_1234
   FROM hash_f8_heap h
   WHERE h.random = '-1234.1234'::float8;
  !  i8096 | f1234_1234
  ! ---+
  !   8906 | -1234.1234
(1 row)
 
UPDATE hash_f8_heap
  --- 171,179 
SELECT h.seqno AS i8096, h.random AS f1234_1234
   FROM hash_f8_heap h
   WHERE h.random = '-1234.1234'::float8;
  !  i8096 |f1234_1234
  ! ---+---
  !   8906 | -1234.12356777216
(1 row)
 
UPDATE hash_f8_heap
 
  ... a result that certainly makes no sense.  The results are not
  repeatable, failing in equally odd ways in different tests on different
  runs.  This is happening in all the back branches too, not just HEAD.

 I have
 no idea what is causing the current issue - the machine is stable
 software-wise, and only has private builds of dependency libraries
 update periodically (which are not used for the buildfarm). If I had
 to hazard a guess, I'd suggest this is an early symptom of an old
 machine which is starting to give up.

 Agreed.  Rerunning each animal against older commits would test that theory.
 Say, run against the last 6 months of REL9_0_STABLE commits.  If those runs
 show today's failure frequencies instead of historic failure frequencies, it's
 not a PostgreSQL regression.  Not that I see a commit back-patched near the
 time of the failure uptick (2014-08-06) that looks remotely likely to have
 introduced such a regression.

 It would be sad to lose our only buildfarm coverage of plain Solaris and of
 the Sun Studio compiler, but having buildfarm members this unstable is a pain.
 Perhaps have those animals retry the unreliable steps up to, say, 7 times?

That would require changes to the buildfarm client. I'll see if I can
find some alternate resources we can use.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] What in the world is happening with castoroides and protosciurus?

[Dave Page]

On Tue, Aug 26, 2014 at 1:46 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 For the last month or so, these two buildfarm animals (which I believe are
 the same physical machine) have been erratically failing with errors that
 reflect low-order differences in floating-point calculations.

 A recent example is at

 http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=protosciurusdt=2014-08-25%2010%3A39%3A52

 where the only regression diff is

 *** 
 /export/home/dpage/pgbuildfarm/protosciurus/HEAD/pgsql.22860/src/test/regress/expected/hash_index.out
Mon Aug 25 11:41:00 2014
 --- 
 /export/home/dpage/pgbuildfarm/protosciurus/HEAD/pgsql.22860/src/test/regress/results/hash_index.out
 Mon Aug 25 11:57:26 2014
 ***
 *** 171,179 
   SELECT h.seqno AS i8096, h.random AS f1234_1234
  FROM hash_f8_heap h
  WHERE h.random = '-1234.1234'::float8;
 !  i8096 | f1234_1234
 ! ---+
 !   8906 | -1234.1234
   (1 row)

   UPDATE hash_f8_heap
 --- 171,179 
   SELECT h.seqno AS i8096, h.random AS f1234_1234
  FROM hash_f8_heap h
  WHERE h.random = '-1234.1234'::float8;
 !  i8096 |f1234_1234
 ! ---+---
 !   8906 | -1234.12356777216
   (1 row)

   UPDATE hash_f8_heap

 ... a result that certainly makes no sense.  The results are not
 repeatable, failing in equally odd ways in different tests on different
 runs.  This is happening in all the back branches too, not just HEAD.

 Has there been a system software update on this machine a month or so ago?
 If not, it's hard to think anything except that the floating point
 hardware on this box has developed problems.

There hasn't been a software update, but something happened about two
months ago, and we couldn't get to the bottom of exactly what it was -
essentially, castoroides started failing with C compiler cannot
create executables. It appeared that the compiler was missing from
the path, however the config hadn't changed. Our working theory is
that there was previously a symlink to the compiler in one of the
directories in the path, that somehow got removed. The issue was fixed
by adding the actual compiler location to the path.

However, that would have only affected castoroides, and not
protosciurus which runs under a different environment config. I have
no idea what is causing the current issue - the machine is stable
software-wise, and only has private builds of dependency libraries
update periodically (which are not used for the buildfarm). If I had
to hazard a guess, I'd suggest this is an early symptom of an old
machine which is starting to give up.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] Are postgresql-9.4 binaries available on Windows XP?

[Dave Page]

Hi

On Fri, Aug 22, 2014 at 4:45 AM, Hiroshi Inoue in...@tpf.co.jp wrote:
 Hi Dave and Andrew,

 I recently noticed the thread
   [BUGS] BUG #11039: installation fails when trying to install C++
 redistributable .

 Unfortunately I have no XP machine at hand and can't test the
 installer by myself.

 Looking at the binaries in the package, they seem to be built using
 Visual Studio 2013. I'm suspicious if the binaries are available on
 Windows XP.

 If I recognize correctly, Visual Studio 2012 or later doesn't support
 Windows XP by default and Platform Toolset v120_xp (or v110_xp) must
 be specified so as to build binaries guaranteed to be avaiable on
 Windows XP. However MSBuildProject.pm seems to specify v120 (or v110) as
 the PlarformToolset property. Is it intentional?

I can't say whether that was an intentional change or not as I wasn't
involved in writing that patch, but I can say that EDB will not be
supporting Windows XP for our future builds now that it's been
(finally) made obsolete by Microsoft when extended support ended in
April.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] Possible fix for occasional failures on castoroides etc

[Dave Page]

On Sat, May 3, 2014 at 8:29 PM, Andres Freund and...@2ndquadrant.com wrote:
 On 2014-05-03 13:25:32 -0400, Tom Lane wrote:
 Andres Freund and...@2ndquadrant.com writes:
  On 2012-09-17 08:23:01 -0400, Dave Page wrote:
  I've added MAX_CONNECTIONS=5 to both Castoroides and Protosciurus.

  I've just noticed (while checking whether backporting 4c8aa8b5aea caused
  problems) that this doesn't seem to have fixed the issue. One further
  thing to try would be to try whether tcp connections don't have the same
  problem.

 I did some googling on this, and found out that people have seen identical
 behavior on Solaris with mysql and other products, so at least we're not
 alone.

 Yea, I found a couple report of that as well.

  Googling also reminded me that we could have a look at the source
 (duh), which is still available from hg.openindiana.org.

 I didn't get that far ;)

 I think we should try whether the problem disappears if tcp connections
 are used. That ought to be much more heavily used in the real
 world. Thus less likely to be buggy.

 While It's not documented as such, passing --host=localhost to
 pg_regress seems to have the desired effect. Dave, could you make your
 animal specify that?

I've added:

EXTRA_REGRESS_OPTS = '--host=localhost',

to the build_env setting for both animals.


-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] Sending out a request for more buildfarm animals?

[Dave Page]

Hamid@EDB; Can you please have someone configure anole to build git
head as well as the other branches? Thanks.

Andres, Andrew; I think the only other gap EDB could fill at the
moment is RHEL6 on Power7 (though we do have a couple of Power8 boxes
on order that should be here pretty soon). Dotterel is building some
branches (including head). I'm not sure what generation of Power CPU
that box has. Bernd?

On Fri, May 2, 2014 at 4:04 PM, Andres Freund and...@2ndquadrant.com wrote:
 Hi,

 There's pretty little coverage of non mainstream platforms/compilers in
 the buildfarm atm. Maybe we should send an email on -announce asking for
 new ones?
 There's no coverage for OS-wise;
 * AIX (at all)
 * HP-UX (for master at least)
 (* Tru64)
 (* UnixWare)

 Architecture wise there's no coverage for:
 * some ARM architecture varians
 * mips
 * s390/x
 * sparc 32bit
 (* s390)
 (* alpha)
 (* mipsel)
 (* M68K)

 A couple of those aren't that important (my opinion indicated by ()),
 but the other ones really should be covered or desupported.

 Greetings,

 Andres Freund

 --
  Andres Freund http://www.2ndQuadrant.com/
  PostgreSQL Development, 24x7 Support, Training  Services



-- 
Dave Page
Chief Architect, Tools  Installers
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake


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

Re: [HACKERS] Securing make check (CVE-2014-0067)

[Dave Page]

 On 2 Mar 2014, at 05:20, Noah Misch n...@leadboat.com wrote:
 
 On Sat, Mar 01, 2014 at 05:51:46PM -0500, Andrew Dunstan wrote:
 On 03/01/2014 05:10 PM, Tom Lane wrote:
 One other thought here: is it actually reasonable to expend a lot of effort
 on the Windows case?  I'm not aware that people normally expect a Windows
 box to have multiple users at all, let alone non-mutually-trusting users.
 
 As Stephen said, it's fairly unusual. There are usually quite a few
 roles, but it's rare to have more than one human type role
 connected to the machine at a given time.
 
 I, too, agree it's rare.  Rare enough to justify leaving the vulnerability
 open on Windows, indefinitely?

It's not that rare in my experience - certainly there are far more single user 
installations, but Terminal Server configurations are common for deploying apps 
Citrix-style or VDI. The one and only Windows server maintained by the EDB 
infrastructure team is a terminal server for example.

  I'd say not.  Windows itself has been pushing
 steadily toward better multi-user support over the past 15 years or so.
 Releasing software for Windows as though it were a single-user platform is
 backwards-looking.  We should be a model in this area, not a straggler.

Definitely.

 
 I'd be happy doing nothing in this case, or not very much. e.g.
 provide a password but not with great cryptographic strength.
 
 One option that would simplify things is to fix only non-Windows in the back
 branches, via socket protection, and fix Windows in HEAD only.  We could even
 do so by extending HAVE_UNIX_SOCKETS support to Windows through named pipes.
 
 Using weak passwords on Windows alone would not simplify the effort.
 
 -- 
 Noah Misch
 EnterpriseDB http://www.enterprisedb.com
 
 
 -- 
 Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-hackers


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

Re: [HACKERS] narwhal and PGDLLIMPORT

[Dave Page]

On Fri, Feb 14, 2014 at 5:32 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 I wrote:
 Hiroshi Inoue in...@tpf.co.jp writes:
 One thing I'm wondering about is that plperl is linking perlxx.lib
 not libperlxx.a. I made a patch following plpython and it also
 works here.
 Is it worth trying?

 I hadn't noticed that part of plpython's Makefile before.  Man,
 that's an ugly technique :-(.  Still, there's little about this
 platform that isn't ugly.  Let's try it and see what happens.

 And what happens is this:
 http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=narwhaldt=2014-02-14%2017%3A00%3A02
 namely, it gets through plperl now and then chokes with the same
 symptoms on pltcl.  So I guess we need the same hack in pltcl.
 The fun never stops ...

 (BTW, narwhal is evidently not trying to build plpython.  I wonder
 why not?)

Not sure - it's certainly installed on the box. I've enabled it for
now, and will see what happens.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] narwhal and PGDLLIMPORT

[Dave Page]

On Mon, Feb 17, 2014 at 2:58 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 Dave Page dp...@pgadmin.org writes:
 On Fri, Feb 14, 2014 at 5:32 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 (BTW, narwhal is evidently not trying to build plpython.  I wonder
 why not?)

 Not sure - it's certainly installed on the box. I've enabled it for
 now, and will see what happens.

 Sigh ... stop the presses.

 In 9.3, narwhal is *still* showing a PGDLLIMPORT-type failure that no
 other Windows critter is unhappy about:

 dlltool --export-all --output-def worker_spi.def worker_spi.o
 dllwrap -o worker_spi.dll --def worker_spi.def worker_spi.o -L../../src/port 
 -L../../src/common -Wl,--allow-multiple-definition -L/mingw/lib  
 -Wl,--as-needed   -L../../src/backend -lpostgres
 Info: resolving _MyBgworkerEntry by linking to __imp__MyBgworkerEntry 
 (auto-import)
 fu01.o(.idata$3+0xc): undefined reference to `libpostgres_a_iname'
 fu02.o(.idata$3+0xc): undefined reference to `libpostgres_a_iname'
 nmth00.o(.idata$4+0x0): undefined reference to `_nm__MyBgworkerEntry'
 collect2: ld returned 1 exit status

 So we are back to square one AFAICS: we still have no idea why narwhal
 is pickier than everything else.  (BTW, to save people the trouble of
 looking: MyBgworkerEntry is marked PGDLLIMPORT in HEAD but not 9.3.)

 Also, in HEAD narwhal is building things OK, but then seems to be
 dumping core in the dblink regression test, leaving one with not a very
 warm feeling about whether the contrib executables it's building are
 any good.

Well, as we know, Narwhal is really quite old now. I think I built it
seven+ years ago. Is it really worth banging heads against walls to
support something that noone in their right mind should be using for a
build these days?


-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] narwhal and PGDLLIMPORT

[Dave Page]

On Sun, Feb 2, 2014 at 3:59 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 Dave Page dp...@pgadmin.org writes:
 On Sun, Feb 2, 2014 at 1:03 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 I think we should give serious consideration to desupporting this
 combination so that we can get rid of the plague of PGDLLIMPORT
 marks.

 No objection here - though I should point out that it's not been
 offline for a long time (aside from a couple of weeks in January) -
 it's been happily building most pre-9.2 branches for ages. 9.1 seems
 to be stuck, along with HEAD, and I forgot to add 9.3. I'm in the
 process of cleaning that up as time allows, but am happy to drop it
 instead if we no longer want to support anything that old. We
 certainly don't use anything resembling that config for the EDB
 installer builds.

 Further discussion pointed out that currawong, for example, seems to
 want PGDLLIMPORT markings but is able to get by without them in
 some cases that narwhal evidently doesn't like.  So at this point,
 desupporting narwhal's configuration is clearly premature --- we
 should instead be looking into exactly what is causing the different
 cases to fail or not fail.

 I still have hopes that we might be able to get rid of PGDLLIMPORT
 marks, but by actually understanding why they seem to be needed in
 some cases and not others, not by just arbitrarily dropping support.

 In the meantime, please do get HEAD running again on that machine.

Done: 
http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=narwhaldt=2014-02-03%2009%3A26%3A43

It's not happy though :-(

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] narwhal and PGDLLIMPORT

[Dave Page]

On Sun, Feb 2, 2014 at 1:03 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 I happened to notice today that the owner of buildfarm member narwhal
 is trying to revive it after a long time offline, but it's failing in
 the 9.3 branch (and not attempting to build HEAD, yet).  The cause
 appears to be that contrib/postgres_fdw is referencing the DateStyle
 and IntervalStyle variables, which aren't marked PGDLLIMPORT.
 Hm, well, that would be an easy change ... but that code was committed
 last March.  How is it that we didn't notice this long ago?

 What this seems to indicate is that narwhal is the only buildfarm
 animal that has a need for PGDLLIMPORT marks on global variables that
 are referenced by extensions.  Furthermore, nobody has attempted to
 build 9.3 on a platform that needs that (or at least they've not
 reported failure to us).

 According to the buildfarm database, narwhal is running a gcc build on
 Windows 2003.  That hardly seems like a mainstream use case.  I could
 believe that it might be of interest to developers, but clearly no
 developers are actually running such a build.

 I think we should give serious consideration to desupporting this
 combination so that we can get rid of the plague of PGDLLIMPORT
 marks.  Obviously this would depend on confirming that there are
 no more-interesting Windows build methods that require it --- but
 if there are any, I'd sure demand that there be an active buildfarm
 instance to keep us from breaking the case again in future.

No objection here - though I should point out that it's not been
offline for a long time (aside from a couple of weeks in January) -
it's been happily building most pre-9.2 branches for ages. 9.1 seems
to be stuck, along with HEAD, and I forgot to add 9.3. I'm in the
process of cleaning that up as time allows, but am happy to drop it
instead if we no longer want to support anything that old. We
certainly don't use anything resembling that config for the EDB
installer builds.

I'm happy to replace it with something newer as time allows - what do
we consider to be the biggest gap? I have an MSDN subscription, so can
do any versions of Windows or VC++ (and obviously Mingw).

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] proposal: hide application_name from other users

[Dave Page]

On Tue, Jan 28, 2014 at 8:17 PM, Stephen Frost sfr...@snowman.net wrote:
 Greg,

 * Greg Stark (st...@mit.edu) wrote:
 On Tue, Jan 28, 2014 at 11:56 AM, Josh Berkus j...@agliodbs.com wrote:
  For example, I would really like to GRANT an unpriv user access to the
  WAL columns in pg_stat_replication so that I can monitor replication
  delay without granting superuser permissions.

 So you can do this now by defining a security definer function that
 extracts precisely the information you need and grant execute access
 to precisely the users you want. There was some concern upthread about
 defining security definer functions being tricky but I'm not sure what
 conclusion to draw from that argument.

 Yeah, but that sucks if you want to build a generic monitoring system
 like check_postgres.pl.  Telling users to grant certain privileges may
 work out, telling them to install these pl/pgsql things you write as
 security-definer-to-superuser isn't going to be nearly as easy when
 these users are (understandably, imv) uncomfortable having a monitor
 role have superusr privs.

I couldn't agree more. Whatever we do here we need a standard
mechanism that tool developers can expect to be present and the same
on all servers. Otherwise, we make it extremely difficult to build
tools like pgAdmin, check_postgres.pl and so on.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] Deprecations in authentication

[Dave Page]

On Sat, Jan 18, 2014 at 2:59 PM, Andrew Dunstan and...@dunslane.net wrote:

 On 01/16/2014 08:01 AM, Magnus Hagander wrote:


 On Wed, Jan 15, 2014 at 6:57 PM, Tom Lane t...@sss.pgh.pa.us
 mailto:t...@sss.pgh.pa.us wrote:

 Magnus Hagander mag...@hagander.net mailto:mag...@hagander.net

 writes:
  One thing I noticed - in MSVC, the config parameter krb5
 (equivalent of
  the removed --with-krb5) enabled *both* krb5 and gssapi, and
 there is no
  separate config parameter for gssapi. Do we want to rename that
 one to
  gss, or do we want to keep it as krb5? Renaming it would break
  otherwise working environments, but it's kind of weird to leave
 it...

 +1 for renaming --- anybody who's building with krb5 and
 expecting to,
 you know, actually *get* krb5 would probably rather find out about
 this
 change at build time instead of down the road a ways.

 A compromise position would be to introduce a gss parameter while
 leaving
 krb5 in place as a deprecated (perhaps undocumented?) synonym for it.
 But I think that's basically confusing.


 Yeah, I'm not sure it actually helps much.


 Andrew - is this going to cause any issues wrt the buildfarm, by any
 chance?


 None of my Windows buildfarm members builds with krb5. Mastodon does,
 although it seems to have gone quiet for 16 days (Dave - might be worth a
 check). Probably the result of renaming krb5 would be just that the build
 would proceed without it. From memory I don't thing the config settings are
 sanity checked.

Yeah, sorry - we had an aircon failure where my animals live, so
they've been down for a couple of weeks. We've got a complete new
system 90% installed, that should be finished today, so hopefully one
of my colleagues can bring everything up again tomorrow (I'm out of
town for a couple of days).

-- 
Dave Page
PostgreSQL Core Team
http://www.postgresql.org/


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

Re: [HACKERS] Compiling extensions on Windows

[Dave Page]

On Mon, Jan 6, 2014 at 3:32 AM, Craig Ringer cr...@2ndquadrant.com wrote:
 Hi all

 Out of personal interest (in pain and suffering) I was recently looking
 into how to compile extensions out-of-tree on Windows using Visual
 Studio (i.e. no PGXS).

 It looks like the conventional answer to this is Do a source build of
 PG, compile your ext in-tree in contrib/, and hope the result is binary
 compatible with release PostgreSQL builds for Windows. Certainly that's
 how I've been doing it to date.

 How about everyone else here? Does anyone actually build and distribute
 extensions out of tree at all?

 I'm interested in making the Windows installer distributions a bit more
 extension dev friendly. In particular, I'd really like to see EDB's
 Windows installers include the libintl.h for the included libintl, since
 its omission, combined with Pg being built with ENABLE_NLS, tends to
 break things horribly. Users can always undefine ENABLE_NLS, but it's an
 unnecessary roadblock.

Sandeep, can you work on fixing this please?

Thanks.

 Are there any objections from -hackers to including 3rd party headers
 for libs we expose in our public headers in the binary distribution?

 Other than bundling 3rd party headers, any ideas/suggestions for how we
 might make ext building saner on Windows?

 --
  Craig Ringer   http://www.2ndQuadrant.com/
  PostgreSQL Development, 24x7 Support, Training  Services


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



-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] Compiling extensions on Windows

[Dave Page]

On Mon, Jan 6, 2014 at 10:57 AM, Sandeep Thakkar
sandeep.thak...@enterprisedb.com wrote:
 Sure. I'll make the changes so that the next available Windows installers
 include lbintl.h in $Installdir/include. How about the changes with respect
 to NLS?

No, there's nothing to change there. Craig was suggesting that users
could disable NLS in their extension to avoid issues with the missing
header, but that's not a good solution.

 On Mon, Jan 6, 2014 at 2:44 PM, Dave Page dp...@pgadmin.org wrote:

 On Mon, Jan 6, 2014 at 3:32 AM, Craig Ringer cr...@2ndquadrant.com
 wrote:
  Hi all
 
  Out of personal interest (in pain and suffering) I was recently looking
  into how to compile extensions out-of-tree on Windows using Visual
  Studio (i.e. no PGXS).
 
  It looks like the conventional answer to this is Do a source build of
  PG, compile your ext in-tree in contrib/, and hope the result is binary
  compatible with release PostgreSQL builds for Windows. Certainly that's
  how I've been doing it to date.
 
  How about everyone else here? Does anyone actually build and distribute
  extensions out of tree at all?
 
  I'm interested in making the Windows installer distributions a bit more
  extension dev friendly. In particular, I'd really like to see EDB's
  Windows installers include the libintl.h for the included libintl, since
  its omission, combined with Pg being built with ENABLE_NLS, tends to
  break things horribly. Users can always undefine ENABLE_NLS, but it's an
  unnecessary roadblock.

 Sandeep, can you work on fixing this please?

 Thanks.

  Are there any objections from -hackers to including 3rd party headers
  for libs we expose in our public headers in the binary distribution?
 
  Other than bundling 3rd party headers, any ideas/suggestions for how we
  might make ext building saner on Windows?
 
  --
   Craig Ringer   http://www.2ndQuadrant.com/
   PostgreSQL Development, 24x7 Support, Training  Services
 
 
  --
  Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
  To make changes to your subscription:
  http://www.postgresql.org/mailpref/pgsql-hackers



 --
 Dave Page
 Blog: http://pgsnake.blogspot.com
 Twitter: @pgsnake

 EnterpriseDB UK: http://www.enterprisedb.com
 The Enterprise PostgreSQL Company




 --
 Sandeep Thakkar




-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] PostgreSQL Service on Windows does not start. ~ is not a valid Win32 application

[Dave Page]

,
CreateProcessAsUser(a Windows Function called by pg_ctl.exe)
tries to create a process using the other file such
as Program, so the service fails to start.
 
Accordingly, I think that the command path should be
enclosed in quotation.
 
I created a patch to fix this failure,
So could anyone confirm?
 
Regards,
 
Naoya
 
---
Naoya Anzai
Engineering Department
NEC Soft, Ltd.
E-Mail: anzai-na...@mxu.nes.nec.co.jp
---
 
 
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
 
 
 
 
 

 以上、よろしくお願い致します。

 
 NECソフト株式会社
 PFシステム事業部 テーマソフト開発G
 安西 直也

 外線(03)5534-2353
 内線(8)57-40364
 Mail:NES-N2363
 E-mail:anzai-na...@mxu.nes.nec.co.jp
 
 ≪本メールの取り扱い≫
 ・区分：秘密
 ・開示：必要最小限で可
 ・持出：禁止
 ・期限：無期限
 ・用済後：廃棄






-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

[HACKERS] Upcoming backbranch releases

[Dave Page]

Just a quick heads-up: following discussion within the core team and
the packagers, we're planning to wrap tarballs for 8.4 - 9.3
backbranch releases on Monday 7th October for release
on Thursday 10th.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] Feature Request on Extensions

[Dave Page]

On Sun, Aug 18, 2013 at 10:34 PM, Hannu Krosing ha...@2ndquadrant.com wrote:
 On 08/18/2013 10:20 PM, Dimitri Fontaine wrote:
 Hi,

 I had the chance to being at OSCON this year and had a chat with the
 Open Shift team while there. Thanks for posting your use case!

 Tom Lane t...@sss.pgh.pa.us writes:
 Right offhand, it seems like you have or could grant a developer
 superuser/DBA privileges with respect to his own PG instance, so I'm not
 actually seeing why you have a problem.  What exactly is stopping him
 from installing his extension in the normal way?
 They use the same binary installation for everyone, and an OS packaged
 one at that. Which means that there's a single `libdir` and `pkglibdir`
 shared globally on the system. And no individual user has any privileges
 down there as it's a global OS location.

 What they want is to be able to run the same binary for every user, yet
 have a personal `libdir` place where to load extension's .so files from,
 and point that to a place owned by the initdb bootstrap superuser,
 different each time.

 The easiest way for them here would be for this parameter to be a fully
 dynamic setting, second best an initdb option, IIUC.

 The way they make that secure in their model is by using modern
 approaches to security, or at least modern enough that we don't get to
 envision those offerings when we usually talk about the idea of allowing
 the backend to load non-root-owned binary modules: SElinux and CGroups.
 Even without SELinux I can not immediately see the security weakening
 when you allow the backend to load .so-s from directories which are
 owned by the user both the client and the backend runs as.

 so say there is system user 'bob' who has his own instance of database
 initdb-ed in /home/bob/pgsql and running as user bob, with bob also being
 the main superuser for the cluster.

 User bob can then CREATE EXTENSION which loads .so-s from
 /home/bob/libpgsql
 and if a more restricted user is needed for web client database access
 then bob can do CREATE USER lesserbob; for this.

 I think this is something that should be secure even with standard
 non-SELinux install.

 Feel free to point out where a security escalation is possible with such
 a use case.

The escalation happens because in a normal system-wide installation of
PostgreSQL as you'd see on most production systems will have the
installation directories and binaries owned by the root user, so if
the server or a superuser account on it is compromised, the attacker
still can't (easily) modify the PostgreSQL installation to do Bad
Things, assuming the sysadmin has disabled untrusted PLs.

I can see the use case for the change suggested, but I feel pretty
strongly that it should not be allowed in a default configuration, and
should be something that can be disabled from outside of
postgresql.conf (which of course, can often be modified through
PostgreSQL by a superuser - and yes, I realise there is risk there
too, but no sense adding to that).

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] Feature Request on Extensions

[Dave Page]

On Mon, Aug 19, 2013 at 10:21 AM, Dimitri Fontaine
dimi...@2ndquadrant.fr wrote:
 Dave Page dp...@pgadmin.org writes:
 The escalation happens because in a normal system-wide installation of
 PostgreSQL as you'd see on most production systems will have the
 installation directories and binaries owned by the root user, so if
 the server or a superuser account on it is compromised, the attacker
 still can't (easily) modify the PostgreSQL installation to do Bad
 Things, assuming the sysadmin has disabled untrusted PLs.

 I appreciate that line of arguments, but it's been proven more than once
 (last time by Andres) to be just false. Given a malicious user with
 superuser privileges on a standard PostgreSQL backend without any
 extension nor PL installed, arbitrary code execution is already possible
 and quite easy to achieve.

 Given how easy it is, that whole line of thoughs really is moot.

If you find a hole in the boat, the preferred option is to fix it, not
to say meh, well another won't hurt.

 I can see the use case for the change suggested, but I feel pretty
 strongly that it should not be allowed in a default configuration, and
 should be something that can be disabled from outside of
 postgresql.conf (which of course, can often be modified through
 PostgreSQL by a superuser - and yes, I realise there is risk there
 too, but no sense adding to that).

 My proposal here would be in the lines of a dynamic GUC where you could
 add directories to consider at LOAD time (including .so dependencies
 resolution, that's the main trick). That GUC would default to being
 empty, which should answer your valid concern here.

That wouldn't address my concern, which is preventing someone with
only DB server superuser access from enabling the feature.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] Adding optionally commit number in PG_VERSION_STR

[Dave Page]

On Wed, Jul 17, 2013 at 2:55 AM, Michael Paquier
michael.paqu...@gmail.com wrote:
 Hi all,

 It happens that I work occasionally on multiple builds based on
 different stable branches at the same time to check fixes that need to
 be backpatched, and I tend to easily lose track on which version the
 build I created is based on (Duh!). There is of course the version
 number up to the 3rd digit available (for example 9.2.4, 9.3beta2,
 etc.), but as a developer I think that it would be helpful to include
 the commit ID in PG_VERSION_STR to get a better reference on exactly
 what the development build is based on. This could be controlled by an
 additional flag in ./configure.in called something like
 --enable-version-commit, of course disabled by default. If enabled,
 PG_VERSION_STR would be generated with the new information. configure
 would also return an error when this flag is enabled if git is either
 not found, or if the repository where configure is not a native git
 repository.

FYI, we include the output from git describe --always in the pgAdmin
version meta info, which is displayed on the About box along with the
regular version info. That has proven to be extremely useful in the
past, particularly during QA where people may be testing snapshot
builds.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] robots.txt on git.postgresql.org

[Dave Page]

On Wed, Jul 10, 2013 at 9:25 AM, Craig Ringer cr...@2ndquadrant.com wrote:
 On 07/09/2013 11:30 PM, Andres Freund wrote:
 On 2013-07-09 16:24:42 +0100, Greg Stark wrote:
 I note that git.postgresql.org's robot.txt refuses permission to crawl
 the git repository:

 http://git.postgresql.org/robots.txt

 User-agent: *
 Disallow: /


 I'm curious what motivates this. It's certainly useful to be able to
 search for commits.

 Gitweb is horribly slow. I don't think anybody with a bigger git repo
 using gitweb can afford to let all the crawlers go through it.

 Wouldn't whacking a reverse proxy in front be a pretty reasonable
 option? There's a disk space cost, but using Apache's mod_proxy or
 similar would do quite nicely.

It's already sitting behind Varnish, but the vast majority of pages on
that site would only ever be hit by crawlers anyway, so I doubt that'd
help a great deal as those pages would likely expire from the cache
before it really saved us anything.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] Configurable location for extension .control files

[Dave Page]

On Wed, Jun 12, 2013 at 7:24 AM, Tom Dunstan pg...@tomd.cc wrote:

 Another alternative is for the Postgres.app to add its bin dir to the user's
 (or system's) path on first startup. Then the correct pg_config will be
 found (and the correct psql, pgdump etc etc as well). The app could in
 theory even go looking for existing pg gem installed under .rvm or whatever
 and prompt the user to reinstall the gem.

Messing with the path (or the dynamic load path) can cause all sorts
of fun and interesting problems for users, as we found in the early
days with the EDB installers. I realise it doesn't help these users
(who doubtless don't know it exists) but what we do these days is drop
a pg_env.sh file in the installation directory that the user can
source to set their PATH and various PG* environment variables when
they need/want to.


--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] pg_rewind, a tool for resynchronizing an old master after failover

[Dave Page]

On Wed, Jun 5, 2013 at 6:10 PM, Josh Berkus j...@agliodbs.com wrote:

 I'm not a lawyer and I make no judgement on how solid a practice this
 is but that's VMware doesn't seem to be doing anything special here.
 They can retain copyright ownership of their contributions as long as
 they're happy releasing it under the Postgres copyright. Ideally they
 wold also be happy with a copyright notice that includes all of the
 PGDG just to reduce the maintenance headache.

 Many other projects also take this approach: Linux Kernel, Drizzle, etc.
  There's some legal advantages, as well as disadvantages, in having the
 copyright rest with the original contributors.  Mostly, it prevents
 relicensing of the whole project.

No it doesn't - it just makes it a pain in the arse (I know, I've done it).

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

[HACKERS] Implicit rule created for materialized views

[Dave Page]

Hi

In playing with materialized views, I noticed that they still seem to
have an _RETURN rule implicitly created like a regular view. This
doesn't seem right to me - is there a reason?

viper:~ dpage$ /usr/local/pgsql-9.3/bin/psql -p 5433 -U postgres
psql (9.3beta1)
Type help for help.

postgres=# CREATE MATERIALIZED VIEW ruletest AS SELECT * FROM pg_class;
SELECT 298
postgres=# SELECT pg_get_ruledef(oid) FROM pg_rewrite WHERE ev_class =
'ruletest'::regclass;
 pg_get_ruledef
-
 CREATE RULE _RETURN AS   +
 ON SELECT TO ruletest DO INSTEAD  SELECT pg_class.relname, +
 pg_class.relnamespace, +
 pg_class.reltype,  +
 pg_class.reloftype,+
 pg_class.relowner, +
 pg_class.relam,+
 pg_class.relfilenode,  +
 pg_class.reltablespace,+
 pg_class.relpages, +
 pg_class.reltuples,+
 pg_class.relallvisible,+
 pg_class.reltoastrelid,+
 pg_class.reltoastidxid,+
 pg_class.relhasindex,  +
 pg_class.relisshared,  +
 pg_class.relpersistence,   +
 pg_class.relkind,  +
 pg_class.relnatts, +
 pg_class.relchecks,+
 pg_class.relhasoids,   +
 pg_class.relhaspkey,   +
 pg_class.relhasrules,  +
 pg_class.relhastriggers,   +
 pg_class.relhassubclass,   +
 pg_class.relispopulated,   +
 pg_class.relfrozenxid, +
 pg_class.relminmxid,   +
 pg_class.relacl,   +
 pg_class.reloptions+
FROM pg_class;
(1 row)


--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] Implicit rule created for materialized views

[Dave Page]

On Mon, Jun 3, 2013 at 1:06 PM, Kevin Grittner
kevin.gritt...@enterprisedb.com wrote:
 Yes, that is currently used for REFRESH, and will be used to drive the
 incremental maintenance when that is added.  Without it, CREATE MATERIALIZED
 VIEW wouldn't be different from CREATE TABLE AS.

OK.

 A materialized view is pretty much like a view, but with the results
 materialized.

Yeah, I get that, but what is confusing is that this now seems to be a
special kind of relation where there is an ON SELECT DO INSTEAD rule
which isn't actually executed on SELECTs from the view but at some
arbitrary time in the future.

 On Mon, Jun 3, 2013 at 6:58 AM, Dave Page dp...@pgadmin.org wrote:

 Hi

 In playing with materialized views, I noticed that they still seem to
 have an _RETURN rule implicitly created like a regular view. This
 doesn't seem right to me - is there a reason?

 viper:~ dpage$ /usr/local/pgsql-9.3/bin/psql -p 5433 -U postgres
 psql (9.3beta1)
 Type help for help.

 postgres=# CREATE MATERIALIZED VIEW ruletest AS SELECT * FROM pg_class;
 SELECT 298
 postgres=# SELECT pg_get_ruledef(oid) FROM pg_rewrite WHERE ev_class =
 'ruletest'::regclass;
  pg_get_ruledef
 -
  CREATE RULE _RETURN AS   +
  ON SELECT TO ruletest DO INSTEAD  SELECT pg_class.relname, +
  pg_class.relnamespace, +
  pg_class.reltype,  +
  pg_class.reloftype,+
  pg_class.relowner, +
  pg_class.relam,+
  pg_class.relfilenode,  +
  pg_class.reltablespace,+
  pg_class.relpages, +
  pg_class.reltuples,+
  pg_class.relallvisible,+
  pg_class.reltoastrelid,+
  pg_class.reltoastidxid,+
  pg_class.relhasindex,  +
  pg_class.relisshared,  +
  pg_class.relpersistence,   +
  pg_class.relkind,  +
  pg_class.relnatts, +
  pg_class.relchecks,+
  pg_class.relhasoids,   +
  pg_class.relhaspkey,   +
  pg_class.relhasrules,  +
  pg_class.relhastriggers,   +
  pg_class.relhassubclass,   +
  pg_class.relispopulated,   +
  pg_class.relfrozenxid, +
  pg_class.relminmxid,   +
  pg_class.relacl,   +
  pg_class.reloptions+
 FROM pg_class;
 (1 row)


 --
 Dave Page
 Blog: http://pgsnake.blogspot.com
 Twitter: @pgsnake

 EnterpriseDB UK: http://www.enterprisedb.com
 The Enterprise PostgreSQL Company





--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] Implicit rule created for materialized views

[Dave Page]

On Mon, Jun 3, 2013 at 3:59 PM, Kevin Grittner kgri...@ymail.com wrote:
 Dave Page dp...@pgadmin.org wrote:
 Kevin Grittner kevin.gritt...@enterprisedb.com wrote:
 Dave Page dp...@pgadmin.org wrote:

 In playing with materialized views, I noticed that they still
 seem to have an _RETURN rule implicitly created like a regular
 view.

 A materialized view is pretty much like a view, but with the
 results materialized.

 Yeah, I get that, but what is confusing is that this now seems to
 be a special kind of relation where there is an ON SELECT DO
 INSTEAD rule which isn't actually executed on SELECTs from the
 view but at some arbitrary time in the future.

 Perhaps this way of looking at it will allow it to make sense: It
 generates values which will be returned by SELECT -- it just does
 that in advance and caches them on disk for quicker return when
 queried.

That perspective certainly makes it clearer.

 As a practical matter, a materialized view needs to store exactly
 the same information about its query, in the same form, as a
 regular view.  To add a new table to store this in a different
 place, with references and such maintained in the same way, would
 have multiplied the size of the patch with a lot of copy/pasted
 code.  I'm pretty sure the result would have been something which
 was harder to review and maintain.

Yeah, I have no desire for that to be done. I'm just trying to
understand what looked like some weirdness in the way it all worked.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] improving PL/Python builds on OS X

[Dave Page]

On Fri, May 10, 2013 at 2:47 AM, Peter Eisentraut pete...@gmx.net wrote:
 On Thu, 2013-05-09 at 10:11 -0400, Peter Eisentraut wrote:
 On 5/9/13 3:25 AM, Dave Page wrote:
  BTW - it's always worked fine for us on 64 bit machines with the past
  few major releases of both PG and Python - are you saying that's pure
  chance?

 It works because ActiveState Python has PIC inside a static library.
 But we have no straightforward way of knowing that (AFAIK), other than
 observing whether the build result crashes or not.

 After further digging, it seems to me that their build is not a standard
 build.  They must be patching in compiler options through the backdoor
 somehow.  Their config/Makefile has

 BASECFLAGS=  -fno-strict-aliasing -fPIC

 meaning that they compile *everything* with those options.  But that's
 not something that the standard Python configure script can produce.

Huh, interesting. I used to have a contact at ActiveState - I'll see
if I can get some more details.

 Again, reliably detecting that might be difficult.

Yeah, that's understandable. As long as I can force it though (and
your suggestion on that front seems to work fine), I'm happy.

Thanks.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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