0

In Postgres, only the schema owner, table owner, and super users should be able to drop a table but it seems I am able to drop one while being neither and I can figure out what I am missing.

I have two users, let's call them database_owner and table_owner.

The database_owner creates the database and then runs the following SQL to allow connection for the table_owner user:

GRANT CONNECT, CREATE ON DATABASE mydb TO table_owner;
GRANT USAGE, CREATE ON SCHEMA public TO table_owner;

Then I log in with the table_owner user, create a schema, and run various migrations. Eg:

CREATE SCHEMA "my_schema";
CREATE TABLE "my_schema"."test_table" ("id" serial,"value" text);

At this point I verify that I am the owner of the object and I am logged in with the table_owner user:

SELECT current_user, session_user;
-- returns table_owner  table_owner

SELECT nsp.nspname AS object_schema,
       cls.relname AS object_name, 
       rol.rolname AS owner, 
       CASE cls.relkind
         WHEN 'r' THEN 'TABLE'
         WHEN 'm' THEN 'MATERIALIZED_VIEW'
         WHEN 'i' THEN 'INDEX'
         WHEN 'S' THEN 'SEQUENCE'
         WHEN 'v' THEN 'VIEW'
         WHEN 'c' THEN 'TYPE'
         ELSE cls.relkind::text
       END AS object_type
FROM pg_catalog.pg_class cls
  JOIN pg_roles rol ON rol.oid = cls.relowner
  JOIN pg_namespace nsp ON nsp.oid = cls.relnamespace
WHERE nsp.nspname NOT IN ('information_schema', 'pg_catalog')
  AND nsp.nspname NOT LIKE 'pg_toast%'
  AND rol.rolname = current_user  --- remove this if you want to see all objects
ORDER BY nsp.nspname, cls.relname;

-- returns
-- my_schema    test_table_id_seq   database_owner  SEQUENCE
-- my_schema    test_table          database_owner  TABLE

SELECT n.nspname AS schema_name, pg_get_userbyid(n.nspowner) AS owner
FROM   pg_namespace n
WHERE  n.nspname NOT LIKE 'pg_%'
AND    n.nspname <> 'information_schema'
ORDER  BY 1;
-- returns
-- my_schema    database_owner
-- public       azure_superuser

Then I move back to the database_owner user and verify that I am not a super user and I am the database owner:

SELECT current_user, session_user;
-- returns database_owner database_owner

SHOW IS_SUPERUSER;
-- returns off

SELECT pg_catalog.pg_get_userbyid(d.datdba) as "Owner"
FROM pg_catalog.pg_database d
WHERE d.datname = 'test'
ORDER BY 1;
-- returns database_owner

Now I try to delete my table and I succeed.

DROP TABLE "my_schema"."test_table";
-- returns Query 1 Ok: DROP TABLE

What I am missing? Does the database owner has full access to all objects in the database even when no privilege is granted?

1 Answer 1

0

The database owner does not automatically have permissions to drop a table in the database, nor does the schema owner. You can drop a table only if

  • you are a superuser

  • you are the owner of the table, or you are directly or indirectly a member of the owner of the table

Since you say that database_owner is not a superuser, it must be a member of table_owner. The only other explanation could be that you are not really using PostgreSQL, but a fork where the permission system has been hacked up.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.