Tag: postgresql

Waiting for 9.5 – Row-Level Security Policies (RLS)

On 19th of September, Stephen Frost committed patch:

Row-Level Security Policies (RLS)
 
Building on the updatable security-barrier views work, add the
ability to define policies on tables to limit the set of rows
which are returned from a query and which are allowed to be added
to a table.  Expressions defined by the policy for filtering are
added to the security barrier quals of the query, while expressions
defined to check records being added to a table are added to the
with-check options of the query.
 
New top-level commands are CREATE/ALTER/DROP POLICY and are
controlled by the table owner.  Row Security is able to be enabled
and disabled by the owner on a per-table basis using
ALTER TABLE .. ENABLE/DISABLE ROW SECURITY.
 
Per discussion, ROW SECURITY is disabled on tables by default and
must be enabled for policies on the table to be used.  If no
policies exist on a table with ROW SECURITY enabled, a default-deny
policy is used and no records will be visible.
 
By default, row security is applied at all times except for the
table owner and the superuser.  A new GUC, row_security, is added
which can be set to ON, OFF, or FORCE.  When set to FORCE, row
security will be applied even for the table owner and superusers.
When set to OFF, row security will be disabled when allowed and an
error will be thrown if the user does not have rights to bypass row
security.
 
Per discussion, pg_dump sets row_security = OFF by default to ensure
that exports and backups will have all data in the table or will
error if there are insufficient privileges to bypass row security.
A new option has been added to pg_dump, --enable-row-security, to
ask pg_dump to export with row security enabled.
 
A new role capability, BYPASSRLS, which can only be set by the
superuser, is added to allow other users to be able to bypass row
security using row_security = OFF.
 
Many thanks to the various individuals who have helped with the
design, particularly Robert Haas for his feedback.
 
Authors include Craig Ringer, KaiGai Kohei, Adam Brightwell, Dean
Rasheed, with additional changes and rework by me.
 
Reviewers have included all of the above, Greg Smith,
Jeff McCormick, and Robert Haas.

Continue reading Waiting for 9.5 – Row-Level Security Policies (RLS)

Waiting for 9.5 – Add width_bucket(anyelement, anyarray).

On 9th of September, Tom Lane committed patch:

Add width_bucket(anyelement, anyarray).
 
This provides a convenient method of classifying input values into buckets
that are not necessarily equal-width.  It works on any sortable data type.
 
The choice of function name is a bit debatable, perhaps, but showing that
there's a relationship to the SQL standard's width_bucket() function seems
more attractive than the other proposals.
 
Petr Jelinek, reviewed by Pavel Stehule

Continue reading Waiting for 9.5 – Add width_bucket(anyelement, anyarray).

Waiting for 9.5 – Add psql PROMPT variable showing which line of a statement is being edited.

On 2nd of September, Andres Freund committed patch:

Add psql PROMPT variable showing which line of a statement is being edited.
 
The new %l substitution shows the line number inside a (potentially
multi-line) statement starting from one.
 
Author: Sawada Masahiko, heavily editorialized by me.
Reviewed-By: Jeevan Chalke, Alvaro Herrera

Continue reading Waiting for 9.5 – Add psql PROMPT variable showing which line of a statement is being edited.

Waiting for 9.5 – Support ALTER SYSTEM RESET command.

On 2nd of September, Fujii Masao committed patch:

Support ALTER SYSTEM RESET command.
 
This patch allows us to execute ALTER SYSTEM RESET command to
remove the configuration entry from postgresql.auto.conf.
 
Vik Fearing, reviewed by Amit Kapila and me.

Continue reading Waiting for 9.5 – Support ALTER SYSTEM RESET command.

Waiting for 9.5 – Implement ALTER TABLE .. SET LOGGED / UNLOGGED

On 22nd of August, Alvaro Herrera committed patch:

Implement ALTER TABLE .. SET LOGGED / UNLOGGED
 
This enables changing permanent (logged) tables to unlogged and
vice-versa.
 
(Docs for ALTER TABLE / SET TABLESPACE got shuffled in an order that
hopefully makes more sense than the original.)
 
Author: Fabrízio de Royes Mello
Reviewed by: Christoph Berg, Andres Freund, Thom Brown
Some tweaking by Álvaro Herrera

Continue reading Waiting for 9.5 – Implement ALTER TABLE .. SET LOGGED / UNLOGGED

Waiting for 9.5 – Implement IMPORT FOREIGN SCHEMA.

On 10th of July, Tom Lane committed patch:

Implement IMPORT FOREIGN SCHEMA.
 
This command provides an automated way to create foreign table definitions
that match remote tables, thereby reducing tedium and chances for error.
In this patch, we provide the necessary core-server infrastructure and
implement the feature fully in the postgres_fdw foreign-data wrapper.
Other wrappers will throw a "feature not supported" error until/unless
they are updated.
 
Ronan Dunklau and Michael Paquier, additional work by me

Continue reading Waiting for 9.5 – Implement IMPORT FOREIGN SCHEMA.

Waiting for 9.5 – Add cluster_name GUC which is included in process titles if set.

On 29th of June, Andres Freund committed patch:

Add cluster_name GUC which is included in process titles if set.
 
When running several postgres clusters on one OS instance it's often
inconveniently hard to identify which "postgres" process belongs to
which postgres instance.
 
Add the cluster_name GUC, whose value will be included as part of the
process titles if set. With that processes can more easily identified
using tools like 'ps'.
 
To avoid problems with encoding mismatches between postgresql.conf,
consoles, and individual databases replace non-ASCII chars in the name
with question marks. The length is limited to NAMEDATALEN to make it
less likely to truncate important information at the end of the
status.
 
Thomas Munro, with some adjustments by me and review by a host of people.

Continue reading Waiting for 9.5 – Add cluster_name GUC which is included in process titles if set.

Waiting for 9.5 – Implement UPDATE tab SET (col1,col2,…) = (SELECT …), …

On 18th of June, Tom Lane committed patch:

Implement UPDATE tab SET (col1,col2,...) = (SELECT ...), ...
 
This SQL-standard feature allows a sub-SELECT yielding multiple columns
(but only one row) to be used to compute the new values of several columns
to be updated.  While the same results can be had with an independent
sub-SELECT per column, such a workaround can require a great deal of
duplicated computation.
 
The standard actually says that the source for a multi-column assignment
could be any row-valued expression.  The implementation used here is
tightly tied to our existing sub-SELECT support and can't handle other
cases; the Bison grammar would have some issues with them too.  However,
I don't feel too bad about this since other cases can be converted into
sub-SELECTs.  For instance, "SET (a,b,c) = row_valued_function(x)" could
be written "SET (a,b,c) = (SELECT * FROM row_valued_function(x))".

Continue reading Waiting for 9.5 – Implement UPDATE tab SET (col1,col2,…) = (SELECT …), …