So, I'm trying to figure out how to properly administer a postgresql database. I'm new with postgres and DBA in general.
I'm currently trying to have a dedicated role for a specific database.
I also want to have other user with a grant on that role so that I could do SET ROLE my_db_role and be able to manipulate this specific database from there.
However, I've got permission leakage, meaning that I can manipulate my database without having to do this SET ROLE my_db_role command.
Here are the commands I do to get my unssuccessful result :
=# CREATE ROLE test NOINHERIT;
=# CREATE USER myuser;
=# CREATE DATABASE test OWNER test;
=# \c test
=# DROP SCHEMA public;
=# CREATE SCHEMA AUTHORIZATION test;
=# GRANT test TO myuser;
=# \c test myuser
=> CREATE TABLE test.mytable(id integer);
CREATE TABLE
Temps : 46,469 ms
Why did the last commands succeeded ?
In my opinion, myuser should have no right on test database/schema, as test role has the NOINHERIT flag, so this CREATE command should not be possible.
It should need to do SET ROLE test to succeed which is not the case here.
What am I missing ?
On a side note, I have a hard time finding good source of information on how to administer properly postgresql apart from the official doc. If you can share some good material about it, you're more than welcome.
I'm afraid I don't know of any particularly good resource covering role management and administration. The standards here show all the signs of having to please several stake-holders and are flexible but confusing.
As to your immediate question though, the issue is that the "NOINHERIT" is on the wrong role. However, this feature is not really a security constraint.
test=# ALTER USER myuser NOINHERIT;
test=# \c - myuser
You are now connected to database "test" as user "myuser".
test=> CREATE TABLE test.mytable(id int);
ERROR: permission denied for schema test
LINE 1: CREATE TABLE test.mytable(id int);
^
test=> SET ROLE test;
SET
test=> CREATE TABLE test.mytable(id int);
CREATE TABLE
As you can see, myuser doesn't inherit the permissions of test but there is nothing to stop you switching directly to that role.
If you find this fiddly and confusing, then you are far from alone. I find it useful to add some tests to check any configuration I set up.