Just wondering if I've bumped into some security issue.
I'm somewhat surprised that "grant connect to database <dbname> to <role>" appears to be stored "by name"?
I have the luxury of dropping/recreate databases at will (within limits). My script for creating a new db also creates a role and grants that role access to that database. Of course on re-running the script a second time (with same names for db, role) the portion of the script dealing with the role, in its own transaction, fails after the "create role" line. All that is clearly understood. (And I realize I could clean up the role per drop db.) However, I can still connect to the new database (of same name) using the previously connected role using psql --user role --dbname db. For the original "grant connect" to still be in play, wouldn't it have to be store using dbname as opposed to an id? In a different environment, I can see it might be surprising that an "old" role could connect to a "new" database.
begin; create schema if not exists study\p\g create role role with login encrypted password 'password'\p\g alter role role set search_path=study,base,public\p\g grant connect on database dbname to role\p\g -- -- Allow this role to diddle with base, bulk and project tables -- grant all on schema base, bulk, study to role\p\g grant all on all tables in schema base, bulk, study to role\p\g commit;
