Currently I'm using PostgreSQL for my application. Since I am trying to put every SQL that contains a transaction (i.e. insert, update, delete) in a function, I stumbled upon this problem:
Is it possible that a database user may only be allowed to call functions and Select-Statements while he can not call SQL-Statements which contains a transaction? By "call functions" I mean any function. Regardless if it contains a transaction or not.
I already tried to create a user which can only call functions and Select-Statements. But I always end up with an error, when calling functions which contains transactions. For what I understand a dbuser needs write permissions if a he calls a function which uses an insert, update or delete statement.
Am I missing something? Is this scenario really not possible? Security-wise this would be really great because you pretty much prevent SQL-injection in the first place.
The view usage_privileges identifies USAGE privileges granted on various kinds of objects to a currently enabled role or by a currently enabled role. In PostgreSQL, this currently applies to collations, domains, foreign-data wrappers, foreign servers, and sequences.
There is no "privilege on SELECT
". All you need is the privilege to EXECUTE
functions. Relevant function can run with SECURITY DEFINER
to inherit all privileges of the owner. To restrict possible privilege escalation to a minimum a priori, make a daemon role own relevant functions with only the necessary privileges - not a superuser!
As superuser ...
Create a non-superuser role myuser
.
CREATE ROLE myuser PASSWORD ...;
Create a group role mygroup
and make myuser
member in it.
CREATE ROLE mygroup;
GRANT mygroup TO myuser;
You may want to add more users just like myuser
later.
Do not grant any privileges at all to myuser
.
Only grant these to mygroup
:
GRANT CONNECT ON DATABASE mydb TO mygroup;
GRANT USAGE ON SCHEMA public TO mygroup;
GRANT EXECUTE ON FUNCTION foo() TO mygroup;
Remove all privileges for public
that myuser
shouldn't have.
REVOKE ALL ON ALL TABLES IN SCHEMA myschema FROM public;
There may be more. I quote the manual:
PostgreSQL grants default privileges on some types of objects to
PUBLIC
. No privileges are granted to PUBLIC by default on tables, columns, schemas or tablespaces. For other types, the default privileges granted to PUBLIC are as follows:CONNECT
andCREATE TEMP TABLE
for databases;EXECUTE
privilege for functions; andUSAGE
privilege for languages. The object owner can, of course,REVOKE
both default and expressly granted privileges. (For maximum security, issue theREVOKE
in the same transaction that creates the object; then there is no window in which another user can use the object.) Also, these initial default privilege settings can be changed using theALTER DEFAULT PRIVILEGES
command.
Create a daemon role to own relevant functions.
CREATE ROLE mydaemon;
Grant only privileges necessary to execute these functions to mydaemon
, (including EXECUTE ON FUNCTION
to allow another function to be called). Again, you can use group roles to bundle privileges and grant them to mydaemon
GRANT bundle1 TO mydaemon;
In addition you can use DEFAULT PRIVILEGES
to automatically grant certain privileges for future objects to a bundle or the daemon directly:
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO bundle1;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT USAGE ON SEQUENCES TO bundle1;
This applies only to the role it is executed for. Per the documentation:
If
FOR ROLE
is omitted, the current role is assumed.
To also cover pre-existing objects in the schema (see rob's comment):
GRANT SELECT ON ALL TABLES IN SCHEMA public TO bundle1;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO bundle1;
Make mydaemon
own relevant functions. Could look like this:
CREATE OR REPLACE FUNCTION foo()
...
SECURITY DEFINER SET search_path = myschema, pg_temp;
ALTER FUNCTION foo() OWNER TO mydaemon;
REVOKE EXECUTE ON FUNCTION foo() FROM public;
GRANT EXECUTE ON FUNCTION foo() TO mydaemon;
GRANT EXECUTE ON FUNCTION foo() TO mygroup;
-- possibly others ..
###Note
Due to this bug in the current version 1.16.1 of pgAdmin the necessary command
REVOKE EXECUTE ON FUNCTION foo() FROM public;
is missing in the reverse engineered DDL script. Remember to add it, when recreating.
This bug is fixed in the current version pgAdmin 1.18.1.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With