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;
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 |