connect permission based on database name

From: Rob Sargent <robjsargent(at)gmail(dot)com>
To: "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: connect permission based on database name
Date: 2022-05-25 14:09:50
Message-ID: 3659d499-9a98-6362-7c33-d8f2947474b4@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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;

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2022-05-25 14:11:12 Re: PG 13.6 : Data corruption error message not being sent to syslog
Previous Message David G. Johnston 2022-05-25 13:32:23 Re: cast to domain with default collation issue.