How to Change Ownership in PostgreSQL?

Ownership of a database and the tables in it in PostgreSQL is very crucial because this has a big influence on users who want to carry out CRUD (Create, Read, Update, Delete) activities in a PostgreSQL database. If the user is not the owner of the database and the tables in it, then the user will not be able to perform CRUD.

Problem

How to change ownership in PostgreSQL?

Solution

For example, I have a mydb database and mydb_user as a user. When I access the database using the user mydb_user and perform a query:

select * from employee;

There is an error as shown below:

ERROR: permission denied for table employee

After I searched on the internet, it turned out that this was caused by the user mydb_user not being the owner of the mydb database and the tables in it. To see the owner of the database, you can use the query below:

SELECT datname AS database_name,
       pg_catalog.pg_get_userbyid(datdba) AS owner
FROM pg_database;

You can see in the picture above, the postgres user is the owner of the mydb database. This is because when creating the database uses the postgres user and not the mydb_user.

SELECT d.datname AS database_name, r.rolname AS owner
FROM pg_database d
JOIN pg_roles r ON d.datdba = r.oid
WHERE d.datname = 'your_database_name';

\l

Similarly, if you want to see the owners of the tables in the database (but you must first log in to that database to run this query), use the query below:

SELECT tablename, tableowner
FROM pg_tables
WHERE schemaname = 'public';

Then there will be a display like the following:

You can see in the image above that the owner of the table employee in the mydb database is a postgres user. This is because when creating the tables uses user postgres and user mydb.

SELECT 
    schemaname,
    tablename,
    tableowner
FROM pg_tables
ORDER BY schemaname, tablename;

A. Change database owner

If you want to change ownership of the mydb database from user postgres to user mydb_user, then you have to log in to PostgreSQL as an existing user, which in the case of a postgres user:

ALTER DATABASE mydb OWNER to mydb_user;

Run the query below to check ownership of the database:

\l

You can see that the owner of the mydb database has changed to user mydb_user. But even so, you still can’t run CRUD on the database because the tables in the database still belong to the postgres user.

B. Change the owner of the table

Still using the postgres user, log in to the mydb database using the command:

\c mydb;

Then run the query below to change the table, sequence, and view to mydb_user:

DO $$
DECLARE
    r RECORD;
BEGIN
    -- Change the owner of all tables
    FOR r IN SELECT schemaname, tablename FROM pg_tables WHERE schemaname = 'public'
    LOOP
        EXECUTE format('ALTER TABLE %I.%I OWNER TO mydb_user;', r.schemaname, r.tablename);
    END LOOP;

    -- Change the owner of all sequences
    FOR r IN SELECT sequence_schema, sequence_name FROM information_schema.sequences WHERE sequence_schema = 'public'
    LOOP
        EXECUTE format('ALTER SEQUENCE %I.%I OWNER TO mydb_user;', r.sequence_schema, r.sequence_name);
    END LOOP;

    -- Change the owner of all views
    FOR r IN SELECT table_schema, table_name FROM information_schema.views WHERE table_schema = 'public'
    LOOP
        EXECUTE format('ALTER VIEW %I.%I OWNER TO mydb_user;', r.table_schema, r.table_name);
    END LOOP;
END $$;

After that, run the command below to view the ownership of the tables in the mydb database:

SELECT tablename, tableowner
FROM pg_tables
WHERE schemaname="public";

From the image above, you can see the owner of the tables in the mydb database is not a postgres user but a mydb_user. So, you should be able to do CRUD using the mydb_user user as shown in the image below:

Note

As a reminder, to create databases and tables in the database, you should not use postgres user but create a new user because it is very dangerous to use postgres user to carry out daily activities in the PostgreSQL database.

References

commandprompt.com
stackoverflow.com
w3resource.com