Thread: pgsql: Row-Level Security Policies (RLS)
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. Branch ------ master Details ------- http://git.postgresql.org/pg/commitdiff/491c029dbc4206779cf659aa0ff986af7831d2ff Modified Files -------------- doc/src/sgml/catalogs.sgml | 100 ++ doc/src/sgml/config.sgml | 40 + doc/src/sgml/event-trigger.sgml | 18 + doc/src/sgml/keywords.sgml | 7 + doc/src/sgml/ref/allfiles.sgml | 3 + doc/src/sgml/ref/alter_policy.sgml | 135 ++ doc/src/sgml/ref/alter_role.sgml | 3 + doc/src/sgml/ref/alter_table.sgml | 17 + doc/src/sgml/ref/create_policy.sgml | 318 ++++ doc/src/sgml/ref/create_role.sgml | 20 + doc/src/sgml/ref/drop_policy.sgml | 109 ++ doc/src/sgml/reference.sgml | 3 + src/backend/catalog/Makefile | 2 +- src/backend/catalog/aclchk.c | 19 + src/backend/catalog/dependency.c | 9 + src/backend/catalog/heap.c | 1 + src/backend/catalog/objectaddress.c | 58 + src/backend/catalog/system_views.sql | 32 +- src/backend/commands/Makefile | 2 +- src/backend/commands/alter.c | 4 + src/backend/commands/copy.c | 66 +- src/backend/commands/createas.c | 14 + src/backend/commands/dropcmds.c | 9 + src/backend/commands/event_trigger.c | 3 + src/backend/commands/policy.c | 988 +++++++++++ src/backend/commands/tablecmds.c | 70 + src/backend/commands/user.c | 46 + src/backend/executor/execMain.c | 18 +- src/backend/nodes/copyfuncs.c | 37 +- src/backend/nodes/equalfuncs.c | 33 +- src/backend/nodes/outfuncs.c | 1 + src/backend/nodes/readfuncs.c | 1 + src/backend/optimizer/plan/planner.c | 3 + src/backend/optimizer/plan/setrefs.c | 8 +- src/backend/parser/gram.y | 153 +- src/backend/rewrite/Makefile | 3 +- src/backend/rewrite/rewriteHandler.c | 106 +- src/backend/rewrite/rowsecurity.c | 557 ++++++ src/backend/tcop/utility.c | 31 + src/backend/utils/adt/acl.c | 3 +- src/backend/utils/adt/ri_triggers.c | 29 +- src/backend/utils/cache/plancache.c | 45 +- src/backend/utils/cache/relcache.c | 28 +- src/backend/utils/misc/guc.c | 30 + src/backend/utils/misc/postgresql.conf.sample | 1 + src/bin/pg_dump/common.c | 4 + src/bin/pg_dump/pg_backup.h | 1 + src/bin/pg_dump/pg_backup_archiver.c | 9 + src/bin/pg_dump/pg_dump.c | 308 +++- src/bin/pg_dump/pg_dump.h | 22 +- src/bin/pg_dump/pg_dump_sort.c | 11 +- src/bin/pg_dump/pg_dumpall.c | 23 +- src/bin/pg_dump/pg_restore.c | 4 + src/bin/psql/describe.c | 150 +- src/bin/psql/tab-complete.c | 158 +- src/include/catalog/catversion.h | 2 +- src/include/catalog/dependency.h | 1 + src/include/catalog/indexing.h | 6 + src/include/catalog/pg_authid.h | 12 +- src/include/catalog/pg_class.h | 24 +- src/include/catalog/pg_rowsecurity.h | 53 + src/include/commands/policy.h | 33 + src/include/miscadmin.h | 1 + src/include/nodes/nodes.h | 2 + src/include/nodes/parsenodes.h | 33 + src/include/nodes/plannodes.h | 3 + src/include/nodes/relation.h | 3 + src/include/optimizer/planmain.h | 3 +- src/include/parser/kwlist.h | 1 + src/include/rewrite/rowsecurity.h | 80 + src/include/utils/acl.h | 2 + src/include/utils/plancache.h | 4 + src/include/utils/rel.h | 2 + src/test/regress/expected/dependency.out | 20 +- src/test/regress/expected/privileges.out | 46 +- src/test/regress/expected/rowsecurity.out | 2236 +++++++++++++++++++++++++ src/test/regress/expected/rules.out | 30 +- src/test/regress/expected/sanity_check.out | 1 + src/test/regress/expected/updatable_views.out | 36 +- src/test/regress/parallel_schedule | 2 +- src/test/regress/serial_schedule | 1 + src/test/regress/sql/rowsecurity.sql | 921 ++++++++++ 82 files changed, 7279 insertions(+), 152 deletions(-)
Stephen Frost <sfrost@snowman.net> writes: > Row-Level Security Policies (RLS) In http://www.postgresql.org/docs/devel/static/sql-createpolicy.html in Per-Command policies DELETE is mentionned twice, once for UPDATE and once for DELETE. Also, I guess it would be useful to provide for some examples on how to use the feature? I'm not seeing any high level description of it with use case examples… and I think this feature does warrant a high level introductory chapter in the manual… Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
On 9/19/14 11:41 AM, Stephen Frost wrote: > Row-Level Security Policies (RLS) src/include/commands/policy.h needs to include a file that defines Relation, so that it can stand on its own.
* Peter Eisentraut (peter_e@gmx.net) wrote: > On 9/19/14 11:41 AM, Stephen Frost wrote: > > Row-Level Security Policies (RLS) > > src/include/commands/policy.h needs to include a file that defines > Relation, so that it can stand on its own. Hum. I wonder if that's because I got a bit over-aggressive at removing #includes while reviewing the patch. In any case, will fix. Thanks! Stephen
Attachment
Stephen Frost wrote: > * Peter Eisentraut (peter_e@gmx.net) wrote: > > On 9/19/14 11:41 AM, Stephen Frost wrote: > > > Row-Level Security Policies (RLS) > > > > src/include/commands/policy.h needs to include a file that defines > > Relation, so that it can stand on its own. > > Hum. I wonder if that's because I got a bit over-aggressive at removing > #includes while reviewing the patch. In any case, will fix. This kind of problem is easy to miss. We need some way to have includes checked and failures reported by buildfarm, or perhaps directly during compilation. I have a make rule for that somewhere, gcc-dependent AFAIK, but it's ugly and leaves .gch files behind. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On 2014-09-21 13:41:55 -0300, Alvaro Herrera wrote: > Stephen Frost wrote: > > * Peter Eisentraut (peter_e@gmx.net) wrote: > > > On 9/19/14 11:41 AM, Stephen Frost wrote: > > > > Row-Level Security Policies (RLS) > > > > > > src/include/commands/policy.h needs to include a file that defines > > > Relation, so that it can stand on its own. > > > > Hum. I wonder if that's because I got a bit over-aggressive at removing > > #includes while reviewing the patch. In any case, will fix. > > This kind of problem is easy to miss. We need some way to have includes > checked and failures reported by buildfarm, or perhaps directly during > compilation. I have a make rule for that somewhere, gcc-dependent > AFAIK, but it's ugly and leaves .gch files behind. IIRC cplupluscheck catches such problem. Annoyingly it doesn't work properly in vpath builds... Master causes these warnings for me btw: /home/andres/src/postgresql/src/backend/commands/policy.c:48:19: warning: type qualifiers ignored on function return type [-Wignored-qualifiers] static const char parse_row_security_command(const char *cmd_name); ^ /home/andres/src/postgresql/src/backend/commands/policy.c:106:1: warning: type qualifiers ignored on function return type [-Wignored-qualifiers] parse_row_security_command(const char *cmd_name) ^ Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Andres,
On Sunday, September 21, 2014, Andres Freund <andres@2ndquadrant.com> wrote:
On Sunday, September 21, 2014, Andres Freund <andres@2ndquadrant.com> wrote:
IIRC cplupluscheck catches such problem. Annoyingly it doesn't work
properly in vpath builds...
Doh- that is annoying as that's almost exclusively what I use..
Master causes these warnings for me btw:
/home/andres/src/postgresql/src/backend/commands/policy.c:48:19:
warning: type qualifiers ignored on function return type
[-Wignored-qualifiers]
static const char parse_row_security_command(const char *cmd_name);
^
/home/andres/src/postgresql/src/backend/commands/policy.c:106:1:
warning: type qualifiers ignored on function return type
[-Wignored-qualifiers]
parse_row_security_command(const char *cmd_name)
^
Right- those were noted up-thread by Andrew and are also on my list.
Thanks!
Stephen