Subject: Re: Setting up a database with minimum access rights
To: Jeff Janes <jeff.janes@gmail.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
References: <b41dd282-c77f-1fe4-bb44-ef8308562c51@gmail.com>
 <CAMkU=1wKgv9SC9J-QCZhgWa0-7=bSnQNLD0Ox0pay3ssX52biA@mail.gmail.com>
From: Bo Victor Thomsen <bo.victor.thomsen@gmail.com>
Message-ID: <90829287-de11-7949-061c-5eb914b73393@gmail.com>
Date: Sun, 26 Apr 2020 21:36:51 +0200
User-Agent: Mozilla/5.0 (Windows NT 6.3; WOW64; rv:68.0) Gecko/20100101
 Thunderbird/68.7.0
MIME-Version: 1.0
In-Reply-To: 
 <CAMkU=1wKgv9SC9J-QCZhgWa0-7=bSnQNLD0Ox0pay3ssX52biA@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------F6DD9774EC52BBB9BAAD6823"
Content-Language: en-GB
Precedence: bulk

This is a multi-part message in MIME format.
--------------F6DD9774EC52BBB9BAAD6823
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit

Hi Jeff -

Spot on !!  (Right hand reaching for the Postgres manual,  left hand 
searching for "Brown paper bag to pull over head" )

I was executing the script with user "postgres" and creating the table 
with another user "greenadm_adm".

With the missing qualifier "FOR USER.."  in the "ADD DEFAULT 
PRIVILEGES.. " command, the command only works for tables created by 
user "postgres".

Using the same user for both scripting and creating tables works.

I owe you a large cold beer (or whatever beverage you prefer) If we ever 
meet in person :-)

-- 
Med venlig hilsen / Kind regards

Bo Victor Thomsen

Den 26-04-2020 kl. 19:37 skrev Jeff Janes:
> On Sun, Apr 26, 2020 at 12:57 PM Bo Victor Thomsen 
> <bo.victor.thomsen@gmail.com <mailto:bo.victor.thomsen@gmail.com>> wrote:
>
> ...
>
>     After execution of the above commands, I create a table in schema
>     "data_schema" using the "postgres" superuser. But I can't access the
>     created table with user "loki"
>
>
> Were all the above steps executed as "postgres", or was just the 
> CREATE TABLE statement done as that user?
>
> If it is all done as "postgres" superuser then it works for me.  Your 
> example ALTER DEFAULT PRIVILEGES without specifying the FOR ROLE only 
> applies to objects created by the same user who executed the ALTER 
> DEFAULT PRIVILEGES.
>
> Cheers,
>
> Jeff


--------------F6DD9774EC52BBB9BAAD6823
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p>Hi Jeff -</p>
    <p>Spot on !!  (Right hand reaching for the Postgres manual,  left
      hand searching for "Brown paper bag to pull over head" )</p>
    <p>I was executing the script with user "postgres" and creating the
      table with another user "greenadm_adm". <br>
    </p>
    <p>With the missing qualifier "FOR USER.."  in the "ADD DEFAULT
      PRIVILEGES.. " command, the command only works for tables created
      by user "postgres". <br>
    </p>
    <p>Using the same user for both scripting and creating tables works.</p>
    <p>I owe you a large cold beer (or whatever beverage you prefer) If
      we ever meet in person :-) <br>
    </p>
    <p> </p>
    <pre class="moz-signature" cols="72">-- 
Med venlig hilsen / Kind regards

Bo Victor Thomsen</pre>
    <div class="moz-cite-prefix">Den 26-04-2020 kl. 19:37 skrev Jeff
      Janes:<br>
    </div>
    <blockquote type="cite"
cite="mid:CAMkU=1wKgv9SC9J-QCZhgWa0-7=bSnQNLD0Ox0pay3ssX52biA@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="ltr">
        <div dir="ltr">On Sun, Apr 26, 2020 at 12:57 PM Bo Victor
          Thomsen &lt;<a href="mailto:bo.victor.thomsen@gmail.com"
            moz-do-not-send="true">bo.victor.thomsen@gmail.com</a>&gt;
          wrote:</div>
        <div dir="ltr"><br>
        </div>
        <div dir="ltr">...</div>
        <div class="gmail_quote">
          <div> </div>
          <blockquote class="gmail_quote" style="margin:0px 0px 0px
            0.8ex;border-left:1px solid
            rgb(204,204,204);padding-left:1ex">After execution of the
            above commands, I create a table in schema <br>
            "data_schema" using the "postgres" superuser. But I can't
            access the <br>
            created table with user "loki"<br>
          </blockquote>
          <div><br>
          </div>
          <div>Were all the above steps executed as "postgres", or was
            just the CREATE TABLE statement done as that user?</div>
          <div><br>
          </div>
          <div>If it is all done as "postgres" superuser then it works
            for me.  Your example ALTER DEFAULT PRIVILEGES without
            specifying the FOR ROLE only applies to objects created by
            the same user who executed the ALTER DEFAULT PRIVILEGES.</div>
          <div><br>
          </div>
          <div>Cheers,</div>
          <div><br>
          </div>
          <div>Jeff</div>
        </div>
      </div>
    </blockquote>
    <pre class="moz-signature" cols="72">
</pre>
  </body>
</html>

--------------F6DD9774EC52BBB9BAAD6823--