Revoke in TEMPORARY TABLE in Postgresql, is it possible?

Asked

Viewed 157 times

1

It is possible to give a REVOKE (Privilege removal) so users don’t create temporary tables? The CREATE TABLE I need to keep, however, I need to block the TEMPORARY TABLE.

It is possible?

sql database postgresql

  • Did any of the answers solve your question? Do you think you can accept one of them? Check out the [tour] how to do this, if you haven’t already. You would help the community by identifying what was the best solution for you. You can accept only one of them. But you can vote on any question or answer you find useful on the entire site (when you have enough score).

2 answers

3

  • Great, I saw this command in https://postgrespro.com/docs/postgresql/9.5/sql-revoke.html but I couldn’t mount it.

  • Portraying me, the above command is right.

  • I didn’t say I was wrong, I just didn’t get hehe.

0

revoking the create;

REVOKE CREATE ON SCHEMA public FROM user;

repealing the Temporary

REVOKE TEMPORARY ON DATABASE userdb from user;

Note: The Temporary table only exists while the user who made the connection created it and stay 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 command dt example below:

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

So the command of REVOKE TEMPORARY, is like this:

REVOKE TEMPORARY ON DATABASE userdb from alex;

SCHEMA is temporary, so it makes no sense to take the SCHEMA object.

And if the user is SUPERUSER | DBCREATE, he will continue creating Temporary table;

The command will be necessary 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 IN CASE NONE OF THE ABOVE TIPS WORK;

 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 due to the difficulty that can occur; Why of the command because the Access privileges user=Cc/user will be user; Not template0 or template1; because template0 has postgres=Ctc/postgres privilege

The l command in psql helps to visualize, but test each case and a case depends on your postgre database configuration.

  • Hard that didn’t work. ERROR: TEMP privilege type is invalid for schema

  • look if your SCHEMA is public, and if in this SCHEMA your user has a role |SUPERUSER and switch to | NOSUPERUSER and | NOCREATEDB

  • @L.Gustavo improved the answer from a look to see if it solves your problem.

Browser other questions tagged sql database postgresql

You are not signed in. Login or sign up in order to post.