CODEWITHSUNDEEP
sqlpostgresqlstored-procedurespostgresql-9.5
Gonzalo Moreno Caballero
Gonzalo Moreno Caballero

Reputation: 398

How to grant stored procedure creation permission to an user in PostgreSQL

I need to grant permission to a specific user to create stored procedures in PostgreSQL without writing permissions to other tables. The stored procedure should read and write only in one table.

I've already setup the read permission to that table, but I'm struggling with the writting permissions.

GRANT CONNECT ON DATABASE production_database TO user;
GRANT USAGE ON SCHEMA public TO user;
GRANT SELECT ON table TO user;

Upvotes: 1

Views: 5952

Answers (1)

pifor
pifor

Reputation: 7882

If you want to write a procedure in PL/PGSQL you need to use PostgreSQL 11 or 12.

In PostgreSQL there is no explicit privilege to create a procedure or a function.

However you can try:

  • to create a specific schema just for the procedure

  • to grant USAGE to this schema only to the specific user

  • to create the procedure with SECURITY DEFINER as the table owner

Example:

create user myuser password 'myuser';
--
create table public.t(x int);
--
create schema myschema;
--
create or replace procedure myschema.myproc(param int)
language plpgsql
as
$$
declare
 v int;
begin
 insert into public.t values(param);
end;
$$
security definer
set search_path='';
--
grant usage on schema myschema to myuser;

Here the table owner is superuser postgres and the table schema is public:

With this script:

\c postgres myuser
select * from t;
call myschema.myproc(1);
\c postgres postgres
select * from t;

I get:

You are now connected to database "postgres" as user "myuser".
select * from t;
psql:cp.sql:25: ERROR:  permission denied for table t
call myschema.myproc(1);
CALL
You are now connected to database "postgres" as user "postgres".
select * from t;
 x 
---
 1
(1 row)

Upvotes: 3

Related Questions