Grant only select on table not export or import data

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

Comments

  •  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)

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file