Show users grant postgresql

How to Check Postgres Privileges for User?

This quick tutorial will show you how to get privileged information about a user available on the server.

Types of Privileges

Before we proceed to query PostgreSQL for information about the users, let us highlight the various permissions and what they allow the user assigned them to do.

The types of privileges in the PostgreSQL DBMS are:

• SELECT – The select privilege allows a user to select values from any columns of any table-like object. Table-like objects in PostgreSQL include a table, a view, materialized view, etc.
• INSERT – The insert permission allows the user to insert new rows into a table. You can also grant the insert privilege on a specific column allowing insert rows into only the set columns.
• UPDATE – This privilege type enables the set-user to update rows in any columns in a table or view. Similar to the insert permission, you can set it on a specific column to allow the user to update rows of a specific column(s) only.
• DELETE – This will allow a user to drop/delete a row from any modifiable table-like object. This requires that the user have the SELECT permission since it needs to reference table columns to verify the rows to be dropped.
• CONNECT – The connect permission allows a user to connect to the server. This type of permission is checked on connection startup by the pg_hba.conf file.
• CREATE – The create privilege enables a user to create either a new schema, a table in a set database. It can also allow a user to install extensions on a database. If this permission is revoked on a user, it does not remove all the existing objects until the termination point.
• TRUNCATE – as the name suggests, it grants the user permission to truncate a table.
• TRIGGER – This enables a user to create a trigger on table-like objects.
• TEMPORARY – Allows users to create a temporary table while connected to a set database.
• EXECUTE – execute permission enables a user to call functions or procedures. This is the only type of permission that can apply to functions/procedures.
• REFERENCES – Allows a user to create foreign key constraints that reference a table or columns.

How to Show User Privileges

Listing user privileges is simple. In psql, use the query \du+ as shown in the output below:

The above output shows the Postgres and temp users with their corresponding permissions.

Another way to do this is to use the information_schema schema and query the table_privileges table as:

The above query will show detailed information about user privileges on databases as well as tables.

To filter for a specific user, you can add the WHERE clause:

Conclusion

This short tutorial has discussed ways to fetch privileged information about the users in a PostgreSQL server.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list

Источник

How To Use Roles and Manage Grant Permissions in PostgreSQL on a VPS

Introduction

PostgreSQL, or Postgres, is an open-source relational database management system. As with other relational databases, PostgreSQL stores data in tables made up of rows and columns. Users can define, manipulate, control, and query data using Structured Query Language, more commonly known as SQL. PostgreSQL is a powerful tool that can be used to manage application and web data on a Virtual Private Server.

This guide will demonstrate how to properly manage privileges and grant user permissions. This will allow you to provide your applications the privileges necessary without affecting separate databases.

Prerequisites

To follow along with this tutorial, you will need:

One Ubuntu 22.04 server that has been configured by following our Initial Server Setup for Ubuntu 22.04 guide. After completing this prerequisite tutorial, your server should have a non-root user with sudo permissions and a basic firewall.

To complete Step 1 of our How To Install and Use PostgreSQL on Ubuntu 22.04 tutorial to have Postgres installed on your server.

With your environment prepared and Postgres running on your server, you can begin learning about how Postgres handles permissions.

Viewing Roles and Permissions in PostgreSQL

Postgres manages permissions through the concept of roles. Roles are different from traditional Unix-style permissions in that there is no distinction between users and groups. Roles can be manipulated to resemble both of these conventions, but they are also more flexible. Upon installation, Postgres is set up to use peer authentication, meaning that it associates Postgres roles with a matching Unix/Linux system account. If a role exists within Postgres, a Unix/Linux username with the same name is able to sign in as that role.

The installation procedure created a user account called postgres that is associated with the default Postgres role. In order to use Postgres, you can log into that account.

First, make sure your server is running by using the systemctl start command:

Then, you can switch to the postgres account by typing:

You can now access the PostgreSQL prompt immediately by typing:

To list the roles in your Postgres instance, type the following command:

Currently, there is only one default role with many powerful privileges.

Creating Roles in PostgreSQL

There are a number of different ways to create roles for Postgres. It is possible to create roles from within Postgres, or from the command line.

Creating Roles From Within PostgreSQL

One way of creating a new role is from within the Postgres prompt interface. The following is the syntax for creating a new role within the Postgres prompt interface:

To demonstrate this, create a new role called demo_role:

Check the defined users again:

Your output will reveal two users.

Creating Roles from the Command Line

An alternative method of creating roles is using the createuser command from the command line.

First, exit out of the PostgreSQL command prompt for a moment by typing:

Then, log into the postgres account:

You can create new roles from the command line with the createuser command. Using the —interactive flag will prompt you for the name of the new role and also ask whether it should have superuser permissions.

Logged in as the postgres account, you can create a new user by typing:

The script will prompt you with some choices and, based on your responses, execute the correct Postgres commands to your specifications:

By answering n for no to all of these prompts, you will create a user similar to the previous user.

Log back into your psql Postgres prompt:

Then execute the du command to reveal the differences between the two new roles. This command starts with \ because it is a psql specific meta-command that is processed by psql itself and not by PostgreSQL:

Notice that the user created from the command line does not have Cannot login listed as an attribute.

Deleting Roles In PostgreSQL

You can delete a role using the following syntax:

To demonstrate, delete the demo_role role by typing:

If you issue the command on a non-existent user, you will receive an error message:

To avoid this situation and make the drop command delete a user if present, and quietly do nothing if the user does not exist, use the following syntax:

With this option specified, the command will complete successfully regardless of the validity of the role. Trying to remove the demo_role with the above commands will result in this:

The role is now deleted.

Defining Privileges Upon Role Creation

Now, you are ready to recreate the demo_role with altered permissions. You can do this by specifying the permissions you want after the main create clause like this:

To see the full list of the options, type:

You can give the demo_role user the ability to log in by typing:

Checking the attributes with the \du command, the two users now have identical privileges:

You can get to this state without specifying the LOGIN attribute with every role creation. By using the following CREATE USER command, it automatically gives the role login privileges:

The role is created with privilege automatically granted.

Changing Privileges of Roles in PostgreSQL

To change the attributes of an already created role, use the ALTER ROLE command. The syntax for this command is:

This command allows you to define privilege changes without having to delete and recreate users as demonstrated earlier. For instance, you can change demo_role back to its previous state of Cannot login by issuing this command:

You can confirm the change with the \du command:

To change it back to a role with login access, use the following command:

Now the role has been reverted.

Logging In as a Different User in PostgreSQL

By default, users are only allowed to login locally if the system username matches the PostgreSQL username. You can alter this by either changing the login type, or by specifying that PostgreSQL should use the loopback network interface. This changes the connection type to remote even though it is actually a local connection.

First, create a password for the user you want to connect with, so that it can authenticate. You can try this with the test_user you created earlier by giving it a password:

You will be prompted to enter and confirm a password. Now, exit the PostgreSQL interface and exit back to your normal user with this command:

PostgreSQL assumes that when you log in, you will be using a username that matches your operating system username, and that you will be connecting to a database with the same name.

To explicitly specify the options you want to use, use the following syntax with your parameters:

Here’s a brief breakdown of each item in the command:

• The user_name should be replaced with the username you want to connect with.
• The database_name should be the name of an existing database that you have access to.
• The -h 127.0.0.1 section is the part that specifies that you will be connecting to the local machine, but through a network interface, which allows you to authenticate even though your system username does not match.
• The -W flag tells PostgreSQL that you will be entering a password.

To log in with your test_user, issue the following command:

You will need to enter a password after this command.

In this example, you use the database postgres. This is the default database set up during the installation. If you attempt to perform some actions in this session, you will see that you don’t have the ability to do many things. This is because test_user has not been granted administrative permissions.

Exit the current session:

Then get back into the administrative postgres session:

Next you’ll be granting permissions.

Granting Permissions in PostgreSQL

When a database or table is created, usually only the role that created it, not including the roles with superuser status, has permission to modify it. This behavior can be altered by granting permissions to other roles.

You can grant permissions using the GRANT command with this general syntax:

You can create a table to practice these concepts with the following commands:

To view the table you created, enter this command:

Notice that there is one table type and one sequence type. The sequence is generated for you when you used the id serial command in your table creation. This generates an auto-incrementing integer.

You can now grant some privileges to the new demo table to the demo_role. To do so, give the demo_role user UPDATE privileges with the following command:

You can grant full permissions to a user by substituting the permission type with the word ALL . Grant this permission to the test_user with this command:

If you want to specify permissions for every user on the system, you can use PUBLIC instead of a specific user:

To view the grant table, use the following command:

This reveals all the grant permissions that have been assigned.

Removing Permissions in PostgreSQL

You can remove permissions by using the REVOKE command. The REVOKE command uses almost the same syntax as grant:

You can use the same shorthand words, ALL and PUBLIC , in the command as well:

The permissions you set before have now been revoked.

Using Group Roles in PostgreSQL

Roles are flexible enough to allow grouping of other roles to allow for widespread permissions control. For instance, you can create a new role called temporary_users and then add demo_role and test_user to that group.

First create the new role that will be used as a group:

Then assign the users to the newly created temporary_users group:

Now these two users can have their permissions managed by manipulating the temporary_users group role instead of managing each member individually.

You can view the role membership information by typing:

Any member of a group role can act as the group role they are a member of by using the SET ROLE command. Since the postgres user you are logged in as currently has superuser privileges, you can use SET ROLE command even though it’s not a member of the temporary_users group:

Now, any tables that are created are owned by the temporary_users role:

Now, check the table ownership by issuing this command:

The new table, and the sequence associated with the serial data type, is owned by the temporary_users role.

To get back to the original role permissions, enter the following command:

If you give a user the INHERIT property with the ALTER ROLE command, that user will automatically have all the privileges of the roles they belong to without using the SET ROLE command:

Now test_user will have every permission of the roles it is a member of. You can remove a group role, or any role, with the DROP ROLE command. You can test this with the temporary_users group by typing the following command:

This outputs an error because the hello table is owned by temporary_users. You can solve this problem by transferring ownership to a different role:

You can check if temporary_users no longer owns any of the tables with the following:

You can now drop the temporary_users role successfully by issuing this command:

This will destroy the temporary_users role. The former members of temporary_users are not removed.

Conclusion

You now have the basic skills necessary to administer your PostgreSQL database permissions. It is important to know how to manage permissions so that your applications can access the databases they need, while not disrupting data used by other applications.

If you’d like to learn more about Postgres and how to use it, we encourage you to check out the following guides:

Want to learn more? Join the DigitalOcean Community!

Join our DigitalOcean community of over a million developers for free! Get help and share knowledge in our Questions & Answers section, find tutorials and tools that will help you grow as a developer and scale your project or business, and subscribe to topics of interest.

Источник