Typically only the owner of a table can truncate that table. For fun, shown below is a "simple" example that shows how to use an external procedure to allow a non-owning user to truncate the table:
1. Create a table owned by the dbadmin user and insert a record:
Code: Select all
dbadmin=> create table public.test (c1 int);
CREATE TABLE
dbadmin=> insert into public.test values (1);
OUTPUT
--------
1
(1 row)
dbadmin=> commit;
COMMIT
dbadmin=> \dt public.test;
List of tables
Schema | Name | Kind | Owner | Comment
--------+------+-------+---------+---------
public | test | table | dbadmin |
(1 row)
Code: Select all
dbadmin=> create user jim;
CREATE USER
dbadmin=> grant select on public.test to jim;
GRANT PRIVILEGE
Code: Select all
dbadmin=> \q
[dbadmin@vertica01 ~]$ cat truncate_table.sh
#!/bin/bash
/opt/vertica/bin/vsql -c "TRUNCATE TABLE $1;"
exit 0
[dbadmin@vertica01 ~]$ chmod +x+s truncate_table.sh
[dbadmin@vertica01 ~]$ ls -lrt truncate_table.sh
-rwsrwsr-x. 1 dbadmin dbadmin 48 Sep 10 13:29 truncate_table.sh
Code: Select all
[dbadmin@vertica01 ~]$ admintools -t install_procedure -d mydbtest -f truncate_table.sh
Installing external procedure...
External procedure installed
Code: Select all
[dbadmin@vertica01 ~]$ vsql
Welcome to vsql, the Vertica Analytic Database interactive terminal.
Type: \h or \? for help with vsql commands
\g or terminate with semicolon to execute query
\q to quit
dbadmin=> CREATE PROCEDURE public.truncate_table(arg1 VARCHAR) AS 'truncate_table.sh' LANGUAGE 'external' USER 'dbadmin';
CREATE PROCEDURE
Code: Select all
dbadmin=> GRANT EXECUTE ON PROCEDURE public.truncate_table(arg1 VARCHAR) TO jim;
GRANT PRIVILEGE
Code: Select all
dbadmin=> \c dbadmin jim;
You are now connected to database "dbadmin" as user "jim".
dbadmin=> SELECT * FROM public.test;
c1
----
1
(1 row)
dbadmin=> TRUNCATE TABLE public.test;
ROLLBACK 3989: Must be owner of relation test
dbadmin=> SELECT public.truncate_table('public.test');
INFO 4427: Procedure reported:
TRUNCATE TABLE
truncate_table
----------------
0
(1 row)
dbadmin=> SELECT * FROM public.test;
c1
----
(0 rows)
If you plan on using this method make sure you test it out!!! And be careful who gets granted access to the procedure!
Have fun!