{"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":28,"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=\"Open source Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Open source Team\" \/>\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\":\"Open source Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"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\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"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\\\/4bae5e5dd8c7d48edbd948772b49b202\",\"name\":\"Open source Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"caption\":\"Open source Team\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/open-source-team\\\/\"}]}<\/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":"Open source Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Open source Team","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":"Open source Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"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\/4bae5e5dd8c7d48edbd948772b49b202"},"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\/4bae5e5dd8c7d48edbd948772b49b202","name":"Open source Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","caption":"Open source Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/open-source-team\/"}]}},"_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\/28"}],"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}]}}