From: MichaelDBA <MichaelDBA@sqlexec.com>
To: Teju Jakkidi vlogs <teja.jakkidi05@gmail.com>
Cc: pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: Issue with "ALTER DEFAULT PRIVILEGES" command - Not working as expected
Date: Sat, 21 May 2022 16:27:56 -0400
Message-ID: <95098d75-e513-9070-6856-73127ceb1b4a@sqlexec.com> (raw)
In-Reply-To: <CAKA2XvY-yJFsecXZwNy2gNgVOjm02xLmrkREPeis45o27U-YTw@mail.gmail.com>
References: <CAKA2XvY-yJFsecXZwNy2gNgVOjm02xLmrkREPeis45o27U-YTw@mail.gmail.com>
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@sqlexec.com <mailto:michaelvitale@sqlexec.com>
703-600-9343
Attachments:pgadvanced3.jpg (image/jpeg, 20.6K) downloadview image
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: MichaelDBA@sqlexec.com, teja.jakkidi05@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Issue with "ALTER DEFAULT PRIVILEGES" command - Not working as expected
In-Reply-To: <95098d75-e513-9070-6856-73127ceb1b4a@sqlexec.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox