Execute GRANT statements to a remote database in Postgres

Question

Execute GRANT statements to a remote database in Postgres

Navigation

#1 by (1 votes)

1

I would like to automate the grant action for a given user (myuser) to all databases within a Postgres instance (from the base / Postgres user) . My first attempt was to make the following script:

select dblink_exec('dbname=mibase host=IP user=usersuport password=xxx',
        'GRANT SELECT on all tables in schema public to myuser;');

But I get a message saying that I can not execute those types of sentences, I have tried using the EXEC with the same results, I have seen examples but I have only found for cases of update , delete e insert , there is also no% for create .

Any idea would be very helpful.

Thank you.

postgresql

asked by Fran.J 07.11.2018 в 17:17

source

1 answer

Show notice when trying to delete foreign key LARAVEL exclude data in my sql search

score 1 · Answer 1

Actually the correct approach for Postgres equal to or greater than 9.3 would be to use the FDW extension. If you do not have it installed, install with

CREATE EXTENSION postgres_fdw;

Then you create a reference to your remote server using that extension:

  CREATE SERVER servidor_remoto
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'IP', dbname 'mibase');

Then you create a mapping of users such that one of your local users equals a remote user. If the remote user usersuport already has permissions on public then you can map the local user myuser with the remote user:

  CREATE USER MAPPING FOR myuser
  SERVER servidor_remoto
  OPTIONS (user 'usersuport', password 'xxx');

Then you "mount" a remote schema in a local schema (I do not remember if you have to create the local schema or it is created explicitly). The important thing is that with this approach all the tables in the remote schema are mapped to FOREIGN TABLES in the local schema:

IMPORT FOREIGN SCHEMA public
FROM SERVER servidor_remoto
INTO esquema_public_remoto;

And finally, you can give your local user permissions on the tables in your local schema:

GRANT SELECT on all tables in schema esquema_public_remoto to myuser;

That below would make your local user execute the select without your local DB to say: "Moment, you do not have permission on this scheme", but also, since before you mapped it to a remote user, once that the schema allows it to do the select, in addition the remote server is not going to say to him: "moment, here there is nobody that is called myuser".