Menu
I have been looking for this for years and I have tried everything on the web with no success.
I am able to do it in MSSQL, but I didn´t find a way to do it in PostgreSQL.
What I want to achieve is just create a role with login that cannot create, drop or alter databases, functions, tables or anything else. Just select specific functions.
For example, if I have a table called costumer and two functions called return_customers() and return_time() I just want a role with login that are able to select return_customers() and select return_time(). Nothing more than that.
Thank you very much for supporting this useful web site!
568
Without further explanation, below is the step for creating role or user in PostgreSQL : 1. Make sure to connect to the PostgreSQL database using the admin account. Normally, the admin account is ‘postgres’. The following is the command for connecting to the PostgreSQL database using the admin account :
In order to manage the permissions for accessing the database, PostgreSQL uses a mechanism of roles. We can create a role as a user role or as a group role. The user can have the right to log in whereas the group role is not having login rights.
When roles contain other roles, they are called group roles. When you create a role, it is valid in all databases in the database server (or cluster). To create a new role, you use the CREATE ROLE statement as follows: To get all roles in the current PostgreSQL database server, you can query them from the pg_roles system catalog as follows:
The following is the command for connecting to the PostgreSQL database using the admin account : user@hostname:~$ psql -Upostgres postgres psql (10.5) Type "help" for help. postgres=> 2. After connecting to the database using the admin account, execute the following query to create a new role or a new user :
Execute this connected to the database you want to configure.
-- Create the user.
CREATE ROLE somebody WITH LOGIN PASSWORD '...';
-- Prevent all authenticated users from being able to use the database,
-- unless they have been explicitly granted permission.
REVOKE ALL PRIVILEGES ON DATABASE foo FROM PUBLIC;
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM PUBLIC;
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public FROM PUBLIC;
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM PUBLIC;
-- Allow the user to only use the specified functions.
GRANT CONNECT ON DATABASE foo TO somebody;
GRANT EXECUTE ON FUNCTION return_customers(), return_time() TO somebody;
If you have more schemas than "public" then you will need to add those to the two REVOKE ALL PRIVILEGES ON ALL ... statements.
Do not forget that the functions must have been created with SECURITY DEFINER or this user will still be unable to execute them, as the contents of the function will be executed with the permissions of this user, instead of the user who created the function.
See:
CREATE FUNCTION particularly SECURITY DEFINER
GRANT both for adding users to roles and for assigning access rights to tables, sequences, etcREVOKECREATE ROLE
103
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
