Grant pg_read_all_data to roles

Hi! I'm trying to set up my neondb instance. Part of my migration files is grant pg_read_all_data to <role>. This doesn't seem to work because: DETAIL: Only roles with the ADMIN option on role "pg_read_all_data" may grant this role. I'm using my neondb_owner role to run the migrations. Is there something that needs to be done to enable the owner role to be admin on pg_read_all_data ? Thank you

11 Replies

correct-apricotOP•2y ago

Seems like neon_superuser indeed does not have admin_option on that role. Is that expected? I was under the imression that this role would have full access?

SELECT roleid::regrole, member::regrole, grantor::regrole, admin_option 
FROM   pg_auth_members where roleid = 'pg_read_all_data'::regrole;
      roleid      |     member     |   grantor   | admin_option 
------------------+----------------+-------------+--------------
 pg_read_all_data | neon_superuser | cloud_admin | f
(1 row)

SELECT roleid::regrole, member::regrole, grantor::regrole, admin_option 
FROM   pg_auth_members where roleid = 'pg_read_all_data'::regrole;
      roleid      |     member     |   grantor   | admin_option 
------------------+----------------+-------------+--------------
 pg_read_all_data | neon_superuser | cloud_admin | f
(1 row)

overseas-lavender•2y ago

Hi @Kezlar As a managed service, we do limit some privileges (and do not grant Postgres superuser). neon_superuser privileges are described here: https://neon.tech/docs/manage/roles#the-neonsuperuser-role, and we cover database object ownership in the context of migrations in this section of the docs: https://neon.tech/docs/import/import-from-postgres#database-object-ownership-considerations It would be good to know if the proposed approach of specifying the -O, --no-owner option in your pg_restore command is a possibility for you or a blocker.

Neon

Import data from Postgres - Neon Docs

This topic describes migrating data from another Postgres database to Neon using the pg_dump and pg_restore command line utilities. Avoid using pg_dump over a pooled connection string (see PgBouncer i...

Neon

Manage roles - Neon Docs

In Neon, roles are Postgres roles. Each Neon project is created with a default Postgres role that takes its name from your Neon account (the email, GitHub, Google, or partner account that you register...

correct-apricotOP•2y ago

Hey Daniel, appreciate the response! I'm not sure if it's applicable here since I'm not using pg_restore. I'm running my sql migrations (which are a bunch of create tables, function and roles commands) directly on neon. The "blocker" part of it is that the command that adds the read_all_data role is in the middle of the migration, I can't really skip it.

overseas-lavender•2y ago

Sorry about that. Unfortunately, we don't support granting pg_read_all_data. A headache, but an option might be to load the data into a local Postgres and then use pg_dump and then pg_restore with the -O, --no-owner option. I'll communicate this blocker internally. Perhaps we can look at expanding privileges, but no timelines on that.

correct-apricotOP•2y ago

Gotcha. If at all possible to grant it temporarily just so I can run that migration and then revoke it, that would be great as well. Once I have that migration in it should be smooth sailing beyond 😅 Having the role is not really critical for our app beyond running the migration, it was done for snaplet integration which we don’t really need for neon, at least for now

overseas-lavender•2y ago

Unfortunately, these privileges are built in, so we can't just turn them on/off. Our Postgres team would have to test it, implement it, merge it into a release, etc. If you're a paying user, you might reach out to our Support team to see what they could do for you.

correct-apricotOP•2y ago

I see. Was definitely planning on upgrading to a paid plan but was hoping to see it work before doing that,

overseas-lavender•2y ago

Totally get it. My apologies.

adverse-sapphire•2y ago

Hey @Kezlar, Would it make sense for the case to update the migration query to silently ignore the error with BEGIN...EXCEPTION...END blocks?

correct-apricotOP•2y ago

The migration tool I’m using doesn’t really support that unfortunately. There’s some hacky ways of doing it but I’m not sure it would work.

adverse-sapphire•2y ago

Interesting, let me try and think of more things for you.

Gaming

Programming

Grant pg_read_all_data to roles

Did you find this page helpful?