How to specify the database name in GRANT command?

Question

While the current connection is made to db1, I want to change privileges on db2. Like:

GRANT ALL PRIVILEGES ON SCHEMA "db2"."public" TO "user";

But this results in error, and PostgreSQL does not recognize the database qualifier in the statement! Is there any way I can do this without disconnecting from db1 and reconnecting to db2?

BTW, I'm commencing these commands from a programming language and not the psql.

[UPDATE]

As I was told in comments, it seems that this feature is intentionally blocked (due to implementation challenges or security reasons - I'm not sure).

In any case, I don't see a good reason for that and that's my personal opinion. If it is hard to implement, then it's a design flaw. And I think the security argument can be easily scratched as adding steps to the process for a user who's already been authenticated and authorized to do so, is not considered a better security.

Again, just my opinion.

In any case, I'm really thankful for all the time and effort put into PostgreSQL project. Thanks guys.

You can't grant privileges for another database. You need to connect to that database first. — , Jul 26 '16 at 21:40
@a_horse_with_no_name Sorry, I didn't mean to offend you. I believe you when you say that's the inner working of PosgreSQL. I just think it shouldn't be like this. I don't see any reasoning behind it. A simple database qualifier goes a long way in this case while it doesn't take much to implement. — Mehran, Jul 26 '16 at 21:56
If you think that it is easy to implement then send in a patch. — , Jul 27 '16 at 05:32
@Mehran : The reasoning behind that is that different databases should be isolated from each other. That's a feature. There was a [long thread on the -hackers list](https://www.postgresql.org/message-id/flat/CA%2BU5nMLwCe2XmH7QQPE98oU4cMmrgidsQzqRjdfqGXOhc%3D2s0g%40mail.gmail.com#CA+U5nMLwCe2XmH7QQPE98oU4cMmrgidsQzqRjdfqGXOhc=2s0g@mail.gmail.com) in 2012 that has some good arguments why we want that as well as technical points why it is hard to change. I guess you come from MySQL where "database" is roughly what "schema" is in PostgreSQL. Why not use schemas? — Laurenz Albe, Jul 27 '16 at 07:33
@LaurenzAlbe You are right, I've been working with MySQL before I started working with PostgreSQL. And as I go deeper and deeper into PostgreSQL, I get more and more reasons to use schemas as you've mentioned. But I'll give it more tries before giving up on databases altogether. Thanks. — Mehran, Jul 27 '16 at 11:26
@a_horse_with_no_name Again sorry if I offended you, that was not my intention. I really like PostgreSQL and I can see a million things that are caught perfectly in it. That's why I lose control when I see something that has gone wrong with it. I've been implementing ACL layers for applications a couple of times now and I kinda relate to the topic. When I say it's simple, I mean for someone who's already involved with the project. Not that anyone can do it. Of course, it needs skills and experience but I don't see any challenges, that's what I mean by simple. In any case, thanks. — Mehran, Jul 27 '16 at 11:33

score 1 · Accepted Answer · edited Aug 20 '16 at 17:58

First, I don't know if this is by intent or by the lack of implementation but this is a long-standing behavior in PostgreSQL. There are no cross-database references. There are a number of reasons why at present there probably never will be, especially the difficulty in doing things like making the system catalogs behave properly. Basically, database A has no knowledge of the structures in database B.

Secondly, cross-database information sharing is also a little inconsistent in PostgreSQL. pg_locks (for some strange reason) shows all across all databases. So I am thinking that this is probably an organic development rather than people sitting down and coming up with a policy on this. Similarly, as you are probably aware, roles and users are cluster-global. So I can see why this would seem a little bit inconsistent.

The correct way to do this in PostgreSQL is to connect to the DB you want to manage permissions in. There is no other way currently for PostgreSQL to get access to the appropriate catalog entries and modify them and the changes are likely to be so breaking that such seems to me to be unlikely to ever happen.

How to specify the database name in GRANT command?

1 Answers1