2ndQuadrant is now part of EDB

Bringing together some of the world's top PostgreSQL experts.

2ndQuadrant | PostgreSQL
Mission Critical Databases
  • Contact us
  • EN
    • FR
    • IT
    • ES
    • DE
    • PT
  • Support & Services
  • Products
  • Downloads
    • Installers
      • Postgres Installer
      • 2UDA – Unified Data Analytics
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
  • Postgres Learning Center
    • Webinars
      • Upcoming Webinars
      • Webinar Library
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Blog
    • Training
      • Course Catalogue
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
    • Books
      • PostgreSQL 11 Administration Cookbook
      • PostgreSQL 10 Administration Cookbook
      • PostgreSQL High Availability Cookbook – 2nd Edition
      • PostgreSQL 9 Administration Cookbook – 3rd Edition
      • PostgreSQL Server Programming Cookbook – 2nd Edition
      • PostgreSQL 9 Cookbook – Chinese Edition
    • Videos
    • Events
    • PostgreSQL
      • PostgreSQL – History
      • Who uses PostgreSQL?
      • PostgreSQL FAQ
      • PostgreSQL vs MySQL
      • The Business Case for PostgreSQL
      • Security Information
      • Documentation
  • About Us
    • About 2ndQuadrant
    • 2ndQuadrant’s Passion for PostgreSQL
    • News
    • Careers
    • Team Profile
  • Blog
  • Menu Menu
You are here: Home1 / Blog2 / Craig's PlanetPostgreSQL3 / Emulating row security in PostgreSQL 9.4
craig.ringer

Emulating row security in PostgreSQL 9.4

December 9, 2015/0 Comments/in Craig's PlanetPostgreSQL /by craig.ringer

PostgreSQL 9.5 adds declarative row security. You can declare policies on tables and have them enforced automatically – for example, allowing user joe to only see rows with the owner column equal to joe.

This is a great feature, and it’s been a long time coming. It didn’t make it into PostgreSQL 9.4, but automatically updatable security_barrier views did. They and LEAKPROOF functions form part of the foundation on which row security is built. You can use these pieces without the declarative policy support to achieve row-security-like effects in 9.4.

I discussed security_barrier views earlier. That post contains examples of how information can be leaked from a view and how security_barrier views prevent such leaks. I’ll assume you’re familiar with the principles in the rest of this post, and won’t repeat the demonstration of view information leaks etc.

To achieve a similar effect to a row security policy on a table you must revoke all access to the table except by the privileged (but non-superuser) role you wish to have own the view(s). Then create a security_barrier view owned by that privileged role, with a WHERE clause that limits other users’ ability to see rows based on your chosen predicate – you can use current_user, a current_setting call, etc.

For example:

CREATE ROLE secret_manager;
CREATE ROLE bob;
CREATE ROLE sid;

CREATE TABLE user_secrets(
    secret_id integer primary key,
    owner text not null,
    secret text not null
);

ALTER TABLE user_secrets OWNER TO secret_manager;

INSERT INTO user_secrets (secret_id, owner, secret) VALUES
(1, 'bob', 'pancakes'),
(2, 'fred', 'waffles'),
(3, 'anne', 'cake'),
(4, 'sid', 'fraud');

REVOKE ALL ON user_secrets FROM public;

CREATE VIEW filtered_user_secrets
WITH (security_barrier)
AS
SELECT *
FROM user_secrets
WHERE owner = current_user
WITH CHECK OPTION;

ALTER VIEW filtered_user_secrets OWNER TO secret_manager;

GRANT ALL ON filtered_user_secrets TO public;

RESET ROLE;

Now lets see how it works:

test=# SET ROLE bob;
SET
test=> select * from filtered_user_secrets ;
 secret_id | owner |  secret  
-----------+-------+----------
         1 | bob   | pancakes
(1 row)

test=> SET ROLE sid;
SET
test=> select * from filtered_user_secrets ;
 secret_id | owner | secret 
-----------+-------+--------
         4 | sid   | fraud
(1 row)

test=> SELECT * FROM filtered_user_secrets WHERE owner = 'bob';
 secret_id | owner | secret 
-----------+-------+--------
(0 rows)

test=> INSERT INTO filtered_user_secrets (secret_id, owner, secret) VALUES (5, 'sid', 'larceny');
INSERT 0 1
test=> select * from filtered_user_secrets ;
 secret_id | owner | secret  
-----------+-------+---------
         4 | sid   | fraud
         5 | sid   | larceny
(2 rows)

test=> INSERT INTO filtered_user_secrets (secret_id, owner, secret) VALUES (6, 'joe', 'impersonation');
ERROR:  new row violates WITH CHECK OPTION for view "filtered_user_secrets"
DETAIL:  Failing row contains (secret_id, owner, secret) = (6, joe, larceny).

Behaviour is very like a row security policy, but with a few limitations:

  • ALTERing the underlying table won’t make the changes visible in the view. You must drop and recreate the view.
  • It isn’t transparent to applications. Applications need to use the view instead of the underlying table.

The latter point can be addressed to an extent by using schemas and search_path, e.g.

CREATE SCHEMA filtered_tables;

ALTER TABLE user_secrets SET SCHEMA filtered_tables;

-- Leave the view in the public schema and just rename it
ALTER TABLE filtered_user_secrets RENAME TO user_secrets;

Now clients can query the view without caring that it’s a wrapper for the original table.

Instead of using current_user you could instead use current_setting('myapp.active_user'). If you do this, you should set an empty default at the database level so that current_setting doesn’t ERROR if the setting isn’t defined (unless you’re on 9.5 and can use current_setting('myapp.active_user', 't') to ignore missing entries). E.g.

ALTER DATABASE mydatabase SET myapp_active_user = '';

Important: keep in mind that if you use current_setting in a view predicate there are no security policies on user-defined configuration options, so any user who is able to execute arbitrary SQL can change the setting. It remains a useful tool when all queries go through an application with total control over the queries run, but it’s not suitable for restricting the actions of users with direct database connections. The same applies to using SET ROLE to switch the active user when using pooled connections in an application, since any user can just RESET ROLE.

It’s much easier to use 9.5’s row security features, but if you need similar functionality you can already achieve it now.

For even older versions of PostgreSQL you can use SECURITY DEFINER plpgsql functions that RETURN QUERY a filtered view of the underlying table. Performance will tend to be terrible, though, because all rows the user can see must be fetched and then filtered, so most indexes cannot be used. Alternately you can use a plain view, if you don’t allow users to define custom functions so leaks are less of a concern.

Row security, and view based approaches like this, would greatly benefit from the ability to define secure variables that can be set once and cannot be reset, or that can be set only by a particular role (possibly via a SECURITY DEFINER function that does sanity checking). No such functionality will be built in to 9.5, but it might be possible with an extension, a possibility I hope to explore later.

Tags: declarative security, PostgreSQL, row security, security, security_barrier, views
Share this entry
  • Share on Facebook
  • Share on Twitter
  • Share on WhatsApp
  • Share on LinkedIn
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Search

Get in touch with us!

Recent Posts

  • Random Data December 3, 2020
  • Webinar: COMMIT Without Fear – The Beauty of CAMO [Follow Up] November 13, 2020
  • Full-text search since PostgreSQL 8.3 November 5, 2020
  • Random numbers November 3, 2020
  • Webinar: Best Practices for Bulk Data Loading in PostgreSQL [Follow Up] November 2, 2020

Featured External Blogs

Tomas Vondra's Blog

Our Bloggers

  • Simon Riggs
  • Alvaro Herrera
  • Andrew Dunstan
  • Craig Ringer
  • Francesco Canovai
  • Gabriele Bartolini
  • Giulio Calacoci
  • Ian Barwick
  • Marco Nenciarini
  • Mark Wong
  • Pavan Deolasee
  • Petr Jelinek
  • Shaun Thomas
  • Tomas Vondra
  • Umair Shahid

PostgreSQL Cloud

2QLovesPG 2UDA 9.6 backup Barman BDR Business Continuity community conference database DBA development devops disaster recovery greenplum Hot Standby JSON JSONB logical replication monitoring OmniDB open source Orange performance PG12 pgbarman pglogical PG Phriday postgres Postgres-BDR postgres-xl PostgreSQL PostgreSQL 9.6 PostgreSQL10 PostgreSQL11 PostgreSQL 11 PostgreSQL 11 New Features postgresql repmgr Recovery replication security sql wal webinar webinars

Support & Services

24/7 Production Support

Developer Support

Remote DBA for PostgreSQL

PostgreSQL Database Monitoring

PostgreSQL Health Check

PostgreSQL Performance Tuning

Database Security Audit

Upgrade PostgreSQL

PostgreSQL Migration Assessment

Migrate from Oracle to PostgreSQL

Products

HA Postgres Clusters

Postgres-BDR®

2ndQPostgres

pglogical

repmgr

Barman

Postgres Cloud Manager

SQL Firewall

Postgres-XL

OmniDB

Postgres Installer

2UDA

Postgres Learning Center

Introducing Postgres

Blog

Webinars

Books

Videos

Training

Case Studies

Events

About Us

About 2ndQuadrant

What does 2ndQuadrant Mean?

News

Careers 

Team Profile

© 2ndQuadrant Ltd. All rights reserved. | Privacy Policy
  • Twitter
  • LinkedIn
  • Facebook
  • Youtube
  • Mail
Creating a PostgreSQL Service on Ubuntu The End of MongoDB
Scroll to top
×