Postgresql – Postgres Table Ownership

postgresql

I'm new to PostgreSQL and I'm trying to change the table owner for a bunch of tables. I was able to change the ownership by logging in a the postgres user and executing alter table owner to user1, but when I login as user1 it still shows postgres as the owner. For instance, when logged in as the postgres user and executing \dt or select * from pg_tables where tableowner = 'user1' it shows user1 as the owner, but when I login as user1 and do the same thing it still shows postgres as the table owner. I've tried restarting the service and that didn't change anything. What am I missing?

Best Answer

You must be seeing different tables from different database clusters, databases or schemas.

Either you are logging into a different database by mistake or you got yourself confused with tables of the same name in different schemas of the same database.

The latter would typically happen with two different roles that have different search_path settings or even the (default) setting:

search_path = "$user",public

Without schema-qualification, tables of the same name are found in the "current" schema first ...

Your test would also find the "wrong" table:

select * from pg_tables where tableowner = 'user1';

Test instead with:

select * from pg_tables where tablename = 'my_tbl';

You should see two or more rows for the same table name ...

Of just use schema-qualified table names to avoid possible confusion:

ALTER TABLE my_schema.my_tbl OWNER TO user1;
SELECT * FROM my_schema.my_tbl;

Start by reading about schemas in the manual.

Related Solutions

Postgresql – Postgres DB user permission

You should log in as the table owner (the user you used to create the tables) and then grant the necessary privileges to your user:

grant select on acs_activities to your_user;

If you want to do that for all tables, you can use:

grant select on all tables in schema public to you_user;

Or maybe you created them as the superuser, but your regular user should be the real owner, then you should do:

alter table acs_activities owner to your_user;

PostgreSQL – Resolving ‘Alter Default Privileges’ Issue

Either you've forgot to add a permission to SELECT in the GRANT below, quoted from the question:

ALTER DEFAULT PRIVILEGES FOR USER access1
   IN SCHEMA access1 GRANT INSERT, UPDATE, DELETE ON TABLES TO access1_rw;

... or, if you really meant that having the role access1_rw doesn't let read the tables even tough it lets write to them, then you must grant the role access1_ro to user1 (in addition to granting the role access1_rw).

Not having either of those has the consequence that user1 was never granted the right to SELECT from tables in the mentioned schema.

Best Answer

Related Solutions

Postgresql – Postgres DB user permission

PostgreSQL – Resolving ‘Alter Default Privileges’ Issue

Related Question