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

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(at)sqlexec(dot)com <mailto:michaelvitale(at)sqlexec(dot)com>

703-600-9343