[HACKERS] GRANT USAGE on FOREIGN SERVER exposes passwords

2015-02-03 Thread Noah Yetter
When a user is granted USAGE on a foreign server, the psql command \deu+
will show them the username and password bound to the applicable user
mapping.

To demonstrate (9.3+):
(as a superuser)
# create extension postgres_fdw ;

# create foreign server loopback_server
foreign data wrapper postgres_fdw
options(host '127.0.0.1', port '5432') ;

# create user mapping for public
server loopback_server
options(username 'foo', password 'bar') ;

(as a normal user)
 \deu+
List of user mappings
Server | User name |  FDW Options
---+---+
 loopback_server   | public|
(1 row)

So far so good?

 select * from dblink('loopback_server', 'select current_date') as
x(column1 date) ;
ERROR:  permission denied for foreign server loopback_server

OK, I can't do that now.  Let's fix it:

# grant usage on foreign server loopback_server to public ;

 select * from dblink('loopback_server', 'select current_date') as
x(column1 date) ;
  column1

 2015-02-03
(1 row)

Sweet!  But...

 \deu+
  List of user mappings
Server | User name |  FDW Options
---+---+
 loopback_server   | public| (user 'foo', password 'bar')
(1 row)

Crap.

(FWIW, it doesn't matter whether you grant to PUBLIC or to a specific user,
the result is the same.)

The obvious objection is, well you should just use foreign tables instead
of dblink().  I'll cut a long story short by saying that doesn't work for
us.  We are using postgres_fdw to allow our analysts to run queries against
AWS Redshift and blend those results with tables in our OLTP schema.  If
you know anything about Redshift, or about analysts, you'll realize
immediately why foreign tables are not a viable solution.  Surely there are
many others in a similar position, where the flexibility offered by
dblink() makes it preferable to fixed foreign tables.

S... what gives?  This seems like a really obvious security hole.  I've
searched the mailing list archives repeatedly and found zero discussion of
this issue.


--
Noah Yetter
Data Architect/DBA @ Craftsy


Re: [HACKERS] Pg_upgrade and toast tables bug discovered

2014-09-04 Thread Noah Yetter
The 9.3.5 release notes contain...


   -

   Fix pg_upgrade for cases where the new server creates a TOAST table but
   the old version did not (Bruce Momjian)

   This rare situation would manifest as relation OID mismatch errors.


...which I thought was this bug, hence my confusion.  If anyone else is
experiencing this bug, they may erroneously be led to believe that 9.3.5
contains the fix.


I will attempt to build 9.3 stable head and retry my upgrade.


On Wed, Sep 3, 2014 at 6:03 PM, Bruce Momjian br...@momjian.us wrote:

 On Wed, Sep  3, 2014 at 05:12:30PM -0600, Noah Yetter wrote:
  I'm not sure it's fixed.  I am attempting a pg_upgrade from 9.2.8 to
 9.3.5 and
  it dies like so:
 
  (...many relations restoring successfully snipped...)
  pg_restore: creating SEQUENCE address_address_id_seq
  pg_restore: [archiver (db)] Error while PROCESSING TOC:
  pg_restore: [archiver (db)] Error from TOC entry 1410; 1259 17670
 SEQUENCE
  address_address_id_seq javaprod
  pg_restore: [archiver (db)] could not execute query: ERROR:  could not
 create
  file base/16414/17670: File exists
 
  Inspecting a copy of the source cluster, OID 17670 does indeed
 correspond to
  address_address_id_seq, but inspecting the partially-upgraded cluster
 that OID
  is taken by pg_toast_202359_index.  Again conferring with a copy of the
 source
  (9.2.8) cluster, the relation corresponding to filenode 202359 does not
 have a
  toast table.
 
  (I know pg-hackers isn't the right place to discuss admin issues, but
 this
  thread is the only evidence of this bug I can find.  If anyone can
 suggest a
  workaround I would be infinitely grateful.)

 Actually, there was a pg_upgrade fix _after_ the release of 9.3.5 which
 explains this failure:

 commit 4c6780fd17aa43ed6362aa682499cc2f9712cc8b
 Author: Bruce Momjian br...@momjian.us
 Date:   Thu Aug 7 14:56:13 2014 -0400

 pg_upgrade: prevent oid conflicts with new-cluster TOAST tables

 Previously, TOAST tables only required in the new cluster
 could cause
 oid conflicts if they were auto-numbered and a later
 conflicting oid had
 to be assigned.

 Backpatch through 9.3

 Any chance you can download the 9.3.X source tree and try that?  You
 need an entire install, not just a new pg_upgrade binary.  I am
 disapointed I could not fix this before 9.3.5 was released.

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

   + Everyone has their own god. +



Re: [HACKERS] Pg_upgrade and toast tables bug discovered

2014-09-04 Thread Noah Yetter
Isn't that exactly what the release note says?
where the new server creates a TOAST table but the old version did not
vs.
where the new cluster needs a TOAST table that the old cluster didn't

At any rate, I've additionally observed that the relation which is blowing
up pg_upgrade is a VIEW in the source cluster but gets created as a TABLE
in the upgraded cluster, which may better explain why it had no toast table
before and now it does.  Is this some kind of expected behavior for views?


On Thu, Sep 4, 2014 at 12:39 PM, Bruce Momjian br...@momjian.us wrote:

 On Thu, Sep  4, 2014 at 11:37:27AM -0600, Noah Yetter wrote:
  The 9.3.5 release notes contain...
 
 
• Fix pg_upgrade for cases where the new server creates a TOAST table
 but the
  old version did not (Bruce Momjian)
 
  This rare situation would manifest as relation OID mismatch errors.
 
 
  ...which I thought was this bug, hence my confusion.  If anyone else is
  experiencing this bug, they may erroneously be led to believe that 9.3.5
  contains the fix.
 
 
  I will attempt to build 9.3 stable head and retry my upgrade.

 Yes, please let us know.  The post-9.3.5 fix is for the reverse case,
 where the new cluster needs a TOAST table that the old cluster didn't.

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

   + Everyone has their own god. +



Re: [HACKERS] Pg_upgrade and toast tables bug discovered

2014-09-04 Thread Noah Yetter
Doing the upgrade with an installation built from REL9_3_STABLE at
commit 52eed3d4267faf671dae0450d99982cb9ba1ac52 was successful.

The view that I saw get re-created as a table doesn't have any circular
references, or indeed any references to other views, nor do any other views
reference it.  But since it does seem that there are valid cases where a
view gets temporarily re-created as a table during an upgrade, I'm going to
assume it's not a bug per se.  My upgraded cluster using built-from-source
binaries has these views as views, so when the process is complete they end
up in the correct state.

Is there an expected release date for 9.3.6?


On Thu, Sep 4, 2014 at 2:01 PM, Bruce Momjian br...@momjian.us wrote:

 On Thu, Sep  4, 2014 at 03:48:17PM -0400, Robert Haas wrote:
  On Thu, Sep 4, 2014 at 3:35 PM, Bruce Momjian br...@momjian.us wrote:
   At any rate, I've additionally observed that the relation which is
 blowing up
   pg_upgrade is a VIEW in the source cluster but gets created as a
 TABLE in the
   upgraded cluster, which may better explain why it had no toast table
 before and
   now it does.  Is this some kind of expected behavior for views?
  
   Uh, it certainly should not be creating a table instead of a view,
   though it will get a pg_class entry.
 
  Actually, there's a way this can happen.  If you create two (or more)
  views with circular dependencies between them, then pg_dump will emit
  commands to create one of them as a table first, then create the
  others as views, then convert the first table to a view by adding a
  _SELECT rule to it.

 Wow, that's super-interesting.

  If pg_upgrade's logic can't cope with that, that's a bug in
  pg_upgrade, because there's no other way to restore views with
  circular dependency chains.

 I don't see why pg_upgrade would have any problem with it as it just
 looks at the old schema and post-restore schema.

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

   + Everyone has their own god. +



Re: [HACKERS] Pg_upgrade and toast tables bug discovered

2014-09-03 Thread Noah Yetter
I'm not sure it's fixed.  I am attempting a pg_upgrade from 9.2.8 to 9.3.5
and it dies like so:

(...many relations restoring successfully snipped...)
pg_restore: creating SEQUENCE address_address_id_seq
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 1410; 1259 17670 SEQUENCE
address_address_id_seq javaprod
pg_restore: [archiver (db)] could not execute query: ERROR:  could not
create file base/16414/17670: File exists

Inspecting a copy of the source cluster, OID 17670 does indeed correspond
to address_address_id_seq, but inspecting the partially-upgraded cluster
that OID is taken by pg_toast_202359_index.  Again conferring with a copy
of the source (9.2.8) cluster, the relation corresponding to filenode
202359 does not have a toast table.

(I know pg-hackers isn't the right place to discuss admin issues, but this
thread is the only evidence of this bug I can find.  If anyone can suggest
a workaround I would be infinitely grateful.)


On Thu, Aug 7, 2014 at 12:57 PM, Bruce Momjian br...@momjian.us wrote:

 On Tue, Aug  5, 2014 at 07:31:21PM -0400, Bruce Momjian wrote:
  On Thu, Jul 10, 2014 at 06:38:26PM -0400, Bruce Momjian wrote:
   On Thu, Jul 10, 2014 at 06:17:14PM -0400, Bruce Momjian wrote:
Well, we are going to need to call internal C functions, often
 bypassing
their typical call sites and the assumption about locking, etc.
 Perhaps
this could be done from a plpgsql function.  We could add and drop a
dummy column to force TOAST table creation --- we would then only
 need a
way to detect if a function _needs_ a TOAST table, which was skipped
 in
binary upgrade mode previously.
   
That might be a minimalistic approach.
  
   I have thought some more on this.  I thought I would need to open
   pg_class in C and do complex backend stuff, but I now realize I can do
   it from libpq, and just call ALTER TABLE and I think that always
   auto-checks if a TOAST table is needed.  All I have to do is query
   pg_class from libpq, then construct ALTER TABLE commands for each item,
   and it will optionally create the TOAST table if needed.  I just have
 to
   use a no-op ALTER TABLE command, like SET STATISTICS.
 
  Attached is a completed patch which handles oid conflicts in pg_class
  and pg_type for TOAST tables that were not needed in the old cluster but
  are needed in the new one.  I was able to recreate a failure case and
  this fixed it.
 
  The patch need to be backpatched because I am getting more-frequent bug
  reports from users using pg_upgrade to leave now-end-of-life'ed 8.4.
  There is not a good work-around for pg_upgrade failures without this
  fix, but at least pg_upgrade throws an error.

 Patch applied through 9.3, with an additional Assert check. 9.2 code was
 different enough that there was too high a risk for backpatching.

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

   + Everyone has their own god. +


 --
 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] all_visible replay aborting due to uninitialized pages

2013-11-10 Thread Noah Yetter
Like your customer, this bug has blown up my standby servers, twice in the
last month: the first time all 4 replicas, the second time (mysteriously
but luckily) only 1 of them.

At any rate, since the fix isn't available yet, is/are there any
configuration changes that can be made or maintenance procedures that can
be undertaken to prevent or reduce the probability of this bug popping up
again in the meantime?  I really can't afford to be without my standby
servers during the holidays, even for the few hours it takes to build a new
one.


On Tue, May 28, 2013 at 11:58 AM, Andres Freund and...@2ndquadrant.comwrote:

 Hi,

 A customer of ours reporting a standby loosing sync with the primary due
 to the following error:
 CONTEXT:  xlog redo visible: rel 1663/XXX/XXX; blk 173717
 WARNING:  page 173717 of relation base/XXX/XXX is uninitialized
 ...
 PANIC:  WAL contains references to invalid pages

 Guessing around I looked and noticed the following problematic pattern:
 1) A: wants to do an update, doesn't have enough freespace
 2) A: extends the relation on the filesystem level
 (RelationGetBufferForTuple)
 3) A: does PageInit (RelationGetBufferForTuple)
 4) A: aborts, e.g. due to a serialization failure (heap_update)

 At this point the page is initialized in memory, but not wal logged. It
 isn't pinned or locked either.

 5) B: vacuum finds that page and it's empty. So it marks it all
 visible. But since the page wasn't written out (we haven't even marked
 it dirty in 3.) the standby doesn't know that and reports the page as
 being uninitialized.

 ISTM the best backbranchable fix for this is to teach lazy_scan_heap to
 log an FPI for the heap page via visibilitymap_set in that rather
 limited case.

 Happy to provide a patch unless somebody has a better idea?

 Greetings,

 Andres Freund

 --
  Andres Freund 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