Re: Restricted access on DataBases

On 09/14/2016 06:52 AM, Durumdara wrote:
> Dear Charles!
>
> I checked your solution. For example:
> db - database
> dbuser, mainuser
>
> 1. dbuser own the database, and the objects in it.
> 2. mainuser member of dbuser.
> 3. public connection revoked.
>
> Ok.
>
> Then dbuser can see all tables, and mainuser too.
>
> Ok.
>
> The operation (overlord):
> 1. set role to mainuser (or login).
> 2. create table test_mainuser(id integer);
> 3. set role to dbuser (or login).
> 4. select * from test_mainuser;
>
> Result: Permission denied.
>
> Hmmm... the owner of test_mainuser is mainuser...
>
> Then I dropped the test_mainuser table.
>
> I tried to use default privileges. They are for future, so they must be
> affected on newly created table.
> I set them all.
>
>
> ALTER DEFAULT PRIVILEGES GRANT INSERT, SELECT, UPDATE, DELETE,
> TRUNCATE, REFERENCES, TRIGGER ON TABLES to dbuser;
>
> I thought this makes all rights to the newly generated table.
>
> I do the test again, but I got same result.
>
> Why? What I do wrong? (Maybe only my mistake).
>
> 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!

Without seeing the actual GRANT commands you issued it will be difficult
to sort out what the state of the permissions is.

FYI, in psql you can use \dp or \z to see the privileges on a table.
That information would also be helpful.

>
> Regards
> dd
>
>
>
>
>
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com