CODEWITHSUNDEEP
postgresqlpermissions
vac
vac

Reputation: 723

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

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.

Upvotes: 2

Views: 2670

Answers (1)

Jim Nasby
Jim Nasby

Reputation: 1268

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;

Upvotes: 1

Related Questions