Revoke in TEMPORARY TABLE in PostgreSQL, is it possible?

1

Is it possible to give%% (Privilege Removal) so that users do not create temporary tables? The REVOKE I need to keep, however, I need to block CREATE TABLE .

Is it possible?

    
asked by anonymous 25.01.2018 / 12:22

2 answers

3
REVOKE TEMPORARY ON DATABASE seuSB FROM usuario

Documentation .

    
25.01.2018 / 13:03
0

revoking create;

REVOKE CREATE ON SCHEMA public FROM user;

revoking temporary

REVOKE TEMPORARY ON DATABASE userdb from user;

Note: The Temporary table only exists as long as the user who made the connection created it and remains connected at the end of the connection it is removed by the database;

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE nome_da_tabela
    [ (nome_da_coluna [, ...] ) ]
    [ WITH ( parâmetro_de_armazenamento [= valor] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
    [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
    [ TABLESPACE espaço_de_tabelas ]
    AS comando

To view your SCHEMA use the \ dt example command below:

Esquema  |  Nome   |  Tipo  |   Dono   
-----------+---------+--------+----------
 pg_temp_8 | t1      | tabela | alex
 public    | pessoas | tabela | alex

So the REVOKE TEMPORARY command looks like this:

REVOKE TEMPORARY ON DATABASE userdb from alex;

SCHEMA is temporary, so it does not make sense to grab the SCHEMA object.

What if the user is SUPERUSER | DBCREATE, it will continue creating temporary table;

The command will need ALTER ROLE;

ALTER ROLE alex WITH NOSUPERUSER NODBCREATE;

 select * from pg_user where pg_user.usename = 'alex';
 usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls |  passwd  | valuntil | useconfig 
---------+----------+-------------+----------+---------+--------------+----------+----------+-----------
 alex    |    16384 | f           | f        | f       | f            | ******** |          | 

With all the changes your user will not be able to create TEMPORARY.

FORCING CASE NONE OF THE ABOVE TIPS

 REVOKE ALL ON DATABASE template0 FROM PUBLIC; 
 REVOKE ALL ON DATABASE template1 FROM PUBLIC; 
 REVOKE ALL ON DATABASE seudb FROM seuuser;

I am putting this because of the difficulty that can occur; The reason of the command because the access privileges user = Cc / user will be of user; Not template0 or template1; because template0 has postgres privilege = Ctc / postgres

The \ l command in psql helps to visualize, but test each case and one case depends on the configuration of your postgre bank.

    
25.01.2018 / 13:10