Default privileges for new users on public schema?

up vote
1
down vote

favorite

I just created a database with an additional application schema.

And for our Java Spring Boot applications I created a new role with the following SQL scripts for setting up the privileges:

CREATE USER app_role WITH ENCRYPTED PASSWORD '#########';



GRANT ALL ON SCHEMA application TO app_role;

Now my expectation was that I could only create and delete tables within the schema application when logging in with this role.

However, I am also able to create and modify tables in the schema public.

Are there any default privileges for the public schema?

Why can I create tables in schemas I did not grant any privileges to?

edited Nov 8 at 10:06

Laurenz Albe

40.9k92745

asked Nov 8 at 9:22

Lennart Blom

16411

1

alter default privileges ...
– a_horse_with_no_name
Nov 8 at 9:42

add a comment |

up vote
1
down vote

favorite

I just created a database with an additional application schema.

And for our Java Spring Boot applications I created a new role with the following SQL scripts for setting up the privileges:

CREATE USER app_role WITH ENCRYPTED PASSWORD '#########';



GRANT ALL ON SCHEMA application TO app_role;

Now my expectation was that I could only create and delete tables within the schema application when logging in with this role.

However, I am also able to create and modify tables in the schema public.

Are there any default privileges for the public schema?

Why can I create tables in schemas I did not grant any privileges to?

edited Nov 8 at 10:06

Laurenz Albe

40.9k92745

asked Nov 8 at 9:22

Lennart Blom

16411

1

alter default privileges ...
– a_horse_with_no_name
Nov 8 at 9:42

add a comment |

up vote
1
down vote

favorite

I just created a database with an additional application schema.

And for our Java Spring Boot applications I created a new role with the following SQL scripts for setting up the privileges:

CREATE USER app_role WITH ENCRYPTED PASSWORD '#########';



GRANT ALL ON SCHEMA application TO app_role;

Now my expectation was that I could only create and delete tables within the schema application when logging in with this role.

However, I am also able to create and modify tables in the schema public.

Are there any default privileges for the public schema?

Why can I create tables in schemas I did not grant any privileges to?

edited Nov 8 at 10:06

Laurenz Albe

40.9k92745

asked Nov 8 at 9:22

Lennart Blom

16411

I just created a database with an additional application schema.

And for our Java Spring Boot applications I created a new role with the following SQL scripts for setting up the privileges:

CREATE USER app_role WITH ENCRYPTED PASSWORD '#########';



GRANT ALL ON SCHEMA application TO app_role;

Now my expectation was that I could only create and delete tables within the schema application when logging in with this role.

However, I am also able to create and modify tables in the schema public.

Are there any default privileges for the public schema?

Why can I create tables in schemas I did not grant any privileges to?

postgresql privileges

edited Nov 8 at 10:06

Laurenz Albe

40.9k92745

asked Nov 8 at 9:22

Lennart Blom

16411

edited Nov 8 at 10:06

Laurenz Albe

40.9k92745

asked Nov 8 at 9:22

Lennart Blom

16411

edited Nov 8 at 10:06

Laurenz Albe

40.9k92745

edited Nov 8 at 10:06

Laurenz Albe

40.9k92745

edited Nov 8 at 10:06

Laurenz Albe

40.9k92745

asked Nov 8 at 9:22

Lennart Blom

16411

asked Nov 8 at 9:22

Lennart Blom

16411

asked Nov 8 at 9:22

Lennart Blom

16411

1

alter default privileges ...
– a_horse_with_no_name
Nov 8 at 9:42

add a comment |

1

alter default privileges ...
– a_horse_with_no_name
Nov 8 at 9:42

alter default privileges ...
– a_horse_with_no_name
Nov 8 at 9:42

add a comment |

1 Answer
1

active

oldest

votes

up vote
0
down vote

accepted

The public schema has a special role in PostgreSQL, as the documentation describes.

If you don't want that (and it can be a security problem), you can either REVOKE the CREATE privilege or even drop the schema alogether.

answered Nov 8 at 10:04

Laurenz Albe

40.9k92745

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53204724%2fdefault-privileges-for-new-users-on-public-schema%23new-answer', 'question_page');
}
);

Post as a guest

Name

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

up vote
0
down vote

accepted

The public schema has a special role in PostgreSQL, as the documentation describes.

If you don't want that (and it can be a security problem), you can either REVOKE the CREATE privilege or even drop the schema alogether.

answered Nov 8 at 10:04

Laurenz Albe

40.9k92745

add a comment |

up vote
0
down vote

accepted

The public schema has a special role in PostgreSQL, as the documentation describes.

If you don't want that (and it can be a security problem), you can either REVOKE the CREATE privilege or even drop the schema alogether.

answered Nov 8 at 10:04

Laurenz Albe

40.9k92745

add a comment |

up vote
0
down vote

accepted

The public schema has a special role in PostgreSQL, as the documentation describes.

If you don't want that (and it can be a security problem), you can either REVOKE the CREATE privilege or even drop the schema alogether.

answered Nov 8 at 10:04

Laurenz Albe

40.9k92745

The public schema has a special role in PostgreSQL, as the documentation describes.

If you don't want that (and it can be a security problem), you can either REVOKE the CREATE privilege or even drop the schema alogether.

answered Nov 8 at 10:04

Laurenz Albe

40.9k92745

answered Nov 8 at 10:04

Laurenz Albe

40.9k92745

answered Nov 8 at 10:04

Laurenz Albe

40.9k92745

answered Nov 8 at 10:04

Laurenz Albe

40.9k92745

add a comment |

draft saved

draft discarded

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Name

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Agfdhyk