{"id":10405,"date":"2017-08-04T07:52:18","date_gmt":"2017-08-04T05:52:18","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/a-wonderful-postgresql-feature-default-privileges\/"},"modified":"2017-08-04T07:52:18","modified_gmt":"2017-08-04T05:52:18","slug":"a-wonderful-postgresql-feature-default-privileges","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/a-wonderful-postgresql-feature-default-privileges\/","title":{"rendered":"A wonderful PostgreSQL feature: default privileges"},"content":{"rendered":"

Imagine this scenario (which is not so uncommon): You have a lot of objects in a user schema and you want to grant another user access to that tables. You can easily do this by granting<\/a> select on the tables to the user and you’re fine. Really? Maybe now, but what will happen when the user which owns the objects creates new objects? Then you will need to grant those to the second user as well. In PostgreSQL there is an easier solution. Lets go …<\/p>\n

<\/p>\n

Again we start by creating two users each with its own schema:<\/p>\n

\npostgres=# create user a with login password 'a';\nCREATE ROLE\npostgres=# create schema a authorization a;\nCREATE SCHEMA\npostgres=# alter user a set search_path=a;\nALTER ROLE\npostgres=# create user b with login password 'b';\nCREATE ROLE\npostgres=# create schema b authorization b;\nCREATE SCHEMA\npostgres=# alter user b set search_path=b;\nALTER ROLE\npostgres=# du\n                                   List of roles\n Role name |                         Attributes                         | Member of \n-----------+------------------------------------------------------------+-----------\n a         |                                                            | {}\n b         |                                                            | {}\n postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}\n\npostgres=# dn\n  List of schemas\n  Name  |  Owner   \n--------+----------\n a      | a\n b      | b\n public | postgres\n(3 rows)\n<\/pre>\n
User “a” shall be the one owning the objects:<\/p>\n
\npostgres=# c postgres a\nYou are now connected to database \"postgres\" as user \"a\".\npostgres=> create table t1 ( a int );\nCREATE TABLE\npostgres=> create table t2 ( a int );\nCREATE TABLE\npostgres=> insert into t1 (a) values (1);\nINSERT 0 1\npostgres=> insert into t2 (a) values (2);\nINSERT 0 1\npostgres=> d\n       List of relations\n Schema | Name | Type  | Owner \n--------+------+-------+-------\n a      | t1   | table | a\n a      | t2   | table | a\n(2 rows)\n<\/pre>\n
When you want to give user “b” access to these tables you could do:<\/p>\n
\npostgres=> grant select on table t1 to b;\nGRANT\npostgres=> grant select on table t2 to b;\nGRANT\n<\/pre>\n
From now on user “b” should be able to select from the two tables owned by user “a”, right?:<\/p>\n
\npostgres=> c postgres b\nYou are now connected to database \"postgres\" as user \"b\".\npostgres=> select count(*) from a.t1;\nERROR:  permission denied for schema a\nLINE 1: select count(*) from a.t1;\n<\/pre>\n
This is not how it works in PostgreSQL. What you need to do is this:<\/p>\n
\npostgres=> c postgres a\nYou are now connected to database \"postgres\" as user \"a\".\npostgres=> grant usage on schema a to b;\nGRANT\n<\/pre>\n
This allows user “b” access to the schema “a” (remember that a user and a schema are different things in PostgreSQL):<\/p>\n
\npostgres=> c postgres b\nYou are now connected to database \"postgres\" as user \"b\".\npostgres=> select count(*) from a.t1;\n count \n-------\n     1\n(1 row)\n\npostgres=> select count(*) from a.t2;\n count \n-------\n     1\n(1 row)\n<\/pre>\n
What happens now when user “a” creates another object:<\/p>\n
\npostgres=> c postgres a\nYou are now connected to database \"postgres\" as user \"a\".\npostgres=> create table t3 as select * from t1;\nSELECT 1\npostgres=> d\n       List of relations\n Schema | Name | Type  | Owner \n--------+------+-------+-------\n a      | t1   | table | a\n a      | t2   | table | a\n a      | t3   | table | a\n(3 rows)\n<\/pre>\n
Will user “b” be able to select data from it?<\/p>\n
\npostgres=> c postgres b\nYou are now connected to database \"postgres\" as user \"b\".\npostgres=> select count(*) from a.t3;\nERROR:  permission denied for relation t3\n<\/pre>\n
Of course not. The “usage” on a schema grants only access to that schema but not access to the objects in the schema. When we want user “b” being able to select from all tables in schema “a” even when user “a” creates new objects then we can modify the default privileges<\/a>:<\/p>\n
\npostgres=# c postgres postgres\nYou are now connected to database \"postgres\" as user \"postgres\".\npostgres=# alter default privileges in schema a grant select on tables to b;\nALTER DEFAULT PRIVILEGES\n<\/pre>\n
Should user “b” now be able to select from the “t3” table in schema “a”?<\/p>\n
\npostgres=> select current_user;\n current_user \n--------------\n b\n(1 row)\n\npostgres=> select count(*) from a.t3;\nERROR:  permission denied for relation t3\npostgres=> \n<\/pre>\n
No. When you modify the default privileges this will affect only objects created after your modification. Lets create a new table with user “a” in schema “a”:<\/p>\n
\npostgres=> c postgres a\nYou are now connected to database \"postgres\" as user \"a\".\npostgres=> create table t4 as select from t1;\nSELECT 1\n<\/pre>\n
As this table was created after the modification to the default privileges user “b” is allowed to select from it automatically:<\/p>\n
\npostgres=> c postgres b\nYou are now connected to database \"postgres\" as user \"b\".\npostgres=> select count(*) from a.t4;\n count \n-------\n     1\n(1 row)\n<\/pre>\n
When you check the link to the documentation above you’ll notice that you can not only grant select on tables but much more. Hope this helps …<\/p>\n","protected":false},"excerpt":{"rendered":"
Imagine this scenario (which is not so uncommon): You have a lot of objects in a user schema and you want to grant another user access to that tables. You can easily do this by granting select on the tables to the user and you’re fine. Really? Maybe now, but what will happen when the […]<\/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-10405","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql"],"acf":[],"yoast_head":"\nA wonderful PostgreSQL feature: default privileges - 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\/a-wonderful-postgresql-feature-default-privileges\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"A wonderful PostgreSQL feature: default privileges\" \/>\n<meta property=\"og:description\" content=\"Imagine this scenario (which is not so uncommon): You have a lot of objects in a user schema and you want to grant another user access to that tables. You can easily do this by granting select on the tables to the user and you’re fine. Really? Maybe now, but what will happen when the […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/a-wonderful-postgresql-feature-default-privileges\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2017-08-04T05:52:18+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=\"4 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\/a-wonderful-postgresql-feature-default-privileges\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/a-wonderful-postgresql-feature-default-privileges\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"A wonderful PostgreSQL feature: default privileges\",\"datePublished\":\"2017-08-04T05:52:18+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/a-wonderful-postgresql-feature-default-privileges\/\"},\"wordCount\":343,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/a-wonderful-postgresql-feature-default-privileges\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/a-wonderful-postgresql-feature-default-privileges\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/a-wonderful-postgresql-feature-default-privileges\/\",\"name\":\"A wonderful PostgreSQL feature: default privileges - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2017-08-04T05:52:18+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/a-wonderful-postgresql-feature-default-privileges\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/a-wonderful-postgresql-feature-default-privileges\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/a-wonderful-postgresql-feature-default-privileges\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"A wonderful PostgreSQL feature: default privileges\"}]},{\"@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":"A wonderful PostgreSQL feature: default privileges - 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\/a-wonderful-postgresql-feature-default-privileges\/","og_locale":"en_US","og_type":"article","og_title":"A wonderful PostgreSQL feature: default privileges","og_description":"Imagine this scenario (which is not so uncommon): You have a lot of objects in a user schema and you want to grant another user access to that tables. You can easily do this by granting select on the tables to the user and you’re fine. Really? Maybe now, but what will happen when the […]","og_url":"https:\/\/www.dbi-services.com\/blog\/a-wonderful-postgresql-feature-default-privileges\/","og_site_name":"dbi Blog","article_published_time":"2017-08-04T05:52:18+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/a-wonderful-postgresql-feature-default-privileges\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/a-wonderful-postgresql-feature-default-privileges\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"A wonderful PostgreSQL feature: default privileges","datePublished":"2017-08-04T05:52:18+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/a-wonderful-postgresql-feature-default-privileges\/"},"wordCount":343,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/a-wonderful-postgresql-feature-default-privileges\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/a-wonderful-postgresql-feature-default-privileges\/","url":"https:\/\/www.dbi-services.com\/blog\/a-wonderful-postgresql-feature-default-privileges\/","name":"A wonderful PostgreSQL feature: default privileges - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2017-08-04T05:52:18+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/a-wonderful-postgresql-feature-default-privileges\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/a-wonderful-postgresql-feature-default-privileges\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/a-wonderful-postgresql-feature-default-privileges\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"A wonderful PostgreSQL feature: default privileges"}]},{"@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\/10405","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=10405"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/10405\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=10405"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=10405"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=10405"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=10405"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}