CLOUDSQLADMIN super user or other super users to be created

[Savithlalwumdro]

Hi Team,

We dont have any super admin user in our SQL DB and inline are the list of users we have..

None of the users are super user..We have couple of use cases to be developed using the super user and all the use cases are halted now..If we use the aforesiad users then either we are getting error message like “Permission denied” or “You need to be a super user”

Request you to suggest how to create Super users..

Thanks,

Savithlal.H

[ms4446]

In Cloud SQL, you cannot create users with superuser privileges. However, you can create users with the cloudsqlsuperuser role, which has some superuser privileges. To do this, follow these steps:

1. Go to the Cloud SQL Instances page in the Google Cloud console.
2. Click the name of the instance that you want to create a superuser for.
3. Click the Users tab.
4. Click Add user account.
5. In the Add a user account to instance dialog, select the Built-in authentication option.
6. Enter a username and password for the new user.
7. In the Roles section, select the cloudsqlsuperuser role.
8. Click Add.

Once you have created a user with the cloudsqlsuperuser role, they will be able to perform all of the actions that a superuser can perform, except for creating new superusers.

Here are some of the privileges that the cloudsqlsuperuser role grants:

• Creating extensions that require superuser privileges.
• Creating event triggers.
• Creating replication users.
• Creating replication publications and subscriptions.
• Full access to the pg_largeobject catalog table.

If you need to perform an action that requires superuser privileges, you can use a user with the cloudsqlsuperuser role. However, it is important to note that this role should be used with caution, as it grants a lot of power to the user.

[Savithlalwumdro]

Hi ms446,

Thanks you but all the users created has a role as “cloudsqlsuperuser”..I just recreated another user as per your suggestion and inline is the screen shot please

When we filer the user table in postgres

Still the “usersuper” for the user[kadellabs_savith] is not enabled..How to enable this column?

Thanks,

Savithlal.H

[ms4446]

Cloud SQL does not allow super user rights like native Postgres. The cloudsqlsuperuser role is a special role that is managed by Cloud SQL. It grants some of the privileges of a superuser, but it does not grant all of the privileges.

[Savithlalwumdro]

Hi ms4446,

Thank you but native postgres in cloudsql also doesnt have the super user permission any luck to grant the “usesuper” privelege for POSTGRES?

Thanks,

Savithlal.H

[ms4446]

The highest level permissions a user can have in CloudSQL is be a member of the cloudsqlsuperuser

[Savithlalwumdro]

Thank you ms4446

[kvijay460e]

can we add this user using gcloud cli with role: cloudsqlsuperuser?

[ms4446]

The gcloud command-line tool does not directly support assigning the cloudsqlsuperuser role to a user in Google Cloud SQL. The cloudsqlsuperuser role is a special role within the Cloud SQL environment, and typically, user roles and permissions within the database are managed through SQL commands or the Cloud Console, not directly through the gcloud CLI.

[kkit]

I have the same problem and seems can’t find the roles section here

[ms4446]

Here’s how to find and assign the cloudsqlsuperuser role:

Steps:

1. Cloud SQL Instances: Navigate to the Cloud SQL Instances page in your Google Cloud Console.
2. Your Instance: Click on the name of the specific instance you want to manage users for.
3. Users Tab: Click the “Users” tab within the instance details view.
4. Add User Account (or Edit Existing):
   • If creating a new user, click “Add User Account.”
   • If modifying an existing user, click the pencil icon next to the username.
5. Built-in Authentication: Ensure “Built-in authentication” remains selected.
6. Roles Section: The “Roles” section should now be visible. From here, you can select the cloudsqlsuperuser role (and any other roles needed)
7. Save: Complete the user creation or modification.

[kkit]

as you can see from my question and attached picture above, there is no roles section there. So how could I find it?

[lazydba]

Hello,

I can’t see roles section tab in the Add a user account to instance tab.
In my case, I need to grant access with superuser privileges.
How can do this? Should I use gcloud commandine or is there a way on the interface?

[ms4446]

Cloud SQL, being a managed service, places certain constraints on user privileges for security and stability.

• You cannot directly create true superuser accounts in Cloud SQL. This is done to protect the integrity of the managed database environment.

How to Work Within the Constraints

1. The cloudsqlsuperuser Role: This role is the closest approximation to a superuser within Cloud SQL. It grants significant privileges, including:

   • Creating extensions that might normally require superuser status
   • Creating event triggers
   • Managing replication users, publications, and subscriptions
   • Full control of the pg_largeobject table

2. Assigning the Role: While the Cloud Console doesn’t have a direct “roles” tab, you can achieve this in the following ways:

   • Cloud Console:

     1. Navigate to your Cloud SQL instance.
     2. Click the “Users” tab.
     3. Add or edit a user.
     4. Under “PostgreSQL roles”, you should be able to select cloudsqlsuperuser amongst other available roles.

   • SQL Commands:

     1. Connect to your Cloud SQL instance.

     2. Use the following SQL command:

          ```
          GRANT cloudsqlsuperuser TO your_username;
        
          ```
        

Please Note:

• The cloudsqlsuperuser role doesn’t provide full, unrestricted superuser powers.
• Carefully assess if the privileges provided by cloudsqlsuperuser align with your specific needs. If your requirements exceed these, you might need to consider alternatives outside of the fully managed Cloud SQL offering.

[lazydba]

There isn’t be able to select cloudsqlsuperuser rolse under “PostgreSQL roles” and I can’t see any roles .

I don’t have access SQL ommand line when I try to connect on gcloud but I get error message like this to below;

How can I fix the problem, there is any solution or alternative method?

Thanks.

[ms4446]

Ensure the Google Cloud account you are using has the necessary IAM permissions to manage users and roles on your Cloud SQL instance. Check for the cloudsql.instances.update permission.

The error message “It seems your client does not have ipv6 connectivity and the database instance does not have an ipv4 address…” indicates a network configuration problem. Here’s what to do:

• Enable IPv4:
  1. Navigate to your Cloud SQL instance in the Google Cloud Console.
  2. Click “Edit”.
  3. Locate the “Connectivity” section.
  4. Ensure that “Assign a public IP address” is selected.
  5. Save your changes.
• IP Allowlisting:
  1. On the same “Connectivity” section of your instance, find the “Authorized networks” area.
  2. Add the IP address of your local machine to allow connections.

Alternative Connection Methods:

If fixing these doesn’t solve the gcloud sql connect problem, consider these options:

• Cloud Shell: The Cloud Shell environment in the Google Cloud Console often has preconfigured network connectivity to your Cloud SQL instance. Try connecting using SQL commands directly within the Cloud Shell.
• Cloud SQL Proxy: The Cloud SQL Proxy provides a secure connection tunnel. Follow this guide: https://cloud.google.com/sql/docs/mysql/sql-proxy

[lazydba]

Hello,

How can I check my GCP account has cloudsql.instances.update permission or not?

There is no options for us add or enable IPV4 address on this project.

Best Regards.

[ms4446]

Here’s how you can check your Google Cloud account’s permissions and address the IPv4 address issue:

Checking GCP Permissions

1. IAM Page:
   • Navigate to the “IAM & Admin” section in your Google Cloud Console.
   • Click on “IAM”.
2. Filter:
   • In the search bar at the top, type your account email address to filter the view to show your assigned permissions.
3. Permission Check:
   • Look for the “cloudsql.instances.update” permission. If it’s present, your account can modify Cloud SQL instances.

Addressing the Missing IPv4 Option

There are a couple of scenarios where you might not see the option to enable an IPv4 address:

• Project Restrictions: Some projects might have network configuration restrictions imposed at the project level. Contact your project administrator or owner to verify if there are any limitations in place.
• Limited Quotas: Your project might have reached its quota for external IPv4 addresses. You can check your current quotas in the IAM & Admin section under “Quotas”. If necessary, you may need to request an increase for your IPv4 quota.

Troubleshooting and Alternative Solutions

1. Confirm Permissions: Double-check that your account indeed lacks the cloudsql.instances.update permission. If missing, you’ll need account changes from someone with the appropriate administrative roles in your project.

2. Investigate Project Settings: If you don’t have direct control over the project, work with your project administrator to understand if there are network-level restrictions or quota limitations in place.

3. Alternative Connection Methods (if you have the necessary permissions): While you resolve the permissions or IPv4 issue, explore these workarounds based on your available permissions:

   • Cloud Shell: Try connecting via SQL commands from the Cloud Shell within the Google Cloud Console.
   • Cloud SQL Proxy: Set up the Cloud SQL Proxy to establish a secure tunnel for database connections.

[blame_acceptor]

There are other implications with this that I haven’t seen explicitly documented to the best of my knowledge. One of those is dealing with object ownership and the lack of actual superusers.

In a “normal” PostgreSQL cluster, there are no checks on a superuser. For example:

• a table X is owned by non-superuser role Y that can create table
• a superuser role Z can access, alter table X even though role Y did NOT grant any access to it
• a superuser role Z can grant, revoke privileges to it even though role Y did NOT grant said privileges

In a CloudSQL PostgreSQL cluster, the above will NOT work with members of the cloudsqlsuperuser role. It is a real pain to handle this in my honest opinion.

[archi-eb]

I’m Cloud SQL Admin, still i’m unable to add/update roles.
Also, there is a user postgres created, to which i’m unable to assign cloudsqlsuperuser role