phpPgAdmin , view/create tables etc from one user-CodePudding

I'm trying to achieve the following:

I have user1 which is created. This user1 has databases user1_db1, user1_db2 , user1_db3

When I grant the owner of user1 to all the databases, I am able to do everything with them,except if I create user1_user1 , grant All access ( without the owner ) to user1_db. Then user1_user1 will throw errors like : user1_user1=> ALTER DATABASE "user1_dbl" SET bytea_output = 'escape'; ERROR: must be owner of database user1_db1

What I need is a special user ( in this case user1_user1 ) to be able to do everything with the database that all access have been granted and is still visible in phppgadmin ( when $conf['owned_only'] = true; is set ) when logged with user1 , or if needed I can create a special user for PHPpgAdmin but must view the databases that are with prefix user1 only.

Maybe my approach is not proper so please let me know how can I achieve this.

The goal is to have 1 user , able to access/modify all those databases and in the same scenario , user1_user1 is able to do the same but not accessing phppgadmin. The limitation that I am seeing is the ownership only and I am not able to bypass it... Thank you for your advices!

CodePudding user response：

You can use the same user for all of the databases, but you will need to give that user access to each database. You can do that by running the following SQL:

GRANT ALL PRIVILEGES ON DATABASE user1_db1 TO user1_user1; GRANT ALL PRIVILEGES ON DATABASE user1_db2 TO user1_user1; GRANT ALL PRIVILEGES ON DATABASE user1_db3 TO user1_user1;

You can also give the user access to all databases by running the following SQL:

GRANT ALL PRIVILEGES ON ALL DATABASES TO user1_user1;

You can also give the user access to all tables in a database by running the following SQL:

GRANT ALL PRIVILEGES ON user1_db1.* TO user1_user1;

You can also give the user access to all tables in all databases by running the following SQL:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO user1_user1;

You can also give the user access to all sequences in a database by running the following SQL:

GRANT ALL PRIVILEGES ON user1_db1 TO user1_user1;

You can also give the user access to all sequences in all databases by running the following SQL:

GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO user1_user1;

You can also give the user access to all functions in a database by running the following SQL:

GRANT ALL PRIVILEGES ON user1_db1 TO user1_user1;

You can also give the user access to all functions in all databases by running the following SQL:

GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO user1_user1;

You can also give the user access to all types in a database by running the following SQL:

GRANT ALL PRIVILEGES ON user1_db1 TO user1_user1;

You can also give the user access to all types in all databases by running the following SQL:

GRANT ALL PRIVILEGES ON ALL TYPES IN SCHEMA public TO user1_user1;

You can also give the user access to all operators in a database by running the following SQL:

GROMySQL: How to grant all privileges on all databases and tables to a user?

The best way to do this is to use the GRANT ALL PRIVILEGES ON . TO 'user'@'localhost' syntax.

GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost' IDENTIFIED BY 'password';

This will give the user all privileges on all databases and tables.

CodePudding user response：

I finally made it work with the memberships :

postgres=# \du
                                            List of roles
     Role name     |                         Attributes                         |      Member of
------------------- ------------------------------------------------------------ ---------------------
 user1          |                                                            | {user1_pgsqluser}
 user1_pgsqluser |                                                            | {}

This can be granted as follow :

psql -U postgres -c 'grant $dbuser to $user;'