Question 172199 in PostgreSQL Question posted in
The official documentation can be found here.

Can users be limited to only functions & procedures in Postgresql?

I am new to databases in general and I need some help with user privileges. I created a desktop application in C# that uses npgsql to connect to my PostgreSQL database. Every command to the database is either a function or a procedure. I want to create a user for the database to use in my C# code that only has the right to execute those specific functions and procedures and nothing more.

I used the following code to create the user:

create user userdb with password 'blabla';
GRANT CONNECT ON DATABASE test TO userdb;
REVOKE ALL PRIVILEGES ON DATABASE test FROM userdb;
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM userdb;
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM userdb;

I then performed the following for each procedure and function:

GRANT EXECUTE ON FUNCTION LoadEmails() TO userdb;

When I run any of the procedures or functions, I get an error 42501: permission denied for table emails.

Sample code for LoadEmails():

CREATE OR REPLACE FUNCTION LoadEmails()
RETURNS setof text AS $$
BEGIN
  RETURN QUERY SELECT email FROM emails;
END
$$ LANGUAGE plpgsql;

Any help would be greatly appreciated. Thanks in advance.

Tags:

2

Answers


  1. 0

    By default, functions run with the privileges of the user invoking the function. Specify SECURITY DEFINER to allow the function to use the privileges of the user that owns the function.

    CREATE OR REPLACE FUNCTION LoadEmails()
RETURNS setof text
SECURITY DEFINER
AS $$
BEGIN
  RETURN QUERY SELECT email FROM emails;
END
$$ LANGUAGE plpgsql;
    Login or Signup to reply.
  2. 0

    The error is because the user accessing the database does not have privilege to execute on "emails" table. Function itself requires additional privilege to execute query.

    What you need to do is to grant permissions to the user. This can be done as:

    GRANT SELECT ON TABLE myTable TO myUser;

    For Stored Procedures refer here.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search