Issue with "ALTER DEFAULT PRIVILEGES" command - Not working as expected

From: Teju Jakkidi vlogs <teja(dot)jakkidi05(at)gmail(dot)com>
To: pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org>
Subject: Issue with "ALTER DEFAULT PRIVILEGES" command - Not working as expected
Date: 2022-05-21 20:08:38
Message-ID: CAKA2XvY-yJFsecXZwNy2gNgVOjm02xLmrkREPeis45o27U-YTw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hello pgsql-admin community,

I am new to postgreSQL and we are trying to set up a database (Postgres
14.1) with some roles as below. We executed the ALTER DEFAULT PRIVILEGES
command to make sure that the privileges for the newly created objects will
be added automatically to the role with no manual work.
But the issue we are seeing is: We created the db, schema in the db, roles
and granted the roles to the database user. Then we created few objects in
the schema of the database. Later we tried to access the objects using the
database user which failed. Below are the steps that we performed. Please
let me know if am missing anything.

As postgres user:
================
postgres=# create role testrole1;
>>>>>>>>> this is the readonly role
CREATE ROLE
postgres=# create user globaluser1 password '************';
>>>>>>>>>>>>> this will be the owner of db, schemas,
objects
CREATE ROLE
postgres=# create user testuser1 password '***************';
>>>>>>>>>>>>> this will be the user for connecting to
database by application team
CREATE ROLE
postgres=# create database testdb1 owner globaluser1;
CREATE DATABASE
postgres=# grant connect on database testdb1 to testuser1;
GRANT

Connect to testdb1 to create schema and grant privileges to the role:
--------------------------------------------------------------------------------------------------------
postgres=# \c testdb1
You are now connected to database "testdb1" as user "postgres".
testdb1=# create schema testschema1 authorization globaluser1;
CREATE SCHEMA
testdb1=# grant select on all tables in schema testschema1 to testrole1;
>>>>>>>>>>>>> grant read only access on all
tables of the schema to the role
GRANT
testdb1=# ALTER DEFAULT PRIVILEGES in SCHEMA testschema1 GRANT SELECT ON
TABLES TO testrole1; >>>>this should do the grant by default for any
newly created objects
ALTER DEFAULT PRIVILEGES
testdb1=#
testdb1=# grant usage on schema testschema1 to testuser1;
GRANT
testdb1=# grant testrole1 to testuser1; >>>>>>>>>>>
granting the read only role to the database user
GRANT ROLE
testdb1=#set role globaluser1
>>>>>>>>>> To create objects with owner as globaluser1
testdb1=# create table testschema1.table1 (id int);
CREATE TABLE
testdb1=# select * from testschema1.table1;
id
----
(0 rows)

Connecting as the testuser1 to check read-only access:
======================================================

psql -U testuser1 testdb1
psql (13.7)
Type "help" for help.
testdb1=> select * from testschema1.table1;
ERROR: permission denied for table table1 >>>>>>>>>>>>>>>>>>>>>>>.
failed, which should not have failed.

********** If the objects are created first and then if we are creating
roles with required privileges, it works fine with no issues. But when we
are creating roles first and then creating objects, it fails as above.
But as per my understanding "ALTER DEFAULT PRIVILEGES" should do the work
of assigning privileges on newly created objects with no issues however it
is not working as expected unless I am missing something. Any help or
thoughts are greatly appreciated.

Thanks,
Teja.

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message MichaelDBA 2022-05-21 20:27:56 Re: Issue with "ALTER DEFAULT PRIVILEGES" command - Not working as expected
Previous Message Holger Jakobs 2022-05-21 18:53:41 Re: Steps to Install archived Postgres 9.3 and 9.6