Обсуждение: Setting up a database with minimum access rights

Dear all -

I have a problem with the setup of access rights based on schemas.

The objective is to have a schema with default access rights to tables 
assigned to the schema. And to strip the "public" user group for all 
rights.

So if you create a table in a certain schema ("data_schema") it will be 
generated with the rights defined using a "ALTER DEFAULT ..." command to 
the schema. But I can't get it to work...

Here is the pertinent commands used:

-- Remove all access rights for "public"
REVOKE ALL ON SCHEMA public, data_schema FROM PUBLIC;
REVOKE ALL ON DATABASE greenadm FROM PUBLIC;

-- Create necessary ressource role
CREATE ROLE greenadm_read;

-- Access to database "greenadm" for role "greenadm_read"
GRANT CONNECT, TEMP ON DATABASE greenadm TO greenadm_read;

-- Access to schema for the ressource role
GRANT USAGE ON SCHEMA public, data_schema TO greenadm_read;

-- Default read access rights for ressource role
ALTER DEFAULT PRIVILEGES IN SCHEMA public, data_schema GRANT SELECT  ON 
TABLES    TO greenadm_read;
ALTER DEFAULT PRIVILEGES IN SCHEMA public, data_schema GRANT SELECT  ON 
SEQUENCES TO greenadm_read;
ALTER DEFAULT PRIVILEGES IN SCHEMA public, data_schema GRANT EXECUTE ON 
FUNCTIONS TO greenadm_read;

-- And a login user...
CREATE ROLE loki WITH LOGIN PASSWORD 'sneaky' VALID UNTIL '2021-01-01' 
INHERIT;
GRANT greenadm_read TO loki;

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"

However, if I execute these command after the table creation:

GRANT SELECT  ON ALL TABLES    IN SCHEMA public, data_schema TO 
greenadm_read;
GRANT SELECT  ON ALL SEQUENCES IN SCHEMA public, data_schema TO 
greenadm_read;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public, data_schema TO 
greenadm_read;

it works as expected with read acces to user "loki".  it seem that the 
problem is related to the "ALTER DEFAULT..." commands

I simply can't phantom what I'm doing wrong. (it's probably some newbie 
error :-/ )

-- 
Med venlig hilsen / Kind regards

Bo Victor Thomsen

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