How to grant privileges on schema in arbitrary PostgreSQL database?

Question

Is it possible in PostgreSQL to grant all privileges on a schema of a database other than the one I'm currently connected to (and without using the \c command to switch first)?

For example, I'm connected as user postgres to the database postgres. Now I want to grant someuser all privileges on schema public:

GRANT ALL ON SCHEMA public TO someuser;

This executes successfully, but applies to schema public in the postgres database. What I really want is to apply the privileges on schema public of the other database. Something like this:

GRANT ALL ON SCHEMA <other>.public TO someuser;

No, you have to be connected to the database. Postgres does not do cross database references. — Adrian Klaver
– Adrian Klaver, Commented Sep 14, 2023 at 16:48
From the manual: When connecting to the database server, a client must specify the database name in its connection request. It is not possible to access more than one database per connection. See postgresql.org/docs/current/manage-ag-overview.html — Frank Heikens
– Frank Heikens, Commented Sep 14, 2023 at 17:27

Pepe N O · Accepted Answer · 2023-09-14 18:58:19Z

1

It can be done through dblink extension, like this:

select dblink_exec('dbname=other_database user=remote_privileged_user password=password',
                   'grant all on schema public to someuser');

If your connected user has privileges in both databases, it's like this

select dblink_exec('dbname=other_database',
                   'grant all on schema public to someuser');

Which is better because, as pointed out in comments, could be a security breach for a password to be recorded in a transaction log.

Also the password could be retrieved from a file as shown in official docs for connection string construction since connecting and executing commands to remote PostgreSQL databases (self host or other hosts) has multiple uses.

Be sure that the dblink extension is installed in your local database with

select * from pg_extension;

If don't create it with

create extension dblink;

Official docs

edited Sep 14, 2023 at 18:58

answered Sep 14, 2023 at 17:32

Pepe N O

2,3841 gold badge12 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Frank Heikens Over a year ago

Your password could be written to the postgresql logfiles. Not sure if you want that. Using psql and just connecting to the correct target database, would be much cleaner.

Pepe N O Over a year ago

@FrankHeikens, that's correct, added another example for that matter.

Collectives™ on Stack Overflow

How to grant privileges on schema in arbitrary PostgreSQL database?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related