Grant only select on table not export or import data

majidtahir

Dear,

I am running community edition. I want to give only select grant to table on schema but this also allow export and import table. How to only give select not export or import.

 

Thanks

Adrian_Oprea_1

 If you only give select grant the user will not be able to load data into the table, but export will be available to you as you gave it select grant.

See example:

dbadmin=> create user test identified by 'test123';
CREATE USER
dbadmin=> create schema schema_test;
CREATE SCHEMA
dbadmin=> create table schema_test.bla(name varchar(20));
CREATE TABLE
dbadmin=> insert into schema_test.bla values('Adrian');
 OUTPUT
--------
      1
(1 row)

dbadmin=> select * from schema_test.bla;
  name
--------
 Adrian
(1 row)

dbadmin=> grant select on schema_test.bla to test;
WARNING 5682:  USAGE privilege on schema "schema_test" also needs to be granted to "test"
GRANT PRIVILEGE
dbadmin=> grant usage on schema schema_test to test;
GRANT PRIVILEGE
dbadmin=>
dbadmin=> \c - test
Password:
You are now connected as user "test".
dbadmin=> \dt
                 List of tables
   Schema    | Name | Kind  |  Owner  | Comment
-------------+------+-------+---------+---------
 schema_test | bla  | table | dbadmin |
(1 row)

dbadmin=> select * from schema_test.bla ;
  name
--------
 Adrian
(1 row)

dbadmin=> insert into schema_test.bla values('George');
ERROR 4367:  Permission denied for relation bla
dbadmin=> \! cat /tmp/bla.txt
George
dbadmin=> copy schema_test.bla from '/tmp/bla.txt' direct delimiter ',' ;
ERROR 4367:  Permission denied for relation bla

-- export "you can do this becouse you have select granted"
dbadmin=> \o /tmp/export_bla.txt
dbadmin=> select * from schema_test.bla ;
dbadmin=> \! cat /tmp/export_bla.txt
 name
--------
 Adrian
(1 row)