Thread: connect permission based on database name
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.
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;
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
On 5/25/22 08:20, Tom Lane wrote:
Yes, of course. ThanksRob 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
On 5/25/22 08:20, Tom Lane wrote:
And then the search path is "just a string"?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
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)
On Wednesday, May 25, 2022, Rob Sargent <robjsargent@gmail.com> wrote:
On 5/25/22 08:20, Tom Lane wrote:And then the search path is "just a string"?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
Search_path isn’t a security component and accepts, but ignores, unknown names. So yes, it is just a string.
David J.
On 5/25/22 08:44, David G. Johnston wrote:
On Wednesday, May 25, 2022, Rob Sargent <robjsargent@gmail.com> wrote:Roger that, thanks.On 5/25/22 08:20, Tom Lane wrote:And then the search path is "just a string"?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 laneSearch_path isn’t a security component and accepts, but ignores, unknown names. So yes, it is just a string.David J.