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?










share|improve this question




















  • 1




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

















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?










share|improve this question




















  • 1




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















up vote
1
down vote

favorite









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?










share|improve this question















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






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 8 at 10:06









Laurenz Albe

40.9k92745




40.9k92745










asked Nov 8 at 9:22









Lennart Blom

16411




16411








  • 1




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
















  • 1




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










1




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














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.






share|improve this answer





















    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


















    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
































    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    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.






    share|improve this answer

























      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.






      share|improve this answer























        up vote
        0
        down vote



        accepted







        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.






        share|improve this answer












        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.







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Nov 8 at 10:04









        Laurenz Albe

        40.9k92745




        40.9k92745






























             

            draft saved


            draft discarded



















































             


            draft saved


            draft discarded














            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




















































































            Popular posts from this blog

            Guess what letter conforming each word

            Port of Spain

            Run scheduled task as local user group (not BUILTIN)