Groups, permissions and visibility

[jonathan.lister@xxxxxxxxxxx]

Suppose I have two groups of users and want to keep some sensitive information "hidden" from one group as described in the simple example below.

When connecting as user 'visitor' via pgAdmin I am surprised that I can easily browse the structure of tables and the code of functions owned by 'trusted'. I can't edit the data in the tables, but ideally I would like to restrict visibility of the objects as well. Am I missing something simple?

 

--create two groups and a couple of users
create group trusted;
create user the_boss in group trusted password 'secret';
create group guests;
create user visitor in group guests password 'easy';

 

drop database test;
-- create a test database
CREATE DATABASE test ENCODING = 'UTF8';
-- ensure database is owned by trusted group
ALTER DATABASE pgice OWNER TO trusted;
-- add required languages
CREATE PROCEDURAL LANGUAGE plpgsql;

 

-- connect to the new database
\connect test

 

-- create a schema for the trusted group
CREATE SCHEMA confidential AUTHORIZATION trusted;
ALTER SCHEMA confidential OWNER TO trusted;

 

-- add some tables to confidential schema
CREATE TABLE confidential.application (
    application_key character varying(32) NOT NULL PRIMARY KEY,
    application_type character varying(16) NOT NULL
);
ALTER TABLE confidential.application OWNER TO trusted;

 

-- add some test functions
CREATE FUNCTION confidential.get_applications()
RETURNS refcursor
    AS $$
declare
  r refcursor;
begin
  begin
    open r for
      select * from application;
  end;   
  return (r);
end;
$$
    LANGUAGE plpgsql;
ALTER FUNCTION confidential.get_applications() OWNER TO trusted;

 

revoke all on function confidential.get_applications() from public;
revoke all on schema confidential from public;