Next, via command line, navigate to the BigSQL or PostgreSQL directory where pgc is located run the update command (to get the latest releases), install PostgreSQL and install the latest set_user component by running the following command (Windows users don’t prefix the pgc command with ./ as shown in the below examples):
./pgc update ./pgc install pg96 ./pgc start pg96 ./pgc install setuser1-pg96
Invoke session environment variables
Navigate to the pg96 directory. And run the environment variable file.
Linux / OSX:
Add set_user to the shared_preload_libraries line to postgresql.conf using your favorite editor. There are many ways of doing this (including the ALTER SYSTEM command):
Linux / OSX:
start notepad %PGDATA%/postgresql.conf
Add this line to the end of the file:
shared_preload_libraries = 'set_user'
Save the change you made and restart PostgreSQL by navigating to the directory (bigsql or PostgreSQL) and run:
Create a database
psql -U postgres postgres postgres=# CREATE DATABASE testdb; CREATE DATABASE
Create the set_user extension
postgres=# \connect testdb; You are now connected to database "testdb" as user "postgres". testdb=# CREATE EXTENSION set_user; CREATE EXTENSION testdb=# \dx List of installed extensions Name | Version | Schema | Description ----------+---------+------------+-------------------------------------------- plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language set_user | 1.4 | public | similar to SET ROLE but with added logging
Create a new user
Create user with a password:
testdb=# CREATE USER dba_user WITH PASSWORD 'passuser';
Give the new user privileges to execute set_user functions
The concept is to grant the EXECUTE privilege to the set_user() and/or set_user_u() function to otherwise unprivileged postgres users. These users can then transition to other roles, possibly escalating themselves to superuser through use of set_user_u(), when needed to perform specific actions.
The set_user function is used when switching to non-superuser roles (to be discussed in a later post).
Grant dba_user priviliges to execute the set_user and set_user_u functions:
testdb=# GRANT EXECUTE ON FUNCTION set_user(text) TO dba_user; testdb=# GRANT EXECUTE ON FUNCTION set_user_u(text) TO dba_user;
Exit out of this psql session:
Get me outta here: How to drop a user with set_user privileges
If you try to drop user before revoking the set_user EXECUTE privileges, you will get this error:
testdb=# drop user dba_user; ERROR: role "dba_user" cannot be dropped because some objects depend on it DETAIL: privileges for function set_user_u(text) privileges for function set_user(text)
So, first you will need to revoke the EXECUTE ON FUNCTION you GRANTED during the installation and setup.
testdb=# REVOKE EXECUTE ON FUNCTION set_user(text) FROM dba_user; REVOKE testdb=# REVOKE EXECUTE ON FUNCTION set_user_u(text) FROM dba_user; REVOKE
Now, you can successfully drop the user:
testdb=# drop user dba_user; DROP ROLE
In our next post, we will dig deeper into the set_user_u function and how and when to use it.