CODEWITHSUNDEEP
postgresql
P_impl55
P_impl55

Reputation: 148

Setting default privileges on tables not working as expected in Postgres 13

Setup: I'm using Google Cloud Platform's managed Postgres 13 instance, which is a fresh install with no existing tables or users (expect for the postgres admin).

Goal: I want to create a new database called my_db and have two new users steve and mike be able to execute DDL and DML commands on future tables (e.g., create new tables, insert data, read, etc). This also means steve and mike should be able to modify and read/write each other's tables.

Problem: Even when I create the users and set the default privileges in schema public grant all to tables in my_db only the creator (steve) of the new table can read/write the table while mike cannot. Furthermore, even the postgres admin cannot read the new table!

Steps: How to recreate

  1. First I'll create the new database and users, plus give them lenient permissions to alter/read/write future tables in my_db.
-- Logged in as user = postgres (Connection 1)
\c postgres

create database my_db;

-- connect to my_db and create the new users
\c my_db

-- steve user
CREATE USER steve WITH PASSWORD 'pass123';
GRANT connect ON DATABASE my_db TO steve;
alter default privileges in schema public grant all on tables to steve;

-- mike user
CREATE USER mike WITH PASSWORD 'pass456';
GRANT connect ON DATABASE my_db TO mike;
alter default privileges in schema public grant all on tables to mike;

If my understanding is correct, steve and mike can now create and modify tables in my_db. Let's test this.

  1. Then login as steve in a new connection #2 and create a new test table:
-- Logged in as user = steve (Connection 2)
\c my_db

create table test_tbl ( id int4 ); -- success
select * from test_tbl; -- 0 records
  1. Now let's see if mike can read from the test_tbl which he should given the default privileges. We create connection #3 for mike:
-- Logged in as user = mike (Connection 3)
\c my_db

select * from test_tbl; -- ERROR: steve does not have permissions to read test_tbl!

This is my first point of confusion as I thought default permissions would let mike read the test_tbl created by steve.

  1. As a final oddity I decided to circle back to the postgres user to test reading test_tbl:
-- Logged in as user = postgres (Connection 1)
\c my_db

select * from test_tbl; -- ERROR: steve does not have permissions to read test_tbl!

GRANT SELECT, INSERT, UPDATE, delete ON ALL TABLES IN SCHEMA public TO mike; -- same error above!

So not even the admin user postgres can read this new table NOR can I grant permissions...

  1. The only thing that worked is logging back in as steve--the original table creator--and granting postgres and mike permissions:
-- Logged in as user = steve (Connection 2)
\c my_db

GRANT SELECT, INSERT, UPDATE, delete ON ALL TABLES IN SCHEMA public TO postgres; -- success
GRANT SELECT, INSERT, UPDATE, delete ON ALL TABLES IN SCHEMA public TO mike; -- success

This all seems backwards. The default privileges in schema public grant all should take care of allowing users to modify future tables, right? What am I missing?

Thanks in advance.

Upvotes: 2

Views: 4041

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246523

ALTER DEFAULT PRIVILEGES without the FOR ROLE clause affects only objects created by the role that ran the ALTER DEFAULT PRIVILEGES statement.

You would need two such statements to get what you want:

ALTER DEFAULT PRIVILEGES FOR ROLE mike GRANT ... TO steve;
ALTER DEFAULT PRIVILEGES FOR ROLE steve GRANT ... TO mike;

The other thing you want cannot be had in a straigtforward fashion. Only the owner (or members of that role) and superusers can ALTER or DROP an object. There is no way to grant that privilege. Your only solution would be to have a common table_owner role and have both users be a member of the role:

CREATE ROLE table_owner NOLOGIN;

GRANT CREATE ON SCHEMA myschema TO table_owner;

ALTER ROLE mike NOINHERIT;
ALTER ROLE steve NOINHERIT;

GRANT table_owner TO mike, steve;

Now both users need to SET ROLE to create a table in the schema:

SET ROLE table_owner;

CREATE TABLE myschema.atable (...);

That table is then owned by table_owner, and both users can ALTER or DROP it.

Upvotes: 7

Related Questions