Re: Restricted access on DataBases

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Durumdara
Sent: Mittwoch, 14. September 2016 17:13
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Restricted access on DataBases

Dear Adrian and Charles!

I tried to create a step by step instruction.

The real commands are separated by ";"

Other commands are: "login as username", "use db"

I ran them in PGAdmin with changing the connection to simulate what I feel as problem.
I suppused the ex_dbuser have owner rights to the DB, and with "default privileges" it must see the new tables created by ex_mainuser. Without them I would understand why (inherited role would have diffferent rights on creation).

If I want to represent this in other way, I would say:

- ex_mainuser have all rights as ex_dbuser, but it could have more

- but when ex_dbuser got all rights to future objects, it must see what ex_mainuser created on his database

If this not happened then my idea crashes, because we must login with ex_dbuser to create objects, or we must create all objects by ex_mainuser WITH ONLY OWNER SETTING (as ex_dbuser).

The example:

-- login as su
-- CREATE DATABASE ct_db WITH OWNER = ex_dbuser ENCODING = 'UTF8' TABLESPACE = pg_default template = template0;
-- use ct_db

-- login as ex_dbuser
-- begin; create table t_dbuser (id integer);commit;

-- login as ex_mainuser
-- begin; create table t_mainuser (id integer); commit;

-- login as ex_dbuser
-- select * from t_mainuser; -- ERROR!

-- login as su
-- ALTER DEFAULT PRIVILEGES  GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES TO ex_dbuser;

-- login as ex_mainuser
-- begin; create table t_mainuser2 (id integer); commit;

-- login as ex_dbuser
-- select * from t_mainuser2;  -- ERROR!

-- login as su
-- ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES TO ex_dbuser;

-- login as ex_mainuser
-- begin; create table t_mainuser3 (id integer); commit;

-- login as ex_dbuser
-- select * from t_mainuser3;  -- ERROR!

Thanks: dd

As Adrian said, it would help to know what you granted to whom.

Also it would help to know what the exact error message is that you get. In may give an idea if you forgot somewhere some privilege.

And last but not least it would help to know who is CURRENT_USER and in which DB you are before you execute a statement. I don’t mean what you think it is, but what is delivered by

SELECT SESSION_USER, CURRENT_USER;

SELECT current_database();

Since you are using a graphical tool and submit from some editor queries to the DB, I would not assume that you are sending the query to the right database with the correct user without checking it out.

Could you also provide the result of

\ddp

from a psql shell (you can open one from pgAdmin: click on the database you want to check, in the menu plugins->PSQL Console). This is a list of you custom default privileges.

Also try this:

ALTER DEFAULT PRIVILEGES FOR ex_mainuser GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES TO ex_dbuser;

You execute the ALTER DEFAULT PRIVILEGES as su, so the grant applies to objects created by su and not ex_mainuser, unless you specify it with FOR ex_mainuser.

Besides, if the objects in the table will not be created by the owner, but by your admin, then I don’t very much see the point in giving ownership. That could be done anyway in the public schema, unless you changed that.

I have to leave now, but there are some more things that could be verified

Regards

Charles

2016-09-14 16:52 GMT+02:00 Adrian Klaver <adrian.klaver@aklaver.com>:

On 09/14/2016 06:52 AM, Durumdara wrote:

Dear Charles!

 

I thought before this test that mainuser get all rights as dbuser, so it
have rights to the next (future) objects too.
So mainuser and dbuser have equivalent rights in db database.

Thanks for your every info!

In my previous post I mentioned using \dp or \z. The output from those commands can be hard to understand without a key, which I forgot to mention. The key can be found here:

https://www.postgresql.org/docs/9.5/static/sql-grant.html

in the Notes section.

 

Regards
dd

--
Adrian Klaver
adrian.klaver@aklaver.com