Re: Setting up a database with minimum access rights

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(dot)victor(dot)thomsen(at)gmail(dot)com <mailto:bo(dot)victor(dot)thomsen(at)gmail(dot)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