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

On Sun, Apr 26, 2020 at 12:57 PM Bo Victor Thomsen <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