I am working with pg_rdeis_fdw from postgres.
When I try to insert a record to the existing schema from the postgres account, it all works fine.
However, when I try to do the same from another user, I get "permission denied for relation", though i gave the user the following privileges:
grant all on FOREIGN DATA WRAPPER redis_fdw to ami; grant all on FOREIGN SERVER redis_server to ami; grant all on ALL TABLES IN SCHEMA public to ami; GRANT ALL PRIVILEGES ON TABLE user_redis_hash to ami; The definition is as following (and as I was saying, works just fine from user postgres):
CREATE EXTENSION redis_fdw; CREATE SERVER redis_server FOREIGN DATA WRAPPER redis_fdw OPTIONS (address '127.0.0.1', port '6379'); CREATE USER MAPPING FOR PUBLIC SERVER redis_server OPTIONS (password 'secret'); create foreign table user_redis_hash(key text, val text[]) server redis_server options (database '0', tabletype 'hash', tablekeyset 'user:'); thanks, Ami
1 Answer
In my case I had to change the owner of the foreign table to the right role. So you might try something like this (from the postgres account):
ALTER FOREIGN TABLE public.user_redis_hash OWNER TO ami; If you have other foreign tables (as I did) and need to change them all, the following SQL will produce a series of SQL lines you can copy into psql prompt to update each foreign table.
SELECT 'ALTER FOREIGN TABLE '|| foreign_table_schema || '.' || foreign_table_name ||' OWNER TO ami;' FROM information_schema.foreign_tables WHERE NOT foreign_table_schema IN ('pg_catalog', 'information_schema') ORDER BY foreign_table_schema, foreign_table_name; If you have other foreign tables for other servers or data wrappers that you do not want to change ownership on you can limit the above by adding AND foreign_server_name = 'redis_server' to the WHERE clause.
