[SQL] Concatenating bytea types...
Hi all,
i have a little problem.
I'm trying to rewrite one procedure from mysql that involves bytes
concatenation.
This is my snippet from postgres code:
...
cv1 bytea;
...
cv1 := E'\\000'::bytea;
...
cv1 := CONCAT(cv1, DECODE(TO_HEX(11), 'escape'));
...
this third line throws following error:
invalid hexadecimal digit: "\"
I run it through the debugger and saw that after assigning the zero byte
value to cv1 variable, postgres automatically converts it to \x00.
And then inside CONCAT it brakes with above error.
Inside select it works fine
select CONCAT(E'\\000'::bytea, DECODE(TO_HEX(11), 'escape'))
select CONCAT('\x00'::bytea, DECODE(TO_HEX(11), 'escape'))
Is there a way to solve this somehow?
thanks for help,
Marko
[SQL] Need help revoking access WHERE state = 'deleted'
We are working on a project to start storing some data as "soft deleted" (WHERE state = 'deleted') instead of hard-deleting it. To make sure that we never accidentally expose the deleted rows through the application, I had the idea to use a view and permissions for this purpose. I thought I could revoke SELECT access to the "entities" table, but then grant SELECT access to a view: CREATE VIEW entities_not_deleted AS SELECT * FROM entities WHERE state != 'deleted'; We could then find/replace in the code to replace references to the "entities" table with the "entities_not_deleted" table However, this isn't working, I "permission denied" when trying to use the view. (as the same user that has had their SELECT access removed to the underlying table.) We are not stuck on this design. What's a recommended way to solve this problem? Mark -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Need help revoking access WHERE state = 'deleted'
Quoth m...@summersault.com (Mark Stosberg): > > We are working on a project to start storing some data as "soft deleted" > (WHERE state = 'deleted') instead of hard-deleting it. > > To make sure that we never accidentally expose the deleted rows through > the application, I had the idea to use a view and permissions for this > purpose. > > I thought I could revoke SELECT access to the "entities" table, but then > grant SELECT access to a view: > > CREATE VIEW entities_not_deleted AS SELECT * FROM entities WHERE state > != 'deleted'; > > We could then find/replace in the code to replace references to the > "entities" table with the "entities_not_deleted" table (If you wanted to you could instead rename the table, and use rules on the view to transform DELETE to UPDATE SET state = 'deleted' and copy across INSERT and UPDATE...) > However, this isn't working, I "permission denied" when trying to use > the view. (as the same user that has had their SELECT access removed to > the underlying table.) Works for me. Have you made an explicit GRANT on the view? Make sure you've read section 37.4 'Rules and Privileges' in the documentation, since it explains the ways in which this sort of information hiding is not ironclad. Ben -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Need help revoking access WHERE state = 'deleted'
On 02/28/2013 01:02 PM, Ben Morrow wrote: > Quoth m...@summersault.com (Mark Stosberg): >> >> We are working on a project to start storing some data as "soft deleted" >> (WHERE state = 'deleted') instead of hard-deleting it. >> >> To make sure that we never accidentally expose the deleted rows through >> the application, I had the idea to use a view and permissions for this >> purpose. >> >> I thought I could revoke SELECT access to the "entities" table, but then >> grant SELECT access to a view: >> >> CREATE VIEW entities_not_deleted AS SELECT * FROM entities WHERE state >> != 'deleted'; >> >> We could then find/replace in the code to replace references to the >> "entities" table with the "entities_not_deleted" table > > (If you wanted to you could instead rename the table, and use rules on > the view to transform DELETE to UPDATE SET state = 'deleted' and copy > across INSERT and UPDATE...) > >> However, this isn't working, I "permission denied" when trying to use >> the view. (as the same user that has had their SELECT access removed to >> the underlying table.) > > Works for me. Have you made an explicit GRANT on the view? Make sure > you've read section 37.4 'Rules and Privileges' in the documentation, > since it explains the ways in which this sort of information hiding is > not ironclad. Thanks for the response, Ben. Here's a "screenshot" of our issue, showing that even an explicit grant on the view doesn't fix things. This with 9.1. # Revoke from the underlying table db=> revoke select on entities from myuser; REVOKE # Try selecting through the view db=> SELECT 1 FROM entities_not_deleted WHERE some_col = 'y'; ERROR: permission denied for relation entities # Explicitly grant access to the view. db=> grant select on entities_not_deleted to myuser; GRANT # Try again to use the view. Still fails db=> SELECT 1 FROM entities_not_deleted WHERE some_col = 'y'; ERROR: permission denied for relation entities ### I've also now read 37.4. That was helpful, but didn't lead to a breakthrough for me. Mark -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Need help revoking access WHERE state = 'deleted'
Mark Stosberg writes: > # Explicitly grant access to the view. > db=> grant select on entities_not_deleted to myuser; > GRANT > # Try again to use the view. Still fails > db=> SELECT 1 FROM entities_not_deleted WHERE some_col = 'y'; > ERROR: permission denied for relation entities What's failing is that the *owner of the view* needs, and hasn't got, select access on the entities table. This is a separate check from whether the current user has permission to select from the view. Without such a check, views would be a security hole. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Need help revoking access WHERE state = 'deleted'
On 02/28/2013 02:08 PM, Tom Lane wrote: > Mark Stosberg writes: >> # Explicitly grant access to the view. >> db=> grant select on entities_not_deleted to myuser; >> GRANT > >> # Try again to use the view. Still fails >> db=> SELECT 1 FROM entities_not_deleted WHERE some_col = 'y'; >> ERROR: permission denied for relation entities > > What's failing is that the *owner of the view* needs, and hasn't got, > select access on the entities table. This is a separate check from > whether the current user has permission to select from the view. > Without such a check, views would be a security hole. This was precisely our issue. Thanks, Tom. I changed the owner of the view, and our approach is working now. Mark -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
