{"id":16513,"date":"2021-07-01T06:37:56","date_gmt":"2021-07-01T04:37:56","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/new-predefined-roles-for-postgresql-14\/"},"modified":"2021-07-01T06:37:56","modified_gmt":"2021-07-01T04:37:56","slug":"new-predefined-roles-for-postgresql-14","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/new-predefined-roles-for-postgresql-14\/","title":{"rendered":"New predefined roles for PostgreSQL 14"},"content":{"rendered":"

Time is moving fast and PostgreSQL 14 is already in beta 2<\/a>. PostgreSQL 14 will ship with a lot of new features and in this post will look at a smaller one: There are three new predefined roles: pg_read_all_data, pg_write_all_data and pg_database_owner. While it seems to be obvious what the first two roles are about, the third one might sound strange at the beginning. Let’s have a look.<\/p>\n

<\/p>\n

We’ll start with a sample setup, as always:<\/p>\n

\npostgres=# create user u1 with login password 'u1';\nCREATE ROLE\npostgres=# create user u2 with login password 'u2';\nCREATE ROLE\npostgres=# create user u3 with login password 'u3';\nCREATE ROLE\npostgres=# create schema s1;\nCREATE SCHEMA\npostgres=# create schema s2;\nCREATE SCHEMA\npostgres=# alter schema s1 owner to u1;\nALTER SCHEMA\npostgres=# alter schema s2 owner to u2;\nALTER SCHEMA\npostgres=# c postgres u1\nYou are now connected to database \"postgres\" as user \"u1\".\npostgres=> create table s1.t1 ( a int, b text, c date);\nCREATE TABLE\npostgres=> insert into s1.t1 select i,i::text,now() from generate_series(1,1000000) i;\nINSERT 0 1000000\nostgres=> c postgres u2\nYou are now connected to database \"postgres\" as user \"u2\".\npostgres=> create table s2.t1 ( a int, b text, c date);\nCREATE TABLE\npostgres=> insert into s2.t1 select i,i::text,now() from generate_series(1,1000000) i;\nINSERT 0 1000000\npostgres=> c postgres postgres\nYou are now connected to database \"postgres\" as user \"postgres\".\n<\/pre>\n
We’ve created three users (u1,u2,u3) and two schemas (s1,s2). The s1 schema is owned by u1 and the s2 schema is owned by u2. Both users (u1,u2) have a table with some data in their schemas. The u3 user currently does not have anything.<\/p>\n
When we connect as user u3 we of course do not have any permissions on the schemas we created above:<\/p>\n
\npostgres=# c postgres u3\nYou are now connected to database \"postgres\" as user \"u3\".\npostgres=> select count(*) from s1.t1;\nERROR:  permission denied for schema s1\nLINE 1: select count(*) from s1.t1;\n                             ^\npostgres=> select count(*) from s2.t1;\nERROR:  permission denied for schema s2\nLINE 1: select count(*) from s2.t1;\n                             ^\npostgres=> c postgres postgres\nYou are now connected to database \"postgres\" as user \"postgres\".\n<\/pre>\n
Granting pg_read_all_data to u3 gives as read access to all objects:<\/p>\n
\npostgres=# grant pg_read_all_data to u3;\nGRANT ROLE\npostgres=# c postgres u3\nYou are now connected to database \"postgres\" as user \"u3\".\npostgres=> select count(*) from s1.t1;\n  count  \n---------\n 1000000\n(1 row)\n\npostgres=> select count(*) from s2.t1;\n  count  \n---------\n 1000000\n(1 row)\n<\/pre>\n
Writing data or creating object is not yet possible:<\/p>\n
\npostgres=> select current_user;\n current_user \n--------------\n u3\n(1 row)\n\npostgres=> create table s1.t2(a int);\nERROR:  permission denied for schema s1\nLINE 1: create table s1.t2(a int);\n                     ^\npostgres=> create table s2.t2(a int);\nERROR:  permission denied for schema s2\nLINE 1: create table s2.t2(a int);\n\npostgres=> c postgres postgres\nYou are now connected to database \"postgres\" as user \"postgres\".\n<\/pre>\n
This is what pg_write_all_data seems to be about:<\/p>\n
\npostgres=> c postgres postgres\nYou are now connected to database \"postgres\" as user \"postgres\".\npostgres=# grant pg_write_all_data to u3;\nGRANT ROLE\npostgres=# c postgres u3\nYou are now connected to database \"postgres\" as user \"u3\".\npostgres=> create table s1.t2(a int);\nERROR:  permission denied for schema s1\nLINE 1: create table s1.t2(a int);\n                     ^\npostgres=> create table s2.t2(a int);\nERROR:  permission denied for schema s2\nLINE 1: create table s2.t2(a int);\n<\/pre>\n
This role does not grant permissions to create new objects but it grant permission to write data into existing relations:<\/p>\n
\npostgres=> select current_user;\n current_user \n--------------\n u3\n(1 row)\n\npostgres=> insert into s1.t1 values (-1,'xxx',now() );\nINSERT 0 1\npostgres=> insert into s2.t1 values (-1,'xxx',now() );\nINSERT 0 1\n<\/pre>\n
As roles are global objects that works for all databases in a cluster:<\/p>\n
\npostgres=> c postgres postgres\nYou are now connected to database \"postgres\" as user \"postgres\".\npostgres=# create database db1;\nCREATE DATABASE\npostgres=# c db1\nYou are now connected to database \"db1\" as user \"postgres\".\ndb1=# create schema s1;\nCREATE SCHEMA\ndb1=# create table s1.t1(a int);\nCREATE TABLE\ndb1=# insert into s1.t1 values(1);\nINSERT 0 1\ndb1=# c db1 u3\nYou are now connected to database \"db1\" as user \"u3\".\ndb1=> select * from s1.t1;\n a \n---\n 1\n(1 row)\n\ndb1=> insert into s1.t1 values(2);\nINSERT 0 1\n<\/pre>\n
This is really nice if you want to give a specific user or role read and\/or write permissions for all objects in all databases.<\/p>\n
Finally the third predefined role: pg_database_owner. What is this about? Let’s grant that role to the u3 user:<\/p>\n
\ndb1=> c postgres postgres\nYou are now connected to database \"postgres\" as user \"postgres\".\npostgres=# grant pg_database_owner to u3;\nERROR:  role \"pg_database_owner\" cannot have explicit members\n<\/pre>\n
A new predefined role that can not be granted explicitly? Looking at the role definition it can not do much:<\/p>\n
\npostgres=# x\nExpanded display is on.\npostgres=# select * from pg_roles where rolname = 'pg_database_owner';\n-[ RECORD 1 ]--+------------------\nrolname        | pg_database_owner\nrolsuper       | f\nrolinherit     | t\nrolcreaterole  | f\nrolcreatedb    | f\nrolcanlogin    | f\nrolreplication | f\nrolconnlimit   | -1\nrolpassword    | ********\nrolvaliduntil  | \nrolbypassrls   | f\nrolconfig      | \noid            | 6171\n<\/pre>\n
What is it there for then? The answer is here<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"
Time is moving fast and PostgreSQL 14 is already in beta 2. PostgreSQL 14 will ship with a lot of new features and in this post will look at a smaller one: There are three new predefined roles: pg_read_all_data, pg_write_all_data and pg_database_owner. While it seems to be obvious what the first two roles are about, […]<\/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],"type_dbi":[],"class_list":["post-16513","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql"],"acf":[],"yoast_head":"\nNew predefined roles for PostgreSQL 14 - 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\/new-predefined-roles-for-postgresql-14\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"New predefined roles for PostgreSQL 14\" \/>\n<meta property=\"og:description\" content=\"Time is moving fast and PostgreSQL 14 is already in beta 2. PostgreSQL 14 will ship with a lot of new features and in this post will look at a smaller one: There are three new predefined roles: pg_read_all_data, pg_write_all_data and pg_database_owner. While it seems to be obvious what the first two roles are about, […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/new-predefined-roles-for-postgresql-14\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2021-07-01T04:37:56+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=\"5 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\\\/new-predefined-roles-for-postgresql-14\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/new-predefined-roles-for-postgresql-14\\\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"New predefined roles for PostgreSQL 14\",\"datePublished\":\"2021-07-01T04:37:56+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/new-predefined-roles-for-postgresql-14\\\/\"},\"wordCount\":300,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/new-predefined-roles-for-postgresql-14\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/new-predefined-roles-for-postgresql-14\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/new-predefined-roles-for-postgresql-14\\\/\",\"name\":\"New predefined roles for PostgreSQL 14 - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2021-07-01T04:37:56+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/new-predefined-roles-for-postgresql-14\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/new-predefined-roles-for-postgresql-14\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/new-predefined-roles-for-postgresql-14\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"New predefined roles for PostgreSQL 14\"}]},{\"@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":"New predefined roles for PostgreSQL 14 - 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\/new-predefined-roles-for-postgresql-14\/","og_locale":"en_US","og_type":"article","og_title":"New predefined roles for PostgreSQL 14","og_description":"Time is moving fast and PostgreSQL 14 is already in beta 2. PostgreSQL 14 will ship with a lot of new features and in this post will look at a smaller one: There are three new predefined roles: pg_read_all_data, pg_write_all_data and pg_database_owner. While it seems to be obvious what the first two roles are about, […]","og_url":"https:\/\/www.dbi-services.com\/blog\/new-predefined-roles-for-postgresql-14\/","og_site_name":"dbi Blog","article_published_time":"2021-07-01T04:37:56+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/new-predefined-roles-for-postgresql-14\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/new-predefined-roles-for-postgresql-14\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"New predefined roles for PostgreSQL 14","datePublished":"2021-07-01T04:37:56+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/new-predefined-roles-for-postgresql-14\/"},"wordCount":300,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/new-predefined-roles-for-postgresql-14\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/new-predefined-roles-for-postgresql-14\/","url":"https:\/\/www.dbi-services.com\/blog\/new-predefined-roles-for-postgresql-14\/","name":"New predefined roles for PostgreSQL 14 - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2021-07-01T04:37:56+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/new-predefined-roles-for-postgresql-14\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/new-predefined-roles-for-postgresql-14\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/new-predefined-roles-for-postgresql-14\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"New predefined roles for PostgreSQL 14"}]},{"@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\/16513","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=16513"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/16513\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=16513"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=16513"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=16513"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=16513"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}