Groups, permissions and visibility

From: jonathan(dot)lister(at)vaisala(dot)com
To: <pgsql-general(at)postgresql(dot)org>
Subject: Groups, permissions and visibility
Date: 2006-09-11 15:26:23
Message-ID: D3081BDF8E6A5D40A4E82155E83C865EE39D2C@birdx1.corp.vaisala.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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;



Browse pgsql-general by date

  From Date Subject
Next Message Brandon Aiken 2006-09-11 15:28:00 Database migration and redesign
Previous Message Angva 2006-09-11 15:16:00 Re: pg_dump and cluster