How to prevent an existing role being given permissions on a new database?

Question

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:

{postgres=arwdDxt/postgres,"\"role a\"=arwdDxt/postgres"}

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 REVOKE and GRANT statements, but that also doesn't work.

Answer 1

I finally got to the bottom of this. As part of the workflow, I was running pg_dump to create a backup of a database, and then restoring it later. pg_dump was adding a GRANT command into the backup file for role A, causing it to gain privileges on the database B when it was restored.

By passing --no-acl to pg_dump when creating the backup, it no longer puts the GRANT in there and the problem is fixed.