Edited to answer the question related to the \ddp
command not the \dp
command as @personne3000 pointed out in the comment below.
You probably want to use ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA kpi REVOKE EXECUTE ON FUNCTIONS FROM intranet2;
This is because postgres
is the user that was granted the default privilege of execute on the functions in the schema kpi
and is granting it to intranet2
, as noted by the permissions of intranet2=X/postgres
I created a small example to illustrate what's going on.
pgsql@[local]:5432:pgsql:=# CREATE ROLE bob;
CREATE ROLE
Time: 0.526 ms
pgsql@[local]:5432:pgsql:=# CREATE SCHEMA we_like_bob;
CREATE SCHEMA
Time: 0.608 ms
pgsql@[local]:5432:pgsql:=# ALTER DEFAULT PRIVILEGES FOR ROLE bob IN SCHEMA we_like_bob GRANT EXECUTE ON FUNCTIONS TO pgsql;
ALTER DEFAULT PRIVILEGES
Time: 1.480 ms
pgsql@[local]:5432:pgsql:=# \ddp
Default access privileges
Owner | Schema | Type | Access privileges
-------+-------------+----------+-------------------
bob | we_like_bob | function | pgsql=X/bob
(1 row)
User pgsql has the execute privilege, X, granted by bob.
Try to revoke the execute privilege for the role pgsql, as pgsql (a superuser).
pgsql@[local]:5432:pgsql:=# ALTER DEFAULT PRIVILEGES FOR ROLE pgsql IN SCHEMA we_like_bob REVOKE EXECUTE ON FUNCTIONS FROM pgsql;
ALTER DEFAULT PRIVILEGES
Time: 0.176 ms
pgsql@[local]:5432:pgsql:=# \ddp
Default access privileges
Owner | Schema | Type | Access privileges
-------+-------------+----------+-------------------
bob | we_like_bob | function | pgsql=X/bob
(1 row)
Didn't work because the command is not removing the bob role's default ability to grant execute on functions to role pgsql, it is removing the pgsql role's ability to revoke execute on functions from pgsql.
If we change it to remove the privilege of execute from the bob role for the role pgsql, then it works.
pgsql@[local]:5432:pgsql:=# ALTER DEFAULT PRIVILEGES FOR ROLE bob IN SCHEMA we_like_bob REVOKE EXECUTE ON FUNCTIONS FROM pgsql;
ALTER DEFAULT PRIVILEGES
Time: 0.644 ms
pgsql@[local]:5432:pgsql:=# \ddp
Default access privileges
Owner | Schema | Type | Access privileges
-------+--------+------+-------------------
(0 rows)
pgsql@[local]:5432:pgsql:=#
No. There is no way. I think there is a slight misconception of how things work.
Unlike functions, which store the function body as string (late binding!), a view parses the query and does not store the original text at all. All identifiers are resolved according to the current search_path
and only their internal OIDs are saved (early binding!). That's also why SELECT *
always resolves to the list of columns at the point in time when the VIEW
was created.
What you see in pgAdmin or other clients when looking at the definition of a VIEW
is a re-engineered SQL string. public.schedule
will always be public.schedule
and sally.schedule
will always be sally.schedule
(unless you rename table or schema). The (schema-qualified) names that are displayed are chosen such that the identifier is unambiguous with regard to the current search path.
This also shows when you change the names of columns, tables or schemas. The view keeps just working, because it does not depend on these attributions. The display of the definition is adapted dynamically.
Possible solution
If you want late binding you could use a function instead of a view and default to the current search path (one could SET
the search path for the scope of a function to avoid that effect precisely.)
So instead of:
CREATE VIEW v_mechanics_schedule AS
SELECT * FROM schedule s -- use aliases for short qualified column names in display
LEFT JOIN mechanics m USING (mechanic_id);
Without schema-qualification, table names are resolved according to the current search_path
at creation time.
You could:
CREATE FUNCTION f_mechanics_schedule()
RETURNS TABLE (...) AS -- you have to spell out columns
$func$
SELECT * FROM schedule s -- this is typically error prone
LEFT JOIN mechanics m USING(mechanic_id);
$func$ LANGUAGE SQL
Now, table names are resolved according to the search_path
at execution time. Note that the same does not apply to the return type ( RETURNS TABLE (...)
), which is determined at creation time. So this trick only works for compatible tables - the query has to return the same list of columns, only data types and the number of columns matter, column names are ignored (only the names in the definition of the return type are visible outside the function).
That's also why SELECT *
to return values in the function body is typically unreliable. If you change the definition of underlying tables, the function breaks: The defined return type remains the same, but SELECT *
resolves to a different column list ...
OTOH, if you declare a function as RETURNS SETOF some_table
(different syntax variant), a functional dependency is registered and the return type of the function is bound to the table definition, so SELECT * FROM some_table
would make more sense.
This is also the reason why it is unsafe to have SECURITY DEFINER
functions without fixing the search_path
: Any user with the TEMP
privilege can create a temp table that hides other tables because pg_temp
comes first in the search path by default ...
Related:
You really need to understand the underlying mechanisms to play with this.
Best Answer
This drops privileges from objects in the schema named "public". It does not drop them from objects in other schemas, such as "example-prod".
Works for me. It still fails due to the other 3 MV, but no longer for that specific one.