Neon's default super user `neondb_owner` cannot grant `pg_create_subscription` to another user

[guidomb]

Steps to reproduce

Using neon's default user with super admin permission I create a new database and then create a new users as follows:

CREATE ROLE "app_user" WITH LOGIN PASSWORD '<password>' REPLICATION;

-- Grant permission to connect to the database
-- CREATE is needed in order to create extensions when executing migrations
GRANT CREATE, CONNECT ON DATABASE "<database>" TO "app_user";

-- Grant USAGE and CREATE on the schema
GRANT USAGE, CREATE ON SCHEMA public TO "app_user";

-- Grant SELECT, INSERT, UPDATE, DELETE on all existing tables in the schema
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO "app_user";

-- grant SELECT on all tables added in the future
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO "app_user";

-- Grant USAGE on all sequences in the schema
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO "app_user";

-- Grant USAGE on all sequences added in the future
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE ON SEQUENCES TO "app_user";

I then try to execute (again using neondb_owner super user):

GRANT pg_create_subscription TO app_user;

Which fails with the message:

ERROR:  permission denied to grant role "pg_create_subscription"
DETAIL:  Only roles with the ADMIN option on role "pg_create_subscription" may grant this role.

If I execute this query SELECT rolname, rolsuper FROM pg_roles WHERE rolname = current_user; I get the following output:

This query

SELECT 
    grantee.rolname AS user_with_admin_option,
    m.admin_option
FROM 
    pg_auth_members AS m
JOIN 
    pg_roles AS grantee ON m.member = grantee.oid
JOIN 
    pg_roles AS granted_role ON m.roleid = granted_role.oid
WHERE 
    granted_role.rolname = 'pg_create_subscription';

returns:

Looks like neon super user is not that "super user" enough. This is an issue because I need to give pg_create_subscription to another user and there seems to be no way of doing it.

Expected result

GRANT pg_create_subscription TO app_user; to work

Actual result

ERROR:  permission denied to grant role "pg_create_subscription"
DETAIL:  Only roles with the ADMIN option on role "pg_create_subscription" may grant this role.

Environment

Neon Postgres 16

Logs, links

[ololobus]

Thank you for raising the ticket. If it's tolerable for you that new users will also have neon_superuser, then you can create new roles/users via Neon console UI or API. In this case, they will be implicitly granted neon_superuser and pg_create_subscription as well. I think that's the only workaround that immediately comes to mind

[guidomb]

That's the thing that I wanted to avoid, give super user to the user our application uses to connect to the database.

Apart from the unnecessary permission upgrade this would requiere some changes in our running app due to how tables were created.

Maybe you can help me with the issue that made me found about this. The thing is that I was creating a subscription using the neondb_owner super user but replication wasn't working because that user is not the owner of the replicating table. Is there a way to make it work?

[ololobus]

Got it, yes, makes sense.

Maybe you can help me with the issue that made me found about this. The thing is that I was creating a subscription using the neondb_owner super user but replication wasn't working because that user is not the owner of the replicating table. Is there a way to make it work?

Don't see any better options than granting needed permissions to your role. Internally, we can escalate access to the superuser upon the customer's request and try to adjust permissions for your users. I see you have already created a support ticket, so our support team should help. They will be in touch with engineers if needed

[guidomb]

Got it, yes, makes sense.

Maybe you can help me with the issue that made me found about this. The thing is that I was creating a subscription using the neondb_owner super user but replication wasn't working because that user is not the owner of the replicating table. Is there a way to make it work?

Don't see any better options than granting needed permissions to your role. Internally, we can escalate access to the superuser upon the customer's request and try to adjust permissions for your users. I see you have already created a support ticket, so our support team should help. They will be in touch with engineers if needed

I thought that an easy solution would be to change ownership of the table to be owned by neondb_owner. I tried executing ALTER TABLE utility_companies OWNER TO neondb_owner; logged in as neondb_owner and it didn't work. I got ERROR: must be owner of table utility_companies. I tried logging in as the table owner and executing the same command and got ERROR: must be able to SET ROLE "neondb_owner"

Reading Postgres docs https://www.postgresql.org/docs/8.1/sql-altertable.html I see:

To alter the owner, you must also be a direct or indirect member of the new owning role, and that role must have CREATE privilege on the table's schema. (These restrictions enforce that altering the owner doesn't do anything you couldn't do by dropping and recreating the table. However, a superuser can alter ownership of any table anyway.)

I guess the neon_superuser does not have the privilege to change table ownership then

[guidomb]

OK just to add more context I created a new database (owned by the postgres superuser) in my local postgres installation. I created a user with the same privileges my app user has (using the same setup scripts)

-- Create the role
CREATE ROLE "<role>" WITH LOGIN PASSWORD '<password>' REPLICATION;

-- Grant permission to connect to the database
-- CREATE is needed in order to create extensions when executing migrations
GRANT CREATE, CONNECT ON DATABASE "<database>" TO "<role>";

-- Grant USAGE and CREATE on the schema
GRANT USAGE, CREATE ON SCHEMA public TO "<role>";

-- Grant SELECT, INSERT, UPDATE, DELETE on all existing tables in the schema
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO "<role>";

-- grant SELECT on all tables added in the future
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO "<role>";

-- Grant USAGE on all sequences in the schema
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO "<role>";

-- Grant USAGE on all sequences added in the future
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE ON SEQUENCES TO "<role>";

I created the all my application's table using the migration script using the newly created application user.

I now create the subscription as follows connected as the postgres super user

CREATE SUBSCRIPTION guidomb_local_utility_companies_sub CONNECTION 'DATABASE_URL' PUBLICATION utility_companies_pub WITH (copy_data = true);

This works. So the conclusion is that the neondb_owner is lacking the privilege that a regular Postgres superuser has that allows for a subscription created by it to replicate insert / delete / update statement to a table it does not own.

[guidomb]

@ololobus I managed to make the replication work in my use case by adding run_as_owner = true when creating the subscription with the neondb_owner (which is not the table owner). According to https://www.postgresql.org/docs/current/logical-replication-security.html#LOGICAL-REPLICATION-SECURITY

The subscription apply process will, at a session level, run with the privileges of the subscription owner. However, when performing an insert, update, delete, or truncate operation on a particular table, it will switch roles to the table owner and perform the operation with the table owner's privileges. This means that the subscription owner needs to be able to SET ROLE to each role that owns a replicated table.

If the subscription has been configured with run_as_owner = true, then no user switching will occur. Instead, all operations will be performed with the permissions of the subscription owner. In this case, the subscription owner only needs privileges to SELECT, INSERT, UPDATE, and DELETE from the target table, and does not need privileges to SET ROLE to the table owner. However, this also means that any user who owns a table into which replication is happening can execute arbitrary code with the privileges of the subscription owner. For example, they could do this by simply attaching a trigger to one of the tables which they own. Because it is usually undesirable to allow one role to freely assume the privileges of another, this option should be avoided unless user security within the database is of no concern.

The problem was the because neondb_owner is not a true super user and does not have the privilege to SET ROLE the replication was failing and got disabled.

My feedback would be to clarify this in the docs and provide a way of reading the replication logs in the UI console to make it easier to debug the issue. Maybe considering having a way of executing commands as the true Postgres super user, I image that there good reasons not to expose the super user (probably something related to access to Neon's infrastructure) but it is a bit worrisome that as a customer I need to through technical support if I really need to use the super user. Is this something that you guys are working on? Can you improve / change your design in order for customer to access the superuser?

[ololobus]

My feedback would be to clarify this in the docs

Yeah, I'll forward this to the docs team. Thanks a lot for digging deeper in to the issue

provide a way of reading the replication logs in the UI console to make it easier to debug the issue

Yes, better user-facing observability and giving access to the logs is on our roadmap, but I do not have any specific estimate

Maybe considering having a way of executing commands as the true Postgres super user, I image that there good reasons not to expose the super user (probably something related to access to Neon's infrastructure) but it is a bit worrisome that as a customer I need to through technical support if I really need to use the super user. Is this something that you guys are working on? Can you improve / change your design in order for customer to access the superuser?

I totally understand the point. The current reason is not even security as we migrated the whole compute fleet to virtual machines instead of containers, so they should be pretty well isolated. It's more about preventing users from breaking the Postgres in a way that will require support or eng escalation. With a superuser, it's 'super' easy as Neon compute needs some specific options to run, which could be easily broken. Yet, it's our long-term goal to provide the superuser to our customers, and we are taking some gradual steps to improve the role grants/permissions system, but that's a long journey, unfortunately