Search code examples
postgresqlschemaprivileges

Can't grant all privileges on schema public to a user in postgresql

I tried to give all privileges to user in order to access the table pg_largeobject in public schema:

GRANT ALL PRIVILEGES ON SCHEMA PUBLIC TO my_user ;

even using this:

GRANT ALL PRIVILEGES ON TABLE PG_LARGEOBJECT TO my_user;

returns this error:

ERROR:  permission denied for schema public

How can I give privileges to this user or I should connect to database with postgres which has superuser privileges?

Solution

  • You have to be a superuser to grant permissions on pg_largeobjects.

    But you should never do that.

    There is no need to modify that system catalog directly, and doing so will jeopardize the integrity of your PostgreSQL database.

    To delete a large object, use

    SELECT lo_unlink(4711);

    Here 4711 is the OID of the large object you want to delete.