Skip to content

Change owner of a database (postgreSQL)

This guide explains how to change the ownership of database objects in PostgreSQL. Database ownership determines which user has control over objects like tables, sequences, views, and functions.

When to Change Ownership

You may need to change ownership when:

  • Reorganizing database permissions and user roles
  • Transferring database management responsibilities
  • Correcting ownership after importing or migrating data
  • Consolidating objects under a single administrative user

Best Practice: Keep ownership on admin user

It is recommended to keep object ownership assigned to your admin user. This simplifies database management and ensures consistent control over all objects.

Important: If you later want to delete a user who owns database objects, PostgreSQL will return an error indicating that objects are still linked to that user. You must first transfer ownership or drop those objects before the user can be deleted.

Before You Begin

To change ownership, you need to:

  1. Connect to your PostgreSQL database using your preferred client (psql, pgAdmin, DBeaver, etc.)
  2. Have appropriate permissions (you must be a superuser or the current owner of the object)
  3. Know the connection credentials for your database

Verify current ownership first

Before making changes, use the verification queries below to check which user currently owns specific objects.

Change Owner for Individual Objects

Use the following ALTER commands to change ownership of individual database objects. Replace table_name, sequence_name, etc. with your actual object names, and new_owner with the username you want to assign ownership to.

Tables

1
ALTER TABLE table_name OWNER TO new_owner;

Example: 

1
ALTER TABLE customers OWNER TO admin_user;

Sequences

1
ALTER SEQUENCE sequence_name OWNER TO new_owner;

Example: 

1
ALTER SEQUENCE customers_id_seq OWNER TO admin_user;

Views

1
ALTER VIEW view_name OWNER TO new_owner;

Example: 

1
ALTER VIEW customer_overview OWNER TO admin_user;

Functions

1
ALTER FUNCTION function_name OWNER TO new_owner;

Example: 

1
ALTER FUNCTION calculate_total() OWNER TO admin_user;

Schemas

1
ALTER SCHEMA schema_name OWNER TO new_owner;

Example: 

1
ALTER SCHEMA public OWNER TO admin_user;

Database

1
ALTER DATABASE database_name OWNER TO new_owner;

Example: 

1
ALTER DATABASE my_application OWNER TO admin_user;

Note

You must be connected to a different database (not the target database) to change a database's owner.

Best Practices

  1. Keep ownership on the admin user - This simplifies management and ensures you maintain control over all database objects.

  2. Document ownership changes - Keep a record of ownership changes for audit and troubleshooting purposes.

  3. Check dependencies before deleting users - Always verify what objects a user owns before attempting to delete that user account. Use the verification queries above.

  4. Use transactions for bulk changes - When changing ownership of many objects, wrap your operations in a transaction:

1
2
3
BEGIN;
-- Run your ALTER statements here
COMMIT;

If something goes wrong, you can ROLLBACK; to undo all changes.

  1. Test in development first - Always test ownership changes in a development or staging environment before applying them to production databases.

  2. Consider using roles - Instead of assigning ownership to individual users, consider using PostgreSQL roles (groups) for easier permission management across multiple users.

Need help?

If you encounter issues changing database ownership or need assistance with complex permission scenarios, please contact Nexaa support.