[DOCS] Incorrect REVOKE command in docs

[Stephen Frost]

Greetings,

  The 'REVOKE' command given in section 2.8.4 under Chapter 2. "Data
  Definition" is not correct.  The command should be:

  REVOKE CREATE ON SCHEMA public FROM PUBLIC;

  Note that 'SCHEMA' is missing.  This is at least on the web page:
  http://www.postgresql.org/docs/7.3/static/ddl-schemas.html
  under the 7.3 docs.

  If I should have sent this to -bugs let me know.

Stephen


pgp0.pgp
Description: PGP signature

Re: [DOCS] Proposed doc-patch: Identifying the Current WAL file

[Stephen Frost]

* Bruce Momjian (pgman@candle.pha.pa.us) wrote:
> In the first case, x2 is current, having be just switched to from x1,
> while in the second case, x1 is current.  In BSD, you can use ls -ltT to
> see the seconds, but in Linux it is something different, and I am sure
> there are some operating systems that don't allow you to see the seconds
> at all.  What general command-line solution can we propose for this
> process?  And if we can't provide one, should we supply an SQL function
> to return the current WAL name?

When we were looking into this we actually thought that it looked like
multiple WALs were written to concurrently by the DB so we used what I
suppose might have been something excessive- we just rsync the entire
directory to a seperate area on the backup server.  Our setup is
more-or-less like this:

Full backups:
pg_start_backup
Find the starting checkpoint and WAL from the backup_label
rsync
pg_stop_backup
Find the stopping WAL from the .backup file (using the checkpoint and
starting WAL to find the correct .backup file)
Copy all the WALs between (inclusive) the starting WAL and stopping WAL,
which still exist on the server, to the backup server (seems to be only
one usually).
Run a command on the backup server which finds all the WALs necessary
for restoring the *backup* and copy them into a 'backup_wals' directory
under the 'base' directory of the rsync'd backup.
Run a command on the backup server which looks for the oldest 'base'
backup (we rotate through three base backups), finds the starting WAL
for that backup (from backup_label) and then deletes all WAL files in
the 'archived_logs' directory which are before it.

WAL archival:
scp the WAL from the server to the backup server into an 'archived_logs'
directory outside of the base backup directories.  After a 'base' backup
this will overwrite the partial log file on the backup server which was
created immediately following the pg_stop_backup.

Partial WAL copying:
Every 5 minutes rsync the entire pg_xlog directory to the backup
server, into a 'pg_xlog_5min' directory that's outside the base backups.
Since this is using rsync it only copies what has actually changed and
hasn't seemed to be terribly expensive so far (then again, this is on a
local gigabit network with some decent systems on both sides).

All comparisons are done in hex using bc.  Everything is implemented in
shell scripts.

We then have three base backups which we rotate through weekly.  We also
do tape backups of the most recent 'base' backup plus the archived_logs
and pg_xlog_5min directories each night.

I'm guessing the reason this question has come up is that people would
like to do the 'Partial WAL copying' of only the most recent WAL log?  I
agree with the idea of having a function to find out the most recent
WAL.  It'd also be really nice to be able to tell Postgres "please log
even a partial WAL every 5 minutes, unless nothing has changed" or 
similar.  I think one or both of those may be on the TODO.

I'd certainly like to know if anyone can see any problems with this
setup or any reason it'd be less than perfect...  If this is a
reasonable way to set things up then I could try to write up some docs
outlining it as an example setup and/or provide the various shell
scripts we use.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [DOCS] pg_file_settings and catalog.sgml

[Stephen Frost]

* Fujii Masao (masao.fu...@gmail.com) wrote:
> On Fri, Jun 12, 2015 at 1:47 AM, Fujii Masao  wrote:
> > Hi,
> >
> > System catalogs and views should be listed in alphabetical order,
> > but only pg_file_settings view not. The attached patch fixes this
> > alphabetization.
> >
> > The patch also fixes two typos in comments related to pg_file_settings.
> 
> Applied.

Many thanks.  I had seen this and planned to do it, but happy that you
took care of it.

Thanks again!

Stephen


signature.asc
Description: Digital signature

Re: [DOCS] sepgsql and row-level security

[Stephen Frost]

Heikki,

* Heikki Linnakangas (hlinnakan...@pivotal.io) wrote:
> The docs on sepgsql says 
> (http://www.postgresql.org/docs/9.5/static/sepgsql.html#SEPGSQL-LIMITATIONS):
> 
> >PostgreSQL does not support row-level access; therefore, sepgsql does
> >not support it either.
> 
> Should that be adjusted for 9.5?

Yes, we should note in the 9.5 docs that PG supports RLS, but sepgsql
does not yet.

I'll make that change soon.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [DOCS] sepgsql and row-level security

[Stephen Frost]

Heikki,

* Heikki Linnakangas (hlinnakan...@pivotal.io) wrote:
> The docs on sepgsql says 
> (http://www.postgresql.org/docs/9.5/static/sepgsql.html#SEPGSQL-LIMITATIONS):
> 
> >PostgreSQL does not support row-level access; therefore, sepgsql does
> >not support it either.
> 
> Should that be adjusted for 9.5?

Done.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [DOCS] A typo in install-windows.sgml

[Stephen Frost]

Alexander,

* Alexander Law (exclus...@gmail.com) wrote:
> There is a typo in install-windows,sgml. Patch attached.

Fix pushed.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [DOCS] A hyphen missing in runtime.sgml

[Stephen Frost]

* Alexander Law (exclus...@gmail.com) wrote:
> I've found a hyphen missing in runtime.sgml. Patch attached.

Fix pushed.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [DOCS] Default privileges not working

[Stephen Frost]

Greetings,

* ati...@gmail.com (ati...@gmail.com) wrote:
> Thanks for the reply.
[...]
> alter default privileges in schema public grant select on tables to
> "test_readonly", "test_readwrite", "test_power";
> alter default privileges in schema public grant insert, update, delete on
> tables to "test_readwrite", "test_power";
> alter default privileges in schema public grant all on tables to
> "test_power";

Default privileges are assigned to roles.  In other words, you can only
say "tables created by user X have default privileges Y."  If you omit
the user from the ALTER DEFAULT PRIVILEGES command, then the
CURRENT_USER is used.

Use: \ddp
in psql to see the default privileges created and which user they are
associated with.  My guess is that in the above scenario, default
privileges were only set up for the 'postgres' user.

> -- CONNECT AS USER: user_power
> select * from a;
> create table b (x numeric); -- ok, created
> insert into a values (3);
> insert into b values (4); -- ok, everything like expected

> -- CONNECT AS USER: user_readwrite
> select * from b; -- SQL Error [42501]: ERROR: permission denied for relation
> b
> -- why? according to grant default privileges on tables for insert update
> delete and select this user can do any selection insertion or deletion from
> tables in public schema
> insert into b values (5); -- SQL Error [42501]: ERROR: permission denied for
> relation b

There were no default privileges set up for the "user_power" role and,
therefore, when that role created a table, no privileges were set for
it.  That's why the query by user_readwrite failed.

Try doing this first:

ALTER DEFAULT PRIVILEGES FOR user_power IN SCHEMA PUBLIC GRANT ...

And then creating a table as the "user_power" role.

> -- So no one except user who create table b can read from it.
> -- But with tables created as USER: postgres, everything is ok.
> -- How can I use default privileges to grant read to any new tables created
> to USER readonly.
> -- And grant all CRUD operations to USER readwrite, and grant delete table
> by USER power?

Assign default privileges for all roles which will be creating objects.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [DOCS] Default privileges not working

[Stephen Frost]

Jozef,

* Jozef Pažin (ati...@gmail.com) wrote:
> -- grant for new tables
> -- only users "postgres" and "test_power" can create tables;
> alter default privileges for role "test_power" in schema public grant
> select on tables to "test_readonly", "test_readwrite", "test_power";
> alter default privileges for role "test_power" in schema public grant
> insert, update, delete on tables to "test_readwrite", "test_power";
> alter default privileges for role "test_power" in schema public grant all
> on tables to "test_power";
> 
> alter default privileges for user "postgres" in schema public grant select
> on tables to "test_readonly", "test_readwrite", "test_power";
> alter default privileges for user "postgres" in schema public grant insert,
> update, delete on tables to "test_readwrite", "test_power";
> alter default privileges for user "postgres" in schema public grant all on
> tables to "test_power";

Above, you set default privileges for the 'postgres' and the
'test_power' roles, however...

> -- CONNECT AS USER: user_power

Here, you are connecting as the 'user_power' role, for which no default
privileges were set.

> select * from a;
> create table b (x numeric); -- ok

This table is created as the 'user_power' role and, since there were no
default privileges set for this role, it is created with no privileges
granted.

Leading to...

> -- CONNECT AS USER: user_readwrite
> select * from b; -- wrong -- SQL Error [42501]: ERROR: permission denied
> for relation b
> insert into b values (5); -- wrong -- SQL Error [42501]: ERROR: permission
> denied for relation b

These permission denied errors, which are entirely correct because no
default privileges were set for the case where the 'user_power' role
creates objects in the 'public' schema.

Please use \dp to see what the privileges are after object creation, and
use \ddp to see what the default privileges will be for objects created
by which roles in which schemas.

> -- How can I use default privileges to grant read to any new tables
> -- created to USER readonly. And grant all CRUD operations
> -- to USER readwrite, and grant delete table by USER power?

You must set up default privileges for all roles which will be creating
objects.  Above, you only set them for the 'postgres' role and the
'test_power' role, but then the 'user_power' role created objects.

One approach to dealing with this is to have fewer roles which can
create objects and then require users to do a 'SET ROLE' prior to
creating an object, eg:

CONNECT AS USER: user_power
SET ROLE test_power;
CREATE TABLE b (a int);

The above action creates the table as the 'test_power' role and
therefore the default privileges for the 'test_power' role will be
applied to all newly created objects.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [DOCS] Missing important information in backup.sgml

[Stephen Frost]

Gunnar, all,

* Gunnar "Nick" Bluth (gunnar.bluth.ext...@elster.de) wrote:
> Am 16.11.2016 um 11:37 schrieb Gunnar "Nick" Bluth:
> > I ran into this issue (see patch) a few times over the past years, and
> > tend to forget it again (sigh!). Today I had to clean up a few hundred
> > GB of unarchived WALs, so I decided to write a patch for the
> > documentation this time.
> 
> Uhm, well, the actual problem was a stale replication slot... and
> tomatoes on my eyes, it seems ;-/. Ashes etc.!
> 
> However, I still think a warning on (esp. rsync's) RCs >= 128 is worth
> considering (see -v2 attached).

Frankly, I wouldn't suggest including such wording as it would imply
that using a bare rsync command is an acceptable configuration of
archive_command.  It isn't.  At the very least, a bare rsync does
nothing to ensure that the WAL has been fsync'd to permanent storage
before returning, leading to potential data loss due to the WAL
segment being removed by PG before the new segment has been permanently
stored.

The PG documentation around archive command is, at best, a starting
point for individuals who wish to implement their own proper backup
solution, not as examples of good practice for production environments.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [DOCS] Missing important information in backup.sgml

[Stephen Frost]

Greetings,

* Gunnar "Nick" Bluth (gunnar.bl...@pro-open.de) wrote:
> Now, what could happen is

All you need is for the archive server to be reset at the wrong time
(and there are a lot of potential "wrong times" to choose from) and
you'll end up with an incomplete archive.

> a) complete DC power outage
> b) outage of DB server
> c) outage of archive server (or the network connection to it)
> d) outage of storage system
> e) complete DC outage caused by your DB server vanishing (burning down,
> exploding, melting, ...),
> f) a complete _loss_ of the DC (atomar strike, plane crash, ...)
> 
> In case a), your DB server would have fsync'd all committed transactions
> => no _data_ loss, but your _archive_ is potentially incomplete.

To be clear, the concern that I was pointing out is primairly that the
archive could end up incomplete and potentially render significant
portions of your archive as unusable (as in, everything since the event
til the next backup).

> In case b), the same applies, but your archive should be intact.

That isn't entirely accurate as you'll lose whatever happened since the
last WAL segment was shipped to the archive server, but that's true in
general unless you're using pg_receivexlog with sync mode.  Of course,
anything archive_command-based will have this issue.

On the other hand, you mentioned DB and archive on the same system, in
which case an inopportune reset of that server could result in an
incomplete archive, though you shouldn't lose any data in the database
assuming you can get the disks back.

> In case c), the archiver would retry until your archiving server comes
> back online => no _data_ loss, no _archive_ loss.

That depends entirely upon the circumstances of the archive server
outage- if the archive server is reset at the wrong time, you will
almost certainly lose some about of your archive.  If you just lose
network connectivity then you should be ok- if the command you're
using for archive_command returns the correct error code in that case.

> So, losing actual _data_ is unlikely (at least from the archiving point
> of view...), but not explicitly fsync'ing the archive _may_ lead to
> incomplete archives. Which is exactly what I tried to point out by
> "[...], rendering your archive incomplete in case of a power outage".

One of the very important things that should be done as part of a backup
is to ensure that all of the archive files required to restore the
database to a consistent state are safely stored in the archive.  If
that isn't done then it's possible that an incomplete archive may also
render backups invalid.

> Am I missing something?

For my 2c, at least, the archive should be viewed with nearly the same
care and consideration as the primary data.  As with your database, you
really want your backups to work when you need them.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [DOCS] Missing important information in backup.sgml

[Stephen Frost]

Greetings,

* Gunnar "Nick" Bluth (gunnar.bl...@pro-open.de) wrote:
> Am 23.11.2016 um 21:41 schrieb Stephen Frost:
> > * Gunnar "Nick" Bluth (gunnar.bl...@pro-open.de) wrote:
> > One of the very important things that should be done as part of a backup
> > is to ensure that all of the archive files required to restore the
> > database to a consistent state are safely stored in the archive.  If
> > that isn't done then it's possible that an incomplete archive may also
> > render backups invalid.
> 
> Well, the need to have a complete archive is described in the docs
> already. Maybe the potential consequences of an incomplete archive
> should be pointed or more drastically...?

We should probably add to "step 5" something about "verify that all WAL
files have been archived between the start and stop backup" or similar.

> Now, the main purpose of my patch was to document a behaviour that many
> of us have run into, namely that FATAL error showing up in the log when
> the archive_command exits with RC > 127. It's a nuisance only, but it
> does send people on false tracks and should at least be mentioned in the
> documentation.

I agree that we should add information to the documentation that certain
error codes are handled differently.

> And since a couple of people does use rsync (or some wrappers around it)
> for archiving, and that is notoriously giving RCs > 127, it seems legit
> to at least mention it, no?

The low-level API documentation should be focused on the API and not how
to (mis)use the API using common unix commands.

> diff --git a/doc/src/sgml/backup.sgml b/doc/src/sgml/backup.sgml
> index 6eaed1e..a8f574e 100644
> --- a/doc/src/sgml/backup.sgml
> +++ b/doc/src/sgml/backup.sgml
> @@ -587,7 +587,8 @@ tar -cf backup.tar /usr/local/pgsql/data
>  the administrator specify a shell command to be executed to copy a
>  completed segment file to wherever it needs to go.  The command could be
>  as simple as a cp, or it could invoke a complex shell
> -script — it's all up to you.
> +script — it's all up to you. There however are some things to 
> consider
> +when creating such a command, most of which are covered below.
> 

If we're changing this then we should remove the notion that it could be
"as simple as a cp" because it really should *not* be.  That is a
dangerous and very poor recommendation to be making to our users.

> 
> @@ -636,7 +637,11 @@ test ! -f 
> /mnt/server/archivedir/000100A90065 && cp pg_wal/0
>  PostgreSQL will assume that the file has been
>  successfully archived, and will remove or recycle it.  However, a nonzero
>  status tells PostgreSQL that the file was not archived;
> -it will try again periodically until it succeeds.
> +it will try again periodically until it succeeds. Note that an exit
> +status of 128 or higher will cause the archiver to exit, resulting in a
> +FATAL error in the server log. It will be restarted by the
> +postmaster and continue where it left. E.g., rsync is known
> +for returning exit statuses of 255 on network issues.
> 

I agree with adding documentation about what happens with different exit
status values.  I don't believe we should make any mention of rsync.  A
sophisticated enough user to understand exit codes should be able to
work out what the exit code is for whatever tool or tools they're using,
it's not on us to document what the exit codes are for every possible
tool (nor should one even use something as simple as a bare cp or rsync
in archive_command).

> 
> @@ -696,6 +701,16 @@ test ! -f 
> /mnt/server/archivedir/000100A90065 && cp pg_wal/0
>  preserve the file name (%f).
> 
>  
> +  
> +Depending on your specific requirements (and datacenter layout), you may
> +want to make sure that the archived WAL segments have been written out to
> +persistent storage before the archive_command returns.
> +Otherwise, a WAL segment that is assumed by PostgreSQL
> +to be archived could be recycled or removed prematurely, rendering your
> +archive incomplete (and thus disabling a recovery) in case of an outage 
> of
> +the archiving destination.
> +  

I dislike the tone of this.  The wording I would suggest would be more
along the lines of:

The archive_command should only return success once the WAL segment has
been completely copied, written out, and synced to persistent storage as
PostgreSQL will recycle or remove the segment shortly after the
archive_command returns.  If the WAL segment is lost due to an outage of
the archive server or other issue, any backup which was performed during
the time that the WAL segment was written will be unusable and
Point-In-Time-Recovery from an earlier backup will not be usable past
the missing WAL segment.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [DOCS] wrong column header text in grant doc page

[Stephen Frost]

Dmitriy,

* Dmitriy Olshevskiy (olshevski...@bk.ru) wrote:
> please, check the column header text " Column access privileges"
> in the GRANT doc
> page. I
> think there must be "Column privileges",

Ah, yes, though the actual \dp mytable output would also include
'Policies' (which is why 'access' was removed, in fact), as this:

=> \dp mytable
  Access privileges
 Schema |  Name   | Type  |   Access privileges   |   Column privileges   | 
Policies 
+-+---+---+---+--
 public | mytable | table | miriam=arwdDxt/miriam+| col1:+| 
| |   | =r/miriam+|   miriam_rw=rw/miriam | 
| |   | admin=arw/miriam  |   | 
(1 row)

I'll update the docs and back-patch this to 9.5, where RLS was added.

> as in the psql \dp output, src/bin/psql/po/*.po files and
> regression tests outputs.

The po files should be updated by the translation team.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [DOCS] Problems with ROW LEVEL SECURITY

[Stephen Frost]

Greetings,

* blaugetc...@gmail.com (blaugetc...@gmail.com) wrote:
> This works in version 9.3?

No, RLS was not added to PostgreSQL until 9.5.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [DOCS] Use of term Master/Slave

[Stephen Frost]

Alvaro, all,

* Alvaro Herrera (alvhe...@2ndquadrant.com) wrote:
> Simon Riggs wrote:
> > On 31 July 2017 at 22:13,   wrote:
> > > The following documentation comment has been logged on the website:
> > >
> > > Page: https://www.postgresql.org/docs/9.6/static/release-9-6.html
> > > Description:
> > >
> > > Wondering why PostgreSQL still uses the terms master and slave when there
> > > are other terms like primary/secondary that can be used in the same 
> > > manner.
> > 
> > Do you think primary/secondary is more descriptive?
> 
> I think "primary" is fine, but "secondary" isn't.
> 
> > I started using the terms Primary and Secondary in the original use,
> > but I think we've moved away from that towards Master/Standby, which
> > fits better with a world where "muti-master" is a frequently used term
> > and an eventual goal in core. Multi-primary doesn't seem to make much
> > sense.
> 
> Elsewhere we've started using the terms "origin" and "replica".
> "Multi-origin" sounds sensible enough to me whereas "multi-primary"
> doesn't.

I don't feel like we see much of that terminology being used, whereas
'primary' and 'replica' seem to be more common (particularly since
that's what the big O company uses).

Multi-origin doesn't "feel" any better to me than multi-primary does
(neither is great...), but when it comes to the logical replication side
of things, publishers and subscribers does seem to fit well and so I'm
not entirely sure that we actually need to use the terms "multi-primary"
or "multi-origin"..?

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [DOCS] Row Level Security Policies documentation doesn't mention lack of support for views

[Stephen Frost]

David, Bruce,

* David G. Johnston (david.g.johns...@gmail.com) wrote:
> The CREATE VIEW documentation links to
> 
> https://www.postgresql.org/docs/9.6/static/rules-privileges.html
> 
> which covers this dynamic in considerable detail (and there is a blurb on
> the CREATE VIEW page as well), and specifically:
> 
> "Relations that are used due to rules get checked against the privileges of
> the rule owner, not the user invoking the rule."
> 
> It does feel like an additional blurb about views and a link to the above
> page would be warranted on the ddl-rowsecurity.html page.

I tend to agree, almost always, that additional documentation is a
benefit.  The only drawback to it is that, sometimes, we end up saying
the same thing too much and that leads to readers skipping past
important sections.

I do think we need to provide more documentation around how views and
our privilege system work as I find that the question comes up somewhat
regularly.  Note that this isn't RLS specific, but applies to both the
GRANT system and RLS- views are executed as the user of the view and not
with the privileges of the view user.

I can certainly try to help with crafting additional documentation
around this once I'm back from PostgresOpen in San Francisco next week.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [DOCS] inappropriate word 'victim'

[Stephen Frost]

Tom, all,

* Tom Lane (t...@sss.pgh.pa.us) wrote:
> "Jonathan S. Katz"  writes:
> > So there is a precedent for using “target” for this command.
> 
> I wouldn't object to substituting "target" for "victim"; they're both
> pretty specific, and there's little risk of misunderstanding which
> database is meant.  I don't know if that amounts to much of an
> advance in political correctness, though.

Based on my review of what 'dict' returns for each, 'target' is
distinctly more appropriate.  Both definitions in WordNet for
'victim' refer to a 'person' and other dictionaries definitely
refer to 'person or living creature' more than not.

The definitions for 'target', on the other hand, more generally refer to
a goal or something which is being shot at and the references to
'person' in those definitions appears more in the vein of "covering all
bases" as it relates to 'person, place, or thing'.

> I'm less happy about substituting vaguer words like "subject".
> Particularly for non-native English speakers, that seems like it
> could be confusing --- eg, if you know the distinction between
> subject and object of a sentence, you might think it means the
> DB where the command is being issued.

Agreed.

Thanks!

Stephen


signature.asc
Description: Digital signature

[DOCS] Migration to pglister - Before

[Stephen Frost]

Greetings,

We will be migrating these lists to pglister in the next few minutes.

This final email on the old list system is intended to let you know
that future emails will have different headers and you will need to
adjust your filters.

The changes which we expect to be most significant to users can be found
on the wiki here: https://wiki.postgresql.org/wiki/PGLister_Announce

Once the migration of these lists is complete, an 'after' email will be
sent out.

Thanks!

Stephen


signature.asc
Description: Digital signature