I'm fairly new to postgres permissions. I'm working with a postgres server to add/remove databases to it as part of a workflow. Each database needs to have a role associated with it that can do everything within that database, but nothing outside of it. I've got it working so far where I create a database and a role, and give the role (what I think are) the correct permissions. This seems to be working well.
This is the workflow:
- Create database A
- Create role A and limit its permissions to database A
- Create database B
- Create role B and limit its permissions to database B
However at the step when I create database B, role A is somehow being given permissions on database B. This means that when I later remove database A and its associated role A, I get an error because role A has objects that depend on it:
role "role A" cannot be dropped because some objects depend on it Detail: 4 objects in database database B
In querying the
pg_catalog.pg_class table in database B just after creating it, I can see that role A is in there:
Any ideas what's going wrong, and more importantly how to prevent it?
--- EDIT ---
Here is the SQL I run to create the database and role:
CREATE ROLE roleA WITH LOGIN PASSWORD 'password' NOINHERIT; ALTER DATABASE databaseA OWNER TO roleA; REVOKE ALL PRIVILEGES ON SCHEMA public FROM roleA; REVOKE ALL PRIVILEGES ON DATABASE databaseA FROM PUBLIC; GRANT GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO roleA;
I've tried various combinations of other permissions, but can't make it work with the permissions that I need. I've tried not doing the
GRANT statements, but that also doesn't work.