Arquivos do Blog

PostgreSQL 8.4 – Agora com GRANT/REVOKE por coluna

Olá, pessoal

Após um período de inatividade estou voltando com força total e neste retorno aproveito para falar de uma das funcionalidades que entraram no core do PostgreSQL na versão 8.4.

Alguns bancos de dados como o Oracle já possuiam esta característica e o PostgreSQL ainda não, porém a partir da versão 8.4 é possível conceder e retirar privilégios a colunas específicas de uma tabela.

Segue um exemplo bem simples:

Criação de um banco de dados para o exemplo.

postgres=# CREATE DATABASE exemplo_grant_revoke;

Após criado o banco é realizada uma conexão a ele.

postgres=# \c exemplo_grant_revoke
psql (8.4.0)
You are now connected to database “exemplo_grant_revoke”.

Criação de uma tabela para exemplificar a funcionalidade.

exemplo_grant_revoke=# CREATE TABLE tabela01(codigo int PRIMARY KEY,nome varchar(30));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index “tabela01_pkey” for table “tabela01”
CREATE TABLE

Inserção de dois registros na tabela.

exemplo_grant_revoke=# INSERT INTO tabela01 VALUES (1,’Jota.Comm’),(2,’PostgreSQL’);
INSERT 0 2
exemplo_grant_revoke=# SELECT * FROM tabela01;
codigo |    nome
——–+————
1 | Jota.Comm
2 | PostgreSQL
(2 rows)

Criação de um usuário para conceder e revocar privilégios.

exemplo_grant_revoke=# CREATE ROLE usuario_grant_revoke LOGIN;
CREATE ROLE

Conexão ao banco de dados com o usuário criado.

exemplo_grant_revoke=# \c exemplo_grant_revoke usuario_grant_revoke
psql (8.4.0)
You are now connected to database “exemplo_grant_revoke” as user “usuario_grant_revoke”.

Realização uma operação de SELECT na tabela criada.

Neste caso ocorrerá um erro pois o usuário usuario_grant_revoke não tem permissão de acesso ao objeto.

exemplo_grant_revoke=> SELECT * FROM tabela01;
ERROR:  permission denied for relation tabela01

Verificando as permissões de acesso. Observa-se que o usuário usuario_grant_revoke não possui nenhuma permissão no objeto tabela01.

exemplo_grant_revoke=> \z tabela01
Access privileges
Schema |   Name   | Type  | Access privileges | Column access privileges
——–+———-+——-+——————-+————————–
public | tabela01 | table |                   |
(1 row)

Conexão com o superuser (postgres).

exemplo_grant_revoke=> \c exemplo_grant_revoke postgres
psql (8.4.0)
You are now connected to database “exemplo_grant_revoke” as user “postgres”.

Concessão do privilégio de SELECT em todas as colunas da tabela.

exemplo_grant_revoke=# GRANT SELECT ON tabela01 TO usuario_grant_revoke;
GRANT

Concessão do privilégio de UPDATE na coluna nome da tabela tabela01.

exemplo_grant_revoke=# GRANT UPDATE(nome) ON tabela01 TO usuario_grant_revoke;
GRANT

Conexão ao banco com o usuário usuario_grant_revoke.

exemplo_grant_revoke=# \c exemplo_grant_revoke usuario_grant_revoke
psql (8.4.0)
You are now connected to database “exemplo_grant_revoke” as user “usuario_grant_revoke”.

Tentativa da operação de UPDATE sobre a coluna código da tabela tabela01. Um erro será exibido visto que o usuário não tem permissão de UPDATE na coluna código. O privilégio foi concedido apenas para a coluna nome.

exemplo_grant_revoke=> UPDATE tabela01 SET codigo=10 WHERE codigo=1;
ERROR:  permission denied for relation tabela01

Operação de UPDATE na coluna nome da tabela tabela01.

exemplo_grant_revoke=> UPDATE tabela01 SET nome=’Jota’ WHERE codigo=1;
UPDATE 1

Operação de SELECT em toda a tabela tabela01.

exemplo_grant_revoke=> SELECT * FROM tabela01;
codigo |    nome
——–+————
2 | PostgreSQL
1 | Jota
(2 rows)

Vefiricando as permissões do usuário usuario_grant_revoke na tabela tabela01.

exemplo_grant_revoke=> \x
Expanded display is on.
exemplo_grant_revoke=> \z tabela01
Access privileges
-[ RECORD 1 ]————+———————————-
Schema                   | public
Name                     | tabela01
Type                     | table
Access privileges        | postgres=arwdDxt/postgres
: usuario_grant_revoke=r/postgres
Column access privileges | nome:
:   usuario_grant_revoke=w/postgres

O usuário usuario_grant_revoke possui privilégio de SELECT na tabela (usuario_grant_revoke=r/postgres) e possui privilégio de UPDATE na coluna nome (usuario_grant_revoke=w/postgres).

Conexão ao banco.

exemplo_grant_revoke=> \c exemplo_grant_revoke postgres
psql (8.4.0)
You are now connected to database “exemplo_grant_revoke” as user “postgres”.

Remoção dos privilégios concedidos.

exemplo_grant_revoke=# REVOKE SELECT ON tabela01 FROM usuario_grant_revoke;
REVOKE
exemplo_grant_revoke=# REVOKE UPDATE(nome) ON tabela01 FROM usuario_grant_revoke;
REVOKE

Conexão ao banco com o usuário usuario_grant_revoke.

exemplo_grant_revoke=# \c exemplo_grant_revoke usuario_grant_revoke
psql (8.4.0)
You are now connected to database “exemplo_grant_revoke” as user “usuario_grant_revoke”.

A partir de agora o usuario usuario_grant_revoke não possui mais nenhum privilégio na tabela tabela01.

exemplo_grant_revoke=> SELECT * FROM tabela01;
ERROR:  permission denied for relation tabela01

exemplo_grant_revoke=> UPDATE tabela01 SET nome=’Jota.Comm’ WHERE codigo=1;
ERROR:  permission denied for relation tabela01

Verificando os privilégios.

exemplo_grant_revoke=> \z tabela01
Access privileges
Schema |   Name   | Type  |     Access privileges     | Column access privileges
——–+———-+——-+—————————+————————–
public | tabela01 | table | postgres=arwdDxt/postgres |
(1 row)

Espero que tenha sido claro e didático no exemplo. Até a próxima.

Abraços