Skip to main content
Share Your Experience: Take the 2024 Developer Survey
26 votes
Accepted

PostgreSQL, display role members

To get all members of all roles: SELECT r.rolname as username,r1.rolname as "role" FROM pg_catalog.pg_roles r JOIN pg_catalog.pg_auth_members m ON (m.member = r.oid) JOIN pg_roles r1 ON (m.roleid=r1....
Daniel Vérité's user avatar
22 votes

PostgreSQL - Who or what is the "PUBLIC" role?

While this is true it is not the full picture. Public also acts as an implicit role that other roles belong to and that has its own permissions not always reflected and reported that get inherited. ...
Ruan Malan N's user avatar
21 votes
Accepted

Postgres get function owner

There is a simple and a complicated way. The complicated way is to join with pg_roles and get the user name from there. But since PostgreSQL hackers don't want to type more than necessary, they came ...
Laurenz Albe's user avatar
14 votes
Accepted

How to prevent sysadmin server role from executing a stored procedure

Members of the sysadmin role cannot be denied permission to anything in SQL Server. The documentation for DENY (Transact-SQL) states: ...except that DENY does not apply to object owners or members ...
Tony Hinkle's user avatar
  • 7,954
12 votes

How to view the query of another session in pg_stat_activity without being super user?

Starting with PostgreSQL 10 you can grant the role pg_read_all_stats in order to achieve the desired result.
Radu Dumbrăveanu's user avatar
11 votes

Obvious reason Postgres Users can't read a table?

Connect to the respective database by \c DATABASE_NAME It should have the Connect permission: GRANT CONNECT ON DATABASE DATABASE_NAME TO USER_NAME; Grant the permission to use the schema GRANT USAGE ...
Sandy's user avatar
  • 227
10 votes

How to get user-specific settings in PostgreSQL?

Unfortunately pg_shadow does not show roles with no LOGIN privilege. I find it more useful to use @dezso hint view in the following query: SELECT coalesce(role.rolname, 'database wide') as role, ...
Zorg's user avatar
  • 301
10 votes
Accepted

Purpose of SQL Server Application Roles

Application roles do not eliminate the need to supply initial connection credentials. One must first successfully authenticate to the server/database using a login (SQL or Windows account/group), or ...
Dan Guzman's user avatar
  • 28.3k
10 votes
Accepted

Grant deny permission stacking

This is documented behavior provided for backwards compatibility. Documentation excerpt: Caution A table-level DENY does not take precedence over a column-level GRANT. This inconsistency in the ...
Dan Guzman's user avatar
  • 28.3k
9 votes

SQL Server database level roles for creating tables

If you want to create a role with specific rights, you could do this: CREATE ROLE CreateObjects GRANT CREATE TABLE TO CreateObjects GRANT CREATE VIEW TO CreateObjects GRANT CREATE FUNCTION TO ...
Randi Vertongen's user avatar
8 votes

How do I detect execute permission granted to a role (when no ON clause was used)

GRANT EXECUTE TO [principal] is simply a shortcut for GRANT EXECUTE ON DATABASE::<dbname> TO [principal]; You can check this using the following: SELECT dp.name , perms.class_desc , ...
Hannah Vernon's user avatar
  • 70.1k
8 votes
Accepted

What is the equivalent of SQL Server's db_datareader/db_datawriter in PostgreSQL?

There is no builtin role like that in PostgreSQL. However, you can set up one: CREATE ROLE db_datawriter; After this, you have to assign write privileges (usually INSERT, UPDATE, DELETE, maybe ...
András Váczi's user avatar
8 votes
Accepted

How to GRANT permission to execute stored procedure?

in Excel when they go to Data -> Get Data -> From Database -> From SQL Server Database That's PowerQuery. Just ran through that on Excel, and stored procedures don't appear, but Table-Valued ...
David Browne - Microsoft's user avatar
8 votes

How to force a sysadmin to store data in schema other than dbo?

The only way for a member of the sysadmin fixed server role to use a non-dbo schema is to explicitly qualify their object names with the schema name: CREATE TABLE MySchema.TableName (... instead of: ...
Solomon Rutzky's user avatar
7 votes
Accepted

Problem granting user privileges via roles in Oracle 12c

You did not mention it, but this is quite typical when using PL/SQL. Privileges granted through roles are disabled for named PL/SQL blocks that are defined to execute with definer's rights. How ...
Balazs Papp's user avatar
  • 40.7k
7 votes

Automatically invoke `SET ROLE` when connecting to PostgreSQL

As answered by @phemmer here you can use set command like this: ALTER ROLE child_role SET ROLE parent_role; This way, child_role change automatically to parent_role at login. Provided that child_role ...
Mika's user avatar
  • 351
7 votes

Why public is not mentioned as a fixed role in system database in SQL Server?

Public is just an odd duck. But to apply some retroactive continuity to the documentation you might say that all these built in roles are "fixed" or unchangeable in ways that normal roles are not. ...
David Browne - Microsoft's user avatar
7 votes
Accepted

Is it safe to delete the default "postgres" role?

I'm trying to understand if it's okay for me drop the "postgres" role, or at the least, remove it as the superuser. Dropping the role owning the template databases and all the system tables of all ...
Daniel Vérité's user avatar
6 votes

How to get all roles that a user is a member of (including inherited roles)?

Here is my take on it. It works for one specific user or all users. select a.oid as user_role_id , a.rolname as user_role_name , b.roleid as other_role_id , c.rolname as other_role_name from pg_roles ...
Alexis.Rolland's user avatar
6 votes
Accepted

Change owner of all schema objects

to avoid mentionned error, try changing the order: CREATE OR REPLACE FUNCTION public.chown(in_schema character varying, new_owner character varying) RETURNS void LANGUAGE plpgsql AS $function$ ...
Vao Tsun's user avatar
  • 1,213
6 votes
Accepted

Can I GRANT SELECT on all schemas in a database?

You can wait for PostgsqlSQL v14 and its pg_read_all_data system role. Other than that, you will have to grant USAGE on all schemas and SELECT on all tables individually. GRANT SELECT ON ALL TABLES IN ...
Laurenz Albe's user avatar
5 votes

Difference between Owned Schemas and Role Membership for Microsoft SQL Server

Well.. this is a very old thread but still 2 cents from me for those who will still visit here. This link has the best answer for the above query. https://learn.microsoft.com/en-us/dotnet/framework/...
KGB's user avatar
  • 61
5 votes

Granting Select access to all tables within a specific Database in MS SQL

To grant SELECT permissions on the whole database use this: USE <MY_DATABASE> GRANT SELECT ON DATABASE :: <MY_DATABASE> TO <MY_USER> where <MY_USER> is user <MY_DATABASE&...
Peter Trcka's user avatar
5 votes

Granting Select access to all tables within a specific Database in MS SQL

You can use the built-in database role db_datareader EXEC sp_addrolemember N'db_datareader', N'<MYUSER>' or you can create your own database role USE <MYDB> CREATE ROLE [<MYROLE>] ...
Henrik Høyer's user avatar
5 votes

Object Level Security

Look into using Schemas. Each User can have (and own) their own Schema for their objects that they have full control over. Then you can grant SELECT access for them to other Schemas. When doing this, ...
Solomon Rutzky's user avatar
5 votes
Accepted

Why public is not mentioned as a fixed role in system database in SQL Server?

One reason that I can think of why public is NOT a fixed role - is_fixed_role is that the permissions can be changed. From BOL: The permissions that are granted to the fixed server roles (except ...
Kin Shah's user avatar
  • 62.3k
5 votes

How can I determine which permissions needs to be assigned to the newly created login?

The link by @Ross will definitely help you determine syntax and what all of your options are, but for a little more guidance: There are two database roles that will probably be useful to you, they ...
Jonathan Fite's user avatar
5 votes

Sql Server roles

I am not sure what your question is. It seems you or the one who has asked you question are a bit confused between these terminologies. Server-Level Roles SQL Server provides server-level roles to ...
Learning_DBAdmin's user avatar
5 votes

Granting a user a permission to restore a database

Yes, this is actually fairly easy to accomplish using Module Signing. With module signing, you create a module (in this case, a stored procedure) and grant the required permission to the module, not ...
Solomon Rutzky's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible