PostgreSQL - Hide database structure but allow to query selected views for certain role (user)

Question

I'm having a big database in PostgreSQL 9.4 with a top secret structure. There is a lot of data constantly changing in it. I would like to give an access to one of my customer to certain views in this database.

I don't won't the customer to see the structure of this database - he should just gain access to do some read-only queries on selected views/tables.

If I give read only access to selected view I want customer to do any SELECT query with data from this view (it's up to customer), i.e.:

SELECT * FROM view_1;
SELECT MIN(col_a), MAX(col_b) FROM view_1;

Is it possible to do so in PostgreSQL?

If no... what would be the best workaround?

Customer need to access through standard PostgreSQL connection and make standard SQL SELECTs (middleware/api for DB is not acceptable). Table replication is not possible because of limited ammount of space. Maybe I can make database for customer with some views that taking data somehow from my database? But in such case I'm affraid that customer may see user and password to my database.

Jim Nasby · Accepted Answer · 2016-03-24 23:55:04Z

1

All you need to do is put the views in a separate schema, and grant USAGE on that schema (and only that schema) to the user:

CREATE SCHEMA tables;
CREATE TABLE tables.some_table(i int);
INSERT INTO tables.some_table SELECT generate_series(1,9);
CREATE ROLE test;  
CREATE SCHEMA views;
GRANT USAGE ON SCHEMA views TO test;
CREATE VIEW views.some_view AS SELECT * FROM tables.some_table;
GRANT SELECT ON views.some_view TO test;
SET ROLE test;
SELECT count(*) FROM views.some_view;
 count 
-------
     9
(1 row)

Trying to select from the table will give you an error:

SELECT count(*) FROM tables.some_table; 
ERROR:  permission denied for schema tables
LINE 1: SELECT count(*) FROM tables.some_table;

answered Mar 24, 2016 at 23:55

Jim Nasby

1,27810 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

vac Over a year ago

Limited access to table data is working fine - thanks. But what about accessing database structure? If I login as test with pgadmin I can see all schemes, secret table names, column definition etc.

vac Over a year ago

SELECT relname FROM pg_stat_all_tables WHERE schemaname='tables'; shows: some_table, secret_table...

bumpkin Over a year ago

@vac did you find a solution?

vac Over a year ago

@bumpkin no, unfortunately not

Collectives™ on Stack Overflow

PostgreSQL - Hide database structure but allow to query selected views for certain role (user)

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related