PostgreSQL query using table without permission

Question

I am dealing with a remote data base where I have permission to only access specific tables.

The relevant data has the form:

CREATE TABLE t_a(v_a VARCHAR(32), v_b VARCHAR(32));
CREATE TABLE t_b(v_b VARCHAR(32), v_c VARCHAR(32));

INSERT INTO t_a VALUES ('one', 'abc1');
INSERT INTO t_a VALUES ('two', 'abc2');
INSERT INTO t_a VALUES ('three', 'abc3');

INSERT INTO t_b VALUES ('abc1', 'eins');
INSERT INTO t_b VALUES ('abc2', 'zwei');
INSERT INTO t_b VALUES ('abc3', 'drei');

My query looks like:

SELECT DISTINCT ON (v_a) v_a, v_c FROM t_a, t_b WHERE t_a.v_b = t_b=v_b;

Now the actual question: Can I somehow get the same information without permission to read from t_b? Is there another approach I could try?

EDIT

Sadly, I do not have the rights to change the permissions. My line of thought was that since I only want to have an association between v_a and v_c, I could get away with not selecting any columns from t_b. After a bit more thinking, I can see why this should not be allowed by the permission system - after all, I try to read some information from t_b.

I was also hoping that maybe there are different permission layers where one of them permits non-selecting queries.

Do you mean you want to include t_b in a query even though you do not have read permission on it? What kind of database system would allow that? — harmic
– harmic, Commented Feb 28, 2014 at 22:17
Did you GRANT access on the table to PUBLIC or to some user/role? If not, then only the owner can access it. — Craig Ringer
– Craig Ringer, Commented Feb 28, 2014 at 22:38
Not related but: using distinct on without specifying an order by is not such a good idea — user330315
– user330315, Commented Mar 1, 2014 at 1:25
@harmic, Craig Ringer: I cannot change the permission of the data base. I edited the question to give more info on my reasoning. — phaebz
– phaebz, Commented Mar 2, 2014 at 20:47

Kuberchaun · Accepted Answer · 2014-02-28 22:18:23Z

2

You can create that table with a different account that either owns it or has been GRANTED read to the table. You than create a stored function created by the same account that owns or has rights to read the table. GRANT EXECUTE to the new function to you personal account that doesn't have rights to the table. Execute the new function and you can get at the data. This is called SECURITY DEFINER and more can be read here http://www.postgresql.org/docs/9.3/static/sql-createfunction.html

With this setup your account won't have access to the table directly, like by doing select * from your_table. You can only get to it via the function. Same can be done with views and is how a lot of database designs setup access to tables. The function or view is the public interface

answered Feb 28, 2014 at 22:18

Kuberchaun

30.5k8 gold badges54 silver badges59 bronze badges

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

1 Comment

phaebz Over a year ago

I think this would actually be the kind of permission level I mentioned in my edit. Thanks for the link!

Collectives™ on Stack Overflow

PostgreSQL query using table without permission

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related