1

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?

  • did you revoke permission from user 'public'? – Mladen Uzelac Apr 11 '16 at 9:53
  • Just create. CREATE ROLE cdr_user LOGIN NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE NOREPLICATION; – Dmitry Apr 11 '16 at 10:31
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.