{"id":5398,"date":"2015-08-29T11:27:29","date_gmt":"2015-08-29T09:27:29","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/row-level-security-is-coming-to-postgresql\/"},"modified":"2015-08-29T11:27:29","modified_gmt":"2015-08-29T09:27:29","slug":"row-level-security-is-coming-to-postgresql","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/row-level-security-is-coming-to-postgresql\/","title":{"rendered":"Row level security is coming to PostgreSQL"},"content":{"rendered":"

Before PostgreSQL 9.5 (which is in alpha2<\/a> currently) you could grant access to individual columns of a table to users or roles. A little test script to demonstrate this:<\/p>\n

\n\n(postgres@[local]:5432) [postgres] > select version();\n                                                   version                                                    \n--------------------------------------------------------------------------------------------------------------\n PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit\n(1 row)\n\ncreate table t ( id int primary key, name varchar(50), salary bigint );\ninsert into t ( id, name, salary ) values ( 1,'name1',1000), (2,'name2',20000), (3,'name3',3000);\ncreate user u1 UNENCRYPTED PASSWORD 'u1';\ncreate user u2 UNENCRYPTED PASSWORD 'u2';\ngrant select (id,name) on t to u1;\ngrant select (name,salary) on t to u2;\n<\/pre>\n
User u1 has the right to select columns “id” and “name” and user u2 has the right to select columns “name” and “salary”:<\/p>\n
\n(u1@[local]:5432) [postgres] > select user;\n current_user \n--------------\n u1\n(1 row)\n\n(u1@[local]:5432) [postgres] > select id,name from t;\n id | name  \n----+-------\n  1 | name1\n  2 | name2\n  3 | name3\n(3 rows)\n\n(u1@[local]:5432) [postgres] > select id,name,salary from t;\nERROR:  permission denied for relation t\n(u1@[local]:5432) [postgres] > \n\n\n(u2@[local]:5432) [postgres] > select user;\n current_user \n--------------\n u2\n(1 row)\n\n(u2@[local]:5432) [postgres] > select name,salary from t;\n name  | salary \n-------+--------\n name1 |   1000\n name2 |  20000\n name3 |   3000\n(3 rows)\n\n(u2@[local]:5432) [postgres] > select id,name,salary from t;\nERROR:  permission denied for relation t\n(u2@[local]:5432) [postgres] > \n<\/pre>\n
As you can see from the above selecting data from columns the users are not authorized to results in a “permission denied” message. <\/p>\n
Now lets build the same test case on PostgreSQL 9.5 alpha2:<\/p>\n
\npostgres=# select version();\n                                                     version                                                  \n    \n--------------------------------------------------------------------------------------------------------------\n----\n PostgreSQL 9.5alpha2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-\nbit\n(1 row)\n\n\ncreate table t ( id int primary key, name varchar(50), salary bigint );\ninsert into t ( id, name, salary ) values ( 1,'name1',1000), (2,'name2',20000), (3,'name3',3000);\ncreate user u1 UNENCRYPTED PASSWORD 'u1';\ncreate user u2 UNENCRYPTED PASSWORD 'u2';\ngrant select (id,name) on t to u1;\ngrant select (name,salary) on t to u2;\n<\/pre>\n
The new feature “row level security”<\/a> enables us to do the following:<\/p>\n
\nalter table t enable row level security;\n<\/pre>\n
As a first step row level security needs to be enabled for the table. For this the “alter table” command was extended which is already reflected in the documentation for PostgreSQL 9.5<\/a>. <\/p>\n
But this can not be all we need to do, right? No conditions, no checks at this point in time. This is where the policies<\/a> come into the game:<\/p>\n
\ncreate policy policy1 on t for select to u2 using (salary < 3000);\n<\/pre>\n
In words this policy does the following: If the user is “u2” and this user is selecting from “t” then only let the user “u2” see rows that have a salary less than 3000. Does it work?<\/p>\n
\npostgres=> select user;\n\n current_user \n--------------\n u2\n(1 row)\n\npostgres=> select name,salary from t;\n name  | salary \n-------+--------\n name1 |   1000\n(1 row)\n<\/pre>\n
Exactly what the policy was defined for. Do the column permissions still apply?<\/p>\n
\npostgres=> select id from t;\nERROR:  permission denied for relation t\npostgres=> \n<\/pre>\n
Yes, so starting with PostgreSQL 9.5 a mixture of column and row level security is possible. Btw: Describing tables that have policies applied was enhanced, too:<\/p>\n
\npostgres=> d t\n              Table \"public.t\"\n Column |         Type          | Modifiers \n--------+-----------------------+-----------\n id     | integer               | not null\n name   | character varying(50) | \n salary | bigint                | \nIndexes:\n    \"t_pkey\" PRIMARY KEY, btree (id)\nPolicies:\n    POLICY \"policy1\" FOR SELECT\n      TO u2\n      USING ((salary < 3000))\n<\/pre>\n
In addition there is now a new system information function<\/a> which tells if the current user has row level security enabled for a table:<\/p>\n
\npostgres=> select user;\n current_user \n--------------\n u2\n(1 row)\npostgres=> select row_security_active('t');\n row_security_active \n---------------------\n t\n(1 row)\n<\/pre>\n
Conclusion: Yet another great feature which made it into PostgreSQL. If you are willing to alpha\/beta test there is a wiki page<\/a> which describes the best practices.<\/p>\n","protected":false},"excerpt":{"rendered":"
Before PostgreSQL 9.5 (which is in alpha2 currently) you could grant access to individual columns of a table to users or roles. A little test script to demonstrate this: (postgres@[local]:5432) [postgres] > select version(); version ————————————————————————————————————– PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit (1 row) create table t […]<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[77,25],"type_dbi":[],"class_list":["post-5398","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql","tag-security"],"acf":[],"yoast_head":"\nRow level security is coming to PostgreSQL - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/row-level-security-is-coming-to-postgresql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Row level security is coming to PostgreSQL\" \/>\n<meta property=\"og:description\" content=\"Before PostgreSQL 9.5 (which is in alpha2 currently) you could grant access to individual columns of a table to users or roles. A little test script to demonstrate this: (postgres@[local]:5432) [postgres] > select version(); version -------------------------------------------------------------------------------------------------------------- PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit (1 row) create table t […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/row-level-security-is-coming-to-postgresql\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2015-08-29T09:27:29+00:00\" \/>\n<meta name=\"author\" content=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/row-level-security-is-coming-to-postgresql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/row-level-security-is-coming-to-postgresql\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"Row level security is coming to PostgreSQL\",\"datePublished\":\"2015-08-29T09:27:29+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/row-level-security-is-coming-to-postgresql\/\"},\"wordCount\":289,\"commentCount\":0,\"keywords\":[\"PostgreSQL\",\"Security\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/row-level-security-is-coming-to-postgresql\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/row-level-security-is-coming-to-postgresql\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/row-level-security-is-coming-to-postgresql\/\",\"name\":\"Row level security is coming to PostgreSQL - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2015-08-29T09:27:29+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/row-level-security-is-coming-to-postgresql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/row-level-security-is-coming-to-postgresql\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/row-level-security-is-coming-to-postgresql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Row level security is coming to PostgreSQL\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\/\/x.com\/westermanndanie\"],\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Row level security is coming to PostgreSQL - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/row-level-security-is-coming-to-postgresql\/","og_locale":"en_US","og_type":"article","og_title":"Row level security is coming to PostgreSQL","og_description":"Before PostgreSQL 9.5 (which is in alpha2 currently) you could grant access to individual columns of a table to users or roles. A little test script to demonstrate this: (postgres@[local]:5432) [postgres] > select version(); version -------------------------------------------------------------------------------------------------------------- PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit (1 row) create table t […]","og_url":"https:\/\/www.dbi-services.com\/blog\/row-level-security-is-coming-to-postgresql\/","og_site_name":"dbi Blog","article_published_time":"2015-08-29T09:27:29+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/row-level-security-is-coming-to-postgresql\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/row-level-security-is-coming-to-postgresql\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"Row level security is coming to PostgreSQL","datePublished":"2015-08-29T09:27:29+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/row-level-security-is-coming-to-postgresql\/"},"wordCount":289,"commentCount":0,"keywords":["PostgreSQL","Security"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/row-level-security-is-coming-to-postgresql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/row-level-security-is-coming-to-postgresql\/","url":"https:\/\/www.dbi-services.com\/blog\/row-level-security-is-coming-to-postgresql\/","name":"Row level security is coming to PostgreSQL - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2015-08-29T09:27:29+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/row-level-security-is-coming-to-postgresql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/row-level-security-is-coming-to-postgresql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/row-level-security-is-coming-to-postgresql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Row level security is coming to PostgreSQL"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/5398","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=5398"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/5398\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=5398"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=5398"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=5398"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=5398"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}