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;
