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

[MichaelDBA <MichaelDBA@xxxxxxxxxxx>]

It seems you were the postgres user when you granted default privileges, but then you set role = globaluser1, and then created testschema1.table1.  So the default privileges only apply to tables where postgres is the owner, not globaluser1.

Regards,
Michael Vitale


Teju Jakkidi vlogs wrote on 5/21/2022 4:08 PM:

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.

Regards,

Michael Vitale

Michaeldba@xxxxxxxxxxx

703-600-9343