Postgresql – How to make PostreSQL functions private (inaccessible to end users)

functionspostgresqlpostgresql-10sql-injection

When writing a set of PostgreSQL functions with procedural languages, is it possible to make some of the supporting functions private and not accessible by end users?

I wanted to refactor some common patterns into SQL functions, but are concerned about introducing potential SQL injection in the function parameters. For example, I wanted to implement a copy_table(src regclass, target text) function for copying a table to new table (in a previous question), the second parameter can be subject to SQL-injection if unquoted. (Quoting the 2nd parameter has its own issues as discussed there). On the one hand, I wanted to write this function as a helper function for other public-facing functions; on the other, I don't want to expose it to everyone for security concerns.

Is there a way to hide the dangerous supporting functions so that they can only be called by my functions?

Best Answer

Just use REVOKE to control the permissions Postgres assigns to roles.

Documentation:

The REVOKE command revokes previously granted privileges from one or more roles. The key word PUBLIC refers to the implicitly defined group of all roles.

See the description of the GRANT command for the meaning of the privilege types.

CREATE FUNCTION nocando()
RETURNS void AS $$
  SELECT null::void
$$ LANGUAGE sql;

REVOKE ALL
  ON FUNCTION nocando ()
  FROM PUBLIC ;

Postgres 11 or later

Procedures have been added.
The system catalog pg_proc slightly changed: prokind replaces proisagg and proiswindow - and also tags functions and the new procedures

DO
$do$
DECLARE
   _sql text;
BEGIN
   SELECT INTO _sql
          string_agg(format('DROP %s %s;'
                          , CASE prokind
                              WHEN 'f' THEN 'FUNCTION'
                              WHEN 'a' THEN 'AGGREGATE'
                              WHEN 'p' THEN 'PROCEDURE'
                              WHEN 'w' THEN 'FUNCTION'  -- window function (rarely applicable)
                              -- ELSE NULL              -- not possible in pg 11
                            END
                          , oid::regprocedure)
                   , E'\n')
   FROM   pg_proc
   WHERE  pronamespace = 'public'::regnamespace  -- schema name here!
   -- AND    prokind = ANY ('{f,a,p,w}')         -- optionally filter kinds
   ;

   IF _sql IS NOT NULL THEN
      RAISE NOTICE '%', _sql;  -- debug / check first
      -- EXECUTE _sql;         -- uncomment payload once you are sure
   ELSE 
      RAISE NOTICE 'No fuctions found in schema %', quote_ident(_schema);
   END IF;
END
$do$;

The schema name is case sensitive in this context.
The executing role needs to have the necessary privileges of course.

You might add CASCADE like demonstrated by mehmet but that will also drop depending objects, recursively - not just functions. Makes it even more dangerous. You better know exactly what you are doing.

Related, with more explanation:

Postgres 10 or older

DO
$do$
DECLARE
   _sql text;
BEGIN
   SELECT INTO _sql
          string_agg(format('DROP %s %s;'
                          , CASE WHEN proisagg THEN 'AGGREGATE' ELSE 'FUNCTION' END
                          , oid::regprocedure)
                   , E'\n')
   FROM   pg_proc
   WHERE  pronamespace = 'public'::regnamespace;  -- schema name here!

   IF _sql IS NOT NULL THEN
      RAISE NOTICE '%', _sql;  -- debug / check first
      -- EXECUTE _sql;         -- uncomment payload once you are sure
   ELSE 
      RAISE NOTICE 'No fuctions found in schema %', quote_ident(_schema);
   END IF;
END
$do$;

Best Answer

Related Solutions

PostgreSQL – Changing Float to Numeric Casts from Assignment to Implicit

PostgreSQL – How to Drop All Functions

Postgres 11 or later

Postgres 10 or older

Related Question