0

We have a database that contains two schemas, "FDW" and "main_schema". The IT department and I are trying to grant read access for me to the "FDW", I already have read access to "main_schema". Neither myself nor the IT department built the database and are uncertain how to grant read access to the "FDW". We have tried the below with error returned "SQL Error [42704]: ERROR: user mapping not found for "username""

-- create read only group
CREATE ROLE readaccess;

-- Grant access to existing tables
GRANT USAGE ON schema fdw TO READACCESS;

GRANT SELECT ON ALL TABLES IN SCHEMA fdw TO READACCESS;

CREATE USER username WITH PASSWORD '123';
GRANT readaccess TO username;

How do I create user mapping so I can gain access to the table with in the FDW schema?

Eizy
  • 253
  • 1
  • 9
  • 1
    Create a user mapping as described in the docs. Granting access to schemas or tables is an unrelated topic. If you have trouble creating a user mapping, tell us about the trouble, not about some unrelated topic. – jjanes Aug 17 '23 at 17:59
  • my mistake, did not know it was unrelated topic. I am new to Postgres. – Eizy Aug 17 '23 at 19:45
  • So would it be something like ```CREATE USER MAPPING FOR username SERVER fdw OPTIONS (user 'username', password '123');``` Or instead of "SERVER fdw" it should be "SERVER main_schema"? – Eizy Aug 17 '23 at 19:51
  • First you need to create the server, which it is not clear if you have done that yet. Then you would need to create the mapping FOR SERVER , which would generally not be the same as either of your schemas (but it could be, if you wanted you could give the server and the schema the same name). It is hard to advice, because you haven't really described a situation in which a FDW is needed at all. Two different schemas in the same database shouldn't require a FDW. – jjanes Aug 18 '23 at 13:35

0 Answers0