Search Postgresql Archives

Groups, permissions and visibility

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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;
 
 

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux