Thread: connect permission based on database name

connect permission based on database name

From
Rob Sargent
Date:
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;


Re: connect permission based on database name

From
Tom Lane
Date:
Rob Sargent <robjsargent@gmail.com> writes:
> 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 think you are forgetting that databases have a default GRANT CONNECT
TO PUBLIC.  You need to revoke that before other grants/revokes will
have any functional effect.

            regards, tom lane



Re: connect permission based on database name

From
Rob Sargent
Date:
On 5/25/22 08:20, Tom Lane wrote:
Rob Sargent <robjsargent@gmail.com> writes:
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 think you are forgetting that databases have a default GRANT CONNECT
TO PUBLIC.  You need to revoke that before other grants/revokes will
have any functional effect.
			regards, tom lane
Yes, of course.  Thanks

Re: connect permission based on database name

From
Rob Sargent
Date:
On 5/25/22 08:20, Tom Lane wrote:
Rob Sargent <robjsargent@gmail.com> writes:
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 think you are forgetting that databases have a default GRANT CONNECT
TO PUBLIC.  You need to revoke that before other grants/revokes will
have any functional effect.
			regards, tom lane
And then the search path is "just a string"?
psql --user oldrole --dbname newdb --host $DBHOST
psql (12.11, server 12.7)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

newdb=> show search_path;
    search_path
--------------------
 study, base, public
(1 row)


Re: connect permission based on database name

From
"David G. Johnston"
Date:
On Wednesday, May 25, 2022, Rob Sargent <robjsargent@gmail.com> wrote:
On 5/25/22 08:20, Tom Lane wrote:
Rob Sargent <robjsargent@gmail.com> writes:
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 think you are forgetting that databases have a default GRANT CONNECT
TO PUBLIC.  You need to revoke that before other grants/revokes will
have any functional effect.
			regards, tom lane
And then the search path is "just a string"?


Search_path isn’t a security component and accepts, but ignores, unknown names.  So yes, it is just a string.

David J.
 

Re: connect permission based on database name

From
Rob Sargent
Date:
On 5/25/22 08:44, David G. Johnston wrote:
On Wednesday, May 25, 2022, Rob Sargent <robjsargent@gmail.com> wrote:
On 5/25/22 08:20, Tom Lane wrote:
Rob Sargent <robjsargent@gmail.com> writes:
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 think you are forgetting that databases have a default GRANT CONNECT
TO PUBLIC.  You need to revoke that before other grants/revokes will
have any functional effect.
			regards, tom lane
And then the search path is "just a string"?


Search_path isn’t a security component and accepts, but ignores, unknown names.  So yes, it is just a string.

David J.
 
Roger that, thanks.