PostgreSQL 9.5 Permissions – Deny Functions Body Access

postgresql

I create new role without any grants.

New user can't select on tables, but can see functions body (pl/PgSQL)

I want permissions for new role:

  • allow INSERT into one table.
  • deny see tables, sequences, functions and function's body.

Is there any way to configure it?

Best Answer

As I know there is no possibility to restrict access to the DB metadata at least for reading in PostgreSQL.

As an alternative you can to create another DB for login(s) and use foreign-data wrapper to access to the desired object(s) in the your main DB.

Here is a simple scenario (not fully tested):

Login to the main DB and create test table:

create table t (x int);

Create another DB for login(s):

create database logindb with owner = postgres;
grant all on database logindb to postgres;

Create user:

create role testrole password '111' login nosuperuser noinherit;
revoke connect on database postgres from testrole;

Now login to the newely created DB with user postgres and create FDW:

create extension postgres_fdw;
create server myserver foreign data wrapper postgres_fdw options (host 'localhost', dbname 'postgres', port '5432');
create user mapping for postgres server myserver options(user 'postgres', password '111');
create user mapping for testrole server myserver options(user 'postgres', password '111');
create foreign table t(x int) server myserver options(schema_name 'public', table_name 't');
grant insert on t to testrole;

Theoretically thats all. You can use testrole to connect to the logindb and to insert data into the table t in the main DB postgres. And 'testrole' can not to connect to the main DB and see anything in it.

Related Question